(mysql.info) insert
Info Catalog
(mysql.info) handler
(mysql.info) data-manipulation
(mysql.info) load-data
13.2.4 `INSERT' Syntax
----------------------
Menu
* insert-select `INSERT ... SELECT' Syntax
* insert-delayed `INSERT DELAYED' Syntax
* insert-on-duplicate `INSERT ... ON DUPLICATE KEY UPDATE' Syntax
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, ... ]
`INSERT' inserts new rows into an existing table. The `INSERT ...
VALUES' and `INSERT ... SET' forms of the statement insert rows based
on explicitly specified values. The `INSERT ... SELECT' form inserts
rows selected from another table or tables. `INSERT ... SELECT' is
discussed further in insert-select.
You can use `REPLACE' instead of `INSERT' to overwrite old rows.
`REPLACE' is the counterpart to `INSERT IGNORE' in the treatment of new
rows that contain unique key values that duplicate old rows: The new
rows are used to replace the old rows rather than being discarded. See
replace.
TBL_NAME is the table into which rows should be inserted. The columns
for which the statement provides values can be specified as follows:
* You can provide a comma-separated list of column names following
the table name. In this case, a value for each named column must
be provided by the `VALUES' list or the `SELECT' statement.
* If you do not specify a list of column names for `INSERT ...
VALUES' or `INSERT ... SELECT', values for every column in the
table must be provided by the `VALUES' list or the `SELECT'
statement. If you do not know the order of the columns in the
table, use `DESCRIBE TBL_NAME' to find out.
* The `SET' clause indicates the column names explicitly.
Column values can be given in several ways:
* If you are not running in strict SQL mode, any column not
explicitly given a value is set to its default (explicit or
implicit) value. For example, if you specify a column list that
does not name all the columns in the table, unnamed columns are
set to their default values. Default value assignment is described
in data-type-defaults. See also
constraint-invalid-data.
If you want an `INSERT' statement to generate an error unless you
explicitly specify values for all columns that do not have a
default value, you should use strict mode. See
server-sql-mode.
* Use the keyword `DEFAULT' to set a column explicitly to its
default value. This makes it easier to write `INSERT' statements
that assign values to all but a few columns, because it enables
you to avoid writing an incomplete `VALUES' 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.
You can also use `DEFAULT(COL_NAME)' as a more general form that
can be used in expressions to produce a given column's default
value.
* If both the column list and the `VALUES' list are empty, `INSERT'
creates a row with each column set to its default value:
INSERT INTO TBL_NAME () VALUES();
In strict mode, an error occurs if any column doesn't have a
default value. Otherwise, MySQL uses the implicit default value
for any column that does not have an explicitly defined default.
* You can specify an expression EXPR to provide a column value.
This might involve type conversion if the type of the expression
does not match the type of the column, and conversion of a given
value can result in different inserted values depending on the
data type. For example, inserting the string `'1999.0e-2'' into an
`INT', `FLOAT', `DECIMAL(10,6)', or `YEAR' column results in the
values `1999', `19.9921', `19.992100', and `1999' being inserted,
respectively. The reason the value stored in the `INT' and `YEAR'
columns is `1999' is that the string-to-integer conversion looks
only at as much of the initial part of the string as may be
considered a valid integer or year. For the floating-point and
fixed-point columns, the string-to-floating-point conversion
considers the entire string a valid floating-point value.
An expression EXPR can refer to any column that was set earlier in
a value list. For example, you can do this because the value for
`col2' refers to `col1', which has previously been assigned:
INSERT INTO TBL_NAME (col1,col2) VALUES(15,col1*2);
But the following is not legal, because the value for `col1'
refers to `col2', which is assigned after `col1':
INSERT INTO TBL_NAME (col1,col2) VALUES(col2*2,15);
One exception involves columns that contain `AUTO_INCREMENT'
values. Because the `AUTO_INCREMENT' value is generated after
other value assignments, any reference to an `AUTO_INCREMENT'
column in the assignment returns a `0'.
`INSERT' statements that use `VALUES' syntax can insert multiple rows.
To do this, include multiple lists of column values, each enclosed
within parentheses and separated by commas. Example:
INSERT INTO TBL_NAME (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
The values list for each row must be enclosed within parentheses. The
following statement is illegal because the number of values in the list
does not match the number of column names:
INSERT INTO TBL_NAME (a,b,c) VALUES(1,2,3,4,5,6,7,8,9);
The rows-affected value for an `INSERT' can be obtained using the
`mysql_affected_rows()' C API function. See mysql-affected-rows.
If you use an `INSERT ... VALUES' statement with multiple value lists
or `INSERT ... SELECT', the statement returns an information string in
this format:
Records: 100 Duplicates: 0 Warnings: 0
`Records' indicates the number of rows processed by the statement.
(This is not necessarily the number of rows actually inserted because
`Duplicates' can be non-zero.) `Duplicates' indicates the number of
rows that could not 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'.
For multiple-row `INSERT' statements or `INSERT INTO ... SELECT'
statements, the column is set to the implicit default value for
the column data type. This is `0' for numeric types, the empty
string (`''') for string types, and the `zero' value for date and
time types. `INSERT INTO ... SELECT' statements are handled the
same way as multiple-row inserts because the server does not
examine the result set from the `SELECT' to see whether it returns
a single row. (For a single-row `INSERT', no warning occurs when
`NULL' is inserted into a `NOT NULL' column. Instead, the statement
fails with an error.)
* Setting a numeric column to a value that lies outside the column's
range. The value is clipped to the closest endpoint of the range.
* Assigning a value such as `'10.34 a'' to a numeric column. The
trailing non-numeric text is stripped off and the remaining
numeric part is inserted. If the string value has no leading
numeric part, the column is set to `0'.
* Inserting a string into a string column (`CHAR', `VARCHAR',
`TEXT', or `BLOB') 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 data type. The column is set to the appropriate zero value for
the type.
If you are using the C API, the information string can be obtained by
invoking the `mysql_info()' function. See mysql-info.
If `INSERT' inserts a row into a table that has an `AUTO_INCREMENT'
column, you can find the value used for that column by using the SQL
`LAST_INSERT_ID()' function. From within the C API, use the
`mysql_insert_id()' function. However, you should note that the two
functions do not always behave identically. The behavior of `INSERT'
statements with respect to `AUTO_INCREMENT' columns is discussed
further in information-functions, and mysql-insert-id.
The `INSERT' statement supports the following modifiers:
* If you use the `DELAYED' keyword, the server puts the row or rows
to be inserted into a buffer, and the client issuing the `INSERT
DELAYED' statement can then continue immediately. If the table is
in use, the server holds the rows. When the table is free, the
server begins inserting rows, checking periodically to see whether
there are any new read requests for the table. If there are, the
delayed row queue is suspended until the table becomes free again.
See insert-delayed.
`DELAYED' is ignored with `INSERT ... SELECT' or `INSERT ... ON
DUPLICATE KEY UPDATE'.
* If you use the `LOW_PRIORITY' keyword, 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
because doing so disables concurrent inserts. See
concurrent-inserts.
* If you specify `HIGH_PRIORITY', it overrides the effect of the
-low-priority-updates option if the server was started with that
option. It also causes concurrent inserts not to be used.
* If you use the `IGNORE' keyword, errors that occur while executing
the `INSERT' statement are treated as warnings instead. For
example, without `IGNORE', a row that duplicates an existing
`UNIQUE' index or `PRIMARY KEY' value in the table causes a
duplicate-key error and the statement is aborted. With `IGNORE',
the row still is not inserted, but no error is issued. Data
conversions that would trigger errors abort the statement if
`IGNORE' is not specified. With `IGNORE', invalid values are
adjusted to the closest values and inserted; warnings are produced
but the statement does not abort. You can determine with the
`mysql_info()' C API function how many rows were actually inserted
into the table.
* If you specify `ON DUPLICATE KEY UPDATE', and a row is inserted
that would cause a duplicate value in a `UNIQUE' index or `PRIMARY
KEY', an `UPDATE' of the old row is performed. See
insert-on-duplicate.
Info Catalog
(mysql.info) handler
(mysql.info) data-manipulation
(mysql.info) load-data
automatically generated byinfo2html