Rechercher dans le manuel MySQL
13.2.6 INSERT Syntax
[+/-]
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
with an
ON DUPLICATE KEY UPDATE
clause enables existing
rows to be updated if a row to be inserted would cause a duplicate
value in a UNIQUE
index or PRIMARY
KEY
.
For additional information about
INSERT ...
SELECT
and
INSERT ... ON
DUPLICATE KEY UPDATE
, see
Section 13.2.6.1, “INSERT ... SELECT Syntax”, and
Section 13.2.6.2, “INSERT ... ON DUPLICATE KEY UPDATE Syntax”.
In MySQL 8.0, the DELAYED
keyword
is accepted but ignored by the server. For the reasons for this,
see Section 13.2.6.3, “INSERT DELAYED Syntax”,
Inserting into a table requires the
INSERT
privilege for the table. If
the ON DUPLICATE KEY UPDATE
clause is used and
a duplicate key causes an UPDATE
to
be performed instead, the statement requires the
UPDATE
privilege for the columns to
be updated. For columns that are read but not modified you need
only the SELECT
privilege (such as
for a column referenced only on the right hand side of an
col_name
=expr
assignment in an ON DUPLICATE KEY UPDATE
clause).
When inserting into a partitioned table, you can control which
partitions and subpartitions accept new rows. The
PARTITION
option takes a list of the
comma-separated names of one or more partitions or subpartitions
(or both) of the table. If any of the rows to be inserted by a
given INSERT
statement do not match
one of the partitions listed, the
INSERT
statement fails with the
error Found a row not matching the given partition
set. For more information and examples, see
Section 23.5, “Partition Selection”.
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 replace the old rows rather than
being discarded. See Section 13.2.9, “REPLACE Syntax”.
tbl_name
is the table into which rows
should be inserted. Specify the columns for which the statement
provides values as follows:
Provide a parenthesized list of comma-separated column names following the table name. In this case, a value for each named column must be provided by the
VALUES
list or theSELECT
statement.If you do not specify a list of column names for
INSERT ... VALUES
orINSERT ... SELECT
, values for every column in the table must be provided by theVALUES
list or theSELECT
statement. If you do not know the order of the columns in the table, useDESCRIBE
to find out.tbl_name
A
SET
clause indicates columns explicitly by name, together with the value to assign each one.
Column values can be given in several ways:
If strict SQL mode is not enabled, 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.7, “Data Type Default Values”. See also Section 1.8.3.3, “Constraints on Invalid Data”.
If strict SQL mode is enabled, an
INSERT
statement generates an error if it does not specify an explicit value for every column that has no default value. See Section 5.1.11, “Server SQL Modes”.If both the column list and the
VALUES
list are empty,INSERT
creates a row with each column set to its default value:If strict mode is not enabled, MySQL uses the implicit default value for any column that has no explicitly defined default. If strict mode is enabled, an error occurs if any column has no default value.
Use the keyword
DEFAULT
to set a column explicitly to its default value. This makes it easier to writeINSERT
statements that assign values to all but a few columns, because it enables you to avoid writing an incompleteVALUES
list that does not include a value for each column in the table. Otherwise, you must provide the list of column names corresponding to each value in theVALUES
list.If a generated column is inserted into explicitly, the only permitted value is
DEFAULT
. For information about generated columns, see Section 13.1.20.9, “CREATE TABLE and Generated Columns”.In expressions, you can use
DEFAULT(
to produce the default value for columncol_name
)col_name
.Type conversion of an expression
expr
that provides a column value might occur if the expression data type does not match the column data type. Conversion of a given value can result in different inserted values depending on the column type. For example, inserting the string'1999.0e-2'
into anINT
,FLOAT
,DECIMAL(10,6)
, orYEAR
column inserts the value1999
,19.9921
,19.992100
, or1999
, respectively. The value stored in theINT
andYEAR
columns is1999
because the string-to-number conversion looks only at as much of the initial part of the string as may be considered a valid integer or year. For theFLOAT
andDECIMAL
columns, the string-to-number conversion considers the entire string a valid numeric 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 forcol2
refers tocol1
, which has previously been assigned:But the following is not legal, because the value for
col1
refers tocol2
, which is assigned aftercol1
:An exception occurs for columns that contain
AUTO_INCREMENT
values. BecauseAUTO_INCREMENT
values are generated after other value assignments, any reference to anAUTO_INCREMENT
column in the assignment returns a0
.
INSERT
statements that use
VALUES
syntax can insert multiple rows. To do
this, include multiple lists of comma-separated column values,
with lists enclosed within parentheses and separated by commas.
Example:
Each values list must contain exactly as many values as are to be inserted per row. The following statement is invalid because it contains one list of nine values, rather than three lists of three values each:
VALUE
is a synonym for
VALUES
in this context. Neither implies
anything about the number of values lists, nor about the number of
values per list. Either may be used whether there is a single
values list or multiple lists, and regardless of the number of
values per list.
The affected-rows value for an
INSERT
can be obtained using the
ROW_COUNT()
SQL function or the
mysql_affected_rows()
C API
function. See Section 12.15, “Information Functions”, and
Section 28.7.7.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: N1 Duplicates: N2 Warnings: N3
If you are using the C API, the information string can be obtained
by invoking the mysql_info()
function. See Section 28.7.7.36, “mysql_info()”.
Records
indicates the number of rows processed
by the statement. (This is not necessarily the number of rows
actually inserted because Duplicates
can be
nonzero.) 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 declaredNOT NULL
. For multiple-rowINSERT
statements orINSERT INTO ... SELECT
statements, the column is set to the implicit default value for the column data type. This is0
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 theSELECT
to see whether it returns a single row. (For a single-rowINSERT
, no warning occurs whenNULL
is inserted into aNOT 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 nonnumeric text is stripped off and the remaining numeric part is inserted. If the string value has no leading numeric part, the column is set to0
.Inserting a string into a string column (
CHAR
,VARCHAR
,TEXT
, orBLOB
) 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.
For
INSERT
examples involvingAUTO_INCREMET
column values, see Section 3.6.9, “Using AUTO_INCREMENT”.If
INSERT
inserts a row into a table that has anAUTO_INCREMENT
column, you can find the value used for that column by using theLAST_INSERT_ID()
SQL function or themysql_insert_id()
C API function.NoteThese two functions do not always behave identically. The behavior of
INSERT
statements with respect toAUTO_INCREMENT
columns is discussed further in Section 12.15, “Information Functions”, and Section 28.7.7.38, “mysql_insert_id()”.
The INSERT
statement supports the
following modifiers:
If you use the
LOW_PRIORITY
modifier, execution of theINSERT
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 theINSERT LOW_PRIORITY
statement is waiting. It is possible, therefore, for a client that issues anINSERT LOW_PRIORITY
statement to wait for a very long time.LOW_PRIORITY
affects only storage engines that use only table-level locking (such asMyISAM
,MEMORY
, andMERGE
).NoteLOW_PRIORITY
should normally not be used withMyISAM
tables because doing so disables concurrent inserts. See Section 8.11.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. See Section 8.11.3, “Concurrent Inserts”.HIGH_PRIORITY
affects only storage engines that use only table-level locking (such asMyISAM
,MEMORY
, andMERGE
).If you use the
IGNORE
modifier, errors that occur while executing theINSERT
statement are ignored. For example, withoutIGNORE
, a row that duplicates an existingUNIQUE
index orPRIMARY KEY
value in the table causes a duplicate-key error and the statement is aborted. WithIGNORE
, the row is discarded and no error occurs. Ignored errors generate warnings instead.IGNORE
has a similar effect on inserts into partitioned tables where no partition matching a given value is found. WithoutIGNORE
, suchINSERT
statements are aborted with an error. WhenINSERT IGNORE
is used, the insert operation fails silently for rows containing the unmatched value, but inserts rows that are matched. For an example, see Section 23.2.2, “LIST Partitioning”.Data conversions that would trigger errors abort the statement if
IGNORE
is not specified. WithIGNORE
, invalid values are adjusted to the closest values and inserted; warnings are produced but the statement does not abort. You can determine with themysql_info()
C API function how many rows were actually inserted into the table.For more information, see Comparison of the IGNORE Keyword and Strict SQL Mode.
If you specify
ON DUPLICATE KEY UPDATE
, and a row is inserted that would cause a duplicate value in aUNIQUE
index orPRIMARY KEY
, anUPDATE
of the old row occurs. The affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values. If you specify theCLIENT_FOUND_ROWS
flag to themysql_real_connect()
C API function when connecting to mysqld, the affected-rows value is 1 (not 0) if an existing row is set to its current values. See Section 13.2.6.2, “INSERT ... ON DUPLICATE KEY UPDATE Syntax”.INSERT DELAYED
was deprecated in MySQL 5.6, and is scheduled for eventual removal. In MySQL 8.0, theDELAYED
modifier is accepted but ignored. UseINSERT
(withoutDELAYED
) instead. See Section 13.2.6.3, “INSERT DELAYED Syntax”.
An INSERT
statement affecting a partitioned
table using a storage engine such as
MyISAM
that employs table-level locks
locks only those partitions into which rows are actually inserted.
(For storage engines such as InnoDB
that employ row-level locking, no locking of partitions takes
place.) For more information, see
Partitioning and Locking.
Deutsche Übersetzung
Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.
Vielen Dank im Voraus.
Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-insert.html
Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.
Referenzen
Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.