测试各场景下replace into的用法

mysql版本：5.6.24

一.场景一：对没有主键或唯一键的表进行replace into操作

CREATE TABLE `student` (
  `sid` varchar(6) NOT NULL,
  `name` varchar(10) NOT NULL DEFAULT '',
  `class` varchar(10) NOT NULL DEFAULT '',
  `score` int(3) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into student(sid,name,class,score) value('0001','a','1',56),('0002','b','1',61),('0003','c','2',78),('0004','d','2',45),('0005','e','3',76);

(product)root@localhost [test]> select * from student;
+------+------+-------+-------+
| sid  | name | class | score |
+------+------+-------+-------+
| 0001 | a    | 1     |    56 |
| 0002 | b    | 1     |    61 |
| 0003 | c    | 2     |    78 |
| 0004 | d    | 2     |    45 |
| 0005 | e    | 3     |    76 |
+------+------+-------+-------+
5 rows in set (0.00 sec)

(product)root@localhost [test]> replace into student (sid,name,class,score) value('0001','f','3',66);
Query OK, 1 row affected (0.00 sec)

(product)root@localhost [test]>  select * from student;
+------+------+-------+-------+
| sid  | name | class | score |
+------+------+-------+-------+
| 0001 | a    | 1     |    56 |
| 0002 | b    | 1     |    61 |
| 0003 | c    | 2     |    78 |
| 0004 | d    | 2     |    45 |
| 0005 | e    | 3     |    76 |
| 0001 | f    | 3     |    66 |
+------+------+-------+-------+
6 rows in set (0.00 sec)

replace into 相当于insert into一样，将新记录直接插入到表中。

二.场景二：对有主键表进行replace into操作
CREATE TABLE `student` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sid` varchar(6) NOT NULL,
  `name` varchar(10) NOT NULL DEFAULT '',
  `class` varchar(10) NOT NULL DEFAULT '',
  `score` int(3) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

(product)root@localhost [test]> insert into student(sid,name,class,score) value('0001','a','1',56),('0002','b','1',61),('0003','c','2',78),('0004','d','2',45),('0005','e','3',76);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

(product)root@localhost [test]> select * from student;
+----+------+------+-------+-------+
| id | sid  | name | class | score |
+----+------+------+-------+-------+
|  1 | 0001 | a    | 1     |    56 |
|  2 | 0002 | b    | 1     |    61 |
|  3 | 0003 | c    | 2     |    78 |
|  4 | 0004 | d    | 2     |    45 |
|  5 | 0005 | e    | 3     |    76 |
+----+------+------+-------+-------+
5 rows in set (0.00 sec)

(product)root@localhost [test]> replace into student (sid,name,class,score) value('0001','f','3',66);
Query OK, 1 row affected (0.00 sec)

(product)root@localhost [test]> select * from student;
+----+------+------+-------+-------+
| id | sid  | name | class | score |
+----+------+------+-------+-------+
|  1 | 0001 | a    | 1     |    56 |
|  2 | 0002 | b    | 1     |    61 |
|  3 | 0003 | c    | 2     |    78 |
|  4 | 0004 | d    | 2     |    45 |
|  5 | 0005 | e    | 3     |    76 |
|  6 | 0001 | f    | 3     |    66 |
+----+------+------+-------+-------+
6 rows in set (0.00 sec)

replace into 相当于insert into一样，将新记录直接插入到表中。

三.场景三：对有唯一键的表进行replace into操作

CREATE TABLE `student` (
  `sid` varchar(6) NOT NULL,
  `name` varchar(10) NOT NULL DEFAULT '',
  `class` varchar(10) NOT NULL DEFAULT '',
  `score` int(3) NOT NULL DEFAULT '0',
 unique (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
(product)root@localhost [test]> insert into student(sid,name,class,score) value('0001','a','1',56),('0002','b','1',61),('0003','c','2',78),('0004','d','2',45),('0005','e','3',76);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

(product)root@localhost [test]>  select * from student;
+------+------+-------+-------+
| sid  | name | class | score |
+------+------+-------+-------+
| 0001 | a    | 1     |    56 |
| 0002 | b    | 1     |    61 |
| 0003 | c    | 2     |    78 |
| 0004 | d    | 2     |    45 |
| 0005 | e    | 3     |    76 |
+------+------+-------+-------+
5 rows in set (0.00 sec)

(product)root@localhost [test]> replace into student (sid,name,class,score) value('0001','f','3',66);
Query OK, 2 rows affected (0.00 sec)

(product)root@localhost [test]> select * from student;
+------+------+-------+-------+
| sid  | name | class | score |
+------+------+-------+-------+
| 0001 | f    | 3     |    66 |
| 0002 | b    | 1     |    61 |
| 0003 | c    | 2     |    78 |
| 0004 | d    | 2     |    45 |
| 0005 | e    | 3     |    76 |
+------+------+-------+-------+
5 rows in set (0.00 sec)

由于用replace into插入的记录'0001'重复，所以replace操作会先将第一行'0001'的记录删除，再插入新记录。

四.场景四：对有主键和唯一键的表进行replace into操作

CREATE TABLE `student` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `sid` varchar(6) NOT NULL,
  `name` varchar(10) NOT NULL DEFAULT '',
  `class` varchar(10) NOT NULL DEFAULT '',
  `score` int(3) NOT NULL DEFAULT '0',
 primary key(`id`),
 unique (`sid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
(product)root@localhost [test]> insert into student(sid,name,class,score) value('0001','a','1',56),('0002','b','1',61),('0003','c','2',78),('0004','d','2',45),('0005','e','3',76);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

(product)root@localhost [test]> select * from student;
+----+------+------+-------+-------+
| id | sid  | name | class | score |
+----+------+------+-------+-------+
|  1 | 0001 | a    | 1     |    56 |
|  2 | 0002 | b    | 1     |    61 |
|  3 | 0003 | c    | 2     |    78 |
|  4 | 0004 | d    | 2     |    45 |
|  5 | 0005 | e    | 3     |    76 |
+----+------+------+-------+-------+
5 rows in set (0.00 sec)

(product)root@localhost [test]> replace into student (sid,name,class,score) value('0001','f','3',66);
Query OK, 2 rows affected (0.00 sec)

(product)root@localhost [test]> select * from student;
+----+------+------+-------+-------+
| id | sid  | name | class | score |
+----+------+------+-------+-------+
|  2 | 0002 | b    | 1     |    61 |
|  3 | 0003 | c    | 2     |    78 |
|  4 | 0004 | d    | 2     |    45 |
|  5 | 0005 | e    | 3     |    76 |
|  6 | 0001 | f    | 3     |    66 |
+----+------+------+-------+-------+
5 rows in set (0.00 sec)
由于用replace into插入的记录'0001'重复，所以replace操作会先将第一行'0001'的记录删除，再插入新记录，注意自增主键值的变化，原来的id=1那行记录被删除了，新插入记录的id值为6。

所以在对有自增主键和唯一键存在的表进行replace into操作时，会有如下问题：

1.新记录与老记录的主键值不同，所以其他表中所有与本表老数据主键id建立的关联全部会被破坏。
2.频繁的replace into会造成新记录的主键的值迅速增大。当达到最大值后就会因为数据太大溢出，而没法再插入新记录。

五.场景五：对有主键和唯一键的表进行insert into+ON DUPLICATE KEY UPDATE操作
(product)root@localhost [test]> CREATE TABLE `student` (
    -> `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `sid` varchar(6) NOT NULL,
    ->   `name` varchar(10) NOT NULL DEFAULT '',
    ->   `class` varchar(10) NOT NULL DEFAULT '',
    ->   `score` int(3) NOT NULL DEFAULT '0',
    ->  primary key(`id`),
    ->  unique (`sid`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

(product)root@localhost [test]> insert into student(sid,name,class,score) value('0001','a','1',56),('0002','b','1',61),('0003','c','2',78),('0004','d','2',45),('0005','e','3',76);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

(product)root@localhost [test]>  select * from student;
+----+------+------+-------+-------+
| id | sid  | name | class | score |
+----+------+------+-------+-------+
|  1 | 0001 | a    | 1     |    56 |
|  2 | 0002 | b    | 1     |    61 |
|  3 | 0003 | c    | 2     |    78 |
|  4 | 0004 | d    | 2     |    45 |
|  5 | 0005 | e    | 3     |    76 |
+----+------+------+-------+-------+
5 rows in set (0.00 sec)

(product)root@localhost [test]> insert into student (sid,name,class,score) value('0001','f','3',66) ON DUPLICATE KEY UPDATE name=values(name),class=values(class),score=values(score);
Query OK, 2 rows affected (0.00 sec)

(product)root@localhost [test]> select * from student;
+----+------+------+-------+-------+
| id | sid  | name | class | score |
+----+------+------+-------+-------+
|  1 | 0001 | f    | 3     |    66 |
|  2 | 0002 | b    | 1     |    61 |
|  3 | 0003 | c    | 2     |    78 |
|  4 | 0004 | d    | 2     |    45 |
|  5 | 0005 | e    | 3     |    76 |
+----+------+------+-------+-------+
5 rows in set (0.00 sec)

用insert into+on duplicate key update操作解决了场景四中遇到的问题，鉴于此，很多使用replace into 的场景，实际上需要的是insert into… on duplicate key update，
在正确理解replace into行为和副作用的前提下，谨慎使用replace into。