Skip to main content

Strapi v3 中的 MongoDB 与 SQL 实现差异

¥MongoDB vs. SQL implementation differences in Strapi v3

本文档解释了在 Strapi v3 项目上下文中将数据从 MongoDB 迁移到 SQL 时要考虑的关键结构差异。本地迁移数据时应作为参考(参见 Strapi v3 中的 MongoDB 到 SQL 迁移)。

¥This documentation explains the key structural differences to take into account when migrating data from MongoDB to SQL in the context of a Strapi v3 project. It should be used as a reference when migrating data locally (see MongoDB to SQL migration in Strapi v3).

模型设置

¥Model settings

用于在 Strapi v3 中定义模型的 model.settings.json 文件包含 MongoDB 和 SQL 数据库以不同方式处理的参数。

¥The model.settings.json files, used to define models in Strapi v3, include parameters that are handled differently by MongoDB and SQL databases.

命名约定

¥Naming conventions

表/集合名称

¥Table/collection names

SQL 数据库中的表相当于 MongoDB 中的集合,都是使用 model.settings.json 文件的 collectionName 选项中定义的名称创建的。

¥Table in SQL databases, equivalent to collection in MongoDB, are created with the name defined in the collectionName option of the model.settings.json file.

从 MongoDB 切换到 SQL 时,每个 MongoDB 集合都会创建一个 SQL 表,并会为 relations 创建新的 SQL 表。

¥When switching from MongoDB to SQL, one SQL table is created per MongoDB collection, and new SQL tables are created for relations.

列/字段名称

¥Column/field names

SQL 中的列相当于 MongoDB 中的字段,是使用 model.setting.json 文件的 attributes 选项中定义的名称创建的。

¥Columns in SQL, equivalent to fields in MongoDB, are created with the names defined in the attributes option of the model.setting.json file.

model.settings.json 中定义的示例 attribute_a 将按如下方式存储在 MongoDB 和 SQL 数据库中:

¥An example attribute_a defined in model.settings.json would be stored like the following in MongoDB and SQL databases:

// model.settings.json
{
"attributes": {
"attribute_a": {
"type": "string"
}
}
}
MongoDB
{
"_id": ObjectId("1")
"attribute_a": "abcd"
}
SQL
{
"id": 1
"attribute_a": "abcd"
}

时间戳

¥Timestamps

如果在 model.settings.js 文件中定义了 timestamps 选项,则无需迁移,MongoDB 和 SQL 数据库中的属性将相同。

¥If the timestamps option is defined in the model.settings.js file, no migration is required, the properties will be the same in MongoDB and SQL databases.

如果未设置 timestamps 选项,则应迁移默认值,在 SQL 数据库中使用小写蛇形写法:

¥If no timestamps option is set, the defaults should be migrated, using lower snake case in SQL databases:

Field name in MongoDBField name in SQL databases
createdAtcreated_at
updatedAtupdated_at

关系

¥Relations

✏️ 注意

关系的自定义列名称不能在 MongoDB 和 SQL 数据库中使用。这种情况不需要特定的迁移,并且可以将自定义列名称视为未使用它们。

¥Custom column names for relations can't be used in both MongoDB and SQL databases. No specific migrations are needed for this case and custom column names can be considered as if they were not used.

在 Strapi 中,模型之间的关系在 model.settings.json 文件的 attributes 部分中定义。

¥In Strapi, relations between models are defined in the attributes section of the model.settings.json files.

以下部分解释了如何在模型属性中声明每种类型的关系,并给出了如何在 MongoDB 和 SQL 数据库中反映模型属性的示例:

¥The following section explains how each type of relation is declared in the model attributes and gives an example of how the model attributes are reflected in the MongoDB and SQL databases:

在 MongoDB 中,oneWay 关系的 id 位于文档中,并以 model.settings.json 文件中的属性命名。

¥In MongoDB, the id of a oneWay relation is in the document and is named after the property in the model.settings.json file.

在 SQL 数据库中,oneWay 关系是行中的列,并以 model.settings.json 文件中的属性命名。

¥In SQL databases, the oneWay relation is a column in the row and is named after the property in the model.settings.json file.

模块:

¥Models:

