13.2.6.1 INSERT ... SELECT Statement
13.2.6.2 INSERT ... ON DUPLICATE KEY UPDATE Statement
13.2.6.3 INSERT DELAYED Statement
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[(col_name [, col_name] ...)]
{ {VALUES | VALUE} (value_list) [, (value_list)] ...
|
VALUES row_constructor_list
}
[AS row_alias[(col_alias [, col_alias] ...)]]
[ON DUPLICATE KEY UPDATE assignment_list]
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[AS row_alias[(col_alias [, col_alias] ...)]]
SET assignment_list
[ON DUPLICATE KEY UPDATE assignment_list]
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[(col_name [, col_name] ...)]
[AS row_alias[(col_alias [, col_alias] ...)]]
{SELECT ... | TABLE table_name}
[ON DUPLICATE KEY UPDATE assignment_list]
value:
{
expr | DEFAULT}
value_list:
value [, value] ...
row_constructor_list:
ROW(value_list)[, ROW(value_list)][, ...]
assignment:
col_name = [row_alias.]value
assignment_list:
assignment [, assignment] ...
INSERT inserts new rows into an existing table. The INSERT ... VALUES, INSERT ... VALUES ROW(), 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. You can also use INSERT ... TABLE in MySQL 8.0.19 and later to insert rows from a single table. 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
. In MySQL 8.0.19 and later, a row alias with one or more optional column alises can be used with ON DUPLICATE KEY UPDATE
to refer to the row to be inserted.
INSERT将新行插入到现有表中。INSERT ... VALUES,INSERT ... VALUES ROW()和IINSERT ... SET形式,根据显式指定的值插入行。INSERT ... SELECT形式,插入从另一个或多个表中选择的行。你也可以使用INSERT ... TABLE在MySQL 8.0.19和更高版本中插入一个表中的行。如果要插入的行会导致UNIQUE索引或PRIMARY KEY中的重复值,则使用ON DUPLICATE KEY UPDATE子句的INSERT允许更新现有行。在MySQL 8.0.19和更高版本中,一个带有一个或多个可选列的行别名可以与ON DUPLICATE KEY UPDATE一起使用,以引用要插入的行。
For additional information about INSERT ... SELECT and INSERT ... ON DUPLICATE KEY UPDATE, see Section 13.2.6.1, “INSERT ... SELECT Statement”, and Section 13.2.6.2, “INSERT ... ON DUPLICATE KEY UPDATE Statement”.
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 Statement”,
在MySQL 8.0中,延迟关键字被接受,但服务器忽略。原因见13.2.6.3节“INSERT DELAYED Statement”,
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).
插入到表中需要该表的INSERT权限。如果使用ON DUPLICATE KEY UPDATE子句,而重复的键导致执行UPDATE,则该语句需要更新列的UPDATE特权。对于读但不修改的列,您只需要SELECT权限(例如,对于只在on DUPLICATE KEY UPDATE子句中的col_name=expr赋值右边引用的列)。
When inserting into a partitioned table, you can control which partitions and subpartitions accept new rows. The PARTITION
clause 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