MySQL INSERT statement Operation example explain _mysql

The syntax of the insert

Copy Code code as follows:

INSERT [Low_priority | Delayed | High_priority] [IGNORE]
[Into] tbl_name [(Col_name,...)]
VALUES ({expr | DEFAULT},...), (...),...
[on DUPLICATE KEY UPDATE col_name=expr, ...]
Or:

INSERT [Low_priority | Delayed | High_priority] [IGNORE]
[Into] Tbl_name
SET col_name={expr | DEFAULT}, ...
[on DUPLICATE KEY UPDATE col_name=expr, ...]
Or:

INSERT [Low_priority | High_priority] [IGNORE]
[Into] tbl_name [(Col_name,...)]
SELECT ...
[on DUPLICATE KEY UPDATE col_name=expr, ...]

If the list of columns and values are empty, insert creates a row and each column is set to the default value:

Copy Code code as follows:

INSERT into Tbl_name () VALUES ();

If the worker table has only name and email, insert a piece of data

Copy Code code as follows:

INSERT into worker values ("Tom", "tom@yahoo.com");

BULK INSERT multiple data

Copy Code code as follows:

INSERT into worker values (' Tom ', ' tom@yahoo.com '), (' Paul ', ' paul@yahoo.com ');

Give the column to which you want to assign a value, and then list the inserted data for the value

Copy Code code as follows:

INSERT into worker (name) VALUES (' Tom ');
INSERT into worker (name) VALUES (' Tom '), (' Paul ');

Inserting data Using Set

Copy Code code as follows:

INSERT into worker set name= ' Tom ';

Unnamed rows in the SET clause are given a default value, and INSERT statements that use this form cannot insert multiple rows.

A expression can refer to any column previously set in a value table, for example:

Copy Code code as follows:

INSERT into Tbl_name (col1,col2) VALUES (15,col1*2);
--but not like that.
INSERT into Tbl_name (col1,col2) VALUES (col2*2,15);

Use Insert ... SELECT statement inserts rows selected from other tables

Copy Code code as follows:

Insert into tbl_name1 (col1,col2) select Col3,col4 from Tbl_name2;
--If each column has data
Insert INTO tbl_name1 select Col3,col4 from Tbl_name2;

The query cannot contain an ORDER BY clause, and the destination table for the INSERT statement cannot appear in the FROM clause of the SELECT query section.

On DUPLICATE KEY UPDATE

If you specify on DUPLICATE key update and the insert row causes duplicate values to occur in a unique index or primary key, the old row update is executed.

Copy Code code as follows:

--assuming A,b is a unique index, table tables do not have 1,2 such rows are normal to insert data, when conflicting, update the value of column C
INSERT into table (a,b,c) VALUES (1,2,3) on DUPLICATE KEY UPDATE c=3;
--or a
INSERT into table (a,b,c) VALUES (1,2,3) on DUPLICATE KEY UPDATE c=values (c);
--referencing other columns to update conflicting rows
INSERT into table (a,b,c) VALUES (1,2,3), (4,5,6) on DUPLICATE KEY UPDATE c=values (a) +values (b);
Inserts null into a column that has been defined as NOT NULL. For a multiple-line INSERT statement or INSERT INTO ... SELECT statement, depending on the type of column data, the column is set to the implied default value. For numeric types, the default value is 0; for string types, the default value is an empty string ('); for date and time types, the default value is ' zero '.

INSERT into ... On DUPLICATE KEY UPDATE for Select

Copy Code code as follows:

Insert into tbl_name1 (A,B,C)
Select Col1,col2,col3 from Tbl_name2
On DUPLICATE KEY UPDATE c=values (c);

INSERT delayed

This option is useful if your client cannot wait for the insert to complete, and when a client uses insert delayed, it immediately obtains a confirmation from the server. and rows are queued and the row is inserted when the table is not being used by another thread.

Another important benefit of using insert delayed is that inserts from many clients are lumped together and written into a block. This is much faster than performing many separate inserts.

Copy Code code as follows:

INSERT delayed into worker (name) VALUES (' Tom '), (' Paul ');

There are some limitations when using delayed:

The 1.INSERT delayed only applies to MyISAM, memory and archive tables. For MyISAM tables, if there is no free block in the middle of the data file, both SELECT and INSERT statements are supported. In these cases, you basically do not need to use insert delayed for MyISAM.

2.INSERT delayed should be used only for INSERT statements that specify a list of values. Server ignored for insert delayed ... Delayed and insert delayed of the SELECT statement ... On the delayed of the DUPLICATE UPDATE statement.

3. Because the statement returns immediately before the row is inserted, you cannot use last_insert_id () to get the auto_increment value. The auto_increment value may be generated by the statement.

4. For SELECT statements, the delayed row is not visible until the rows are actually inserted.

The

5.DELAYED is ignored in the slave replication server because delayed does not produce data that is not the same as the primary server in the secondary server.