Mysql updates multiple records at a time _ MySQL

Mysql updates multiple different records at a time, bitsCN.com

Mysql updates multiple different records at a time

Recently, a friend in oschina asked me how to update multiple different records in mysql. I know two methods: on duplicate key update syntax and replace into syntax.

Both of these syntaxes require support for primary key indexes or unique indexes. The following is an example.

Table structure and data for testing

1

Create table 'T '(

2

'Id' int (11) not null AUTO_INCREMENT,

3

'C1 'varchar (50) not null default '',

4

'C2 'varchar (50) not null default '',

5

'C3 'varchar (50) not null default '',

6

Primary key ('id '),

7

Unique key 'C1' ('C1 ')

8

ENGINE = InnoDB AUTO_INCREMENT = 125 default charset = utf8;

9

Insert into t values (1, 2, 3, 4), (5, 6, 7, 8 );

On duplicate key update syntax

Official description http://docs.oracle.com/cd/E17952_01/refman-5.1-en/insert-on-duplicate.html for on duplicate key update syntax

1

If you specify on duplicate key update, and a row is inserted that wowould cause a duplicate value in a UNIQUE index or primary key, MySQL performs an UPDATE of the old row.

It performs the insert operation first. when a column with a primary key or unique index conflicts, it performs the update operation on the conflicting row to update the specified columns in the SQL statement. If no columns conflict, this syntax works the same as the simple insert into syntax. For example:

01

Mysql> insert into t (id, c1, c2) values (1, 20, 30), (5, 60, 70) on duplicate key update c1 = values (c1 ), c2 = values (c2 );

02

Query OK, 4 rows affected (0.00 sec)

03

Records: 2 Duplicates: 2 Warnings: 0

04

05

Mysql> select * from t;

06

+ ---- +

07

| Id | c1 | c2 | c3 |

08

+ ---- +

09

| 1 | 20 | 30 | 4 |

10

| 5 | 60 | 70 | 8 |

11

+ ---- +

12

2 rows in set (0.00 sec)

The result is that the column c1 and c2 are updated, and the column c3 is not changed.

Replace into syntax

Replace into syntax official description http://docs.oracle.com/cd/E17952_01/refman-5.5-en/replace.html

1

REPLACE works exactly like INSERT, before t that if an old row in the table has the same value as a new row for a primary key or a UNIQUE index, the old row is deleted before the new row is inserted.

The replace and insert operations are identical. The difference is that when a column with a primary key or a unique index conflicts, before the insert operation, the delete operation is performed on the row of data. The result is that columns with no specified values in this row will be updated with the default value of the cost column. If all columns do not conflict, this syntax works the same as the simple insert into syntax. For example:

Completely replace two rows of records

01

Mysql> replace into t (id, c1) values (1,200), (5,600 );

02

Query OK, 4 rows affected (0.00 sec)

03

Records: 2 Duplicates: 2 Warnings: 0

04

05

Mysql> select * from t;

06

+ ---- + ----- + ---- +

07

| Id | c1 | c2 | c3 |

08

+ ---- + ----- + ---- +

09

| 1 | 200 |

10

| 5 | 600 |

11

+ ---- + ----- + ---- +

12

2 rows in set (0.00 sec)

Replace records with unique indexes without ID

01

Mysql> replace into t (c1, c2) values );

02

Query OK, 4 rows affected (0.00 sec)

03

Records: 2 Duplicates: 2 Warnings: 0

04

05

Mysql> select * from t;

06

+ ----- + ---- +

07

| Id | c1 | c2 | c3 |

08

+ ----- + ---- +

09

| 127 | 200 | 3 |

10

| 128 | 600 | 7 |

11

+ ----- + ---- +

12

2 rows in set (0.00 sec)

The result is that the id is also replaced.

When a unique index is used and duplicate values are added to the column of the unique index

01

Mysql> replace into t (id, c1) values (127,200), (128,200 );

02

Query OK, 5 rows affected (0.00 sec)

03

Records: 2 Duplicates: 3 Warnings: 0

04

05

Mysql> select * from t;

06

+ ----- + ---- +

07

| Id | c1 | c2 | c3 |

08

+ ----- + ---- +

09

| 128 | 200 |

10

+ ----- + ---- +

11

1 row in set (0.00 sec)

Why is there only one record left? Before the row (127,200) is inserted, the row with id = 127 or c1 = 200 is deleted and then inserted. Before this row is inserted (128,200), rows with id = 128 or c1 = 200 will be deleted. in the row inserted above, c1 = 200, therefore, the previous row was deleted and only one row was left.

How many records can be updated at a time?

Mysql does not have a limit on the number of update records at a time, but it has a limit on the length of SQL statements. To execute an excessively long SQL statement, you need to adjust the configuration parameter max_allowed_packet.

Official description http://docs.oracle.com/cd/E17952_01/refman-5.5-en/replication-features-max-allowed-packet.html for max_allowed_packet parameters

1

Max_allowed_packet sets an upper limit on the size of any single message between the MySQL server and clients, including replication slaves.

This parameter specifies the maximum length of a single message before the mysql server and client, which is also valid during mysql master-slave synchronization.

BitsCN.com