Heim  >  Artikel  >  Datenbank  >  mysql一次更新多条不同的记录_MySQL

mysql一次更新多条不同的记录_MySQL

WBOY
WBOYOriginal
2016-06-01 13:32:341259Durchsuche

bitsCN.com

mysql一次更新多条不同的记录

 

最近oschina上又有朋友问到了mysql中一次更新多条不同的记录的方法,我知道的方法有两种,使用on duplicate key update语法和使用 replace into语法。 

  这两个语法都需要主键索引或唯一索引支持,下面举例说明。 

  测试用的表结构和数据

 

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 语法

  on duplicate key update 语法的官方说明http://docs.oracle.com/cd/E17952_01/refman-5.1-en/insert-on-duplicate.html

 

1

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row.

  它会先执行插入操作,碰到有主键或唯一索引的列发生冲突时,对冲突的这一行,执行update操作,更新sql语句中指定的某几列。如果所有的列都不冲突,此语法和简单的insert into语法效果一样。例如:

 

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)

  结果是c1,c2这两列被更新了,c3这一列没有变。

 

replace into 语法

  replace into 语法的官方说明http://docs.oracle.com/cd/E17952_01/refman-5.5-en/replace.html

 

1

REPLACE works exactly like INSERT, except 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.

  replace和insert所作的工作完全相同,区别是当碰到有主键或唯一索引的列发生冲突时,对冲突的这一行,在insert前会先对这行数据执行delete操作。效果是这一行中没有被指定值的列会被更新成本列的默认值,如果所有的列都不冲突,此语法和简单的insert into语法效果一样。例如: 

  完全替换两行记录

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)

 

  不使用ID,使用唯一索引来替换记录

01

mysql> replace into t (c1,c2) values(200,3),(600,7);  

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)

  效果是id也被替换掉了. 

 

  当使用唯一索引,并重且给唯一索引这一列加了重复的值时

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)

  最后为什么只剩一条记录了?插入(127,200)这一行前,会删掉id=127或c1=200的行,然后执行插入。插入(128,200)这一行前,会删掉id=128或c1=200的行,刚好前面插入的那一行中,c1=200,所以前面那一行也被删掉了,最后只留下了一行。

 

一次最多能更新多少条记录?

  mysql中没有一次更新记录数的限制,但是有sql语句长度的限制。如果需要执行超长的sql语句,需要调整max_allowed_packet这个配置参数。
 

  max_allowed_packet参数的官方说明http://docs.oracle.com/cd/E17952_01/refman-5.5-en/replication-features-max-allowed-packet.html

 

1

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

  此参数规定mysql服务端和客户端之前的单个消息最大长度,在mysql主从同步时同样有效。

 

bitsCN.com
Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Vorheriger Artikel:MySQL for mac安装_MySQLNächster Artikel:rpm安装配置mysql5.0过程_MySQL