如何在MySQL中使用JSON数据？

使用MySQL处理JSON数据可直接在关系型数据库中存储、查询和操作半结构化数据，自5.7版本起支持JSON类型；通过JSON数据类型定义列并插入合法JSON值，MySQL会自动验证语法；可使用JSON_EXTRACT()或->（返回带引号字符串）和->>（返回无引号值）提取数据，如profile->>"$.city"获取城市名；支持通过WHERE子句过滤JSON值，建议使用生成列和索引提升性能，如ADD city VARCHAR(50) GENERATED ALWAYS AS (profile->>"$.city")并创建索引；可使用JSON_SET()添加或更新字段，JSON_REPLACE()替换现有键，JSON_REMOVE()删除键；处理数组时可用JSON_CONTAINS()判断值是否存在，JSON_LENGTH()获取数组长度，JSON_ARRAY_APPEND()追加元素；无法直接索引JSON列，应通过生成列如ADD age INT AS (profile->>"$.age")并创建索引以优化查询；常用函数包括JSON_VALID()验证JSON、JSON_OBJECT()创建对象、JSON_ARRAY()创建数组、JSON_KEYS()获取键列表、JSON_SEARCH()搜索字符串；示例中可构造JSON输出如JSON_OBJECT('name', name, 'city', profile->>"$.city")；最佳实践包括插入前验证JSON、对频繁查询字段使用生成列和索引、避免存储过大JSON对象、优先使用规范化表存储结构化数据，JSON适用于需要灵活模式的场景，但需合理设计索引以保障查询效率。

Working with JSON data in MySQL allows you to store, query, and manipulate semi-structured data directly within your relational database. MySQL has supported the JSON data type since version 5.7, making it easier to handle dynamic or schema-less data. Here's how to effectively work with JSON in MySQL.

Storing JSON Data

To store JSON, use the JSON data type when defining a column:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    profile JSON
);

You can insert valid JSON objects or arrays:

INSERT INTO users (name, profile) VALUES (
    'Alice',
    '{"age": 30, "city": "New York", "hobbies": ["reading", "cycling"]}'
);

MySQL validates the JSON syntax on insert or update. Invalid JSON will cause an error.

Querying JSON Data

MySQL provides several functions to extract and query JSON values.

Extracting Values

Use JSON_EXTRACT() or the shorthand -> (returns quoted string) and ->> (returns unquoted value):

-- Returns "New York" (with quotes)
SELECT profile->'$.city' FROM users WHERE name = 'Alice';

-- Returns New York (without quotes)
SELECT profile->>'$.city' FROM users WHERE name = 'Alice';

-- Extract nested or array values
SELECT profile->>'$.hobbies[0]' FROM users;

The path syntax:

• $ refers to the root
• $.key accesses a top-level key
• $.array[index] accesses array elements
• $.nested.key accesses nested objects

Filtering with JSON

You can use JSON values in WHERE clauses:

SELECT * FROM users
WHERE profile->>'$.city' = 'New York';

For better performance, consider adding a generated (virtual) column and indexing it:

ALTER TABLE users
ADD city VARCHAR(50) GENERATED ALWAYS AS (profile->>'$.city');

CREATE INDEX idx_city ON users(city);

Modifying JSON Data

Use JSON_SET(), JSON_REPLACE(), and JSON_REMOVE() to update JSON fields.

-- Add or update a field
UPDATE users
SET profile = JSON_SET(profile, '$.age', 31, '$.country', 'USA')
WHERE name = 'Alice';

-- Replace only if key exists
UPDATE users
SET profile = JSON_REPLACE(profile, '$.city', 'Brooklyn');

-- Remove a key
UPDATE users
SET profile = JSON_REMOVE(profile, '$.country');

Other useful functions:

• JSON_INSERT() – adds value only if key doesn’t exist
• JSON_ARRAY_APPEND() – appends to an array
• JSON_MERGE_PATCH() / JSON_MERGE_PRESERVE() – merge JSON documents

Working with JSON Arrays

You can query and manipulate arrays stored in JSON.

-- Check if a value exists in an array
SELECT * FROM users
WHERE JSON_CONTAINS(profile->'$.hobbies', '"cycling"');

-- Get array length
SELECT JSON_LENGTH(profile->'$.hobbies') FROM users;

-- Append to array
UPDATE users
SET profile = JSON_ARRAY_APPEND(profile, '$.hobbies', 'hiking');

Indexing JSON Data

Since you can’t directly index a JSON column, create a generated column for frequently queried fields and index that:

ALTER TABLE users
ADD age INT AS (JSON_UNQUOTE(JSON_EXTRACT(profile, '$.age')));

CREATE INDEX idx_age ON users(age);

Or more simply:

ADD age INT AS (profile->>'$.age');
CREATE INDEX idx_age ON users(age);

This enables efficient filtering on JSON fields.

Useful JSON Functions Summary

• JSON_VALID() – checks if a string is valid JSON
• JSON_OBJECT() – creates a JSON object
• JSON_ARRAY() – creates a JSON array
• JSON_KEYS() – returns top-level keys in a JSON object
• JSON_SEARCH() – finds a string within JSON

Example:

SELECT JSON_OBJECT('name', name, 'city', profile->>'$.city') AS info
FROM users;

Final Tips

• Always validate JSON input before inserting.
• Use generated columns and indexes for performance.
• Avoid storing large JSON blobs if you query them often.
• Prefer normalized tables for structured, frequently accessed data.

Working with JSON in MySQL adds flexibility, especially for dynamic attributes, but should be balanced with performance and schema design.

Basically, use JSON when schema flexibility is needed, but index and extract values smartly for queries.

以上是如何在MySQL中使用JSON数据？的详细内容。更多信息请关注PHP中文网其他相关文章！