我正在尝试将MySQL列从varchar(9000) NULL更改为MySQL 5.7中的新JSON数据类型。该列包含有效的JSON字符串,但有些值为空。当我尝试以下操作时:
alter table log modify request json它会失败,并显示以下错误:
Invalid JSON text: "The document is empty." at position 0 in value for column '#sql-2f36_168a6.request'但是,当我创建一个新列时:
alter table log add request_json json然后插入相同的数据:
update log set request_json=json where request != ''将更新新的request_json列。如何将已有的列修改为JSON数据类型,并保留JSON数据而不创建新列?
发布于 2016-06-15 03:13:01
..。
..。
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'.尝试:
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)https://stackoverflow.com/questions/37812581
复制相似问题