Use redis's list data structure to execute sql statements in batches

There are now more than 3,000 such records in the database. Each record contains the Chinese name of the region and the English name of the region (as shown below)

I now want to use redis queues lpush and rpop to implement batch updates. I have already written the sql statement (as shown below)

I wrote the code like this (as shown below). I stored more than 3,000 sql statements in the list (lpush), and prepared to take them out one at a time and execute them (rpop), and execute them one by one. I don’t know how to write them next. Please tell me how to write next. Now I have put the sql statement in the list. I also checked the value of the list under cli. The Chinese seems to be garbled (as shown below). I added --raw when starting the cli. Please give me some answers.

Thank you everyone~

Reply content:

There are now more than 3,000 such records in the database. Each record contains the Chinese name of the region and the English name of the region (as shown below)

I now want to use redis queues lpush and rpop to implement batch updates. I have already written the sql statement (as shown below)

I wrote the code like this (as shown below). I stored more than 3,000 sql statements in the list (lpush), and prepared to take them out one at a time and execute them (rpop), and execute them one by one. I don’t know how to write them next. Please tell me how to write next. Now I have put the sql statement in the list. I also checked the value of the list under cli. The Chinese seems to be garbled (as shown below). I added --raw when starting the cli. Please give me some answers.

Thank you everyone~

Thanks for the invitation
In fact, you don’t need redis. You can put all the SQL in a file, read each one and execute each one. Isn’t that enough?

<code class="php">function getSql()
{
    $fp = fopen('sql.txt', 'r');
    while (!feof($fp)) {
        yield fgets($fp);
    }
}

foreach (getSql() as $sql) {
    executeSql($sql);
}

function executeSql()
{
    //执行语句
}</code>

<code>insert into base_region(region_name, en_name) values('中国','Zhongguo'), ('北京', 'Beijing'),...,('上海', 'Shanghai') on duplicate key update en_name = values(en_name);</code>

It can be done with just one SQL statement with minimal overhead (one database connection, the performance is similar to batch insert into data).

Two points to note:

1. values(),(),(),() The value of () needs to be spliced ​​using a for loop

2. region_name must be a unique index. You can temporarily add a unique index to this field, and then drop it after the SQL execution is completed