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. INSERT将新行插入到现有表中。The 语句的INSERT ... VALUES, INSERT ... VALUES ROW(), and INSERT ... SET forms of the statement insert rows based on explicitly specified values. INSERT ... VALUES、INSERT ... VALUES ROW()和INSERT ... SET形式根据显式指定的值插入的行。The INSERT ... SELECT form inserts rows selected from another table or tables. INSERT ... SELECT形式插入从另一个或多个表中选择的行。You can also use 您也可以MySQL8.0.19及更高版本中使用INSERT ... TABLE in MySQL 8.0.19 and later to insert rows from a single table. INSERT ... 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. INSERT配合ON DUPLICATE KEY UPDATE子句启用了在插入的行会导致UNIQUE索引或PRIMARY KEY中的值重复的情况下更新现有的行。In MySQL 8.0.19 and later, a row alias with one or more optional column alises can be used with 在MySQL 8.0.19及更高版本中，可以将带有一个或多个可选列alise的行别名与ON DUPLICATE KEY UPDATE to refer to the row to be inserted.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”.INSERT ... SELECT和INSERT ... ON DUPLICATE KEY UPDATE的其他信息，请参见第13.2.6.1节，“INSERT ... SELECT语句”和第13.2.6.2节，“INSERT ... ON DUPLICATE KEY UPDATE语句”。
In MySQL 8.0, the 在MySQL 8.0中，DELAYED keyword is accepted but ignored by the server. DELAYED关键字被服务器接受但忽略。For the reasons for this, see Section 13.2.6.3, “INSERT DELAYED Statement”,有关原因，请参见第13.2.6.3节，“插入延迟声明”，
Inserting into a table requires the 插入到表中需要表的INSERT privilege for the table. INSERT权限。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. ON DUPLICATE KEY UPDATE子句，而重复键导致执行UPDATE，则语句需要被更新列的UPDATE权限。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).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子句获取表中一个或多个分区或子分区（或两者）的逗号分隔名称列表。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 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. INSERT语句要插入的任何行与列出的某个分区不匹配，则INSERT语句将失败，并出现“发现与给定分区集不匹配的行”错误。For more information and examples, see Section 24.5, “Partition Selection”.有关更多信息和示例，请参阅第24.5节，“分区选区”。
tbl_name is the table into which rows should be inserted. tbl_name是应该插入行的表。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, VALUES ROW() list, or SELECT statement. VALUES列表 、VALUES ROW()列表或SELECT语句必须为每个命名列提供一个值。For the 对于INSERT TABLE form, the number of columns in the source table must match the number of columns to be inserted.INSERT TABLE形式，源表中的列数必须与要插入的列数匹配。
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, SELECT statement, or TABLE statement. INSERT ... VALUES或INSERT ... SELECT指定列名列表，表中每列的值必须由VALUES列表、SELECT语句或TABLE语句提供。If you do not know the order of the columns in the table, use 如果您不知道表中列的顺序，请使用DESCRIBE to find out.tbl_nameDESCRIBE tbl_name来查找。
A SET clause indicates columns explicitly by name, together with the value to assign each one.SET子句通过名称显式地指示列，以及要分配给每个列的值。
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. 如果未启用严格SQL模式，则任何未显式给定值的列都将设置为其默认值（显式或隐式）。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.6, “Data Type Default Values”. 第11.6节，“数据类型默认值”中描述了默认值分配。See also Section 1.7.3.3, “Enforced Constraints on Invalid Data”.另请参阅第1.7.3.3节，“对无效数据的强制约束”。
If strict SQL mode is enabled, an 如果启用了严格SQL模式，INSERT statement generates an error if it does not specify an explicit value for every column that has no default value. INSERT语句如果没有为没有默认值的每个列指定显式值，则会生成错误。See Section 5.1.11, “Server SQL Modes”.请参阅第5.1.11节，“服务器SQL模式”。
If both the column list and the 如果列列表和VALUES list are empty, INSERT creates a row with each column set to its default value:VALUES列表都为空，INSERT将创建一行，其中每列都设置为其默认值：
INSERT INTO tbl_name () VALUES();
If strict mode is not enabled, MySQL uses the implicit default value for any column that has no explicitly defined default. 如果没有启用严格模式，MySQL会对没有显式定义默认值的任何列使用隐式默认值。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. DEFAULT将列显式设置为其默认值。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. INSERT语句，将值分配给除少数列以外的所有列，因为这样可以避免编写不完整的值列表，该列表不包含表中每列的值。Otherwise, you must provide the list of column names corresponding to each value in the 否则，必须提供对应于VALUES list.VALUES列表中每个值的列名列表。
If a generated column is inserted into explicitly, the only permitted value is 如果显式地将生成的列插入到中，则唯一允许的值是DEFAULT. DEFAULT。For information about generated columns, see Section 13.1.20.8, “CREATE TABLE and Generated Columns”.有关生成列的信息，请参阅第13.1.20.8节，“创建表和生成列”。
In expressions, you can use 在表达式中，可以使用DEFAULT( to produce the default value for column col_name)col_name.DEFAULT(为col_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. expr的类型转换。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 an INT, FLOAT, DECIMAL(10,6), or YEAR column inserts the value 1999, 19.9921, 19.992100, or 1999, respectively. INT、FLOAT、DECIMAL(10,6)或YEAR列中插入字符串'1999.0e-2'，分别插入值1999、19.9921、19.992100或1999。The value stored in the INT and YEAR columns is 1999 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. INT和YEAR列中存储的值是1999，因为字符串到数字的转换只查看字符串的初始部分，而可能被视为有效整数或年份。For the 对于FLOAT and DECIMAL columns, the string-to-number conversion considers the entire string a valid numeric value.FLOAT列和DECIMAL列，字符串到数字的转换将整个字符串视为有效的数值。
An expression 表达式expr can refer to any column that was set earlier in a value list. expr可以引用先前在值列表中设置的任何列。For example, you can do this because the value for 例如，之所以可以这样做，是因为col2 refers to col1, which has previously been assigned:col2的值引用了col1，而col1以前已被赋值：
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:col1的值是指col2，它是在col1之后赋值的：
INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15);
An exception occurs for columns that contain 包含AUTO_INCREMENT values. AUTO_INCREMENT值的列发生异常。Because 因为AUTO_INCREMENT values are generated after other value assignments, any reference to an AUTO_INCREMENT column in the assignment returns a 0.AUTO_INCREMENT值是在其他值赋值之后生成的，所以赋值中对AUTO_INCREMENT列的任何引用都返回0。
INSERT statements that use VALUES syntax can insert multiple rows. VALUES语法的INSERT语句可以插入多行。To do this, include multiple lists of comma-separated column values, with lists 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);
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:以下语句无效，因为它包含一个包含九个值的列表，而不是三个分别包含三个值的列表：
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3,4,5,6,7,8,9);
VALUE is a synonym for VALUES in this context. VALUE在这个上下文中是VALUES的同义词。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.无论是单个值列表还是多个列表，也不管每个列表的值数是多少，都可以使用这两种方法。
INSERT statements using VALUES ROW() syntax can also insert multiple rows. VALUES ROW()语法的INSERT语句也可以插入多行。In this case, each value list must be contained within a 在这种情况下，每个值列表必须包含在ROW() (row constructor), like this:ROW()中（ROW构造函数），如下所示：
INSERT INTO tbl_name (a,b,c)
VALUES ROW(1,2,3), ROW(4,5,6), ROW(7,8,9);
The affected-rows value for an 插入的受影响行值可以使用INSERT can be obtained using the ROW_COUNT() SQL function or the mysql_affected_rows() C API function. ROW_COUNT()SQL函数或mysql_impacted_rows()C API函数获得。See Section 12.16, “Information Functions”, and mysql_affected_rows().请参阅第12.16节，“信息函数”和mysql_affected_rows()。
If you use 如果使用INSERT ... VALUES or INSERT ... VALUES ROW() with multiple value lists, or INSERT ... SELECT or INSERT ... TABLE, the statement returns an information string in this format:INSERT ... VALUES或INSERT ... VALUES ROW()配合多个值列表，或使用INSERT ... SELECT或INSERT ... TABLE，则语句返回以下格式的信息字符串：
Records:N1Duplicates:N2Warnings:N3
If you are using the C API, the information string can be obtained by invoking the 如果您使用的是C API，那么可以通过调用mysql_info() function. mysql_info()函数来获取信息字符串。See mysql_info().请参阅mysql_info()。
Records indicates the number of rows processed by the statement. Records指示语句处理的行数。(This is not necessarily the number of rows actually inserted because （这不一定是实际插入的行数，因为Duplicates can be nonzero.) Duplicates可以不为零。）Duplicates indicates the number of rows that could not be inserted because they would duplicate some existing unique index value. Duplicates表示由于重复某些现有唯一索引值而无法插入的行数。Warnings indicates the number of attempts to insert column values that were problematic in some way. Warnings表示尝试插入列值的次数，这些列值在某种程度上有问题。Warnings can occur under any of the following conditions:在以下任何情况下都可能出现警告：
Inserting 将NULL into a column that has been declared NOT NULL. NULL插入已声明为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. INSERT语句或INSERT INTO... SELECT语句，列被设置为列数据类型的隐式默认值。This is 数字类型为0 for numeric types, the empty string ('') for string types, and the “zero” value for date and time types. 0，字符串类型为空字符串（''），日期和时间类型为“零”值。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. INSERT INTO ... SELECT语句的处理方式与多行插入相同，因为服务器不会检查SELECT语句中的结果集是否返回单行。(For a single-row （对于单行插入，在INSERT, no warning occurs when NULL is inserted into a NOT NULL column. NOT NULL列中插入NULL时不会出现警告。Instead, the statement fails with an error.)相反，该语句失败并出现错误。）
Setting a numeric column to a value that lies outside the column range. 将数值列设置为超出列范围的值。The value is clipped to the closest endpoint of the range.该值被剪裁到范围的最近端点。
Assigning a value such as 将诸如'10.34 a' to a numeric column. '10.34 a'之类的值赋给数字列。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 to 如果字符串值没有前导数字部分，则该列设置为0.0。
Inserting a string into a string column (将字符串插入超过列最大长度的字符串列（CHAR, VARCHAR, TEXT, or BLOB) that exceeds the column maximum length. CHAR、VARCHAR、TEXT或BLOB），The value is truncated to the column 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 involving AUTO_INCREMENT column values, see Section 3.6.9, “Using AUTO_INCREMENT”.AUTO_INCREMENT列值的INSERT示例，请参阅第3.6.9节，“使用自动增量”。
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 LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function.INSERT将一行插入到具有AUTO_INCREMENT列的表中，则可以使用SQL函数LAST_INSERT_ID()或C API函数mysql_insert_id()查找用于该列的值。
These two functions do not always behave identically. 这两个函数的行为并不总是相同的。The behavior of INSERT statements with respect to AUTO_INCREMENT columns is discussed further in Section 12.16, “Information Functions”, and mysql_insert_id().INSERT语句对于AUTO_INCREMENT列的行为将在第12.16节，“信息函数”和mysql_insert_id()中进一步讨论。
The INSERT statement supports the following modifiers:INSERT语句支持以下修饰符：
If you use the 如果使用LOW_PRIORITY modifier, execution of the INSERT is delayed until no other clients are reading from the table. LOW_PRIORITY修饰符，INSERT的执行将延迟，直到没有其他客户机从表中读取为止。This includes other clients that began reading while existing clients are reading, and while the 这包括在现有客户机正在读取以及INSERT LOW_PRIORITY statement is waiting. INSERT LOW_PRIORITY语句正在等待时开始读取的其他客户机。It is possible, therefore, for a client that issues an 因此，对于发出INSERT LOW_PRIORITY statement to wait for a very long time.INSERT LOW_PRIORITY语句的客户机来说，等待很长时间是可能的。
LOW_PRIORITY affects only storage engines that use only table-level locking (such as MyISAM, MEMORY, and MERGE).LOW_PRIORITY只影响仅使用表级锁定的存储引擎（如MyISAM、MEMORY和MERGE）。
LOW_PRIORITY should normally not be used with MyISAM tables because doing so disables concurrent inserts. LOW_PRIORITY通常不应用于MyISAM表，因为这样做会禁用并发插入。See Section 8.11.3, “Concurrent Inserts”.请参阅第8.11.3节，“并发插入”。
If you specify 如果指定HIGH_PRIORITY, it overrides the effect of the --low-priority-updates option if the server was started with that option. HIGH_PRIORITY，那么如果服务器是用--low-priority-updates选项启动的，它将覆盖该选项的效果。It also causes concurrent inserts not to be used. 它还会导致不使用并发插入。See Section 8.11.3, “Concurrent Inserts”.请参阅第8.11.3节，“并发插入”。
HIGH_PRIORITY affects only storage engines that use only table-level locking (such as MyISAM, MEMORY, and MERGE).HIGH_PRIORITY只影响仅使用表级锁定的存储引擎（如MyISAM、MEMORY和MERGE）。
If you use the 如果使用IGNORE modifier, ignorable errors that occur while executing the INSERT statement are ignored. IGNORE修饰符，则会忽略执行INSERT语句时发生的可忽略错误。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. IGNORE的情况下，复制表中现有UNIQUE索引或PRIMARY KEY值的行将导致重复键错误，并且语句将中止。With 使用IGNORE, the row is discarded and no error occurs. IGNORE时，将丢弃该行，并且不会发生错误。Ignored errors generate warnings instead.忽略的错误将生成警告。
IGNORE has a similar effect on inserts into partitioned tables where no partition matching a given value is found. IGNORE对插入到分区表中没有找到与给定值匹配的分区有类似的效果。Without 如果不使用IGNORE, such INSERT statements are aborted with an error. IGNORE，这样的INSERT语句将因错误而中止。When 使用INSERT IGNORE is used, the insert operation fails silently for rows containing the unmatched value, but inserts rows that are matched. INSERT IGNORE时，对于包含不匹配值的行，INSERT操作会自动失败，但会插入匹配的行。For an example, see Section 24.2.2, “LIST Partitioning”.例如，请参阅第24.2.2节，“列出分区”。
Data conversions that would trigger errors abort the statement if 如果未指定IGNORE is not specified. IGNORE，将触发错误的数据转换将中止语句。With 使用IGNORE, invalid values are adjusted to the closest values and inserted; warnings are produced but the statement does not abort. IGNORE，将无效值调整为最接近的值并插入；会产生警告，但语句不会中止。You can determine with the 您可以使用mysql_info() C API function how many rows were actually inserted into the table.mysql_info()C API函数确定实际插入到表中的行数。
For more information, see The Effect of IGNORE on Statement Execution.有关详细信息，请参阅IGNORE对语句执行的影响。
You can use 可以使用REPLACE instead of INSERT to overwrite old rows. REPLACE而不是INSERT来覆盖旧行。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. REPLACE是在处理包含重复旧行的唯一键值的新行时INSERT IGNORE的对应项：新行替换旧行，而不是被丢弃。See Section 13.2.9, “REPLACE Statement”.请参阅第13.2.9节，“REPLACE语句”。
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 occurs. ON DUPLICATE KEY UPDATE，并且插入的行会导致UNIQUE索引或PRIMARY KEY中的值重复，则会更新旧行。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. 如果将行作为新行插入，则每行受影响的行值为1；如果更新现有行，则每行受影响的行值为2；如果将现有行设置为其当前值，则每行受影响的行值为0。If you specify the 如果在连接mysqld时为CLIENT_FOUND_ROWS flag to the mysql_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. mysql_real_connect()C API函数指定CLIENT_FOUND_ROWS标志，则如果现有行设置为其当前值，则受影响的行值为1（而不是0)。See Section 13.2.6.2, “INSERT ... ON DUPLICATE KEY UPDATE Statement”.请参阅第13.2.6.2节，“INSERT ... ON DUPLICATE KEY UPDATE语句”。
INSERT DELAYED was deprecated in MySQL 5.6, and is scheduled for eventual removal. INSERT DELAYED在MySQL 5.6中被弃用，并计划最终删除。In MySQL 8.0, the 在MySQL 8.0中，DELAYED modifier is accepted but ignored. DELAYED修饰符被接受，但被忽略。Use 请改用INSERT (without DELAYED) instead. INSERT（不用DELAYED）。See Section 13.2.6.3, “INSERT DELAYED Statement”.请参阅第13.2.6.3节，“INSERT DELAYED语句”。