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=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 |
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 affected (0.00 sec)
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 affected (0.00 sec)
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 affected (0.00 sec)
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累加,其它字段填充默认值,可以理解为删除重复key的记录,新插入一条记录。
mysql> insert into test (name,dept,age) values(‘hong’,'Testing’,24)
-> on duplicate key update age=age+1;
Query OK, 2 rows affected (0.00 sec)
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 affected (0.00 sec)
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相应字段值。