Description of mysql insert statements and insert statements

Description of mysql insert statements and insert statements

Insert syntax

Copy codeThe Code is 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 both the column list and the VALUES list are empty, INSERT creates a row, and each column is set to the default value:

Copy codeThe Code is as follows:
Insert into tbl_name () VALUES ();

Assume that the worker table only has name and email, and a data record is inserted.

Copy codeThe Code is as follows:
Insert into worker values ("tom", "tom@yahoo.com ");

Insert multiple data entries in batches

Copy codeThe Code is as follows:
Insert into worker values ('Tom ', 'Tom @ yahoo.com'), ('Paul ', 'Paul @ yahoo.com ');

The column to be assigned a value, and then the inserted data of the value is listed.

Copy codeThe Code is as follows:
Insert into worker (name) values ('Tom ');
Insert into worker (name) values ('Tom '), ('Paul ');

Use set to insert data

Copy codeThe Code is as follows:
Insert into worker set name = 'Tom ';

The Untitled rows in the SET clause are all given a default value. This form of INSERT statements cannot be used to INSERT multiple rows.

An expression can reference any column previously set in a value table. For example:

Copy codeThe Code is as follows:
Insert into tbl_name (col1, col2) VALUES (15, col1 * 2 );
-- But this is not the case.
Insert into tbl_name (col1, col2) VALUES (col2 * 2, 15 );

Use INSERT... SELECT statement insert selected rows from other tables

Copy codeThe Code is 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 target table of the INSERT statement cannot appear in the FROM clause of the SELECT query.

ON DUPLICATE KEY UPDATE

If you specify on duplicate key update and insert a row, DUPLICATE values will appear in a UNIQUE index or primary key, the old row will be updated.

Copy codeThe Code is as follows:
-- Assume that a and B are the unique indexes. If the table does not have rows like 1 and 2, data is inserted normally. In case of conflict, the value of column c is updated.
Insert into table (a, B, c) VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE c = 3;
-- Or
Insert into table (a, B, c) VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE c = values (c );
-- Reference 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 );
Insert NULL to a column defined as not null. For a multi-row INSERT statement or insert into... SELECT statement, the column is set to an implicit default value based on the column data type. For numeric type, the default value is 0. For string type, the default value is null string (''). For date and time types, the default value is" zero.

Insert into... SELECT ON DUPLICATE KEY UPDATE

Copy codeThe Code is 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 very useful if your client cannot wait for the completion of INSERT. When a client uses insert delayed, it will immediately get a confirmation from the server. And the row is queued. When the table is not used by other threads, the row is inserted.

Another important benefit of using insert delayed is that inserts from many clients are centralized and written into a block. This is much faster than executing many independent inserts.

Copy codeThe Code is as follows:
Insert delayed into worker (name) values ('Tom '), ('Paul ');

There are some restrictions when using DELAYED:

1. insert delayed is only applicable to MyISAM, MEMORY, and ARCHIVE tables. For MyISAM tables, if there is no idle block in the middle of the data file, SELECT and INSERT statements are supported at the same time. In these cases, you do not need to use insert delayed for MyISAM.

2. insert delayed should be used only for the INSERT statement that specifies the Value List. The server ignores the DELAYED and insert delayed... on duplicate update statements used for insert delayed... SELECT statements.

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

4. For SELECT statements, DELAYED rows are invisible until these rows are indeed inserted.

5. DELAYED is ignored in the slave replication server, because DELAYED does not generate data different from the master server in the slave server.