| uid | data | user_name |
+-----+-------------------------------------------------------------------+-----------+
| 1 | {"name": "name1", "amount": 400, "mobile": "15044447279"} | "name1" |
| 2 | {"name": "name1", "amount": 300, "mobile": "15044447279"} | "name1" |
| 3 | {"name": "name2", "amount": 300, "mobile": "15044447278"} | "name2" |
| 4 | {"name": "name3", "amount": 300, "mobile": "15044447277"} | "name3" |
| 5 | {"amount": 300, "mobile": "15044447277"} | NULL |
| 6 | {"amount": "300", "name”:”name2”,”mobile": "15044447278"} | NULL |
+-----+-------------------------------------------------------------------+-----------+
查看原表。
mysql> insert into json_test (uid,data) values (NULL, '{"name":"name1","mobile":"15044447279","amount":300}');
Query OK, 1 row affected (0.00 sec)
插入新数据
mysql> select * from json_test; +-----+-------------------------------------------------------------------+-----------+
| uid | data | user_name |
+-----+-------------------------------------------------------------------+-----------+
| 1 | {"name": "name1", "amount": 400, "mobile": "15044447279"} | "name1" |
| 2 | {"name": "name1", "amount": 300, "mobile": "15044447279"} | "name1" |
| 3 | {"name": "name2", "amount": 300, "mobile": "15044447278"} | "name2" |
| 4 | {"name": "name3", "amount": 300, "mobile": "15044447277"} | "name3" |
| 5 | {"amount": 300, "mobile": "15044447277"} | NULL |
| 6 | {"amount": "300", "name”:”name2”,”mobile": "15044447278"} | NULL |
| 7 | {"name": "name1", "amount": 300, "mobile": "15044447279"} | "name1" |
+-----+-------------------------------------------------------------------+-----------+
7 rows in set (0.00 sec)
确认新数据
mysql> rollback;
回滚数据
mysql> select * from json_test;
+-----+-------------------------------------------------------------------+-----------+
| uid | data | user_name |
+-----+-------------------------------------------------------------------+-----------+
| 1 | {"name": "name1", "amount": 400, "mobile": "15044447279"} | "name1" |
| 2 | {"name": "name1", "amount": 300, "mobile": "15044447279"} | "name1" |
| 3 | {"name": "name2", "amount": 300, "mobile": "15044447278"} | "name2" |
| 4 | {"name": "name3", "amount": 300, "mobile": "15044447277"} | "name3" |
| 5 | {"amount": 300, "mobile": "15044447277"} | NULL |
| 6 | {"amount": "300", "name”:”name2”,”mobile": "15044447278"} | NULL |
+-----+-------------------------------------------------------------------+-----------+
6 rows in set (0.00 sec)
对mysql 5.7 好感度*2啊。。卧槽。
猜测一下虚拟列的做法：
ALTER TABLE json_test ADD user_name varchar(128) GENERATED ALWAYS AS (json_extract(data,'$.name')) VIRTUAL;
应该写入的时候做一个trigger 每个json都运算json_extract(data,'$.name') ��然后写到一个新的不可修改的列里。
这个就可以让原来的行存和文档有一个非常完美的结合，当业务变化大的时候，放到json里面，而当变化稳定下来，就迁移到行存里。
完美。 推荐！
[译]JSON数据范式化（normalizr）
MySQL5.7 JSON类型使用介绍