What is the difference between insert ignore, insert and replace in mysql-Mysql Tutorial-php.cn

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

PHPz

Release： 2023-05-29 16:40:06

forward

1341 people have browsed it

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)
)

Copy after login

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 |
+----+--------+------+

Copy after login

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'

Copy after login

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 |
+----+--------+------+

Copy after login

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 |
+----+--------+------+

Copy after login

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)

Copy after login

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 |
+----+--------+------+

Copy after login

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!