Insert Into,replace into, insert ignore of MySQL database insertion statement

Only recently found that the MySQL INSERT statement has so many uses, here to share.

① about INSERT INTO:

INSERT INTO table_name values ();

INSERT INTO table_name (column) values ();

INSERT INTO TABLE_NAME VALUES (select (column) from table_name2);

The only thing to be aware of here is:

If a primary key conflict occurs (that is, the inserted primary key already exists in the table), the system errors.

②replace into:

Replace into is similar to the INSERT INTO function, except that replace into first attempts to insert data into the table, 1. If you find that this row of data is already in the table (judging by a primary key or a unique index), the row data is first deleted and then the new data is inserted. 2. Otherwise, insert the new data directly.
Note that the table in which the data is inserted must have a primary key or a unique index! Otherwise, replace into will insert the data directly, which will result in duplicate data appearing in the table.

③insert Ignore into

The main difference between insert ignore into and insert into is that when a primary key conflict occurs, the system does not make an error and skips the insertion of the entry directly.

The feeling is not very interesting ...

Let's do an experiment.

CREATE TABLE Test (

' id ' int (one) not null auto_increment comment ' primary key ',

' Name ' varchar (a) NOT null comment ' name ',

Primary KEY (' ID ')

) Engine=innodb DEFAULT CHARSET UTF8 comment= ' Test table ';

Then we insert a few data in.

INSERT into Test (name) VALUES (' vein ');

INSERT into Test (name) VALUES (' vein1 ');

INSERT into Test (name) VALUES (' vein2 ');

When the following statement is executed:

INSERT into Test (Id,name) VALUES (1, ' vein11 ');

The system will error, saying the primary key conflict.

If you execute the following statement:

Insert ignore into Test (Id,name) VALUES (1, ' vein11 ');

System will not error, directly ignored.

Replace into Test (id,name) VALUES (1, ' vein11 ');

The system was inserted successfully, and the modified table is recorded as

1, Vein11.

Edited by Veinqueen

Insert Into,replace into, insert ignore of MySQL database insertion statement