如何在MySQL 8.0中实现类似SQL Server OPENJSON的JSON插入？

MySQL 8.0提供了JSON_TABLE函数，完全可以替代SQL Server的OPENJSON实现将JSON数组转换为关系表并插入的需求，以下是对应你场景的实现代码：

完整插入示例（使用变量） #

INSERT INTO myTable (ColA, ColB, ColC)
SELECT jDataA, jDataB, jDataC
FROM JSON_TABLE(
    @JSON,
    '$.data[*]' COLUMNS (
        jDataA VARCHAR(4) PATH '$.jDataA',
        jDataB INT PATH '$.jDataB',
        jDataC VARCHAR(48) PATH '$.jDataC'
    )
) AS jt;

直接使用JSON字符串的示例 #

如果不需要变量，直接传入JSON内容：

INSERT INTO myTable (ColA, ColB, ColC)
SELECT jDataA, jDataB, jDataC
FROM JSON_TABLE(
    '{
        "data": [{
                "jDataA": "AAAA",
                "jDataB": 1,
                "jDataC": "OK"
            }, {
                "jDataA": "BBBB",
                "jDataB": 2,
                "jDataC": "OK"
            }, {
                "jDataA": "CCCC",
                "jDataB": 3,
                "jDataC": "BAD"
            }, {
                "jDataA": "DDDD",
                "jDataB": 4,
                "jDataC": "BAD"
            }
        ]
    }',
    '$.data[*]' COLUMNS (
        jDataA VARCHAR(4) PATH '$.jDataA',
        jDataB INT PATH '$.jDataB',
        jDataC VARCHAR(48) PATH '$.jDataC'
    )
) AS jt;

关键参数说明 #

• @JSON：对应你SQL Server中的JSON变量，存储要解析的JSON数据
• '$.data[*]'：指定要遍历的JSON数组路径，[*]表示匹配数组中的每一个元素
• COLUMNS子句：和SQL Server的WITH子句功能一致，定义要提取的列名、数据类型以及对应的JSON字段路径
• 如果JSON字段可能缺失，可以添加DEFAULT子句指定默认值，例如：jDataA VARCHAR(4) PATH '$.jDataA' DEFAULT 'N/A'

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