对于希望升级到 MariaDB 的 MySQL 用户来说是个好消息：MariaDB 10.5.7 及更高版本可以理解之前不兼容的 MySQL JSON 字段了！这篇博客文章解释了如何安装和使用它们。
介绍 MYSQL_JSON 插件
从 MariaDB 10.5.7（提交 f6549e）开始，通过加载 MYSQL_JSON 数据类型插件，可以从包含 JSON 的 MySQL 5.7 表进行升级。
MariaDB 和 MySQL 的 JSON 格式不一样。在 MySQL 中，JSON 类型是原生类型，而在 MariaDB 中，JSON 只是 LONGTEXT 的别名。这在实践中意味着 JSON 格式的数据彼此不兼容。从本博客的角度来看，哪种格式更好并不重要。本博客旨在说明如何解决这个问题，通过轻松将 MySQL JSON 字段移植到 MariaDB JSON 字段。
Monty 建议为 MariaDB 提供一个开放的机制，以便在 MariaDB 领域中理解 MySQL JSON。
为了实现这种提议的架构，我们为 10.5 版本引入了一个插件，它将负责所有繁重的工作。
在这篇博客中，我们将探讨在 MYSQL_JSON 插件出现之前的问题状态，以及 MYSQL_JSON 在使用生成的 MySQL JSON 数据的实际示例中的用法。我们将了解如何在 MySQL 中创建 JSON 数据，如何使用生成的数据以及如何手动安装 MYSQL_JSON 插件，以及如何使用配置文件加载插件。之后，我们将执行一个不可逆的操作，修改原始的 MySQL JSON 数据并将其转换为 MariaDB 特有的格式。
在 MySQL 5.7 中创建 JSON 数据
mysql> select @@version;
+--------------+
| @@version |
+--------------+
| 5.7.28 |
+--------------+
1 row in set (0,00 sec)
mysql> create table mysql_json (t json) engine=myisam;
Query OK, 0 rows affected (0,00 sec)
mysql> show create table mysql_json\G
*************************** 1. row ***************************
Table: mysql_json
Create Table: CREATE TABLE `mysql_json` (
`t` json DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0,01 sec
mysql> insert into mysql_json values
('{
"country":"Bosnia and Herzegovina",
"city":["Zenica", "Sarajevo", "Tuzla", "Mostar", "Banja Luka"],
"faculty":["Faculty of Mechanical Engineering in Zenica", "Faculty of Electrical Engineering Sarajevo", "Faculty of Electrical Engineering Tuzla"],
"team": ["Čelik", "Željezničar", "Sarajevo", "Sloboda", "Velež", "Zrinjski", "Borac"]
}');
mysql> select * from mysql_json\G
*************************** 1. row ***************************
t: {"city": ["Zenica", "Sarajevo", "Tuzla", "Mostar", "Banja Luka"], "team": ["Čelik", "Željezničar", "Sarajevo", "Sloboda", "Velež", "Zrinjski", "Borac"], "country": "Bosnia and Herzegovina", "faculty": ["Faculty of Mechanical Engineering in Zenica", "Faculty of Electrical Engineering Sarajevo", "Faculty of Electrical Engineering Tuzla"]}
1 row in set (0,00 sec)
在 MYSQL_JSON 插件出现之前读取 MySQL 5.7 JSON
在 10.5.7 之前，MariaDB 在处理 MySQL JSON 数据类型时，会在“.frm”文件中返回错误的信息，如下所示
MariaDB [test]> show create table mysql_json;
ERROR 1033 (HY000): Incorrect information in file: './test/mysql_json.frm'
MariaDB [test]> select * from mysql_json;
ERROR 1033 (HY000): Incorrect information in file: './test/mysql_json.frm'
安装 MYSQL_JSON 插件
现在，让我们使用从 MySQL 生成的数据目录（“/tmp/mysql-datadir”），并通过 CLI 和配置文件为 MYSQL_JSON 插件使用一些系统变量/命令行 (CLI) 选项来启动 MariaDB。
使用 CLI 安装插件
$ mysqld --no-defaults --datadir=/tmp/mysql-datadir --lc-messages_dir=./sql/share --plugin-dir=<path to directory containing type_mysql_json.so/dll> --plugin-maturity=alpha
Version: '10.5.7-MariaDB' socket: '/tmp/mysql.sock' port: 3306 Source distribution
由于我们将使用名为“MYSQL_JSON”的插件，其共享库“type_mysql_json.so”位于“plugin/type_mysql_json”目录下（从源代码编译时），我们应该为系统变量 plugin_dir （作为插件固有的安全特性）指定插件所在的路径。 由于我们的插件目前处于 alpha 成熟度级别，我们必须将 MariaDB plugin_maturity 服务器系统变量至少设置为此级别才能安装我们的插件。在上面的例子中，我们也可以使用 plugin_load_add mysqld 选项指定要加载插件库中的哪个插件（如下面使用配置文件所示），但在此示例中，我们将手动进行（使用 INSTALL PLUGIN 语句）。
让我们在测试数据上启动 mysql 客户端，看看我们的 mysql 表中发生了什么
MariaDB [(none)]> select @@version;
+----------------------+
| @@version |
+----------------------+
| 10.5.7-MariaDB |
+----------------------+
1 row in set (0.000 sec)
MariaDB [(none)]> select @@plugin_dir;
+--------------------------------------------------------+
| @@plugin_dir |
+--------------------------------------------------------+
|<build_dir>/plugin/type_mysql_json/ |
+--------------------------------------------------------+
1 row in set (0.000 sec)
MariaDB [(none)]> select @@plugin_maturity;
+-------------------+
| @@plugin_maturity |
+-------------------+
| alpha |
+-------------------+
1 row in set (0.000 sec)
MariaDB [test]> show create table mysql_json;
ERROR 4161 (HY000): Unknown data type: 'MYSQL_JSON'
MariaDB [test]> select * from mysql_json;
ERROR 4161 (HY000): Unknown data type: 'MYSQL_JSON'
MariaDB [test]> select * from information_schema.plugins where plugin_name="mysql_json";
Empty set (0.002 sec)
MariaDB [test]> select * from mysql.plugin;
Empty set (0.001 sec)
在带有 JSON 列的表上运行 show create table 或 select 语句会导致“Unknown data type: MYSQL_JSON”错误消息。
我们知道，原因在于缺少插件。
查看 information_schema (IS) 的 plugins 表或执行 show plugins 语句时，不会显示任何插件。现在，让我们安装并验证我们的插件（使用 information_schema.plugins 或 show plugins 语句）。由于我们是使用 INSTALL SONAME 语句手动安装插件，插件的验证也会在 mysql.plugin 表中可见，而通过配置文件加载插件时则不会。
MariaDB [(none)]> install soname 'type_mysql_json';
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> select * from information_schema.plugins where plugin_name="mysql_json"\G
*************************** 1. row ***************************
PLUGIN_NAME: MYSQL_JSON
PLUGIN_VERSION: 0.1
PLUGIN_STATUS: ACTIVE
PLUGIN_TYPE: DATA TYPE
PLUGIN_TYPE_VERSION: 100507.0
PLUGIN_LIBRARY: type_mysql_json.so
PLUGIN_LIBRARY_VERSION: 1.14
PLUGIN_AUTHOR: Anel Husaković, Vicențiu Ciorbaru
PLUGIN_DESCRIPTION: Data type MYSQL_JSON
PLUGIN_LICENSE: GPL
LOAD_OPTION: ON
PLUGIN_MATURITY: Alpha
PLUGIN_AUTH_VERSION: 0.1
1 row in set (0.002 sec)
MariaDB [test]> select * from mysql.plugin;
+------------+--------------------+
| name | dl |
+------------+--------------------+
| MYSQL_JSON | type_mysql_json.so |
+------------+--------------------+
1 row in set (0.001 sec)
使用配置文件加载插件
为了跳过上述手动配置插件的步骤，可以在配置文件中使用以下内容在服务器启动时加载插件。请注意，在这种情况下，mysql.plugin 将是空的
plugin_dir=<path to directory containing type_mysql_json.so/dll>
plugin_load_add=mysql_json=type_mysql_json
plugin_maturity=alpha使用插件时获得的描述性错误
现在，如果我们尝试执行 show create table 或 select 该表，我们会得到一条有意义的消息，两种情况下的消息相同
MariaDB [test]> show create table mysql_json;
ERROR 1707 (HY000): Table rebuild required. Please do "ALTER TABLE `test.mysql_json` FORCE" or dump/reload to fix it!
MariaDB [test]> select * from mysql_json;
ERROR 1707 (HY000): Table rebuild required. Please do "ALTER TABLE `test.mysql_json` FORCE" or dump/reload to fix it!
错误消息显示了两种可能的修复方法
– 使用带有 FORCE 的 ALTER TABLE 进行修复
– 或使用 mysqldump 进行修复。
调用 mysqlcheck 时也会出现相同的消息
$ mysqlcheck -c test mysql_json
Failed to SHOW CREATE TABLE `mysql_json`
Error: Table rebuild required. Please do "ALTER TABLE `test.mysql_json` FORCE" or dump/reload to fix it
修复错误的方法
ALTER TABLE FORCE
让我们按照错误消息的建议进行操作
MariaDB [test]> ALTER TABLE test.mysql_json FORCE;
Query OK, 1 row affected (0.011 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [test]> show create table mysql_json\G
*************************** 1. row ***************************
Table: mysql_json
Create Table: CREATE TABLE `mysql_json` (
`t` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.000 sec)
MariaDB [test]> select * from mysql_json\G
*************************** 1. row ***************************
t: {"city": ["Zenica", "Sarajevo", "Tuzla", "Mostar", "Banja Luka"], "team": ["Čelik", "Željezničar", "Sarajevo", "Sloboda", "Velež", "Zrinjski", "Borac"], "country": "Bosnia and Herzegovina", "faculty": ["Faculty of Mechanical Engineering in Zenica", "Faculty of Electrical Engineering Sarajevo", "Faculty of Electrical Engineering Tuzla"]}
1 row in set (0.000 sec)
可以看到，来自 MySQL 的数据已成功转换为 MariaDB，并且客户端能够读取有效数据。
MySQL mysqldump
启动 MySQL 服务器并调用 mysqldump 客户端
$ mysqldump test>mysql_json_dump.sql使用空的数据目录启动 MariaDB 服务器并启动 mysql 客户端
MariaDB [test]> show tables;
Empty set (0.000 sec)
在 MariaDB 中恢复原始 MySQL 数据
$ mysql test<mysql_json_dump.sql 验证
$ mysql test -e "show create table mysql_json\G select * from mysql_json\G"
*************************** 1. row ***************************
Table: mysql_json
Create Table: CREATE TABLE `mysql_json` (
`t` json /* MySQL 5.7 */ CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
*************************** 1. row ***************************
t: {"city": ["Zenica", "Sarajevo", "Tuzla", "Mostar", "Banja Luka"], "team": ["Čelik", "Željezničar", "Sarajevo", "Sloboda", "Velež", "Zrinjski", "Borac"], "country": "Bosnia and Herzegovina", "faculty": ["Faculty of Mechanical Engineering in Zenica", "Faculty of Electrical Engineering Sarajevo", "Faculty of Electrical Engineering Tuzla"]}
运行 mysql_upgrade force
与 ALTER FORCE 相同的结果可以通过 mysql_upgrade 实现（下面的示例对 3 个 MySQL 表执行升级）
$ mysql_upgrade test mysql_json
...
test.mysql_json Needs upgrade
test.mysql_json_innodb
error : Table rebuild required. Please do "ALTER TABLE `mysql_json_innodb` FORCE" or dump/reload to fix it!
test.tt
error : Table rebuild required. Please do "ALTER TABLE `tt` FORCE" or dump/reload to fix it!
Repairing tables
test.mysql_json OK
`test`.`mysql_json_innodb` FIXED
`test`.`tt` FIXED
探索 JSON 函数
在 MariaDB 中获得带有 JSON 列的表后，我们可以使用一组 JSON 函数来处理 JSON 字段。
让我们创建一个临时（会话）表作为工作表，并使用存储在基础表中的所有数据。
MariaDB [test]> create temporary table my_temp like mysql_json;
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]> insert into my_temp(t) select * from mysql_json;
Query OK, 1 row affected (0.000 sec)
Records: 1 Duplicates: 0 Warnings: 0
现在让我们看看 JSON_DETAILED 函数的实际应用，它是一种漂亮的展示 JSON 结构的方式
MariaDB [test]> select json_detailed(t) from my_temp\G
*************************** 1. row ***************************
json_detailed(t): {
"city":
[
"Zenica",
"Sarajevo",
"Tuzla",
"Mostar",
"Banja Luka"
],
"team":
[
"Čelik",
"Željezničar",
"Sarajevo",
"Sloboda",
"Velež",
"Zrinjski",
"Borac"
],
"country": "Bosnia and Herzegovina",
"faculty":
[
"Faculty of Mechanical Engineering in Zenica",
"Faculty of Electrical Engineering Sarajevo",
"Faculty of Electrical Engineering Tuzla"
]
}
1 row in set (0.000 sec)让我们看看如何使用 JSON_EXTRACT 函数来使用一些数据，通过文档路径获取数据并对结果集应用索引
MariaDB [test]> select json_extract(t,'$.country'), json_extract(t,'$.city') from my_temp\G
*************************** 1. row ***************************
json_extract(t,'$.country'): "Bosnia and Herzegovina"
json_extract(t,'$.city'): ["Zenica", "Sarajevo", "Tuzla", "Mostar", "Banja Luka"]
MariaDB [test]> select json_extract(t,'$.city[0]') from my_temp\G
*************************** 1. row ***************************
json_extract(t,'$.city[0]'): "Zenica"
让我们尝试插入一个包含有效 JSON 对象的新行，然后使用 JSON_CONTAINS 函数来获取该值
MariaDB [test]> insert into my_temp values ('{"key1":"value1"}');
Query OK, 1 row affected (0.000 sec)
MariaDB [test]> select * from my_temp where json_contains(t, '"value1"', '$.key1');
+-------------------+
| t |
+-------------------+
| {"key1":"value1"} |
+-------------------+
1 row in set (0.000 sec)
让我们尝试插入一个包含无效 JSON 对象的新行，然后使用 JSON_TYPE 和 JSON_KEYS 函数检查我们插入了什么
MariaDB [test]> insert into my_temp values ('{"key-wrong":}');
Query OK, 1 row affected (0.000 sec)
MariaDB [test]> select json_type(t) from my_temp;
+--------------+
| json_type(t) |
+--------------+
| OBJECT |
| OBJECT |
| OBJECT |
+--------------+
3 rows in set (0.000 sec)
MariaDB [test]> select json_keys(t) from my_temp;
+----------------------------------------+
| json_keys(t) |
+----------------------------------------+
| ["city", "team", "country", "faculty"] |
| ["key1"] |
| NULL |
+----------------------------------------+
3 rows in set, 1 warning (0.000 sec)
为了防止插入错误的 JSON 数据，我们可以对 JSON 字段使用 CHECK 约束，这意味着会应用 JSON_VALID 函数。
让我们首先尝试在已经存在污染数据的 数据集上使用 CHECK 约束，如上面的示例所示。
MariaDB [test]> alter table my_temp add check(json_valid(t));
ERROR 4025 (23000): CONSTRAINT `CONSTRAINT_1` failed for `test`.`my_temp`正如预期的那样，该语句会失败，因为隐含的约束被违反了。为了解决这个问题，我们必须删除 JSON_KEY 为 NULL 的最后一行，因为我们知道它是错误的行。之后，再次使用 CHECK 约束修改表并验证结果。
MariaDB [test]> delete from my_temp where json_keys(t) is NULL;
Query OK, 1 row affected, 1 warning (0.000 sec)
MariaDB [test]> alter table my_temp add constraint c1 check(json_valid(t));
Query OK, 2 rows affected (0.001 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [test]> show create table my_temp\G
*************************** 1. row ***************************
Table: my_temp
Create Table: CREATE TEMPORARY TABLE `my_temp` (
`t` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
CONSTRAINT `c1` CHECK (json_valid(`t`))
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.000 sec)
如果我们使用的是基础表而不是临时表，CHECK 约束将在 INFORMATION_SCHEMA.CHECK_CONSTRAINTS 表中可见。
现在如果我们尝试插入错误的数据，CHECK 约束将被激活
MariaDB [test]> insert into my_temp values ('{"key-wrong":}');
ERROR 4025 (23000): CONSTRAINT `c1` failed for `test`.`my_temp`
请注意，在 MariaDB 表中添加 JSON 字段时，会自动创建一个带有 JSON_VALID 的 CHECK 约束。
MariaDB [test]> alter table x add column (j json);
Query OK, 0 rows affected (0.017 sec)
MariaDB [test]> show create table x\G
*************************** 1. row ***************************
Table: x
Create Table: CREATE TABLE `x` (
`x` int(11) DEFAULT NULL,
`j` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`j`))
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.000 sec)
有更多演示示例，请参考 视频教程。
结论
展示了使用 JSON 类型的新机制的实际示例。
这种机制的目的是为 MariaDB 用户提供数据一致性的选项，并减少 MySQL 和 MariaDB 领域之间 JSON 令人困惑的状态差异。