MySQL INSERT statement

Syntax for insert

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_nameSETCol_name={expr|DEFAULT}, ...[ on DUPLICATE KEY UPDATE col_name=expr, ... ]insert [low_priority | High_priority [ Ignore [into] tbl_name [ (Col_name,...)  select ... [            /span>         

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:

VALUES ();

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

VALUES ("Tom", "Tom@yahoo. com");

BULK INSERT multiple data

VALUES (' Tom ', ' Tom@yahoo. com '), (' Paul ', ' Paul@yahoo. com '); 

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

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

Inserting data Using Set

Set name= ' Tom ';

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

An expression can reference any column that was previously set on a value table, such as

VALUES (15,col1*2);  -- but not so VALUES (col2*2);         

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

From tbl_name2;  - If each column has data 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 in the Select query section.

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 CINSERTIntoTable (A,B,C)VALUES (1,2,3)On DUPLICATEKEYUPDATE C=3;--or aINSERTIntoTable (A,B,C)VALUES (1,2,3)On DUPLICATEKEYUPDATE C=Values (c); --insert Span style= "color: #0000ff;" >into table (a,b,c) values (1,2,4,5,on DUPLICATE key update C =values (a) +values (b);                

Inserts 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

Into tbl_name1 (a,b,c) from   UPDATE c=values (c);    

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.

Transfer from Http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#insert

MySQL INSERT statement