13.2.5 INSERT 语句
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)] ...
[ON DUPLICATE KEY UPDATE assignment_list]
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
[PARTITION (partition_name [, partition_name] ...)]
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] ...)]
SELECT ...
[ON DUPLICATE KEY UPDATE assignment_list]
value:
{expr | DEFAULT}
value_list:
value [, value] ...
assignment:
col_name = value
assignment_list:
assignment [, assignment] ...
INSERT将新行插入到现有 table 中。语句的插入...值和插入...设置形式基于明确指定的值插入行。 插入...选择table 单插入从另一个 table 或多个 table 中选择的行。如果要插入的行将导致UNIQUE
索引或PRIMARY KEY
中的重复值,则带有ON DUPLICATE KEY UPDATE
子句的INSERT可以更新现有行。
有关插入...选择和插入...在重复的密钥更新上的其他信息,请参见第 13.2.5.1 节“ INSERT ... SELECT 语句”和第 13.2.5.2 节“在重复密钥更新语句上插入...”。
在 MySQL 5.7 中,DELAYED
关键字被接受但被服务器忽略。因此,请参见第 13.2.5.3 节“ INSERT DELAYED 语句”,
插入 table 需要 table 的INSERT特权。如果使用ON DUPLICATE KEY UPDATE
子句,并且使用重复键代替执行UPDATE,则该语句需要UPDATE特权才能更新列。对于已读取但未修改的列,您仅需要SELECT特权(例如,仅在ON DUPLICATE KEY UPDATE
子句中* col_name
* = * expr
*赋值的右侧引用的列)。
当插入分区 table 时,您可以控制哪些分区和子分区接受新行。 PARTITION
选项获取 table 的一个或多个分区或子分区(或两者)的逗号分隔名称列 table。如果给定的INSERT语句插入的任何行与列出的分区之一都不匹配,则INSERT语句将失败,并显示错误“找到与给定的分区集不匹配的行”。有关更多信息和示例,请参见第 22.5 节“分区选择”。
您可以使用REPLACE而不是INSERT来覆盖旧行。 REPLACE在处理新行时与INSERT IGNORE相对,该新行包含与旧行重复的唯一键值:新行替换了旧行,而不是被丢弃。参见第 13.2.8 节“ REPLACE 语句”。
tbl_name
*是应在其中插入行的 table。指定该语句为其提供值的列,如下所示:
-
在 table 名后提供一个用逗号分隔的列名的带括号的列 table。在这种情况下,必须由
VALUES
列 table 或SELECT语句提供每个命名列的值。 -
如果您未指定插入...值或插入...选择的列名列 table,则 table 中每一列的值都必须由
VALUES
列 table 或SELECT语句提供。如果您不知道 table 中各列的 Sequences,请使用DESCRIBE tbl_name
进行查找。 -
SET
子句按名称显式指示列,以及用于分配每个列的值。
列值可以通过几种方式给出:
- 如果未启用严格 SQL 模式,则任何未明确指定值的列都将设置为其默认值(显式或隐式)。例如,如果指定的列列 table 未命名 table 中的所有列,则未命名的列将设置为其默认值。默认值分配在第 11.6 节“数据类型默认值”中描述。另请参见第 1.8.3.3 节“对无效数据的约束”。
如果启用了严格的 SQL 模式,则INSERT语句未为没有默认值的每个列都指定一个显式值时,将生成错误。参见第 5.1.10 节“服务器 SQL 模式”。
- 如果列列 table 和
VALUES
列 table 均为空,则INSERT将创建一行并将每一列设置为其默认值:
INSERT INTO tbl_name () VALUES();
如果未启用严格模式,则 MySQL 对没有明确定义默认值的任何列使用隐式默认值。如果启用了严格模式,则任何列都没有默认值都会发生错误。
-
使用关键字
DEFAULT
可以将列明确设置为其默认值。这使编写INSERT语句为所有列(少数列)分配值变得更加容易,因为它使您避免编写不完整的VALUES
列 table,该列 table 不包含 table 中每一列的值。否则,您必须提供与VALUES
列 table 中的每个值相对应的列名列 table。 -
如果将生成的列显式插入,则唯一允许的值为
DEFAULT
。有关生成的列的信息,请参见第 13.1.18.7 节“创建 table 和生成的列”。
-
在 table 达式中,您可以使用DEFAULT(col_name)来生成列*
col_name
*的默认值。 -
如果 table 达式数据类型与列数据类型不匹配,则可能会进行提供列值的 table 达式*
expr
*的类型转换。给定值的转换可能导致不同的插入值,具体取决于列类型。例如,将字符串'1999.0e-2'
插入到INT,FLOAT,DECIMAL(10,6)或YEAR列中将分别插入值1999
,19.9921
,19.992100
或1999
。存储在INT和YEAR列中的值是1999
,因为字符串到数字的转换只看了字符串初始部分的多少,可能被视为有效的整数或年份。对于FLOAT和DECIMAL列,字符串到数字的转换将整个字符串视为有效的数值。 -
table 达式*
expr
*可以引用值列 table 中先前设置的任何列。例如,您可以这样做,因为col2
的值引用col1
,该值先前已被分配:
INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2);
但是以下内容不合法,因为col1
的值引用col2
,该值是在col1
之后分配的:
INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15);
包含AUTO_INCREMENT
值的列会发生异常。由于AUTO_INCREMENT
个值是在其他值分配之后生成的,因此对分配中AUTO_INCREMENT
列的任何引用都将返回0
。
使用VALUES
语法的INSERT语句可以插入多行。为此,请包括多个用逗号分隔的列值的列 table,列 table 用括号括起来并用逗号分隔。例:
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
每个值列 table 必须包含与每行要插入的值一样多的值。以下语句无效,因为它包含一个九个值的列 table,而不是三个三个值的列 table:
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3,4,5,6,7,8,9);
在这种情况下,VALUE
是VALUES
的同义词。既不暗示关于值列 table 的数量,也不暗示每个列 table 的值的数量。无论是单个值列 table 还是多个列 table,无论每个列 table 的值数量如何,都可以使用。
可以使用ROW_COUNT() SQL 函数或mysql_affected_rows() C API 函数获得INSERT的受影响行值。参见第 12.15 节“信息功能”和第 27.7.6.1 节“ mysql_affected_rows()”。
如果您使用带有多个值列 table 的插入...值语句或插入...选择,则该语句以以下格式返回信息字符串:
Records: N1 Duplicates: N2 Warnings: N3
如果使用的是 C API,则可以通过调用mysql_info()函数获取信息字符串。参见第 27.7.6.36 节“ mysql_info()”。
Records
table 示该语句处理的行数。 (这不一定是实际插入的行数,因为Duplicates
可以为非零.)Duplicates
table 示由于它们会复制某些现有唯一索引值而无法插入的行数。 Warnings
table 示尝试插入以某种方式存在问题的列值的次数。在以下任何情况下都可能发生警告:
-
将
NULL
插入已声明为NOT NULL
的列中。对于多行INSERT语句或插入...选择语句,该列设置为列数据类型的隐式默认值。对于数字类型,此值为0
;对于字符串类型,此值为空字符串(''
);对于日期和时间类型,其值为“零”。 插入...选择语句的处理方式与多行插入相同,因为服务器不会检查SELECT的结果集以查看其是否返回单行。 (对于单行INSERT,将NULL
插入到NOT NULL
列中时不会发生警告。而是,该语句失败并出现错误。) -
将数字列设置为超出列范围的值。该值将被裁剪到范围的最近端点。
-
为数字列分配一个值,例如
'10.34 a'
。尾随的非数字文本被去除,其余的数字部分被插入。如果字符串值没有前导数字部分,则该列设置为0
。 -
将字符串插入超过该列最大长度的字符串列(CHAR,VARCHAR,TEXT或BLOB)中。该值将被截断为该列的最大长度。
-
在数据类型非法的日期或时间列中插入一个值。该列设置为该类型的适当零值。
-
有关包含
AUTO_INCREMET
列值的INSERT示例,请参见第 3.6.9 节“使用 AUTO_INCREMENT”。
如果INSERT在具有AUTO_INCREMENT
列的 table 中插入一行,则可以使用LAST_INSERT_ID() SQL 函数或mysql_insert_id() C API 函数找到用于该列的值。
Note
这两个功能的行为并不总是相同的。 INSERT语句相对于AUTO_INCREMENT
列的行为将在第 12.15 节“信息功能”和第 27.7.6.38 节“ mysql_insert_id()”中进一步讨论。
INSERT语句支持以下修饰符:
- 如果使用
LOW_PRIORITY
修饰符,则INSERT的执行将延迟,直到没有其他 Client 端从 table 中读取为止。这包括在现有 Client 端正在阅读时和INSERT LOW_PRIORITY
语句正在 await 时开始阅读的其他 Client 端。因此,发出INSERT LOW_PRIORITY
语句的 Client 端可能要 await 很长时间。
LOW_PRIORITY
仅影响仅使用 table 级锁定的存储引擎(例如MyISAM
,MEMORY
和MERGE
)。
Note
LOW_PRIORITY
通常不应与MyISAM
table 一起使用,因为这样做会禁用并发插入。参见第 8.11.3 节“并发插入”。
- 如果指定
HIGH_PRIORITY
,并且服务器以该选项启动时,它将覆盖--low-priority-updates选项的效果。这还会导致不使用并发插入。参见第 8.11.3 节“并发插入”。
HIGH_PRIORITY
仅影响仅使用 table 级锁定的存储引擎(例如MyISAM
,MEMORY
和MERGE
)。
- 如果使用
IGNORE
修饰符,则在执行INSERT语句时发生的错误将被忽略。例如,如果没有IGNORE
,则复制 table 中现有UNIQUE
索引或PRIMARY KEY
值的行将导致重复键错误,并且该语句将中止。使用IGNORE
,该行将被丢弃,并且不会发生错误。被忽略的错误会生成警告。
IGNORE
对插入分区 table 的效果类似,在分区 table 中找不到与给定值匹配的分区。如果没有IGNORE
,则此类INSERT语句会因错误而中止。使用INSERT IGNORE时,对于包含不匹配值的行,插入操作将以静默方式失败,但是将插入匹配的行。有关示例,请参见第 22.2.2 节“列 table 分区”。
如果未指定IGNORE
,则会触发错误的数据转换将中止该语句。使用IGNORE
,无效值将调整为最接近的值并插入;产生警告,但该语句不会中止。您可以使用mysql_info() C API 函数确定 table 中实际插入了多少行。
有关更多信息,请参见IGNORE 关键字和严格 SQL 模式的比较。
- 如果指定
ON DUPLICATE KEY UPDATE
,并且插入的行会导致UNIQUE
索引或PRIMARY KEY
中的值重复,则会出现旧行的UPDATE。如果将行作为新行插入,则每行的受影响行值为 1;如果更新了现有行,则为 2;如果将现有行设置为其当前值,则为 0.如果在连接到mysqld时为mysql_real_connect() C API 函数指定CLIENT_FOUND_ROWS
标志,则在将现有行设置为当前值的情况下,受影响的行值为 1(而不是 0)。参见第 13.2.5.2 节“在重复密钥更新语句上插入...”。
- INSERT DELAYED在 MySQL 5.6 中已弃用,并计划最终删除。在 MySQL 5.7 中,
DELAYED
修饰符被接受但被忽略。改用INSERT
(不带DELAYED
)。参见第 13.2.5.3 节“ INSERT DELAYED 语句”。
使用存储引擎(例如MyISAM)使用 table 级锁来影响分区 table 的INSERT
语句仅锁定那些实际插入行的分区。 (对于使用行级锁定的诸如InnoDB之类的存储引擎,不会发生分区锁定。)有关更多信息,请参见第 22.6.4 节“分区和锁定”。