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类型使用介绍
本文永久更新链接地址: