Alter an existing MySQL column to a JSON data type
...
- Automatic validation of JSON documents stored in JSON columns. Invalid documents produce an error.
...
mysql> SHOW CREATE TABLE `log`\G*************************** 1. row *************************** Table: logCreate Table: CREATE TABLE `log` ( `request` json DEFAULT NULL) ENGINE=InnoDB1 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: 0mysql> 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: 0mysql> ALTER TABLE `log` MODIFY `request` JSON;Query OK, 3 rows affected (0,00 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> 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: logCreate Table: CREATE TABLE `log` ( `request` json DEFAULT NULL) ENGINE=InnoDB1 row in set (0,00 sec)