| |
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 Section 13.2.4.1, “INSERT ... SELECT Syntax”.
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
Section 13.2.6, “REPLACE Syntax”.
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
Section 11.1.4, “Data Type Default Values”. See also
Section 1.9.6.2, “Constraints on 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 Section 5.2.5, “The 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 Section 25.2.3.1, “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 Section 25.2.3.34, “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
Section 12.10.3, “Information Functions”, and
Section 25.2.3.36, “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
Section 13.2.4.2, “INSERT DELAYED Syntax”.
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
Section 7.3.3, “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 Section 13.2.4.3, “INSERT ... ON DUPLICATE KEY UPDATE Syntax”.
|
|
