我有一个MySQL DB表，如下：

id | page_name |语言| item_id | item_text

该表包含language-based文本，用于在Web应用中的各个页面上使用。我有以下格式的多种语言的翻译文本和JSON文件中的多个页面：

[
    ["home","fr",1,"French text item 1"]
    ["home","fr",2,"French text item 2"]
    ["home","fr",3,"French text item 3"]
    ["home","fr",4,"French text item 4"]
    ["home","fr",5,"French text item 5"]
]

每个JSON文档都包含一种特定语言的数据和一个特定的网页。

我有大量的数据要插入像这样并插入。

是否可以将类似的数组馈入存储的Proc并循环循环，并插入每一行？

任何帮助将非常感激。

分析解答
CREATE TABLE test (id INT AUTO_INCREMENT PRIMARY KEY,
                   page_name VARCHAR(64),
                   language CHAR(2),
                   item_id INT,
                   item_text VARCHAR(255)
                   );

SET @data := '
[
    ["home","fr",1,"French text item 1"],
    ["home","fr",2,"French text item 2"],
    ["home","fr",3,"French text item 3"],
    ["home","fr",4,"French text item 4"],
    ["home","fr",5,"French text item 5"]
]
  ';

INSERT INTO test (page_name, language, item_id, item_text)
SELECT page_name, language, item_id, item_text
FROM JSON_TABLE(@data,
                '$[*]' COLUMNS ( page_name VARCHAR(64) PATH '$[0]',
                                 language CHAR(2) PATH '$[1]',
                                 item_id INT PATH '$[2]',
                                 item_text VARCHAR(255) PATH '$[3]'                                    
                                )
                ) jsontable;

SELECT * FROM test;
id page_name language item_id item_text
1 home fr 1 French text item 1
2 home fr 2 French text item 2
3 home fr 3 French text item 3
4 home fr 4 French text item 4
5 home fr 5 French text item 5

小提琴