How to insert data from multiple nested lists into multiple rows in the database using a stored procedure-PHP Chinese Network Q&A

Article Topic Learning Download Q&A Programming Dictionary Game Recent Updates

简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)

How to insert data from multiple nested lists into multiple rows in the database using a stored procedure

P粉807471604 2023-08-14 19:11:53

473

I have a MySql database table as shown below:

id | page_name | language | item_id | item_text

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:

[ ["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"] ]

Each JSON document contains data for a specific language and a specific web page.

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.

Is it possible to pass such an array to a stored procedure and loop through it, inserting row by row?

Any help would be greatly appreciated.

P粉807471604

reply all (1)

P粉6765887382023-08-15 13:06:06 1 floor

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