MySQL原生JSON格式的简单使用
MySQL 5.7+ 开始支持原生JSON，可以对JSON格式的内容进行插入校验，以及对其内部字段单独查询，推荐对JSON内容使用JSON格式。
-- 创建表
CREATE TABLE t (id int(11) DEFAULT NULL,info json DEFAULT NULL
);
-- 插入校验
INSERT INTO t(id, info) VALUES (1, '{"name": "Jack", "age": 28}'); -- 插入成功
-- INSERT INTO t(id, info) VALUES (2, 'xxxxxx'); -- 插入失败，Invalid JSON text
-- 只查询info里面的name字段
SELECT info->'$.name' FROM t WHERE id=1; -- Jack 注意，$.name的引号不能省略
-- 或者
SELECT json_extract(info, '$.name') FROM t WHERE id=1; -- Jack
此外，可以对JSON内部的字段创建虚列，虚列的值可以根据JSON的内容自动更新，像普通列一样查询（但是不能插入和更新），以及创建索引。
-- 创建虚列
ALTER TABLE t ADD username varchar(10) GENERATED ALWAYS AS (info->'$.name') VIRTUAL;
-- 或者 ALTER TABLE t ADD username varchar(10) GENERATED ALWAYS AS (json_extract(info, '$.name)) VIRTUAL;
-- 虚列可以像普通列一样查询
SELECT username FROM t WHERE id=1; -- Jack
-- 插入JSON时，自动生成虚列的值
INSERT INTO t(id, info) VALUES (2, '{"name": "Lily", "age": 26}');
SELECT username FROM t WHERE id=2; -- Lily
-- 更改JSON内容，虚列值自动更新
UPDATE t SET info='{"name": "Lucy", "age": 26}' WHERE id=2;
SELECT username FROM t WHERE id=2; -- Lucy
-- 不允许更新虚列的值
-- UPDATE t SET username='Tom' WHERE id=2; -- The value specified for generated column 'username' in table 't' is not allowed.
-- 可以给序列创建索引，就像普通列一样
ALTER TABLE t ADD INDEX idx_username(username);
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
| 1 | SIMPLE | t | NULL | ref | idx_username | idx_username | 33 | const | 1 | 100.00 | NULL |