MySQL针对JSON字段的key创建索引

MySQL在JSON字段的特定键上创建索引，从而提高查询性能

-- 创建表
CREATE TABLE example_table (
    id INT PRIMARY KEY,
    data JSON
);

-- 插入示例数据
INSERT INTO example_table (id, data)
VALUES
(1, '{"name": "Alice", "age": 30, "city": "New York"}'),
(2, '{"name": "Bob", "age": 25, "city": "Los Angeles"}'),
(3, '{"name": "Charlie", "age": 35, "city": "Chicago"}');

-- 添加生成列
ALTER TABLE example_table
ADD COLUMN name VARCHAR(255) GENERATED ALWAYS AS (JSON_UNQUOTE(data->'$.name')) VIRTUAL,
ADD COLUMN age INT GENERATED ALWAYS AS (CAST(JSON_UNQUOTE(data->'$.age') AS UNSIGNED)) VIRTUAL;

-- 创建索引
CREATE INDEX idx_name ON example_table (name);
CREATE INDEX idx_age ON example_table (age);

-- 查询数据
SELECT *
FROM example_table
WHERE name = 'Alice'
  AND age > 25;