MSQL高阶操作

MSQL语言

json串循环提取求和

SELECT
  id AS uid,
  sum(num) AS total_size
FROM
  tbl_cmdb_ci_detail
  JOIN json_table (
    JSON_EXTRACT(content, '$.DiskInfo[*].DiskSize'),
    '$[*]' COLUMNS (num INT path '$')
  ) AS numbers
GROUP BY
  id

逐句解释
SELECT id AS uid, sum(num) AS total_size
SELECT：用于指定要查询的列或计算结果。
id AS uid：将 tbl_cmdb_ci_detail 表中的 id 列重命名为 uid（别名便于后续关联时区分）。
sum(num) AS total_size：对 num 列的值进行求和计算，结果命名为 total_size（用于统计磁盘总容量）。
FROM tbl_cmdb_ci_detail

声明查询的基础数据来源是 tbl_cmdb_ci_detail 表（存储核心业务数据，如服务器信息）。
JOIN json_table (...) AS numbers
json_table：数据库提供的 JSON 解析函数，用于将 JSON 格式的数据转换为关系型表结构（方便用 SQL 处理）。
JSON_EXTRACT(content, '$.DiskInfo[].DiskSize')：
从 tbl_cmdb_ci_detail 表的 content 字段（JSON 类型）中，提取 DiskInfo 数组下所有 DiskSize 的值。
示例：若 JSON 为 {"DiskInfo": [{"DiskSize": 100}, {"DiskSize": 200}]}，提取结果为 [100, 200]。
'$[]' COLUMNS (num INT path '$')：
'$[*]'：遍历 JSON 提取出的数组（如 [100, 200]）。
COLUMNS (num INT path '$')：定义临时表的结构，创建 num 列（整数类型），取值为数组中的每个元素（即 100、200）。
AS numbers：为 json_table 生成的临时表起别名 numbers，便于后续引用。
GROUP BY id
按 tbl_cmdb_ci_detail 表的 id 列分组，结合 sum(num) 实现 “按 id 统计磁盘总容量”（例如，每个 id 对应一台服务器，统计其所有磁盘的总大小）。

REGEXP_REPLACE替换函数

SELECT REGEXP_REPLACE(
  form_data->>'$.data.rannlbug', 
  '[,，]',  -- 正则：匹配英文逗号或中文逗号
  ''        -- 替换为空字符串（即移除逗号）
) AS no_commas

结合concat函数可实现json格式化

concat和group_concat函数

CONCAT(字符串1, 字符串2, ..., 字符串N)
GROUP_CONCAT([DISTINCT] 要拼接的字段 [ORDER BY 排序字段 ASC/DESC] [SEPARATOR '分隔符'])

GROUP_CONCAT(
        CONCAT(
          JSON_UNQUOTE(row_data - > '$.systemName_value'),
          '-',
          JSON_UNQUOTE(row_data - > '$.sysVersion')
        ) SEPARATOR '\n'
      ) AS GLXT

JSON_TABLE

红框内是一段MySQL 8.0+ 关联子查询，用于从 JSON 数组字段中提取用户名并换行拼接，完整格式化后如下：

SELECT
    GROUP_CONCAT(userName SEPARATOR '\n')
FROM
    mdl_instance,
    JSON_TABLE(
        mdl_instance.form_data,
        '$.Executor[*]' COLUMNS (userName VARCHAR(255) PATH '$.userName')
    ) AS jt
WHERE
    mdl_instance.id = i.id```
核心执行逻辑
这段代码嵌套在 CASE WHEN 分支中，作用是：针对外层每一条工单记录，解析其 form_data JSON 字段里的执行人数组，将所有执行人姓名用换行符拼接成一个字符串，作为「变更实施人」列的值。
逐段拆解
JSON_TABLE 行式展开
MySQL 8.0 新增函数，将 JSON 数组转换成一张临时表，实现数组 “拆行”。
'$.Executor[*]'：定位到 form_data 中的 Executor 数组，遍历数组内所有元素。
COLUMNS (userName VARCHAR(255) PATH '$.userName')：从数组每个对象中提取 userName 字段，作为临时表 jt 的 userName 列。
关联外层查询
WHERE mdl_instance.id = i.id：和外层查询的 mdl_instance 表（别名 i）做行级关联，保证只解析当前这一行工单的 JSON 数据。
分组拼接成字符串
GROUP_CONCAT(userName SEPARATOR '\n')：把展开后的多行用户名，用换行符拼接成一整段文本返回。
注意事项与优化建议
版本依赖
JSON_TABLE 仅支持 MySQL 8.0 及以上版本，MySQL 5.x 无法使用，需换用字符串截取 + 数字辅助表的方式解析 JSON 数组。
空值兜底
如果 Executor 数组为空、或所有 userName 都为空，GROUP_CONCAT 会返回 NULL，建议外层加 IFNULL 处理：
sql
IFNULL( (上面整个子查询), '无' )
性能优化
当前写法是逐行关联子查询，数据量大时性能较差，可改为外层 LEFT JOIN JSON_TABLE 的方式批量解析，提升执行效率。
换行符显示
SEPARATOR '\n' 的换行效果仅在支持换行渲染的前端页面 / 报表中生效，命令行查询中会显示为空格或不识别。
VARCHAR(255)这个有啥用
已完成思考
VARCHAR(255) 的核心作用
在你这段 JSON_TABLE 代码里，它是给 JSON 解析出来的临时字段 userName 声明「数据类型 + 最大长度」，具体作用分 4 点：
指定数据类型
明确告诉 MySQL：从 JSON 数组里提取的 userName 是可变长度字符串，不是数字、日期等其他类型。这样后续用 GROUP_CONCAT 拼接、排序、比较时，才会按字符串规则正常处理，不会出现隐式转换报错。
限制最大长度
255 代表这个临时字段最多存储 255 个字符（注意是字符数，不是字节数；utf8mb4 编码下 1 个字符占 4 字节）。
如果 JSON 里的用户名超过 255 个字符，MySQL 会静默截断超出部分，不会报错但会丢失数据。
控制内存与性能
指定明确长度后，MySQL 可以提前为临时表分配刚好够用的内存空间，相比不指定长度的 TEXT 类型，解析速度和内存占用都更优，尤其数据量大时差异明显。
语法强制要求
JSON_TABLE 函数的 COLUMNS 语法里，必须给每个提取列指定数据类型，不能省略，否则 MySQL 无法创建临时表，直接报语法错误。

posted @ 2025-08-05 16:14 有趣儿阅读(36) 评论(0) 收藏举报

刷新页面返回顶部

dl-112