// model A
{
"attributes": {
"one_way": {
"model": "test-a"
}
}
}

// model B
{
"attributes": {
// no attributes on the other side
}
}
MongoDB
// model A
{
"_id": ObjectId("1"),
"one_way": ObjectId("1")
}

// model B
{
"_id": ObjectId("1")
}
SQL
// model A
{
"id": 1,
"one_way": 1
}

// model B
{
"id": 1
}

SQL 连接表名

¥SQL join table names

manyToManymanyWay 关系中使用的 SQL 连接表的名称是根据 collectionName 属性、关系的 attributesrelation 的类型生成的:

¥The name for the SQL join table used in manyToMany and manyWay relations is generated based on the collectionName property, the attributes of the relation, and the type of the relation:

  • manyToMany 关系的连接表遵循以下命名模式:{}_{}

    ¥manyToMany relations have the join table follow this naming pattern: {}_{}

  • manyWay 关系的连接表遵循以下命名模式:{collectionName}__${snakeCase(attributeName)},如下例所示:

    ¥manyWay relations have the join table follow this naming pattern: {collectionName}__${snakeCase(attributeName)}, like in the following example:

    // With the following model A:
    {
    "collectionName": "table_a",
    "attributes": {
    "myManyWay": {
    // ...
    }
    }
    }

    // The SQL join table name will be:
    "table_a__my_many_way"

组件和动态区域

¥Components & Dynamic zones

在 MongoDB 和 SQL 数据库中,组件都有自己的集合并链接到其父级。

¥In both MongoDB and SQL databases, components have their own collection and are links to their parent.

在 MongoDB 中,链接是通过存储在父级中的对象数组完成的。即使是不可重复的 components 也会列在数组中。该数组中的每个对象都有 2 个属性:

¥In MongoDB, the links are done via an array of objects stored in the parent. Even non-repeatable components are listed in an array. Each object from this array has 2 properties:

  • ref 针对特定组件

    ¥ref targets a specific component

  • kind 针对特定集合

    ¥kind targets a specific collection

在 SQL 数据库中,链接是通过 SQL 连接表完成的。表名是按照以下模式生成的:{collectionName}_components,其中 collectionName 位于父模型中。组件的 SQL 表包含以下元素:

¥In SQL databases, the links are done with a SQL join table. The table name is generated following this pattern: {collectionName}_components, where collectionName is in the parent model. SQL tables for components include the following elements:

名称类型描述
component_type柱子使用 collectionName 而不是 globalId 属性
field柱子应等于属性名称
order柱子应从 1 到 x,与 MongoDB 数组中的顺序匹配
component_id外键以组件表为目标
{singular(collectionName)}_id外键定位父表
Example of a component definition in model settings, MongoDB and SQL databases in Strapi v3

模块:

¥Models:

// model A
{
"attributes": {
"compo": {
"type": "component"
"repeatable": true|false
}
}
}

// Component
{
"attributes": {}
}

Mongo:

// model A
{
"_id": ObjectId("1"),
"compo": [
{
"_id": ObjectId("xxx"), // this id doesn't matter
"kind": "CompoGlobalId", // to be converted to collectionName before creating the join in SQL
"ref": ObjectId("1") // actual id of the component
}
]
}

// Component
{
"_id": ObjectId("1"),
}

SQL:

// model A
{
"id": 1,
}

// Component
{
"id": 1,
}

// A_components
{
"id": 1,
"field": "compo",
"order": 1,
"component_type": "compos",
"component_id": 1,
"a_id": 1
}

媒体

¥Media

媒体在 MongoDB 和 SQL 中的存储方式相同。但是,媒体和条目之间创建的链接的存储方式不同:

¥Media are stored the same way in MongoDB and in SQL. However, the links created between media and entries are stored differently:

在 MongoDB 中,媒体链接存储在关系的两侧。related 属性是一个针对媒体集合中相关条目的对象数组,称为 upload_file。每个对象有 3 个属性:

¥In MongoDB, media links are stored on both sides of the relation. The related property is an array of objects targeting the related entries in the media collection, called upload_file. Each object has 3 properties:

  • ref 针对特定媒体

    ¥ref targets a specific media

  • kind 针对特定集合

    ¥kind targets a specific collection

  • field 针对特定属性

    ¥field targets a specific attribute

