mysql 批量更新

今天有同事说他有批量更新的需求，一次更新1000条左右数据，问我有没有好的办法。

他自己先用了replace语句发现，需要更新的字段更新了，但是其他字段变成默认值了。

先看看replace（同事自己用的这个想实现批量更新，发现有问题）:

mysql> 
mysql> create table t1(
id int,
name1 varchar(30) default 'name1',
name2 varchar(30) default 'name2',
name3 varchar(30) default 'name3',
primary key (id)
)engine=innodb charset=utf8;

mysql> replace into t1(id,name1,name2,name3) values(1,'a','b','c');
Query OK, 1 row affected (0.08 sec)

mysql> 
mysql> select * from t1;
+----+-------+-------+-------+
| id | name1 | name2 | name3 |
+----+-------+-------+-------+
|  1 | a     | b     | c     |
+----+-------+-------+-------+
1 row in set (0.00 sec)
mysql> replace into t1(id,name1,name2) values(1,'a','bb');
Query OK, 2 rows affected (0.09 sec)

mysql> 
mysql> select * from t1;
+----+-------+-------+-------+
| id | name1 | name2 | name3 |
+----+-------+-------+-------+
|  1 | a     | bb    | name3 |
+----+-------+-------+-------+
1 row in set (0.00 sec)

mysql> 

看到没，他只想更新name1,name2结果name3是变成默认值了，所以replace不能做批量更新，除非你replace包含所有字段。

看看insert into ... on duplicate key update来实现批量更新（满足需求）：

mysql> select * from t1;
+----+-------+-------+-------+
| id | name1 | name2 | name3 |
+----+-------+-------+-------+
|  1 | a     | bb    | name3 |
|  2 | a2    | b2    | c2    |
|  3 | a3    | b3    | c3    |
+----+-------+-------+-------+
3 rows in set (0.00 sec)

mysql> insert into t1(id,name1,name2)
 values(1,'a1','b1'),(2,'a22','b22'),(3,'a33','b33')  
on duplicate key update name1=values(name1),name2=values(name2);
Query OK, 6 rows affected (0.08 sec)
Records: 3  Duplicates: 3  Warnings: 0

mysql> 
mysql> select * from t1;
+----+-------+-------+-------+
| id | name1 | name2 | name3 |
+----+-------+-------+-------+
|  1 | a1    | b1    | name3 |
|  2 | a22   | b22   | c2    |
|  3 | a33   | b33   | c3    |
+----+-------+-------+-------+
3 rows in set (0.00 sec)