Parse the insert operation in mysql

Syntax of insert

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

    [INTO] tbl_name [(col_name,...)]

    VALUES ({expr | DEFAULT},...),(...),...

    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

或：
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

    [INTO] tbl_name

    SET col_name={expr | DEFAULT}, ...

    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

或：
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]

    [INTO] tbl_name [(col_name,...)]

    SELECT ...

    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]

If the column list and VALUES list are both empty lists, INSERT will create a row with each column Set to the default value:

INSERT INTO tbl_name () VALUES();

Assume that the worker table only has name and email, insert one piece of data

insert into worker values(“tom”,”tom@yahoo.com”);

Insert multiple pieces of data in batches

insert into worker values(‘tom','tom@yahoo.com'),(‘paul','paul@yahoo.com');

Give the column to be assigned a value, and then list the valueInsert data

insert into worker (name) values (‘tom');

insert into worker (name) values (‘tom'), (‘paul');

Use set to insert data

insert into worker set name='tom';

Unnamed rows in the SET clause are assigned a default value. Multiple rows cannot be inserted using this form of INSERT statement.
An expression can reference any column previously set in a value table, for example:

INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);

--但不能这样

INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15);

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

insert into tbl_name1(col1,col2) select col3,col4 from tbl_name2;

--如果每一列都有数据

insert into tbl_name1 select col3,col4 from tbl_name2;

The query cannot contain an ORDER BY clause, and the destination table of the INSERT statement cannot appear in the FROM clause of the SELECT query part.
ON DUPLICATE KEY UPDATE
If you specify ON DUPLICATE KEY UPDATE, and inserting rows would result in duplicate values ​​in a UNIQUEindex or PRIMARY KEY, perform the old row UPDATE.

--假设a,b为唯一索引,表table没有1,2这样的行是正常插入数据，冲突时，更新c列的值

INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=3;

--或者是

INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=values(c);

--引用其他列更新冲突的行

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

 向一个已定义为NOT NULL的列中插入NULL。对于一个多行INSERT语句或INSERT INTO...SELECT语句，根据列数据的类型，列被设置为隐含的默认值。对于数字类型，默认值为0；对于字符串类型，默认值为空字符串('')；对于日期和时间类型，默认值为“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
If you This option is very useful if the client cannot wait for INSERT to complete. When a client uses INSERT DELAYED, it will immediately get a confirmation from the server. And the row is enqueued into the queue, and the row is inserted when the table is not used by other threads.
Another important benefit of using INSERT DELAYED is that inserts from many clients are gathered together and written into a block. This is much faster than performing many independent inserts.

INSERT DELAYED INTO worker (name) values (‘tom'), (‘paul');

There are some limitations when using DELAYED:
1.INSERT DELAYED only applies to MyISAM, MEMORY and ARCHIVE tables. For MyISAM tables, if there are no free blocks in the middle of the data file, both SELECT and INSERT statements are supported. In these cases, there is basically no need to use INSERT DELAYED with MyISAM.

2.INSERT DELAYED should only be used for INSERT statements that specify a list of values. The server ignores DELAYED for INSERT DELAYED...SELECT statements and DELAYED for INSERT DELAYED...ON DUPLICATE UPDATE statements.

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

4. For SELECT statements, DELAYED rows are not visible until these rows are actually inserted.

5.DELAYED is ignored in the slave replication server, because DELAYED will not produce data in the slave server that is different from the master server.

The above is the detailed content of Parse the insert operation in mysql. For more information, please follow other related articles on the PHP Chinese website!