MySQL insert operation

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 the column list andValuesIf the list is emptyInsertA row is created, and each column is set to the default value:

 Insert IntoTbl_name ()Values();
 

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

 
Insert IntoWorkerValues("Tom", "Tom@ Yahoo. Com ");
 

Insert multiple data entries in batches

 
Insert IntoWorkerValues('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 IntoWorker (name)Values('Tom ');Insert IntoWorker (name)Values('Tom '), ('Paul ');
 

Use set to insert data

 
Insert IntoWorkerSetName='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 IntoTbl_name (col1, col2)Values(15, Col1*2);--But it cannot.Insert IntoTbl_name (col1, col2)Values(Col2*2,15);
 

 

Use Insert... Select statement insert selected rows from other tables

Insert IntoTbl_name1 (col1, col2)SelectCol3, col4FromTbl_name2;--If each column has dataInsert IntoTbl_name1SelectCol3, col4FromTbl_name2;
 

The query cannot contain an order by clause, andThe target table of the insert statement cannot appear in the from clause of the SELECT query.

 

On duplicate key update

If you specifyOn duplicate key updateAnd insert a row will causeUniqueIndex orPrimary KeyIf a duplicate value appears, the old row is executed.Update.

 

 --  Assume that column A and column 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 () +  Values (B );

 ToNot nullInsert in ColumnNull. For a multilineInsertStatement orInsert into... selectStatement. The column is set to an implicit default value based on the column data type. For numeric type, the default value is0; For the string type, the default value is a Null String(''); For Date and Time types, the default value is"Zero"Value.

Insert into... select on duplicate key update

Insert IntoTbl_name1 (A, B, C)SelectCol1, col2, col3FromTbl_name2OnDuplicateKey UpdateC=Values(C );
 

 

Insert delayed

If your client cannot waitInsertThis option is very useful,When a client usesInsert delayedWill 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.

UseInsert delayedAnother important benefit is that inserts from many clients are centralized and written into a block. This is much faster than executing many independent inserts.

InsertDelayedIntoWorker (name)Values('Tom '), ('Paul ');
 

UseDelayedThere are some restrictions: 

• Insert delayedApplicable onlyMyISAM, memoryAndArchiveTable. ForMyISAMTable. If there is no idle block in the middle of the data fileSelectAndInsertStatement. In these casesMyISAMUseInsert delayed.
• Insert delayedIt should be used only for the specified value listInsertStatement. Server ignoredInsert delayed... selectStatementDelayed andInsert delayed... on duplicate updateStatementDelayed.
• Because the statement returns immediately before the row is inserted, you cannot useLast_insert_id ()To obtainAuto_incrementValue.Auto_incrementThe value may be generated by the statement.
• ForSelectStatement,DelayedRows are invisible until these rows are indeed inserted.
• DelayedThe slave replication server is ignored becauseDelayedThe slave server does not generate data different from the master server.

 

From http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#insert