将现有的 mysql 列更改为 JSON 数据类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/37812581/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Alter an existing mysql column to a JSON data type
提问by Steve Lloyd
I am trying to change a MySQL column from varchar(9000) NULL to the new JSON data type in MySQL 5.7. The column holds valid JSON strings but some values are null. When I try the following:
我正在尝试将 MySQL 列从 varchar(9000) NULL 更改为 MySQL 5.7 中的新 JSON 数据类型。该列包含有效的 JSON 字符串，但某些值为 null。当我尝试以下操作时：
alter table log modify request json
it fails with the following error:
它失败并出现以下错误：
Invalid JSON text: "The document is empty." at position 0 in value for column '#sql-2f36_168a6.request'
However, when I create a new column:
但是，当我创建一个新列时：
alter table log add request_json json
and then insert the same data:
然后插入相同的数据：
update log set request_json=json where request != ''
the new request_json column is updated. How to I modify the existing column to JSON data type and preserve the JSON data without creating a new column?
新的 request_json 列已更新。如何将现有列修改为 JSON 数据类型并保留 JSON 数据而不创建新列？
回答by wchiquito
...
- Automatic validation of JSON documents stored in JSON columns. Invalid documents produce an error.
...
...
- 自动验证存储在 JSON 列中的 JSON 文档。无效的文档会产生错误。
...
mysql> SHOW CREATE TABLE `log`\G
*************************** 1. row ***************************
Table: log
Create Table: CREATE TABLE `log` (
`request` json DEFAULT NULL
) ENGINE=InnoDB
1 row in set (0,00 sec)
mysql> SELECT `request`, JSON_VALID(`request`)
-> FROM `log`;
+-----------------+-----------------------+
| request | JSON_VALID(`request`) |
+-----------------+-----------------------+
| {"type": "bug"} | 1 |
| NULL | NULL |
| NULL | NULL |
+-----------------+-----------------------+
3 rows in set (0,00 sec)
mysql> UPDATE `log`
-> SET `request` = ''
-> WHERE `request` IS NULL;
ERROR 3140 (22032): Invalid JSON text: "The document is empty." at position 0 in value for column 'log.request'.
Try:
尝试：
mysql> DROP TABLE IF EXISTS `log`;
Query OK, 0 rows affected (0,00 sec)
mysql> CREATE TABLE IF NOT EXISTS `log` (
-> `request` VARCHAR(9000) NULL
-> );
Query OK, 0 rows affected (0,01 sec)
mysql> INSERT INTO `log`
-> (`request`)
-> VALUES
-> ('{"type": "bug"}'),
-> (NULL),
-> ('');
Query OK, 3 rows affected (0,00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT `request`, JSON_VALID(`request`)
-> FROM `log`;
+-----------------+-----------------------+
| request | JSON_VALID(`request`) |
+-----------------+-----------------------+
| {"type": "bug"} | 1 |
| NULL | NULL |
| | 0 |
+-----------------+-----------------------+
3 rows in set (0,00 sec)
mysql> ALTER TABLE `log` MODIFY `request` JSON;
ERROR 3140 (22032): Invalid JSON text: "The document is empty." at position 0 in value for column '#sql-1bab_4.request'.
mysql> UPDATE `log`
-> SET `request` = NULL
-> WHERE `request` = '';
Query OK, 1 row affected (0,00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> ALTER TABLE `log` MODIFY `request` JSON;
Query OK, 3 rows affected (0,00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT `request`, JSON_VALID(`request`)
-> FROM `log`;
+-----------------+-----------------------+
| request | JSON_VALID(`request`) |
+-----------------+-----------------------+
| {"type": "bug"} | 1 |
| NULL | NULL |
| NULL | NULL |
+-----------------+-----------------------+
3 rows in set (0,00 sec)
mysql> SHOW CREATE TABLE `log`\G
*************************** 1. row ***************************
Table: log
Create Table: CREATE TABLE `log` (
`request` json DEFAULT NULL
) ENGINE=InnoDB
1 row in set (0,00 sec)
