MySQL JSON字段

张

张建站

2026/4/20 8:22:48

10分钟阅读

mysql中有没有处理json数据的函数当然有MySQL 从 5.7 版本开始就内置了非常强大的 JSON 处理函数专门用来高效操作你表里的security_info、sales_info、register_info、history_info这些 JSON 字段不用把整个 JSON 取出来改完再塞回去直接在数据库里精准增删改查。我给你整理最常用、最实用、你项目 100% 用得到的 JSON 函数全部大白话可直接复制的 SQL。一、MySQL 最核心的 6 个 JSON 函数背会这 6 个就够用1.JSON_SET—— 修改 / 新增 JSON 里的某个值最常用可以改值也可以新增字段。sqlUPDATE product_customer SET sales_info JSON_SET(sales_info, $.sales_account, new_sale_001) WHERE id 1;2.JSON_EXTRACT—— 取出 JSON 里的某个值写法 1标准sqlSELECT JSON_EXTRACT(sales_info, $.sales_account) FROM product_customer;写法 2简写符号 -sqlSELECT sales_info-$.sales_account FROM product_customer;写法 3去掉引号最常用 -sqlSELECT sales_info-$.sales_account FROM product_customer;3.JSON_REMOVE—— 删除 JSON 里的某个键sqlUPDATE product_customer SET sales_info JSON_REMOVE(sales_info, $.sales_director) WHERE id 1;4.JSON_INSERT—— 只新增不覆盖sqlUPDATE product_customer SET sales_info JSON_INSERT(sales_info, $.dept, 销售部) WHERE id 1;5.JSON_REPLACE—— 只修改不新增sqlUPDATE product_customer SET sales_info JSON_REPLACE(sales_info, $.sales_account, sale_update) WHERE id 1;6.JSON_TYPE—— 查看 JSON 类型sqlSELECT JSON_TYPE(sales_info) FROM product_customer;二、针对你现在的表直接给你写实战例子例子 1查询注册信息里的注册 IPsqlSELECT customer_account, register_info-$.register_ip AS register_ip FROM product_customer;例子 2修改历史资料里的邮箱sqlUPDATE product_customer SET history_info JSON_SET(history_info, $.email, test163.com) WHERE id 1;例子 3给 sales_info 增加一个字段不用改整个 JSONsqlUPDATE product_customer SET sales_info JSON_SET(sales_info, $.sales_phone, 13800138000) WHERE id 1;例子 4WHERE 条件里直接用 JSON 字段查询sqlSELECT * FROM product_customer WHERE register_info-$.register_ip 192.168.1.100;三、你最关心的重点非常重要这些 JSON 函数都是 MySQL 原生的速度极快不用把 JSON 读到程序里修改不用拼接 JSON 字符串不会破坏原来的结构精准修改某一个 key性能极高完全就是为你这种大量使用 JSON 字段的表设计的。四、一句话总结MySQL有非常完善的 JSON 函数最常用的就 3 个-查值JSON_SET改值 / 增加值JSON_REMOVE删除值你现在表里所有 JSON 字段密保、销售、注册、登录、历史资料…全部都能用这几个函数高效操作需要我给你写一套专门适配你这张表的 JSON 增删改查完整版 SQL吗我可以直接给你生成注册、登录、修改密码、修改资料、查询统计全套可运行语句。列表嵌套字典那种json可以增加删除json中的字典吗当然可以而且 MySQL 原生函数完全支持「列表嵌套字典」这种 JSON 格式你说的就是这种结构你表里的service_record就是这种json[ {time:2025-01-01,content:开通服务}, {time:2025-01-02,content:修改密码} ]我直接给你最实用、能直接用在你表上的 4 个操作新增、删除、修改、查询列表里的字典。一、先告诉你 2 个核心函数专门操作数组JSON_ARRAY_APPEND→ 往数组里追加一个字典JSON_REMOVE→ 删除数组里指定下标的字典二、实战 SQL直接复制就能用我用你表里的service_record举例1. 往列表里【追加】一个字典最常用sqlUPDATE product_customer SET service_record JSON_ARRAY_APPEND( service_record, $, -- 表示往最外层数组追加 {time:2025-01-03,content:绑定手机号,operator:admin} ) WHERE id 1;2. 删除列表里【第 N 个】字典按下标删除下标从0开始第 1 个$[0]第 2 个$[1]第 3 个$[2]sql-- 删除第 2 条记录下标 1 UPDATE product_customer SET service_record JSON_REMOVE(service_record, $[1]) WHERE id 1;3. 修改列表里【第 N 个】字典的某个字段sql-- 修改第 1 条记录的 content 内容 UPDATE product_customer SET service_record JSON_SET(service_record, $[0].content, 已续费会员) WHERE id 1;4. 查询列表里【第 N 个】字典内容sqlSELECT service_record-$[0].time AS first_time, service_record-$[0].content AS first_content FROM product_customer WHERE id 1;三、你表里的remark_json、service_record都能用你这种结构json[ {time:xxx, content:xxx}, {time:xxx, content:xxx} ]全部可以用上面的函数追加一条记录 ✅删除一条记录 ✅修改一条记录 ✅查询某条记录 ✅不需要取出整个 JSON不需要程序拼接MySQL 直接在数据库内部高效操作四、一句话总结列表嵌套字典 JSON 数组 → 完全支持增删改查追加JSON_ARRAY_APPEND删除JSON_REMOVE($[下标])修改JSON_SET($[下标].key)查询-$[下标].key需要我给你写一个 ** 专门针对你表结构的「JSON 数组操作工具类」** 吗可以直接集成到你之前的BaseDao里自动追加操作记录、自动删除历史记录。CREATE TABLE test_json ( id INT AUTO_INCREMENT PRIMARY KEY COMMENT 主键ID, account VARCHAR(50) NOT NULL COMMENT 账号, sales_info JSON DEFAULT NULL COMMENT 销售信息JSON, team_info JSON DEFAULT NULL COMMENT 团队信息JSON ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户-销售-团队极简测试表; 请写一个sql语句向这个表中插入一条数据这两个json都列表嵌套字典没问题我直接给你写一条能直接运行、完美符合你要求的 INSERT 语句。你要的是sales_info 列表嵌套字典team_info 列表嵌套字典下面这条 SQL直接复制就能执行格式完全标准、不会报错。sqlINSERT INTO test_json (account, sales_info, team_info) VALUES ( user001, [{sales_name:张三,sales_account:sale1001,operator:admin,operate_time:2025-01-01 10:00:00},{sales_name:李四,sales_account:sale1002,operator:system,operate_time:2025-01-01 11:00:00}], [{team_name:技术一组,member_role:主管,operator:admin,operate_time:2025-01-01 10:00:00},{team_name:销售二组,member_role:成员,operator:system,operate_time:2025-01-01 11:00:00}] );我给你拆开看结构你一看就懂sales_info 格式列表套字典json[ { sales_name: 张三, sales_account: sale1001, operator: admin, operate_time: 2025-01-01 10:00:00 }, { sales_name: 李四, sales_account: sale1002, operator: system, operate_time: 2025-01-01 11:00:00 } ]team_info 格式列表套字典json[ { team_name: 技术一组, member_role: 主管, operator: admin, operate_time: 2025-01-01 10:00:00 }, { team_name: 销售二组, member_role: 成员, operator: system, operate_time: 2025-01-01 11:00:00 } ]插入成功后你可以用下面语句查询验证sqlSELECT * FROM test_json;总结这条 SQL 完全满足✅ 两个 JSON 都是列表嵌套字典✅ 格式标准、MySQL 直接支持✅ 可直接运行、无任何语法错误✅ 包含你之前要求的operator、operate_time需要我再给你写增删改查 JSON 数组里的字典的语句吗请结合刚插入的数据帮我详细讲解一下Json函数的用法好我就用你刚插入的这条真实数据一条一条带你实战 MySQL JSON 函数全部能直接复制运行学完你就彻底掌握列表嵌套字典的增删改查。先把数据结构再明确一下方便你对照jsonsales_info [ {sales_name:张三,sales_account:sale1001}, {sales_name:李四,sales_account:sale1002} ] team_info [ {team_name:技术一组,member_role:主管}, {team_name:销售二组,member_role:成员} ]一、先记住 3 个核心符号$代表整个 JSON$[下标]代表数组第几个元素从 0 开始-取值带引号-取值不带引号最常用二、查询类 JSON 操作最常用1. 查询整个 JSON 字段sqlSELECT sales_info, team_info FROM test_json WHERE id1;2. 查询 sales_info 里第一个字典的销售姓名sqlSELECT sales_info-$[0].sales_name FROM test_json WHERE id1;3. 不带引号干净取值推荐用 -sqlSELECT sales_info-$[0].sales_name AS 销售姓名 FROM test_json WHERE id1;4. 查询 team_info 里第二个团队的角色sqlSELECT team_info-$[1].member_role FROM test_json WHERE id1;5. WHERE 条件直接根据 JSON 内容筛选sqlSELECT * FROM test_json WHERE sales_info-$[0].sales_name 张三;三、修改类 JSON 操作1. 修改第一个销售的姓名JSON_SETsqlUPDATE test_json SET sales_info JSON_SET(sales_info, $[0].sales_name, 张小三) WHERE id1;2. 给第二个团队加一个字段同样用 JSON_SETsqlUPDATE test_json SET team_info JSON_SET(team_info, $[1].group_tag, 核心成员) WHERE id1;四、数组追加往列表里新加一条字典1. 给 sales_info 追加一个新销售sqlUPDATE test_json SET sales_info JSON_ARRAY_APPEND(sales_info, $, {sales_name:王五,sales_account:sale1003} ) WHERE id1;2. 给 team_info 追加一个新团队sqlUPDATE test_json SET team_info JSON_ARRAY_APPEND(team_info, $, {team_name:运营三组,member_role:组员} ) WHERE id1;五、删除操作1. 删除数组里第 2 个元素下标 1sqlUPDATE test_json SET sales_info JSON_REMOVE(sales_info, $[1]) WHERE id1;2. 删除某个字典里的某个 keysqlUPDATE test_json SET team_info JSON_REMOVE(team_info, $[0].member_role) WHERE id1;六、判断是否包含某个 key / 某个值1. 判断 JSON 路径是否存在sqlSELECT JSON_CONTAINS_PATH(sales_info, one, $[2]) FROM test_json WHERE id1;返回 1存在返回 0不存在2. 判断是否包含某个值sqlSELECT JSON_CONTAINS(sales_info, 张三, $[0].sales_name) FROM test_json WHERE id1;七、最实用总结背这几句就够查json-$[下标].key改 / 加字段JSON_SET(字段, $[下标].key, 值)追加字典进数组JSON_ARRAY_APPEND(字段, $, {key:val})删字典JSON_REMOVE(字段, $[下标])删字段JSON_REMOVE(字段, $[下标].key)如果你愿意我可以再给你写一套 Python 通用方法把这些 JSON 操作封装进你之前的BaseDao以后增删改查一行代码搞定。除了以上两个操作还有哪些常用的查询类JSON操作如何在查询结果中只返回特定的键值对如何使用JSON函数进行条件查询