An example of mysqlinsert statement operation

Insert syntax INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [(col_name,...)] VALUES ({expr | DEFAULT },...), (...),... [ONDUPLICATEKEYUPDATEcol_nameexpr,...] or: INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORI

Insert syntax 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_PRIORI

Insert syntax

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:

Insert into tbl_name () VALUES ();

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

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

Insert multiple data entries in batches

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.

Insert into worker (name) values ('Tom ');
Insert into worker (name) values ('Tom '), ('Paul ');

Use set to insert data

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:

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

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.

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

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.

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.