分类： Mysql/postgreSQL
2008-03-12 17:25:25
Replace into VS Insert into …on duplicate key
CREATE TABLE `test` (
`id` tinyint(3) unsigned NOT NULL auto_increment,
`name` char(10) NOT NULL default '',
`dept` char(10) NOT NULL default '',
`age` tinyint(3) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `uni_key` (`name`)
) ENGINE=MyI
mysql> select * from test;
+----+---------+------------+-----+
| id | name | dept | age |
+----+---------+------------+-----+
| 1 | wang | IT | 30 |
| 2 | hong | Accounting | 20 |
| 3 | gang | Sales | 40 |
| 4 | raymond | Service | 20 |
+----+---------+------------+-----+
4 rows in set (0.00 sec)
mysql> replace into test (name,dept,age) values('gang','IT',25);
Query OK, 2 rows affe
mysql> select * from test;
+----+---------+------------+-----+
| id | name | dept | age |
+----+---------+------------+-----+
| 1 | wang | IT | 30 |
| 2 | hong | Accounting | 20 |
| 5 | gang | IT | 25 |
| 4 | raymond | Service | 20 |
+----+---------+------------+-----+
4 rows in set (0.00 sec)
mysql> replace into test (name,dept,age) values('test','IT',25);
Query OK, 1 row affe
mysql> select * from test;
+----+---------+------------+-----+
| id | name | dept | age |
+----+---------+------------+-----+
| 1 | wang | IT | 30 |
| 2 | hong | Accounting | 20 |
| 5 | gang | IT | 25 |
| 4 | raymond | Service | 20 |
| 6 | test | IT | 25 |
+----+---------+------------+-----+
5 rows in set (0.00 sec)
mysql> replace into test (name,dept) values('hong','Sales');
Query OK, 2 rows affe
mysql> select * from test;
+----+---------+---------+-----+
| id | name | dept | age |
+----+---------+---------+-----+
| 1 | wang | IT | 30 |
| 7 | hong | Sales | 0 |
| 5 | gang | IT | 25 |
| 4 | raymond | Service | 20 |
| 6 | test | IT | 25 |
+----+---------+---------+-----+
5 rows in set (0.00 sec)
Replace:
当没有key冲突时，replace相当于普通的insert.
当与key冲突时，replace覆盖相关字段，同时auto_increment累加，其它字段填充默认值。
mysql> insert into test (name,dept,age) values('hong','Testing',24)
-> on duplicate key update age=age+1;
Query OK, 2 rows affe
mysql> select * from test;
+----+---------+---------+-----+
| id | name | dept | age |
+----+---------+---------+-----+
| 1 | wang | IT | 30 |
| 7 | hong | Sales | 1 |
| 5 | gang | IT | 25 |
| 4 | raymond | Service | 20 |
| 6 | test | IT | 25 |
+----+---------+---------+-----+
5 rows in set (0.00 sec)
mysql> insert into test (name,dept,age) values('hong','Manager',24)
on duplicate key update age=100;
Query OK, 2 rows affe
mysql> select * from test;
+----+---------+---------+-----+
| id | name | dept | age |
+----+---------+---------+-----+
| 1 | wang | IT | 30 |
| 7 | hong | Sales | 100 |
| 5 | gang | IT | 25 |
| 4 | raymond | Service | 20 |
| 6 | test | IT | 25 |
+----+---------+---------+-----+
5 rows in set (0.00 sec)
Insert into …on duplicate key:
当与key冲突时，只update相应字段值。
