GNU Info

(mysql.info)INSERT

---

Next: INSERT DELAYED Prev: SELECT Up: Data Manipulation

---

Enter node , (file) or (file)node

`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 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 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
     Note: `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.  Note: INSERT DELAYED.  Note that
     `LOW_PRIORITY' should normally not be used with `MyISAM' tables as
     this disables concurrent inserts. Note: MyISAM.

   * If you specify the keyword `IGNORE' in an `INSERT' with many value
     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 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.
     Note: `configure' options.

   * You can find the value used for an `AUTO_INCREMENT' column with
     the `mysql_insert_id' function.  *Note `mysql_insert_id()':
     mysql_insert_id.

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

INSERT SELECT

`INSERT ... SELECT' Syntax