MySQL 8.0-13.2.6 INSERT Statement

最新推荐文章于 2024-05-29 08:59:05 发布

翻译最新推荐文章于 2024-05-29 08:59:05 发布 · 558 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://dev.mysql.com/doc/refman/8.0/en/insert.html

文章标签：

#mysql #less #数据库

Mysql 专栏收录该内容

142 篇文章

订阅专栏

MySQL 8.0的INSERT语句用于向现有表中插入新行，支持VALUES、SELECT和ON DUPLICATE KEY UPDATE等形式。INSERT ... SELECT允许从其他表中插入数据，ON DUPLICATE KEY UPDATE在遇到重复键时更新已有行。INSERT DELAYED在MySQL 8.0中已被废弃。插入操作需要相应的表权限，涉及列的默认值、类型转换和约束。

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 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 24.5, “Partition Selection”.

当插入到分区表时，您可以控制哪些分区和子分区接受新行。PARTITION子句接受表的一个或多个分区或子分区(或两者)的逗号分隔的名称列表。如果给定的INSERT语句要插入的任何行与列出的分区不匹配，则INSERT语句将失败，并出现错误:发现一行与给定的分区集不匹配。

tbl_name is the table into which rows should be inserted. Specify the columns for which the statement provides values as follows:

Tbl_name是应该插入行的表。指定语句为其提供值的列，如下:

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. For the INSERT TABLE form, the number of columns in the source table must match the number of columns to be inserted.
在表名后面提供用逗号分隔的列名的圆括号列表。这种情况下，每个指定列的值必须由VALUES列表、VALUES ROW()列表或SELECT语句提供。对于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. If you do not know the order of the columns in the table, use DESCRIBE tbl_name to find out.
如果您没有指定INSERT的列名列表…值或INSERT…SELECT，表中每一列的值必须由values列表、SELECT语句或table语句提供。如果您不知道表中列的顺序，可以使用DESCRIBE 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. 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”. See also Section 1.7.3.3, “Enforced Constraints on Invalid Data”. 如果未启用strict SQL模式，则任何未显式给定值的列都将被设置为其默认值(显式或隐式)。例如，如果指定的列列表没有命名表中的所有列，则将未命名列设置为它们的默认值。默认值赋值在11.6节“数据类型默认值”中有描述。参见第1.7.3.3节，“对无效数据的强制约束”。

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”. 如果启用了严格SQL模式，INSERT语句如果没有为没有默认值的每一列指定显式值，就会生成错误。参见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. If strict mode is enabled, an error occurs if any column has no default value. 如果没有启用strict模式，MySQL将对任何没有明确定义default的列使用隐式默认值。如果启用了strict模式，如果任何列没有默认值，则会发生错误。
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 must provide the list of column names corresponding to each value in the VALUES list.
使用关键字DEFAULT将列显式设置为其默认值。这使得编写INSERT语句为除少数几列之外的所有列赋值变得更加容易，因为它使您能够避免编写不完整的values列表，其中不包括表中每一列的值。否则，必须提供与VALUES列表中的每个值对应的列名列表。
If a generated column is inserted into explicitly, the only permitted value is DEFAULT. For information about generated columns, see Section 13.1.20.8, “CREATE TABLE and Generated Columns”.
如果显式插入生成的列，则唯一允许的值是DEFAULT。有关生成列的信息，请参见13.1.20.8节“CREATE TABLE and generated columns”。
In expressions, you can use DEFAULT(col_name) to produce the default value for column 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. 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. 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. For the FLOAT and DECIMAL columns, the string-to-number conversion considers the entire string a valid numeric value.
如果表达式数据类型与列数据类型不匹配，则可能发生提供列值的表达式expr的类型转换。给定值的转换可能导致不同的插入值，这取决于列类型。例如，将字符串'1999.0e-2'插入INT、FLOAT、DECIMAL(10、6)或YEAR列，分别插入值1999、19.9921、19.992100或1999。存储在INT和YEAR列中的值是1999，因为字符串到数字的转换只查看可能被认为是有效整数或年份的字符串的初始部分。对于FLOAT和DECIMAL列，字符串到数字转换将整个字符串视为有效的数值。
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: 表达式expr可以引用之前在值列表中设置的任何列。例如，你可以这样做，因为col2的值指向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，而col2是在col1后面赋值的
```
INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15);
```
An exception occurs for columns that contain AUTO_INCREMENT values. 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值是在其他值赋值之后生成的，所以赋值中对AUTO_INCREMENT列的任何引用都返回0。

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:

使用VALUES语法的INSERT语句可以插入多行。为此，需要包含多个用逗号分隔的列值列表，列表用括号括起来，用逗号分隔。例子:

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:

每个值列表必须包含与每行要插入的值完全相同的值。下面的语句是无效的，因为它包含一个9个值的列表，而不是每个3个值的3个列表:

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. 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.

在这个上下文中，VALUE是VALUES的同义词。这两种方法都不涉及值列表的数量，也不涉及每个列表的值数量。无论是单个值列表还是多个列表，都可以使用这两种方法，而不管每个列表有多少个值。

INSERT statements using VALUES ROW() syntax can also insert multiple rows. In this case, each value list must be contained within a ROW() (row constructor), like this:

