Insert into, replace into, insert ignore, replaceignore

Insert into, replace into, insert ignore, replaceignore

I recently discovered that mysql insert statements have so many usage cases. Here I will share with you.

① Insert:

Insert into table_name values ();

Insert into table_name (column) values ();

Insert into table_name values (select (column) from table_name2 );

Note the following when inserting data:

If a primary key conflict occurs (that is, when the inserted primary key already exists in the table), the system reports an error.

② Replace:

Replace into is similar to insert into. The difference is that replace into first tries to insert data into the table. if this row of data already exists in the table (determined based on the primary key or unique index), delete the row of data and insert new data. 2. Otherwise, insert new data directly.
Note that the table to which data is inserted must have a primary key or a unique index! Otherwise, replace into inserts data directly, which leads to duplicate data in the table.

③ Insert ignore

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

Is it interesting...

Let's create an experiment.

Create table test (

'Id' int (11) not null auto_increment comment 'Primary key ',

'Name' varchar (20) not null comment' name ',

Primary key ('id ')

) ENGINE = InnoDB default charset utf8 comment = 'test table ';

Then we insert several pieces of data into it.

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 reports an error indicating a primary key conflict.

If the following statement is executed:

Insert ignore into test (id, name) values (1, 'vein11 ');

The system does not report errors. Ignore them directly.

Replace into test (id, name) values (1, 'vein11 ');

The system is successfully inserted and the table record is changed

1, vein11.

Edited by VeinQueen

INSERT

First query and then insert

Mysql insertion and update

As far as I know, you cannot use an SQL statement to solve this problem. The score can be written,