MySQL insert operation

1. Basic grammar

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_nameSET 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, ...]

2. If both the column list and the values list are empty lists, insert creates a row and each column is set to the default value:

INSERT  into VALUES ();

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

Insert  into Values ('Tom','[email protected]');

4. Insert multiple data in bulk

Insert  into Values ('jiguohui','[email protected]'), ('Wahaha  ','[email protected] ';

5. Give the column that you want to assign, and then list the inserted data for the value

Insert  into Values ('jiguohui'); Insert  into Values ('wahaha'), ('shita');

6. Inserting data Using Set

Insert  into Set name='jgh';

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

7. An expression can refer to any column previously set in a value table, for example

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

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

Insert  into Select  from tbl_name2; -- if each column has data Insert  into Select  from Tbl_name2;

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

10. On DUPLICATE KEY UPDATE

If you specify an on DUPLICATE KEY update and the insert row causes duplicate values to appear in a unique index or primary KEY, the old line UPDATE is performed.

--Assuming that a a , B is a unique index, tables table does not have a row like this is normal insert data, when conflicting, update the value of column CINSERT  into Table(A,B,C)VALUES(1,2,3) onDUPLICATEKEY UPDATEC=3;--or aINSERT  into Table(A,B,C)VALUES(1,2,3) onDUPLICATEKEY UPDATEC=Values(c);--to update conflicting rows by referencing another columnINSERT  into Table(A,B,C)VALUES(1,2,3),(4,5,6) onDUPLICATEKEY UPDATEC=VALUES(a)+VALUES(b);

11. Insert null into a column that is defined as NOT NULL. For a multi-line INSERT statement or INSERT INTO ... Select statement, the column is set to the implied default value, depending on the type of column data. For numeric types, the default value is 0, and for string types, the default is an empty string ('), and for a date and time type, the default value is "zero".

INSERT into ... On DUPLICATE KEY UPDATE for Select

Insert  into tbl_name1 (a,b,c)    Select  from  onKEYUPDATE c=values(c);

12. INSERT DELAYED

This option is useful if your client cannot wait for insert to complete, and when a client uses insert delayed, it gets a confirmation from the server at once. 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 insertions from many clients are lumped together and written into a block. This is much faster than performing many separate inserts.

VALUES (' Tom '), (' Paul ');

There are some limitations when using delayed:

• The INSERT delayed is only available for 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, you do not need to use Insert DELAYED for MyISAM.
• Insert delayed should be used only for INSERT statements that specify a value list. Server ignored for insert DELAYED ... The DELAYED and insert DELAYED of the SELECT statement ... The delayed of the on DUPLICATE UPDATE statement.
• 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.
• For SELECT statements, the delayed rows are not visible until the rows are actually inserted.
• Delayed is ignored in the subordinate replication server because delayed does not produce data that is not the same as the primary server in the secondary server.

Reprint Source:

Http://www.cnblogs.com/ggjucheng/archive/2012/11/05/2754938.html

Http://dev.mysql.com/doc/search/?q=insert

MySQL insert operation