Shulou(Shulou.com)06/02 Report--

This article will explain in detail what batch operation sentences there are in mysql, and the content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have some understanding of the relevant knowledge after reading this article.

1. Replace into batch updates

Replace into t_student (id,dr) values (1 recorder 2'), (2 recital 3'),... (x recorder y')

Example:

Replace into t_student

(`Id`, `Author`, `CreatedTime`, `UpdatedTime`)

Values

(1) 'Wang Wu', '2016-12-12-12-12-12-12-12-20')

(2) 'Zhang San', '2016-12-12 12 20')

2. Insert into... on duplicate key update batch updates

Insert into t_student (id,dr) values (1 recorder 2'), (2 recital 3'),... (x recorder) on duplicate key update dr=values (dr)

Example:

Insert into t_student

(`Id`, `Author`, `CreatedTime`, `UpdatedTime`)

Values

(1) 'Zhang San', '2017-12-12 12 20')

(2) 'Wang Wu', '2017-12-12 12 20')

On duplicate key update

Author=values (Author)

CreatedTime=values (CreatedTime)

UpdatedTime=values (UpdatedTime)

The difference between replace into and insert into on duplicate key update is that:

The essence of the replace into operation is to delete the duplicate records before insert. If not all the updated fields will set the missing fields to the default values, you should be careful with this, otherwise it is no joke to accidentally empty a large amount of data.

Insert into is only a update duplicate record and does not change other fields.

3. Create a temporary table, update the temporary table first, and then update from the temporary table

Create temporary table tmp (

Id int (4) primary key

Dr varchar (50)

);

Insert into tmp values (0Magneticgone`), (1recordingxx'),... (mrecalogonyy')

Update

T_student, tmp

Set

T_student.dr=tmp.dr

Where

T_student.id=tmp.id

Note: this method requires the user to have create permissions on the temporary table.

4. Use the statements included in mysql to build batch updates

Batch implementation of mysql can be implemented with a few tricks:

UPDATE t_stuent

SET name= CASE id

WHEN 1 THEN 'Zhao Liu'

WHEN 2 THEN'Ma San'

WHEN 3 THEN 'Wang Wu'

END

WHERE id IN (1, 2, 3)

This sql means to update the dingdan field. If id=1, the value of dingdan is 3, and if id=2, the value of dingdan is 4.

The where part does not affect the execution of the code, but it will improve the efficiency of sql execution. Make sure that the sql statement executes only the number of rows that need to be modified, where there are only three pieces of data to update, while the where clause ensures that only three rows of data are executed.

Example: UPDATE t_student

SET Author = CASE id

WHEN 1 THEN 'Huang Feihong'

WHEN 2 THEN 'Fang Shiyu'

WHEN 3 THEN 'Hong Xiguan'

END

WHERE id IN (1, 2, 3)

If you update more than one value, you only need to modify it slightly:

UPDATE categories

SET dingdan = CASE id

WHEN 1 THEN 3

WHEN 2 THEN 4

WHEN 3 THEN 5

END

Title = CASE id

WHEN 1 THEN 'New Title 1'

WHEN 2 THEN 'New Title 2'

WHEN 3 THEN 'New Title 3'

END

WHERE id IN (1, 2, 3)

Example: UPDATE book

SET Author = CASE id

WHEN 1 THEN 'Huang Feihong 2'

WHEN 2 THEN 'Fang Shiyu 2'

WHEN 3 THEN 'Hong Xiguan 2'

END

Code = CASE id

WHEN 1 THEN 'HFH2'

WHEN 2 THEN 'FSY2'

WHEN 3 THEN 'HXG2'

END

WHERE id IN (1, 2, 3)

About which batch operation statements in mysql are shared here, I hope the above content can be of some help to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.