实例演示 replace into 和 insert into ... on duplicate key update 的区别

通过实例演示 replace into 和 insert into … on duplicate key update 的异同以及对 auto_increment 的影响： 话不多说，直接上代码

# 创建一个测试表，其中列 code 上有唯一索引
# 下面的测试均是指会发生冲突的情况：
# replace into  的时候，如果 replace 语句的字段不全的话，则没出现的字段的值会被自动填充为字段类型的默认值
# insert into ... on duplicate key update 的时候，如果语句中没有指定 ID 列的值，则 ID 会增加 1，（这是很好理解的，因为你没有指定 id 的值，所以就取当前 AUTO_INCREMENT 的值）；如果指定了 ID， 则就用指定的值，当然也就不自增了

mysql> create table test (
    -> id int auto_increment primary key, 
    -> code int, 
    -> times int, 
    -> name VARCHAR(10), 
    -> unique key (code));
Query OK, 0 rows affected (0.35 sec)
mysql> 
mysql> 
mysql> INSERT INTO `test` (`code`, `times`, `name`) VALUES ('100', 1, 'wo');
Query OK, 1 row affected (0.12 sec)
mysql> 
mysql> 
mysql> select * from test;
+----+------+-------+------+
| id | code | times | name |
+----+------+-------+------+
|  1 |  100 |     1 | wo   |
+----+------+-------+------+
1 row in set (0.00 sec)
mysql> 
mysql> 
mysql> show create table test \G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` int(11) DEFAULT NULL,
  `times` int(11) DEFAULT NULL,
  `name` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)
mysql> 
mysql> 
mysql> # 该 replace 语句的字段不全，则字段 name 的值会被自动填充为字段类型的默认值 NULL
mysql> replace into test(code, times) values (100, 1);
Query OK, 2 rows affected (0.20 sec)
mysql>
mysql> select * from test                                                                                                                                                                                                                   
+----+------+-------+------+
| id | code | times | name |
+----+------+-------+------+
|  2 |  100 |     1 | NULL |
+----+------+-------+------+
1 row in set (0.00 sec)
mysql> 
mysql> show create table test \G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` int(11) DEFAULT NULL,
  `times` int(11) DEFAULT NULL,
  `name` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)
mysql> 
mysql> 
mysql> 
mysql>--------------紧接着测试 insert into ... on duplicate key update----------------------------------
mysql> 该  insert into ... on duplicate key update 没有指定 ID 列的值，则 ID 会增加 1，
mysql> 这是很好理解的，因为你没有指定 id 的值，所以就取当前 AUTO_INCREMENT 的值
mysql>
mysql> insert  into test( code, times) values (100, 2) on duplicate key update times=times+10;
Query OK, 2 rows affected (0.19 sec)
mysql> 
mysql> 
mysql> select * from test;                                                                                                                                                                                                                  
+----+------+-------+------+
| id | code | times | name |
+----+------+-------+------+
|  2 |  100 |    11 | NULL |
+----+------+-------+------+
1 row in set (0.00 sec)
mysql> 
mysql> 
mysql> show create table test \G                                                                                                                                                                                                            
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` int(11) DEFAULT NULL,
  `times` int(11) DEFAULT NULL,
  `name` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)
mysql> 
mysql> 
mysql>  该  insert into ... on duplicate key update 指定了  ID 列的值，则 ID 就不会增加 1，
mysql> insert  into test(id,  code, times) values (2, 100, 2) on duplicate key update times=times+100;
Query OK, 2 rows affected (0.06 sec)
mysql> 
mysql> select * from test;
+----+------+-------+------+
| id | code | times | name |
+----+------+-------+------+
|  2 |  100 |   111 | NULL |
+----+------+-------+------+
1 row in set (0.00 sec)
mysql> 
mysql> show create table test \G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` int(11) DEFAULT NULL,
  `times` int(11) DEFAULT NULL,
  `name` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)

mysql>