INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name [(col_name,...)] VALUES ((expression | DEFAULT),...),(...),... [ ON DUPLICATE KEY UPDATE col_name=expression, ... ] or INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name [(col_name,...)] SELECT ... or INSERT [LOW_PRIORITY | DELAYED] [IGNORE] [INTO] tbl_name SET col_name=(expression | DEFAULT), ... [ ON DUPLICATE KEY UPDATE col_name=expression, ... ]

INSERT inserts new rows into an existing table. The INSERT ... VALUES form of the statement inserts rows based on explicitly specified values. The INSERT ... SELECT form inserts rows selected from another table or tables. The INSERT ... VALUES form with multiple value lists is supported in MySQL Version 3.22.5 or later. The col_name=expression syntax is supported in MySQL Version 3.22.10 or later.

tbl_name is the table into which rows should be inserted. The column name list or the SET clause indicates which columns the statement specifies values for:

• If you specify no column list for INSERT ... VALUES or INSERT ... SELECT , values for all columns in the table must be provided in the VALUES() list or by the SELECT . If you don't know the order of the columns in the table, use DESCRIBE tbl_name to find out.
• Any column not explicitly given a value is set to its default value. For example, if you specify a column list that doesn't name all the columns in the table, unnamed columns are set to their default values. . You can also use the keyword DEFAULT to set a column to its default value. (New in MySQL 4.0.3.) This makes it easier to write INSERT statements that assign values to all but a few columns, because it allows you to avoid writing an incomplete VALUES() list (a list that does not include a value for each column in the table). Otherwise, you would have to write out the list of column names corresponding to each value in the VALUES() list. MySQL always has a default value for all fields. This is something that is imposed on MySQL to be able to work with both transactional and non-transactional tables. Our view is that checking of fields content should be done in the application and not in the database server.
• An expression may refer to any column that was set earlier in a value list. For example, you can say this: mysql> INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2); But not this: mysql> INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15);
• If you specify the keyword DELAYED , the server puts the row or rows to be inserted into a buffer, and the client issuing the INSERT DELAYED statement then may continue on. If the table is busy, the server holds the rows. When the table becomes free, it begins inserting rows, checking periodically to see whether there are new read requests for the table. If there are, the delayed row queue is suspended until the table becomes free again.
• If you specify the keyword LOW_PRIORITY , execution of the INSERT is delayed until no other clients are reading from the table. This includes other clients that began reading while existing clients are reading, and while the INSERT LOW_PRIORITY statement is waiting. It is possible, therefore, for a client that issues an INSERT LOW_PRIORITY statement to wait for a very long time (or even forever) in a read-heavy environment. (This is in contrast to INSERT DELAYED , which lets the client continue at once.). Note that LOW_PRIORITY should normally not be used with MyISAM tables as this disables concurrent inserts.
• If you specify the keyword IGNORE in an INSERT with many rows, any rows that duplicate an existing PRIMARY or UNIQUE key in the table are ignored and are not inserted. If you do not specify IGNORE , the insert is aborted if there is any row that duplicates an existing key value. You can determine with the C API function mysql_info() how many rows were inserted into the table.
• If you specify ON DUPLICATE KEY UPDATE clause (new in MySQL 4.1.0), and a row is inserted that would cause a duplicate value in a PRIMARY or UNIQUE key, an UPDATE of the old row is performed. For example, the command: mysql> INSERT INTO table (a,b,c) VALUES (1,2,3) -> ON DUPLICATE KEY UPDATE c=c+1; in case of column a is declared as UNIQUE and already holds 1 once, would be identical to the mysql> UPDATE table SET c=c+1 WHERE a=1; Note: If column b is unique too, the UPDATE command would be written as mysql> UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1; and if a=1 OR b=2 matches several rows, only one row will be updated! In general, one should try to avoid using ON DUPLICATE KEY clause on tables with multiple UNIQUE keys. Since MySQL 4.1.1 one can use function VALUES(col_name) to refer to the column value in the INSERT part of the INSERT ... UPDATE command - that is the value that would be inserted if there would be no duplicate key conflict. This function especially useful in multiple-row inserts. Naturally VALUES() function is only meaningful in INSERT ... UPDATE command and returns NULL otherwise. Example: mysql> INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) -> ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b); The command above is identical to mysql> INSERT INTO table (a,b,c) VALUES (1,2,3) -> ON DUPLICATE KEY UPDATE c=3; mysql> INSERT INTO table (a,b,c) VALUES (4,5,6) -> ON DUPLICATE KEY UPDATE c=9; When one uses ON DUPLICATE KEY UPDATE , the DELAYED option is ignored.
• If MySQL was configured using the DONT_USE_DEFAULT_FIELDS option, INSERT statements generate an error unless you explicitly specify values for all columns that require a non- NULL value.
• You can find the value used for an AUTO_INCREMENT column with the mysql_insert_id function.

If you use INSERT ... SELECT or an INSERT ... VALUES statement with multiple value lists, you can use the C API function mysql_info() to get information about the query. The format of the information string is shown here:

Duplicates indicates the number of rows that couldn't be inserted because they would duplicate some existing unique index value. Warnings indicates the number of attempts to insert column values that were problematic in some way. Warnings can occur under any of the following conditions:

• Inserting NULL into a column that has been declared NOT NULL . The column is set to the default value appropriate for the column type. This is 0 for numeric types, the empty string ( '' ) for string types, and the ``zero'' value for date and time types.
• Setting a numeric column to a value that lies outside the column's range. The value is clipped to the appropriate endpoint of the range.
• Setting a numeric column to a value such as '10.34 a' . The trailing garbage is stripped and the remaining numeric part is inserted. If the value doesn't make sense as a number at all, the column is set to 0 .
• Inserting a string into a CHAR , VARCHAR , TEXT , or BLOB column that exceeds the column's maximum length. The value is truncated to the column's maximum length.
• Inserting a value into a date or time column that is illegal for the column type. The column is set to the appropriate zero value for the type.

back to MySQL reference