使用VALUES ROW()语法的INSERT语句也可以插入多行。在本例中，每个值列表必须包含在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. See Section 12.16, “Information Functions”, and mysql_affected_rows().

INSERT的受影响行值可以使用ROW_COUNT() SQL函数或mysql_affected_rows() C API函数获得。参见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…值或INSERT…值ROW()与多个值列表，或INSERT…选择或插入…TABLE，语句返回如下格式的信息字符串:

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 mysql_info().

如果您正在使用C API，则可以通过调用mysql_info()函数来获得信息字符串。看到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:

记录表示语句处理的行数。(这不一定是实际插入的行数，因为duplicate可以是非零的。)duplicate表示无法插入的行数，因为它们会重复某些现有的唯一索引值。警告指示以某种方式插入有问题的列值的尝试次数。警告可能出现在下列任何情况下:

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.)
将NULL插入到已声明为NOT NULL的列中。对于多行INSERT语句或INSERT INTO…在SELECT语句中，列被设置为列数据类型的隐式默认值。数值类型为0，字符串类型为空字符串(")，日期和时间类型为" 0 "值。插入……SELECT语句的处理方式与多行插入相同，因为服务器不会检查SELECT语句的结果集，以确定它是否返回单个行。(对于单行INSERT，当将NULL插入到NOT NULL列时不会出现警告。相反，语句失败并出现错误。)
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. 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.
向数字列赋值，例如'10.34 a'。删除末尾的非数字文本，插入其余的数字部分。如果字符串值没有前导数字部分，则列设置为0。
Inserting a string into a string column (CHAR, VARCHAR, TEXT, or BLOB) that exceeds the column maximum length. The value is truncated to the column maximum length.
将字符串插入超过列最大长度的字符串列(CHAR、VARCHAR、TEXT或BLOB)。该值被截断为列的最大长度。
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节“使用AUTO_INCREMENT”。

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列的表中，您可以通过使用LAST_INSERT_ID() SQL函数或mysql_insert_id() C API函数找到该列使用的值。

Note

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(). 这两个函数的行为并不总是相同的。关于AUTO_INCREMENT列的INSERT语句的行为将在第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. 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. 如果使用LOW_PRIORITY修饰符，INSERT的执行将被延迟，直到没有其他客户端从表中读取数据。这包括在现有客户端正在读取和INSERT LOW_PRIORITY语句等待时开始读取的其他客户端。因此，对于发出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)。

Note

LOW_PRIORITY should normally not be used with MyISAM tables because doing so disables concurrent inserts. See Section 8.11.3, “Concurrent Inserts”. LOW_PRIORITY通常不应该用于MyISAM表，因为这样做会禁止并发插入。请参见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. It also causes concurrent inserts not to be used. See Section 8.11.3, “Concurrent Inserts”.
如果指定HIGH_PRIORITY，它将覆盖——low-priority-updates选项(如果服务器是用该选项启动的)的效果。它还会导致不使用并发插入。

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. 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 is discarded and no error occurs. Ignored errors generate warnings instead.如果使用IGNORE修饰符，则在执行INSERT语句时发生的可忽略错误将被忽略。例如，如果不使用IGNORE，重复表中现有UNIQUE索引或PRIMARY KEY值的行将导致重复键错误，语句将终止。使用IGNORE，该行将被丢弃，不会发生错误。被忽略的错误会生成警告。

IGNORE has a similar effect on inserts into partitioned tables where no partition matching a given value is found. Without IGNORE, such INSERT statements are aborted with an error. When INSERT 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 24.2.2, “LIST Partitioning”.在没有找到匹配给定值的分区的分区表中插入时，IGNORE也有类似的效果。如果没有IGNORE，这样的INSERT语句将被终止并返回错误。当使用INSERT IGNORE时，对于包含不匹配值的行，插入操作会静默失败，但会插入匹配的行。

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.如果未指定IGNORE，将触发错误的数据转换将中止语句。使用IGNORE，无效值被调整到最接近的值并插入;会产生警告，但语句不会中止。您可以使用mysql_info() C API函数确定有多少行实际上被插入到表中。

For more information, see The Effect of IGNORE on Statement Execution.

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 Statement”.您可以使用REPLACE代替INSERT来覆盖旧的行。在处理包含重复旧行的唯一键值的新行时，REPLACE与INSERT IGNORE是对等的:新行替换旧行，而不是被丢弃。
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. 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 the 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. See Section 13.2.6.2, “INSERT ... ON DUPLICATE KEY UPDATE Statement”.
如果指定ON DUPLICATE KEY UPDATE，插入的行会导致UNIQUE索引或PRIMARY KEY中的重复值，则会发生旧行的UPDATE。如果将行作为新行插入，则每行的受影响行值为1，如果更新现有行则为2，如果将现有行设置为其当前值则为0。如果您在连接到mysqld时为mysql_real_connect() C API函数指定CLIENT_FOUND_ROWS标志，则如果将现有行设置为其当前值，则受影响行值为1(不是0)。
INSERT DELAYED was deprecated in MySQL 5.6, and is scheduled for eventual removal. In MySQL 8.0, the DELAYED modifier is accepted but ignored. Use INSERT (without DELAYED) instead. See Section 13.2.6.3, “INSERT DELAYED Statement”.