MySQL 8.0存储过程中如何将JSON数据插入tbleventbyminutes表

第一步：优化JSON结构（推荐） #

原有JSON结构携带SQL片段存在严重SQL注入风险，且处理成本高，建议调整为纯数据的键值对结构，无需拼接SQL片段，示例如下：

[
  {
    "matchid": 10001,
    "minute": 1,
    "halftime": 1,
    "value": "A",
    "userid": 1
  },
  {
    "matchid": 10001,
    "minute": 1,
    "halftime": 1,
    "value": "A",
    "userid": 1
  }
]

其中createddate无需前端传入，可直接在存储过程中取当前系统日期，避免格式兼容问题。

第二步：编写MySQL存储过程 #

MySQL 8.0原生支持JSON_TABLE函数，可以直接将JSON数组解析为关系表，无需循环遍历，性能更高且无注入风险，存储过程代码如下：

DELIMITER //
CREATE PROCEDURE InsertEventByMinutes(IN jsonData JSON)
BEGIN
  -- 直接从解析后的JSON中取数插入表
  INSERT INTO `tbleventbyminutes` (`matchid`, `minute`, `halftime`, `value`, `createddate`, `userid`)
  SELECT 
    matchid,
    minute,
    halftime,
    value,
    DATE_FORMAT(NOW(), '%Y-%m-%d'),
    userid
  FROM JSON_TABLE(
    jsonData,
    '$[*]' COLUMNS (
      matchid INT PATH '$.matchid',
      minute INT PATH '$.minute',
      halftime INT PATH '$.halftime',
      value VARCHAR(1) PATH '$.value',
      userid INT PATH '$.userid'
    )
  ) AS json_parsed_data;
  
  -- 插入完成后可直接在此处补充value字段的公式计算逻辑
  -- 示例：统计本次插入的不同value的数量，可根据实际业务需求修改
  -- SELECT value, COUNT(*) AS value_count FROM tbleventbyminutes WHERE createddate = CURDATE() GROUP BY value;
END //
DELIMITER ;

保留原有JSON结构的兼容方案 #

如果不调整原有JSON结构，可使用动态SQL拼接实现，但存在注入风险，非必要不建议使用：

DELIMITER //
CREATE PROCEDURE InsertEventByMinutesOldJson(IN jsonData JSON)
BEGIN
  SET @insert_sql = 'INSERT INTO `tbleventbyminutes` (`matchid`, `minute`, `halftime`, `value`, `createddate`, `userid`) ';
  -- 递归生成JSON数组下标序列
  WITH RECURSIVE seq AS (
    SELECT 0 AS idx UNION ALL SELECT idx + 1 FROM seq WHERE idx < JSON_LENGTH(jsonData) - 1
  )
  -- 拼接所有UNION ALL查询段
  SELECT GROUP_CONCAT(
    CONCAT(
      'SELECT ',
      REPLACE(JSON_UNQUOTE(JSON_EXTRACT(jsonData, CONCAT('$[', idx, '][0]'))), 'select ', ''), ',',
      JSON_UNQUOTE(JSON_EXTRACT(jsonData, CONCAT('$[', idx, '][1]'))), ',',
      JSON_UNQUOTE(JSON_EXTRACT(jsonData, CONCAT('$[', idx, '][2]'))), ',',
      JSON_UNQUOTE(JSON_EXTRACT(jsonData, CONCAT('$[', idx, '][3]'))), ',',
      JSON_UNQUOTE(JSON_EXTRACT(jsonData, CONCAT('$[', idx, '][4]'))), ',',
      JSON_UNQUOTE(JSON_EXTRACT(jsonData, CONCAT('$[', idx, '][5]')))
    ) SEPARATOR ' UNION ALL '
  ) INTO @union_part FROM seq;

  SET @exec_sql = CONCAT(@insert_sql, @union_part);
  PREPARE stmt FROM @exec_sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

内容的提问来源于stack exchange，提问作者Tanmoy Banerjee