7.13 INSERT syntax

    INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
        [INTO] tbl_name [(col_name,...)]
        VALUES (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, 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 3.22.5 or later. The col_name=expression syntax is supported in MySQL 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 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. Default value assignment is described in CREATE TABLE.
  • 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 LOW_PRIORITY, execution of the INSERT is delayed until no other clients are reading from the table. In this case the client has to wait until the insert statement is completed, which may take a long time if the table is in heavy use. This is in contrast to INSERT DELAYED which lets the client continue at once.
  • If you specify the keyword IGNORE in an INSERT with many value rows, any rows which 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 check with the C API function mysql_info() how many rows were inserted into the table.
  • 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. configure options.
  • The following conditions hold for a INSERT INTO ... SELECT statement:
    • The query cannot contain an ORDER BY clause.
    • The target table of the INSERT statement cannot appear in the FROM clause of the SELECT part of the query, because it's forbidden in ANSI SQL to SELECT from the same table into which you are INSERTing. (The problem is that the SELECT possibly would find records that were inserted earlier during the same run. When using sub-select clauses, the situation could easily be very confusing!)
    • AUTO_INCREMENT columns work as usual.

If you use INSERT ... SELECT or a 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 below: 

Records: 100 Duplicates: 0 Warnings: 0

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 its default value.
  • 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.

The DELAYED option for the INSERT statement is a MySQL-specific option that is very useful if you have clients that can't wait for the INSERT to complete. This is common problem when you use MySQL for logging and you also periodically run SELECT statements that take a long time to complete. DELAYED was introduced in MySQL 3.22.15. It is a MySQL extension to ANSI SQL92.

When you use INSERT DELAYED, the client will get an ok at once and the row will be inserted when the table is not in use by any other thread.

Another major benefit of using INSERT DELAYED is that inserts from many clients are bundled together and written in one block. This is much faster than doing many separate inserts.

Note that currently the queued rows are only stored in memory until they are inserted into the table. This means that if you kill mysqld the hard way (kill -9) or if mysqld dies unexpectedly, any queued rows that weren't written to disk are lost!

The following describes in detail what happens when you use the DELAYED option to INSERT or REPLACE. In this description, the ``thread'' is the thread that received an INSERT DELAYED command and ``handler'' is the thread that handles all INSERT DELAYED statements for a particular table.

  • When a thread executes a DELAYED statement for a table, a handler thread is created to process all DELAYED statements for the table, if no such handler already exists.
  • The thread checks whether or not the handler has acquired a DELAYED lock already; if not, it tells the handler thread to do so. The DELAYED lock can be obtained even if other threads have a READ or WRITE lock on the table. However, the handler will wait for all ALTER TABLE locks or FLUSH TABLES to ensure that the table structure is up to date.
  • The thread executes the INSERT statement but instead of writing the row to the table it puts a copy of the final row into a queue that is managed by the handler thread. Any syntax errors are noticed by the thread and reported to the client program.
  • The client can't report the number of duplicates or the AUTO_INCREMENT value for the resulting row; it can't obtain them from the server, because the INSERT returns before the insert operation has been completed. If you use the C API, the mysql_info() function doesn't return anything meaningful, for the same reason.
  • The update log is updated by the handler thread when the row is inserted into the table. In case of multiple-row inserts, the update log is updated when the first row is inserted.
  • After every delayed_insert_limit rows are written, the handler checks whether or not any SELECT statements are still pending. If so, it allows these to execute before continuing.
  • When the handler has no more rows in its queue, the table is unlocked. If no new INSERT DELAYED commands are received within delayed_insert_timeout seconds, the handler terminates.
  • If more than delayed_queue_size rows are pending already in a specific handler queue, the thread waits until there is room in the queue. This is useful to ensure that the mysqld server doesn't use all memory for the delayed memory queue.
  • The handler thread will show up in the MySQL process list with delayed_insert in the Command column. It will be killed if you execute a FLUSH TABLES command or kill it with KILL thread_id. However, it will first store all queued rows into the table before exiting. During this time it will not accept any new INSERT commands from another thread. If you execute an INSERT DELAYED command after this, a new handler thread will be created.
  • Note that the above means that INSERT DELAYED commands have higher priority than normal INSERT commands if there is an INSERT DELAYED handler already running! Other update commands will have to wait until the INSERT DELAY queue is empty, someone kills the handler thread (with KILL thread_id) or someone executes FLUSH TABLES.
  • The following status variables provide information about INSERT DELAYED commands:
    Delayed_insert_threads Number of handler threads
    Delayed_writes Number of rows written with INSERT DELAYED
    Not_flushed_delayed_rows Number of rows waiting to be written
    You can view these variables by issuing a SHOW STATUS statement or by executing a mysqladmin extended-status command.

Note that INSERT DELAYED is slower than a normal INSERT if the table is not in use. There is also the additional overhead for the server to handle a separate thread for each table on which you use INSERT DELAYED. This means that you should only use INSERT DELAYED when you are really sure you need it!