做MySQL开发这几年遇到“从JSON字符串里把数据捞出来”的需求十个里有八个不是发生在json类型字段上而是躺在varchar、text、日志字段、甚至别人导出的临时表里。很多人以为这很简单一个JSON_EXTRACT塞进去就完事真上了线才发现要么报错要么返回一堆NULL要么处理数组时直接傻眼。这篇文章把我实际处理过的几类场景摊开讲——原生JSON字段怎么取数、字符串里混着日志文本的JSON怎么用SQL扒出来、数组和嵌套结构怎么摊平成行、查询变慢之后怎么用生成列救场。内容比较接地气适合刚接触MySQL JSON函数的初学者也适合被线上脏数据折腾过的老手用来对一下思路。1. 先想清楚你的JSON是字段还是字符串这决定了两条完全不同的路在动手写SQL之前我得先聊存储层。很多人拿“使用MySQL从JSON字符串提取数据”这个需求来找我第一句话都是“JSON怎么取字段”。但MySQL里JSON数据其实分两种形态一种是原生json类型字段另一种是藏在varchar、text、日志文本里的字符串。这两种形态提取数据的手段有重叠踩坑的方式却完全不一样。1.1 原生JSON类型解决了什么问题MySQL从5.7.8开始提供原生json类型。这个类型的本质是数据库在写入时就把JSON文本做了解析和规范化。哪怕你插入时写的是 {b:1,a:2}存进去再查出来可能就变成了 {a:2,b:1}键的顺序被排序重复键会被去重空白字符会被压缩。这样做的好处很实际每次查询不需要重新解析整个文本路径访问可以直接从内部二进制结构里定位速度比纯文本扫描快不少。原生json类型还有个隐藏优点写入时做格式校验。你插一条 {a: 1,} 进去MySQL直接报错而不是等查询时才发现数据是脏的。对数据质量要求高的场景这一条就值回票价。1.2 字符串存JSON的坑但现实项目里JSON以字符串形式存在的比例远比你想象的高。常见来源有三类历史表设计时还没有JSON类型字段是text里面塞了一整个接口返回体日志表把整段请求或响应体写入varchar和普通日志文本混在一起从文件或其他系统导入的数据字段是字符串格式但内容确实是JSON。字符串JSON最折磨人的问题一个是转义一个是空格一个是合法性。我从一张日志表里捞出过这种记录{user_id: 123, user_name: 张三, ext_info: {\level\: 3}}外层是JSON内层的level字段值又是一个被转义的JSON字符串。你要取level不能靠路径一把梭得先解析外层再对ext_info做一次JSON_EXTRACT或者重新解析。这种嵌套转义我后面在日志实操那一节专门讲。1.3 判断该走哪条路的三个问题拿到需求后我一般先问三个问题问清楚再动手问题对应的处理路线JSON是存在json类型列还是varchar/text列决定能不能直接用路径取数还是需要先清洗切割要取单条记录的几个字段还是按数组批量展开成行决定用JSON_EXTRACT还是JSON_TABLE取出来的数据是给报表、接口展示还是作为过滤条件决定要不要考虑索引和类型转换这三个问题的答案基本就决定了你用的是JSON_EXTRACT全家桶还是JSON_TABLE还是先SUBSTRING_INDEX急救。下面按使用频率最高的操作逐一列代码和坑。2. 取数三件套JSON_EXTRACT、- 和 - 怎么选才不会翻车如果只需要从一条JSON里拿一个值用哪个函数很多教程会告诉你JSON_EXTRACT但实际业务里我更常用 -。原因很简单JSON_EXTRACT返回的是带JSON格式的值字符串会带引号直接拿去比较、拼接、写报表很容易出问题。2.1 JSON_EXTRACT的路径写法JSON_EXTRACT(json_doc, path) 的path遵循JSONPath语法最常用的规则$代表整个文档$.name取根节点的name键$.a.b取嵌套对象a下的b$[0]取数组第0个元素$.list[2]取list数组下标为2的元素键名含特殊字符时用双引号包住$.user-name。举个例子SELECT JSON_EXTRACT({name: 张三, age: 30}, $.name); -- 结果为 张三 注意返回结果带双引号取数字时返回的是数值类型SELECT JSON_EXTRACT({age: 30}, $.age); -- 结果为 302.2 - 和 -省掉一层引号的烦恼-等价于JSON_EXTRACT返回结果还是JSON。-等价于JSON_UNQUOTE(JSON_EXTRACT(...))返回的是纯字符串没有外层引号。我用一张示例表说明两者差异CREATE TABLE user_json ( id INT PRIMARY KEY, info JSON ); INSERT INTO user_json VALUES (1, {name: 张三, age: 30, tags: [vip, coach]}), (2, {name: 李四, age: 25, tags: [normal]});下面三条写法结果差异很大SELECT info - $.name AS name_json, -- 张三 带引号 info - $.name AS name_text, -- 张三 不带引号 info - $.age AS age_text -- 30 字符串 FROM user_json WHERE id 1;name_json看起来是张三其实是个JSON字符串拿到程序里去和普通字符串比较容易翻车。name_text是干净的中文文本直接展示没问题。age_text这里有个小坑-取出来的年龄是字符串30如果要参与数值运算最好用-直接返回数值或者CAST转换SELECT info - $.age 1 AS age_plus_one FROM user_json WHERE id 1; -- 31MySQL在字符串和数字比较时虽然能做隐式转换但分组、排序、分页场景下隐式转换经常拖慢执行计划。我建议取值时就明确目标类型别依赖数据库“猜”。2.3 从varchar/text字符串列取数时NULL和坑如果字段是varchar存的内容是合法JSON文本JSON_EXTRACT、-照样能用MySQL会自动尝试把字符串解析成JSON。但注意如果字符串不是合法JSON这两个函数不会报错只会返回NULL。很多人踩的正是这个坑——SQL不报错但结果全是NULL你根本分不清是路径写错还是数据本身脏。我整理过一张log表CREATE TABLE api_log ( id INT PRIMARY KEY, request_body TEXT ); INSERT INTO api_log VALUES (1, {action: login, user_id: 100}), (2, not a json at all), (3, {action: login, user_id: 200);第三条字符串缺了右大括号是非法JSON。你写SELECT request_body - $.user_id FROM api_log;结果只有第一条是100第二条和第三条都是NULL。如果要区分“路径不存在”和“数据不合法”用JSON_VALID先判断SELECT id, JSON_VALID(request_body) AS valid_json, request_body - $.user_id AS user_id FROM api_log;这条SQL跑完你一眼就能看出哪些行是数据本身有问题哪些是路径的问题。排查脏数据时这比盲猜高效太多。2.4 键名带点、带空格、带中划线的处理普通键用$.name没问题但JSON里经常出现形如user.id、user-name、user name这样的键名。如果你直接写$.user.idMySQL会把它当成两级路径也就是去取user节点下的id键结果返回NULL。正确写法是用双引号包住整个键名SELECT {user.id: 1} - $.user.id; -- 1这个细节我印象特别深。有次线上报表一直出不来数据排查了半小时最后发现上游接口把字段名从userId改成了user.idSQL还是老的$.userId。键名带点和路径层级混在一起最容易麻痹大意。3. 数组与嵌套结构实战订单明细、标签列表、多维配置单个对象取字段只是开胃菜。真实业务里JSON里出现最多的其实是数组订单里的商品列表、用户身上的标签数组、配置表里的多维选项。处理数组才是“从JSON字符串提取数据”真正有技术含量的部分。3.1 JSON_TABLE把数组摊平成行MySQL 8.0之前想把数组里的每个元素拆成一行只能靠JSON_EXTRACT配合UNION或者写存储过程又笨又不优雅。8.0开始有了JSON_TABLE可以把JSON数组横向展开和原表做JOIN生成一张虚拟的关系表。在我看来这是MySQL处理JSON最值得掌握的函数。先看一个订单例子CREATE TABLE order_info ( order_id INT PRIMARY KEY, items JSON ); INSERT INTO order_info VALUES (1001, [{sku: A001, qty: 2, price: 20}, {sku: B002, qty: 1, price: 50}]), (1002, [{sku: C003, qty: 3, price: 10}]);要把订单里的商品明细全部展开一行一个商品SELECT o.order_id, item.sku, item.qty, item.price FROM order_info o JOIN JSON_TABLE( o.items, $[*] COLUMNS ( sku VARCHAR(32) PATH $.sku, qty INT PATH $.qty, price DECIMAL(10,2) PATH $.price ) ) AS item;执行结果order_id sku qty price 1001 A001 2 20.00 1001 B002 1 50.00 1002 C003 3 10.00这里的$[*]是数组通配符意思是遍历数组里的每个元素。COLUMNS里声明了三列每列对应一个JSON路径。JOIN之后MySQL会自动把数组长度作为生成行数。这种写法和UNION拼接相比不但清爽而且性能更好因为展开过程发生在SQL引擎内部不需要客户端循环。如果数组里的元素不是对象而是纯字符串数组比如tags: [vip, coach]COLUMNS里可以用FOR ORDINALITY生成序号再用路径$取元素本身SELECT id, tag_seq.seq, tag_seq.tag FROM user_json, JSON_TABLE( info, $.tags[*] COLUMNS ( seq FOR ORDINALITY, tag VARCHAR(32) PATH $ ) ) AS tag_seq;FOR ORDINALITY自动生成从1开始的序号这在恢复数组原始顺序时非常有用。因为JSON_TABLE展开后的行理论上不保证顺序你要做“取第一个标签”就得靠这个序号过滤。3.2 嵌套对象一层层剥到肉嵌套对象比数组简单一点就是路径不断往深走。但坑在于路径太长、层级太多时任何一个键拼错都会静默返回NULL。我建议先用JSON_KEYS看看当前一层有哪些键SELECT JSON_KEYS({user: {profile: {city: 上海}, level: 5}}); -- 结果[user] SELECT JSON_KEYS({user: {profile: {city: 上海}, level: 5}}, $.user); -- 结果[level, profile]一步一步确认路径比盲写$.user.profile.city靠谱得多。嵌套里还有一类很常见对象数组嵌对象。比如用户的历史地址列表{user: 张三, addresses: [{city: 北京, zip: 100000}, {city: 上海, zip: 200000}]}取第一个地址的城市SELECT JSON_EXTRACT({user: 张三, addresses: [{city: 北京, zip: 100000}]}, $.addresses[0].city); -- 结果北京要用JSON_TABLE把addresses展开成地址行路径写成$.addresses[*]列路径写成$.city跟订单例子的逻辑完全一致。3.3 数组长度、包含判断、位置取值除了展开成行还有几个数组操作在业务里高频出现。判断数组长度SELECT JSON_LENGTH([a, b, c]); -- 3判断某个元素是否在数组里SELECT JSON_CONTAINS([vip, coach], vip); -- 1这里要注意第二个参数必须是JSON格式。判断字符串时记得把值包上双引号或者用JSON_QUOTE自动生成带引号的字符串SELECT JSON_CONTAINS([vip, coach], JSON_QUOTE(vip)); -- 1取数组第一个和最后一个元素SELECT JSON_EXTRACT([a, b, c], $[0]) AS first, JSON_EXTRACT([a, b, c], $[LAST]) AS last;$[LAST]是MySQL 8.0.22开始支持的写法5.7里没有。5.7要取最后一个元素可以用JSON_LENGTH配合手工下标SELECT JSON_EXTRACT([a, b, c], CONCAT($[, JSON_LENGTH([a, b, c]) - 1, ]));如果数组元素是对象取最后一个对象的某个字段路径可以写SELECT JSON_EXTRACT({items: [{v: 1}, {v: 2}]}, $.items[LAST].v); -- 2这种方式在取最近一条监控指标、最新版本号时特别实用不用先把数组整个取出来再排序。4. 从混合文本里抢救JSON日志字符串提取的一次完整实操如果说前面的内容还算MySQL JSON函数的“常规操作”那这一节是真正考验综合SQL能力的场景JSON不是单独存在于字段里而是混在一大段日志文本中间需要先定位边界再切出来再解析。我处理日志、接口回调、第三方推送数据时用这套逻辑解决了不少问题。4.1 典型场景日志message里夹着JSON比如这张日志表CREATE TABLE sys_log ( id INT PRIMARY KEY, log_time DATETIME, message TEXT ); INSERT INTO sys_log VALUES (1, 2024-06-01 10:00:00, 用户登录成功业务数据{user_id: 123, user_name: 张三, login_ip: 10.0.0.8} 耗时32ms), (2, 2024-06-01 10:05:00, 订单创建失败返回 {code: 5001, message: 库存不足} 请排查), (3, 2024-06-01 10:06:00, 纯日志没有JSON);肉眼一看JSON都在message文本里以{开头、以}结尾。需求是统计每个用户的登录次数或者按订单返回code统计失败次数。第一步必须是“把JSON切出来”。4.2 用LOCATE SUBSTRING定位JSON边界思路是这样用LOCATE({, message)找到第一个左大括号的位置从左大括号位置开始用SUBSTRING截到结尾找到最后一个右大括号的位置再SUBSTRING截取精确的JSON片段。MySQL没有从后往前查找的LOCATE但有个技巧把字符串反转找到的第一个}就是原字符串最后一个}。代码是这样SELECT id, message, SUBSTRING( message, LOCATE({, message), LENGTH(message) - LOCATE(}, REVERSE(message)) - LOCATE({, message) 2 ) AS json_part FROM sys_log;拆解一下SUBSTRING的第三个参数先算message总长度LENGTH(message)LOCATE(}, REVERSE(message))得到从右往左数第一个}的位置转回原字符串等价于“最后一个右大括号离字符串结尾的距离再加1”原字符串里最后一个}的绝对位置是LENGTH(message) - LOCATE(}, REVERSE(message)) 1截取起点是第一个{的位置终点是最后一个}长度 终点位置 - 起点位置 1化简后就是上面那串。结果id json_part 1 {user_id: 123, user_name: 张三, login_ip: 10.0.0.8} 2 {code: 5001, message: 库存不足} 3 (NULL)第三条没有{LOCATE返回0后面要加WHERE条件过滤掉。4.3 JSON_VALID做最后一层守门把json_part切出来后不能直接当JSON取数。这种“肉眼看起来是JSON”的文本经常有隐藏问题日志里可能带换行、带Tab、JSON前后的文本也被截进来一小段。用JSON_VALID判断最稳妥SELECT id, json_part, JSON_VALID(json_part) AS is_valid, json_part - $.user_id AS user_id FROM ( SELECT id, SUBSTRING( message, LOCATE({, message), LENGTH(message) - LOCATE(}, REVERSE(message)) - LOCATE({, message) 2 ) AS json_part FROM sys_log ) t WHERE json_part IS NOT NULL AND json_part ;想直接统计登录用户次数就在外层加上GROUP BYSELECT json_part - $.user_id AS user_id, COUNT(*) AS login_cnt FROM ( SELECT id, SUBSTRING( message, LOCATE({, message), LENGTH(message) - LOCATE(}, REVERSE(message)) - LOCATE({, message) 2 ) AS json_part FROM sys_log ) t WHERE JSON_VALID(json_part) 1 GROUP BY user_id;4.4 实战延伸跨多行JSON、NULL判断和性能取舍上面这套方案能解决80%的单行JSON提取但有几个延伸问题要提醒。第一如果日志里JSON跨了多行比如用户登录成功 { user_id: 123, user_name: 张三 }LOCATE({, message)仍然能找到{但REVERSE方法找最后一个}依然有效唯一的麻烦是json_part里可能有换行符或缩进。这时候用JSON_VALID时MySQL对空白字符是容忍的能正常解析但如果你要把json_part塞到别的单行字段里记得先用REPLACE把换行替换掉REPLACE(REPLACE(json_part, \n, ), \r, )第二切出来的不一定是完整JSON。比如日志里出现{a: 1} 和 {b: 2}两个JSON对象最后一个}是第二个对象的右括号切割结果会变成两个对象拼在一起JSON_VALID返回0。这时候不能盲目用这种切割法要按业务特征调整边界。比如你明确只需要第一个JSON对象那可以用LOCATE找到第一个{后再从第一个{的位置往后找第一个匹配的右括号但这种“匹配”在JSON嵌套场景下很难纯靠SQL实现。我的经验是常规日志中JSON大多是完整独立的一段切割法够用如果JSON嵌套严重最好在写入日志时就把JSON单独存字段而不是混在message里。第三性能取舍。SUBSTRING LOCATE这类写法在百万级日志表上做全表扫描速度肯定不快。应对办法是缩小范围比如先按log_time把查询限定在半小时内或者只处理message LIKE %特定标记% 的行。JSON提取本身就是高成本的解析操作别指望它能像普通索引查询一样快这是它的物理上限不是SQL写得不对。5. 查询慢了别怪JSON生成列、虚拟索引与拆列策略当数据量上来之后你会发现取数本身不慢慢的是你拿JSON字段去WHERE过滤、去JOIN、去GROUP BY。这方面我踩过不少坑也总结了一套比较成熟的优化路径。5.1 JSON字段没法直接建索引怎么办MySQL的JSON列本身不支持直接建索引——这不是MySQL偷懒而是JSON的语义决定了搜索结果可能是任意深层路径不可能为每个路径都维护一份索引。但实际业务里过滤条件往往集中在某几个固定键上比如user_id、status、order_type。解决办法是生成列Generated Column。MySQL 5.7开始支持8.0里也很成熟。你可以把JSON里高频使用的键提取出来存成一个普通列再在这个列上建索引。生成列可以是虚拟的也可以是存储的。5.2 生成列索引的正确姿势以下面的表为例CREATE TABLE order_json ( order_id INT PRIMARY KEY, biz_data JSON ); INSERT INTO order_json VALUES (1, {user_id: 100, status: paid, amount: 88.5}), (2, {user_id: 200, status: pending, amount: 12.0});要给user_id建索引先加一个生成列ALTER TABLE order_json ADD COLUMN user_id INT GENERATED ALWAYS AS (biz_data - $.user_id) STORED, ADD INDEX idx_user_id (user_id);然后你就能正常用WHERE user_id 100走索引。STORED的意思是物理存储这个列每次插入和更新时由MySQL自动计算。还有一种是VIRTUAL不占磁盘但查询时由引擎计算InnoDB在5.7版本里对VIRTUAL列的索引支持有限我做生产表时默认用STORED省心性能也稳。同样的逻辑如果JSON里存的是数组而你要按数组里的某个值过滤8.0.17之后还有多值索引可以研究但复杂度偏高前期用生成列更符合直觉。5.3 什么时候应该把JSON拆成独立列生成列解决了索引问题但拆列依然是更彻底的办法。也不是所有JSON都要拆我给自己定了几条经验高频出现在WHERE、JOIN、ORDER BY的键一定要拆出来放普通列建索引低频读取但会整体展示的载荷留着JSON字段不拆比如商品的扩展属性、第三方接口的原始返回体用于聚合统计的数值型键比如金额、数量如果查询频率高拆成DECIMAL列比每次JSON_EXTRACT性能稳定很多还方便写加减乘除一次性数据分析场景不用拆直接JSON_TABLE展开即可反正数据量可控。拆列本身有代价应用层写数据时要同时维护JSON字段和独立列有一定的一致性风险。我的建议是优先用生成列因为它由数据库自动计算不存在应用程序双写不一致的问题。只有在生成列仍然不能满足查询复杂度时才考虑拆成普通字段。5.4 MySQL 5.7 和 8.0 的明显差异最后提醒一下版本差异。如果项目还是MySQL 5.7有几个JSON能力用起来要小心JSON_TABLE在5.7就已经有了但功能远不如8.0完整有些用法比如带路径的默认值、嵌套路径组合在5.7下容易报语法错误我建议5.7环境先做小样本测试再上线$[LAST]、JSON_OVERLAPS、JSON_ARRAYAGG这些8.0特性5.7完全没有5.7对JSON列更新是整列替换没有部分更新能力如果JSON很大频繁更新会很浪费8.0支持JSON部分更新性能改善明显。如果你正在新建项目我强烈建议直接上8.0。MySQL 8.0对JSON的支持从函数丰富度到JSON_TABLE的稳定性都比5.7高一个档次没必要为了迁就老版本去绕路。最后再分享一个小技巧。我在处理这些JSON提取任务时习惯把常用的一组JSON操作做成一个存储函数或者视图比如把“从混合文本中切出第一个JSON”的逻辑封装成一个函数后续只要调用一次不用每次复制一大段LOCATESUBSTRING。这个做法在团队协作时特别有用新同事接手报表SQL看到的是一个语义清晰的函数调用而不是一段让人皱眉的字符串切割代码。JSON功能本身不难真正容易出问题的全都在边界处理、脏数据识别和性能设计这些细节里。把这些细节整理成规范比记住任何一个函数都值钱。