INSERT

This MySQL statement is used to add rows of data to a table. It has a few primary syntax with many options.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

INSERT [LOW_PRIORITY|DELAYED|HIGH_PRIORITY] [IGNORE]
  [INTO] table
  SET column={expression|DEFAULT}, ...
  [ON DUPLICATE KEY UPDATE column=expression, ... ]

INSERT [LOW_PRIORITY|DELAYED|HIGH_PRIORITY] [IGNORE]
  [INTO] table [(column, ...)]
  VALUES ({expression|DEFAULT},...),(...),...
  [ON DUPLICATE KEY UPDATE column=expression, ... ]

INSERT [LOW_PRIORITY|HIGH_PRIORITY] [IGNORE]
  [INTO] table [(column, ...)]
  SELECT...
  [ON DUPLICATE KEY UPDATE column=expression, ... ]

Explanation

Use this MySQL statement to add rows of data to a MySQL table. The first format shown can insert only one row of data per statement (see Single-row insertion with SET clause page of our MySQL documentation). The second format can handle one or more rows in a single statement (see Multiple-row insertions page of our MySQL documentation). The columns and their order are specified once, but values for multiple rows may be given. Each row of values is to be contained in its own set of parentheses, separated by commas. The third format inserts columns copied from rows in other tables (see the Inserting rows based on a SELECT page of our MySQL documentation). A few parameters are common to two formats or to all formats.

You can use the LOW_PRIORITY keyword to instruct the MySQL server to wait until all other queries related to the table in which data is to be added are finished before running the INSERT statement. When the table is free, the table is locked for the INSERT statement and will prevent concurrent inserts.

The DELAYED keyword is available for the first two syntaxes and indicates the same priority status, but releases the client so that other queries may be run and so that the connection may be terminated. A DELAYED query that returns without an error message does not guarantee that the inserts will take place; it confirms only that the query is received by the server to be processed. If the MySQL server crashes, the data additions may not be executed when the server restarts and the user won't be informed of the failure. To confirm a DELAYED insert, the user must check the table later for the inserted content with a SELECT statement. The DELAYED option works only with MyISAM, InnoDB, and ISAM tables. It's also not applicable when the ON DUPLICATE KEY UPDATE clause is used.

Use the HIGH_PRIORITY keyword to override a --low-priority-updates server option and to disable concurrent inserts.

The IGNORE keyword instructs the server to ignore any errors encountered and suppress the error messages. In addition, for multiple row insertions, the MySQL statement continues to insert rows after encountering errors on previous rows. Warnings are generated that the user can display with the SHOW WARNINGS statement.

The INTO keyword is optional and only for compatibility with other database systems.

The DEFAULT keyword can be given for a column for the first two syntax formats to instruct the server to use the default value for the column. You can set the default value either with the CREATE TABLE statement when the table is created or with the ALTER TABLE statement for existing tables.

The ON DUPLICATE KEY UPDATE clause tells an INSERT statement how to handle an insert when an index in the table already contains a specified value in a column. With this clause, the statement updates the data in the existing row to reflect the new values in the given columns. Without this clause, the MySQL statement generates an error.