json mysql 字段 默认值_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);

EXPLAIN SELECT * FROM t WHERE username='Jack'; -- 可以用到idx_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 |