<p>I have a MySql database table as shown below: </p> <h2>id | page_name | language | item_id | item_text</h2> <p>This table stores language-based text used for various pages in a web application. I have translated text for multiple languages ​​and multiple pages, stored in a JSON file in the following format: </p> <pre class="brush:php;toolbar:false;">[ ["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"] ]</pre> <p>Each JSON document contains data for a specific language and a specific web page. </p> <p>I have a lot of data to insert and I'm trying to find an efficient way to add this data to my database. I'm assuming using a stored procedure is the best way, but I can't find any tutorials that cover looping through an array like this and inserting. </p> <p>Is it possible to pass such an array to a stored procedure and loop through it, inserting row by row? </p> <p>Any help would be greatly appreciated. </p>

创建表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;