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

页面导航:首页 > 数据库 > Mysql > mysql一次更新多条不同的记录

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

来源: 作者: 时间:2016-02-20 09:26 【

mysql一次更新多条不同的记录最近oschina上又有朋友问到了mysql中一次更新多条不同的记录的方法,我知道的方法有两种,使用on duplicate key update语法和使用 replace into语法。 这两个语法都
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.
 
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
> 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主从同步时同样有效。
 
Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<