Test the use of replace into in each scene

MySQL version: 5.6.24

I. scene One: Replace into operation on a table without a primary key or a unique key

CREATE TABLE ' Student ' (
' SID ' varchar (6) Not NULL,
' Name ' varchar ' not NULL DEFAULT ',
' class ' varchar 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 ',), (' 0002 ', ' B ', ' 1 ',), (' 0003 ', ' C ', ' 2 ', 78), (' 0004 ', ' d ', ' 2 ', C, (' 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 is the same as insert into, inserting new records directly into the table.

Two. Scenario two: Replace into operation with primary key table
CREATE TABLE ' Student ' (
' ID ' int (a) unsigned not NULL auto_increment,
' SID ' varchar (6) Not NULL,
' Name ' varchar ' not NULL DEFAULT ',
' class ' varchar 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 ',), (' 0002 ', ' B ', ', ' 1 ', (' 0003 ', ' C ', ' 2 ',), (' 0004 ', ' d ', ' 2 ', '), (' 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 is the same as insert into, inserting new records directly into the table.

three. Scene three: Replace into operations on tables with unique keys

CREATE TABLE ' student ' (
  ' sid ' varchar (6) NOT NULL,
  ' name ' varchar (TEN) NOT null DEFAULT ',
& nbsp ' class ' varchar NOT null default ',
  ' score ' int (3) is not null default ' 0 ',
 unique (' Sid ')
) EN Gine=innodb DEFAULT Charset=utf8;
(product) root@localhost [test]> INSERT into student (Sid,name,class,score) value (' 0001 ', ' A ', ' 1 ', 56), (' 0002 ', ' B ', ' 1 ', 0003, (' 0004 ', ' C ', ' 2 ', '), (' d ', ', ' 2 ', ', '), (' 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)

The Replace operation deletes the first row of ' 0001 ' records and inserts a new record, because the record ' 0001 ' is repeated with replace into.

Four. Scenario four: Replace into operations on tables with primary keys and unique keys

CREATE TABLE ' student ' (
' id ' int () unsigned not null auto_increment,
  ' sid ' varchar (6) NOT NULL,
& nbsp ' Name ' varchar ' NOT null default ',
  ' class ' varchar ' (a) 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 ',), (' 0002 ', ' B ', ', ' 1 ', (' 0003 ', ' C ', ' 2 ',), (' 0004 ', ' d ', ' 2 ', '), (' 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)
The Replace operation will delete the first row of ' 0001 ' records and insert a new record, noting the change in the value of the original id=1, which is deleted, and the ID value of the new inserted record is 6, because the record ' 0001 ' is repeated with replace into.

so in the case of a replace into operation on a table with a self-added primary key and a unique key, there are the following problems:

1. The new record differs from the primary key value of the old record, so any association with the old data primary key ID in the other table will be corrupted.
2. Frequent replace into results in a rapid increase in the value of the primary key of the new record. When the maximum is reached, the data is too large to overflow, and no new records can be inserted.

Five. Scene five: Insert Into+on DUPLICATE key Update operation on table with primary key and unique key
(product) root@localhost [test]> CREATE TABLE ' student ' (
-> ' id ' int (a) unsigned not NULL auto_increment,
-> ' sid ' varchar (6) Not NULL,
-> ' name ' varchar ' not NULL DEFAULT ',
-> ' class ' varchar 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 ', 0003, (' 0004 ', ' C ', ' 2 ', '), (' d ', ', ' 2 ', ', '), (' 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 ', $) 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)

With the Insert Into+on duplicate key Update operation solves the problem encountered in scenario four, in view of this, many scenarios that use replace into actually need insert into ... on duplicate key update,
With the correct understanding of the replace into behavior and side effects, use replace into as a precaution.