mysql如何使用json函数操作数据-mysql教程-PHP中文网

mysql如何使用json函数操作数据

P粉602998670

发布： 2025-10-22 13:38:01

原创

199人浏览过

MySQL从5.7起支持JSON类型及函数，可高效操作JSON数据。1. 使用JSON类型建表并插入数据，支持JSON_OBJECT()构造；2. 用->和->>提取字段，后者去除引号便于比较；3. JSON_SET、REPLACE、INSERT用于修改；4. JSON_REMOVE删除元素；5. JSON_ARRAY、QUOTE、PRETTY等辅助构造与格式化；6. 可通过生成列加索引优化查询性能。适用于配置、标签等半结构化场景，但应避免滥用影响关系模型优势。

mysql如何使用json函数操作数据

MySQL从5.7版本开始支持JSON数据类型，并提供了一系列JSON函数来操作JSON格式的数据。这些函数可以帮助你查询、修改、验证和构造JSON内容，无需将JSON当作普通字符串处理。

1. 插入和存储JSON数据

MySQL有专门的JSON数据类型，用于确保字段中存储的是合法的JSON格式。

示例建表：

<font face="Courier New">CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  info JSON
);</font>

登录后复制

插入JSON数据：

<font face="Courier New">INSERT INTO users (info) VALUES 
('{"name": "Alice", "age": 25, "hobbies": ["reading", "swimming"]}'),
('{"name": "Bob", "age": 30, "hobbies": ["gaming"]}');</font>

登录后复制

也可以使用JSON_OBJECT()函数构建对象：

<font face="Courier New">INSERT INTO users (info) VALUES 
(JSON_OBJECT("name", "Charlie", "age", 35));</font>

登录后复制

2. 查询JSON字段内容

使用->和->>操作符提取JSON值。

->：返回带引号的JSON值（保留格式）
->>：返回去引号后的实际值（常用于比较）

示例查询名字：

<font face="Courier New">SELECT info->"$.name" AS name FROM users;</font>

登录后复制

输出："Alice"

使用->>去除引号：

<font face="Courier New">SELECT info->>"$.name" AS name FROM users;</font>

登录后复制

输出：Alice（可直接用于WHERE条件）

查询数组元素（如第一个爱好）：

<font face="Courier New">SELECT info->>"$.hobbies[0]" AS hobby FROM users;</font>

登录后复制

3. 修改JSON数据

使用JSON_SET()、JSON_REPLACE()、JSON_INSERT()等函数更新JSON字段。

Find JSON Path Online

Easily find JSON paths within JSON objects using our intuitive Json Path Finder

查看详情

JSON_SET：设置或更新指定路径的值（存在则改，不存在则增）
JSON_REPLACE：仅替换已有路径的值（不存在则不操作）
JSON_INSERT：仅插入新值（路径存在则忽略）

示例：给Alice添加城市信息：

<font face="Courier New">UPDATE users 
SET info = JSON_SET(info, "$.city", "Beijing") 
WHERE info->>"$.name" = "Alice";</font>

登录后复制

修改年龄：

<font face="Courier New">UPDATE users 
SET info = JSON_REPLACE(info, "$.age", 26) 
WHERE info->>"$.name" = "Alice";</font>

登录后复制

4. 删除JSON字段或元素

使用JSON_REMOVE()删除指定路径：

<font face="Courier New">UPDATE users 
SET info = JSON_REMOVE(info, "$.city") 
WHERE id = 1;</font>

登录后复制

这会移除city字段。

5. 构造和格式化JSON

除了JSON_OBJECT()，还有：

JSON_ARRAY()：创建JSON数组
JSON_QUOTE()：将字符串转为带引号的JSON字符串
JSON_PRETTY()：美化输出JSON结构

示例：

<font face="Courier New">SELECT JSON_PRETTY(info) FROM users WHERE id = 1;</font>

登录后复制

输出格式化的JSON，便于查看。

6. 条件查询与索引建议

可以在生成列上创建索引以提升JSON查询性能。

例如，为name字段创建虚拟列并加索引：

<font face="Courier New">ALTER TABLE users 
ADD COLUMN name VARCHAR(50) 
GENERATED ALWAYS AS (info->>"$.name");

CREATE INDEX idx_name ON users(name);</font>

登录后复制

这样按名字查询会更快。

基本上就这些。合理使用MySQL的JSON函数，可以灵活处理半结构化数据，尤其适合配置、标签、动态属性等场景。注意别过度依赖JSON而牺牲了关系模型的优势。

以上就是mysql如何使用json函数操作数据的详细内容，更多请关注php中文网其它相关文章！