MongoDB 还在条目中包含一个属性,其名称类似于模型的媒体属性,它可以是一个数组,也可以是一个针对媒体的单个 ObjectId

¥MongoDB also includes a property in the entries, named like the media attributes of the models, which is either an array or a single ObjectId targeting the media(s).

在 SQL 数据库中,会创建一个 upload_file_morph 连接表,其中包含以下元素:

¥In SQL databases, an upload_file_morph join table is created, with the following elements:

名称类型描述
upload_file_id外键瞄准媒体
related_id柱子目标条目
related_type
Example of media definition in model settings, MongoDB, and SQL databases in Strapi v3

模块:

¥Models:

// model A
{
"attributes": {
"pictures": {
"plugin": "upload",
"collection": "file", // multiple files
"via": "related",

}
}
}

// model B
{
"attributes": {
"cover": {
"plugin": "upload",
"model": "file", // single file
"via": "related",
}
}
}

MongoDB:

// model A
{
"_id": ObjectId("1"),
"pictures": [
ObjectId("1"),
]
}

// model B
{
"_id": ObjectId("1"),
"cover": ObjectId("1")
}

// upload_file
{
"_id": ObjectId("1"),
// ...
"related": [
{
"_id": ObjectId("1"), // this id doesn't matter
"kind": "GlobalIdOfA", // needs to be converted to collectionName for SQL
"ref": ObjectId("1"), // id of the A entry
"field": "pictures", // field in A to which the media is linked
},
{
"_id": ObjectId("2"), // this id doesn't matter
"kind": "GlobalIdOfB", // needs to be converted to collectionName for SQL
"ref": ObjectId("1"), // id of the B entry
"field": "cover", // field in B to which the media is linked
}
]
}

SQL:

// model A
{
"id": 1,
}

// model B
{
"_id": 1,
}

// upload_file
{
"id": 1,
}

// upload_file_morph
[
{
"id": 1, // this id doesn't matter
"upload
"related_type": "collectionNameofA", // collectionName of A
"related_id": 1, // id of the A entry
"field": "pictures", // field in A to which the media is linked
"order": 1,
},
{
"id": 2, // this id doesn't matter
"related_type": "collectionNameofB", // needs to be converted to collectionName for SQL
"related_id": 1, // id of the B entry
"field": "cover", // field in B to which the media is linked
"order": 1
}
]

标量属性

¥Scalar attributes

MongoDB 和 SQL 数据库之间的标量属性没有结构性变化。

¥There are no structural changes in the scalar attributes between MongoDB and SQL databases.

需要考虑的唯一差异如下:

¥The only differences to take into account are the following:

  • time 存储毫秒。

    ¥time stores milliseconds.

  • json 是 MongoDB 中的一个对象。如有必要,请确保在你的目标 SQL 数据库(SQLite 或 MySQL < 5.6)中对其进行字符串化。

    ¥json is an object in MongoDB. Make sure to stringify it if necessary in the SQL database you target (SQLite or MySQL < 5.6).

Strapi 创建的属性

¥Attributes created by Strapi

除了 timestamps 之外,Strapi 创建的属性在 Mongo 和 SQL 数据库中是相同的。这包括以下属性:

¥With the exception of timestamps, attributes created by Strapi are the same in Mongo and SQL databases. This includes the following attributes:

  • published_at

  • created_by

  • updated_by

  • locale

localizationsmanyWay 关系(参见 relations)。

¥localizations is a manyWay relation (see relations).

自定义用例

¥Custom use cases

下表重点介绍了一些特定用例及其可能的解决方案:

¥The following table highlights some specific uses cases and their possible resolution:

使用案例解决
自定义 ID 类型自定义 ID 类型仅在 SQL 中使用。由于 MongoDB 不支持该功能,因此无需迁移。
自定义索引自定义索引不是受支持的功能。必须在 SQL 中手动创建等效索引。
自定义连接表名称迁移 relations 时应考虑自定义联接表名称以查找正确的表名称(请参阅 SQL 连接表名)。
自定义数据库查询迁移到 v3 SQL,然后迁移到 Strapi v4,最后使用 Strapi v4 的 查询引擎 迁移自定义查询。