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"
}
}
}
{
"_id": ObjectId("1")
"attribute_a": "abcd"
}
{
"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 MongoDB | Field name in SQL databases |
---|---|
createdAt | created_at |
updatedAt | updated_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:
- one-way
- one-to-one
- one-to-many
- many-to-one
- many-to-many
- many-way
在 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
}
}
// model A
{
"_id": ObjectId("1"),
"one_way": ObjectId("1")
}
// model B
{
"_id": ObjectId("1")
}
// model A
{
"id": 1,
"one_way": 1
}
// model B
{
"id": 1
}
在 MongoDB 中,oneToOne
关系的 id 同时存在于 2 个文档中,并使用 model.settings.json
文件中定义的名称。
¥In MongoDB, the id of a oneToOne
relation is in the 2 documents at the same time, and uses the names defined in the model.settings.json
file.
在 SQL 数据库中,oneToOne
关系的 id 也同时在 2 个表中,并使用 model.settings.json
文件中定义的名称。
¥In SQL databases, the id of a oneToOne
relation is also in the 2 tables at the same time, and uses the names defined in the model.settings.json
file.
模块:
¥Models:
// model A
{
"attributes": {
"one_to_one": {
"model": "B",
"via": "one_to_one_via"
}
}
}
// model B
{
"attributes": {
"one_to_one_via": {
"model": "A",
"via": "one_to_one"
}
}
}
// model A
{
"_id": ObjectId("1")
"one_to_one": ObjectId("1")
}
// model B
{
"_id": ObjectId("1")
"one_to_one_via": ObjectId("1")
}
// model A
{
"id": 1
"one_to_one": 1
}
// model B
{
"id": 1
"one_to_one_via": 1
}
oneToMany
关系存储在 MongoDB 和 SQL 数据库中关系的相反一侧。
¥A oneToMany
relation is stored on the opposite side of the relation in both MongoDB and SQL databases.
模块:
¥Models:
// model A
{
"attributes": {
"one_to_many": {
"collection": "B",
"via": "many_to_one"
}
}
}
// model B
{
"attributes": {
"many_to_one": {
"model": "A",
"via": "one_to_many"
}
}
}
// model A
{
"_id": ObjectId("1")
}
// model B
{
"_id": ObjectId("1"),
"many_to_one": ObjectId("1")
}
// model A
{
"id": 1
}
// model B
{
"id": 1,
"many_to_one": 1
}
manyToOne
关系是 oneToMany
关系的逆关系。
¥A manyToOne
relation is the inverse of a oneToMany
relation.
在 MongoDB 和 SQL 数据库中,关系都存储在主模型中。
¥In both MongoDB and SQL databases, the relation is stored in the main model.
模块:
¥Models:
// model A
{
"attributes": {
"many_to_one": {
"model": "B",
"via": "one_to_many"
}
}
}
// model B
{
"attributes": {
"one_to_many": {
"collection": "A",
"via": "many_to_one"
}
}
}
// model A
{
"_id": ObjectId("1"),
"many_to_one": ObjectId("1")
}
// model B
{
"_id": ObjectId("1")
}
// model A
{
"id": 1,
"many_to_one": 1
}
// model B
{
"id": 1
}
在 MongoDB 中,manyToMany
关系的 id 存储在 dominant
属性为 true
一侧的数组中。
¥In MongoDB, the ids of a manyToMany
relation are stored in an array in the side where the dominant
property is true
.
在 SQL 数据库中,manyToMany
关系存储在连接表中,其名称遵循特定的命名约定(请参阅 SQL 连接表名)。
¥In SQL databases, a manyToMany
relation is stored in a join table whose name follows a specific naming convention (see SQL join table names).
模块:
¥Models:
// model A
{
"attributes": {
"many_to_many": {
"collection": "B",
"via": "many_to_many_rev",
"dominant": true
}
}
}
// model B
{
"attributes": {
"many_to_many_rev": {
"via": "many_to_many",
"collection": "kitchensink"
}
}
}
// model A
{
"_id": ObjectId("1"),
"many_to_many": [
ObjectId("1")
]
}
// model B
{
"_id": ObjectId("1")
}
// model A
{
"id": 1,
}
// model B
{
"id": 1
}
// link_table
{
"id": 1,
"a_id": 1,
"b_id": 1
}
manyWay
关系是 manyToMany
关系的简化版本。
¥A manyWay
relation is a simplified version of a manyToMany
relation.
数据存储在模型中,其中关系在 MongoDB 和 SQL 数据库的连接表中声明。
¥The data is stored in the model, where the relation is declared in MongoDB and in a join table in SQL databases.
在 SQL 数据库中,关系存储在连接表中,其名称遵循特定的命名约定(请参阅 SQL 连接表名)。
¥In SQL databases, the relation is stored in a join table whose name follows a specific naming convention (see SQL join table names).
模块:
¥Models:
// model A
{
"attributes": {
"many_way": {
"collection": "test-a"
}
}
}
// model B
{
"attributes": {
// no attributes on the other side
}
}
// model A
{
"_id": ObjectId("1"),
"many_way": [
ObjectId("1")
]
}
// model B
{
"_id": ObjectId("1")
}
// model A
{
"id": 1,
}
// model B
{
"id": 1
}
// a__many_way
{
"id": 1,
"a_id": 1,
"b_id": 1
}
SQL 连接表名
¥SQL join table names
manyToMany
和 manyWay
关系中使用的 SQL 连接表的名称是根据 collectionName
属性、关系的 attributes
和 relation
的类型生成的:
¥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 componentkind
针对特定集合¥
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 mediakind
针对特定集合¥
kind
targets a specific collectionfield
针对特定属性¥
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
localizations
是 manyWay
关系(参见 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 的 查询引擎 迁移自定义查询。 |