What is the difference between insert ignore, insert and replace in mysql

The difference between insert ignore, insert and replace

Command	Exists	Does not exist	Example
insert	Error reporting	insert	insert into names(name, age) values(“ Xiao Ming", 23);
insert ignore	ignore	insert	insert ignore into names(name, age) values ("Xiao Ming", 24);
replace	replace	insert	replace into names(name, age) values ("Xiao Ming", 25);

Table requirements: PrimaryKey, or unique index

Result: The table id will be incremented

Test code

Create table

CREATE TABLE names(
    id INT(10) PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) UNIQUE,
    age INT(10)
)

Insert data

mysql> insert into names(name, age) values("小明", 24);
mysql> insert into names(name, age) values("大红", 24);
mysql> insert into names(name, age) values("大壮", 24);
mysql> insert into names(name, age) values("秀英", 24);

mysql> select * from names;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 小明   |   24 |
|  2 | 大红   |   24 |
|  3 | 大壮   |   24 |
|  4 | 秀英   |   24 |
+----+--------+------+

insert

The insertion already exists, the id will be incremented, but if the insertion is unsuccessful, an error will be reported

mysql> insert into names(name, age) values("小明", 23);

ERROR 1062 (23000): Duplicate entry '小明' for key 'name'

replace

Already replaced, delete the original record, add a new record

mysql> replace into names(name, age) values("小明", 23);
Query OK, 2 rows affected (0.00 sec)

mysql> select * from names;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  2 | 大红   |   24 |
|  3 | 大壮   |   24 |
|  4 | 秀英   |   24 |
|  6 | 小明   |   23 |
+----+--------+------+

No replacement, add a new record

mysql> replace into names(name, age) values("大名", 23);
Query OK, 1 row affected (0.00 sec)

mysql> select * from names;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  2 | 大红   |   24 |
|  3 | 大壮   |   24 |
|  4 | 秀英   |   24 |
|  6 | 小明   |   23 |
|  7 | 大名   |   23 |
+----+--------+------+

insert ignore

The insertion already exists, ignore the newly inserted record, the id will be incremented, and no error will be reported

mysql> insert ignore into names(name, age) values("大壮", 25);
Query OK, 0 rows affected, 1 warning (0.00 sec)

The insertion does not exist, add a new record

mysql> insert ignore into names(name, age) values("壮壮", 25);
Query OK, 1 row affected (0.01 sec)

mysql> select * from  names;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  2 | 大红   |   24 |
|  3 | 大壮   |   24 |
|  4 | 秀英   |   24 |
|  6 | 小明   |   23 |
|  7 | 大名   |   23 |
| 10 | 壮壮   |   25 |
+----+--------+------+

The above is the detailed content of What is the difference between insert ignore, insert and replace in mysql. For more information, please follow other related articles on the PHP Chinese website!