Usage of "Go" MySQL Insert

Transferred from: http://www.cnblogs.com/ggjucheng/archive/2012/11/05/2754938.html

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_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 lists, insert creates a row and each column is set to the default value:

INSERT into Tbl_name () VALUES ();

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

INSERT into worker values ("Tom", "[email protected]");

BULK INSERT multiple data

INSERT into worker values (' Tom ', ' [email protected] '), (' Paul ', ' [email protected] ');

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

INSERT into worker (name) VALUES (' Tom ') and insert into worker (name) VALUES (' Tom '), (' Paul ');

Inserting data Using Set

INSERT into worker 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

Insert into Tbl_name (col1,col2) values (15,col1*2);--but not so insert into tbl_name (col1,col2) values (col2*2,15);

Use Insert ... SELECT statement inserts rows selected 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 TB l_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 such rows are normal insert data, when the conflict, update the value of column C insert into table (A,B,C) VALUES (all-in-one) on DUPLICATE KEY UPDATE c=3;-- or INSERT into table (A,B,C) VALUES (All-in-one) on DUPLICATE KEY update c=values (c);--Referencing other columns update conflicting rows INSERT into table (A,B,C) VAL UES (4,5,6) 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

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

INSERT DELAYED into worker (name) 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.

The introduction of the sentence, I hope you can have something to gain.

Usage of "Go" MySQL Insert