replace into和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=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相应字段值。

http://hi.baidu.com/jackbillow/blog/item/de9c4233bee7f3f41b4cffc8.html