JSON 类型可以存储 JSON 这种半结构化的数据,相比于直接将 JSON 存储为字符串,它的好处在于:
JSON 字段本身上,并不能创建索引。相反,可以对 JSON 文档中的某个子字段创建索引。例如:
CREATE TABLE city (
id INT PRIMARY KEY,
detail JSON,
population INT AS (JSON_EXTRACT(detail, '$.population'))
);
INSERT INTO city (id,detail) VALUES (1, '{"name": "Beijing", "population": 100}');
SELECT id FROM city WHERE population >= 100;