MySQL insert 语法
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)] ...
|
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] ...
- 1、INSERT 语句向已存在的表插入新行, INSERT ... VALUES, INSERT ... VALUES ROW(), 和 INSERT ... SET 这三种形式需要明确指定列的值,而 INSERT ... SELECT 形式是插入从其他一个或多个表查询的结果。
- 2、ON DUPLICATE KEY UPDATE 从句可以在 插入包含UNIQUE 索引 或 PRIMARY KEY 的列,且遇到该列的值已存在的情况时,选择更新其他列,而不至于报错。
- 3、MySQL 8.0.19 及以后的版本中: 1) 可以使用 INSERT ... TABLE 来插入单表;2) 可以将插入行起个别名,用到 ON DUPLICATE KEY UPDATE 从句中,简化书写。
- 4、完整的 INSERT 语句需要: 插入权限,更新权限,查询权限。
- 5、插入有分区的表时,需要使用 PARTITION 从句,后接分区名。如果插入行没有匹配到分区,则失败
- 6、插入语句:1)如果指定了列名(可以部分列名),值可以通过 VALUES , VALUES ROW() , or SELECT 语句给出,值的顺序和列名的顺序要一致。2)如果没有指定列名,则需要给出全部列的值,其顺序与表结构中列的顺序一致。
- 7、在非 SQL 严格模式下,没有指定的列则会用默认值(或隐式默认值)插入。而在严格模式下,如果某个列没有指定默认值,且插入时没有给该列指定一个值,则会报错。建议 使用 DEFAULT 明确地设置列的默认值。
- 8、如果列名和列值都为空,则插入一个所有列都是默认值的行
INSERT INTO tbl_name () VALUES();
- 9、给会自动生成值的列指定值,只能指定 DEFAULT。自动生成值的列,指的是其值是通过其他列计算得到的,在创建表时指定了表达式。
- 10、使用表达式时,可使用 DEFAULT(col_name) 给该列指定默认值
- 11、在表达式中,可能会出现类型转换。比如字符串 '1999.0e-2' 在插入 INT, FLOAT, DECIMAL(10,6), YEAR 类型的列时,会分别转换为 1999, 19.9921, 19.992100, 1999
- 12、使用表达式时,可以使用值列表中之前已经设置过的列。如果参考使用的是 AUTO_INCREMENT 的列(自增列没有指定值),则自增列返回的值是0,因为 AUTO_INCREMENT 列是在其他列都赋值之后才生成的。
# 在设置 col2 的值时,使用了col1
INSERT INTO tbl_name (col1,col2) VALUES(15, col1*2);
# id 列自增,结果 col2 的值是 0
INSERT INTO tbl_name (col1,col2) VALUES(15, id*2);
# id 列自增,结果 col2 的值是 200
INSERT INTO tbl_name (id,col1,col2) VALUES(100,15, id*2);
# id 列自增,结果 col2 的值是 0
INSERT INTO tbl_name (id,col1,col2) VALUES(default, 15, id*2);
- 13、多行插入
# 使用 values 形式
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3), (4,5,6), (7,8,9);
# 使用 values row 形式
INSERT INTO tbl_name (a,b,c) VALUES ROW(1,2,3), ROW(4,5,6), ROW(7,8,9);
-
14、使用 LOW_PRIORITY 修饰符,会延迟插入,等到没有其他客户端读取这个表时,才执行插入语句,即插入优先级低于读取优先级。 使用HIGH_PRIORITY 修饰符 提高 插入优先级,会覆盖 --low-priority-updates 选项的作用。
LOW_PRIORITY 和 HIGH_PRIORITY都只会影响使用表级锁的引擎(如 MyISAM, MEMORY, and MERGE),也都会造成不能并发插入数据。 -
15、使用 IGNORE 修饰符,会在执行插入语句时忽略一些可忽略的错误(并不是全部错误),而继续完成数据的插入。比如 UNIQUE 索引 or PRIMARY KEY 的列 重复数据插入 的错误,就会忽略,但数据不会新增。
-
16、
- 1) INSERT ... SELECT 语句,可以通过查询语句(SELECT)实现快速插入
- 2)从 MySQL 8.0.19 版本开始,可以使用TABLE 代替 SELECT,用来将源表中的所有列插入到目标表中。 TABLE tb 等价于 SELECT * FROM tb。
- 3)INSERT语句的目标表可能出现在查询的SELECT部分的FROM子句中,或者作为TABLE 命名的表。但是,不能在子查询中插入表同一表中的数据。
能通过INSERT... SELECT 语句插入同一个表的数据,是因为MySQL会创建一个内部临时表来保存SELECT中的行,然后将这些行插入到目标表中。另外该表本身不能是临时表。 - 4) INSERT ... SELECT 不允许并发插入
- 5) INSERT ... SELECT 允许在 SELECT部分使用 别名,但 TABLE 语句不支持别名
TABLE table_name [ORDER BY column_name] [LIMIT number [OFFSET number]]
INSERT INTO tbl_temp2 (fld_id)
SELECT tbl_temp1.fld_order_id
FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;
# 从 MySQL 8.0.19 版本开始,可以使用TABLE 代替 SELECT
INSERT INTO ta TABLE tb;
# 插入本表中的数据
INSERT INTO computer_room (room_name, room_address, room_maintainer, creator_id)
SELECT room_name, room_address, room_maintainer, creator_id FROM computer_room WHERE id=7
- 17、INSERT ... ON DUPLICATE KEY UPDATE, 可以处理 UNIQUE index or PRIMARY KEY 列 重复数据的问题
- 1)ON DUPLICATE KEY UPDATE 从句 尽量避免包含多个唯一键的列,因为多个唯一键的列可能会匹配到多行,但也只修改一行。
- 2)影响行数:如果插入新行是1;如果是更新行是2;如果新设置的值和原来一样是0,但当连接到mysqld使用 mysql_real_connect() C API 函数且指定了CLIENT_FOUND_ROWS 标志,结果仍然是1,而不是0。
- 3)无论是新增还是更新, LAST_INSERT_ID() 函数 返回的都是 AUTO_INCREMENT 的值
# a 是 唯一键,且已存在 a 为1的 行
# 下面两个语句,结果类似:数据都会一样,只是对于 InnoDB 的表 且 a 是自增的列 ,INSERT语句会增加 auto-incremen (自增)的值,UPDATE 语句不会。
INSERT INTO t1 (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1;
UPDATE t1 SET c=c+1 WHERE a=1;
# 使用 VALUES(col_name),获取语句中的相应列的值
INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6)
ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);
# 等价于下面两句
INSERT INTO t1 (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=3;
INSERT INTO t1 (a,b,c) VALUES (4,5,6)
ON DUPLICATE KEY UPDATE c=9;
# MySQL 8.0.19 后, 使用别名 替代 VALUES,获取语句中的值 ,别名要唯一,能够区分
# 行别名
INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new
ON DUPLICATE KEY UPDATE c = new.a+new.b;
# 列别名,行别名依旧要有
INSERT INTO t1 (a,b,c) VALUES (1,2,3),(4,5,6) AS new(m,n,p)
ON DUPLICATE KEY UPDATE c = m+n;
# set 形式语句
INSERT INTO t1 SET a=1,b=2,c=3 AS new
ON DUPLICATE KEY UPDATE c = new.a+new.b;
INSERT INTO t1 SET a=1,b=2,c=3 AS new(m,n,p)
ON DUPLICATE KEY UPDATE c = m+n;
## 从 MySQL 8.0.20 开始,INSERT ... SELECT ... ON DUPLICATE KEY UPDATE 中的 UPDATE 使用 VALUES(),会抛出warning
INSERT INTO t1
SELECT c, c+d FROM t2
ON DUPLICATE KEY UPDATE b = VALUES(b);
# 使用子查询替换,就不会有警告
INSERT INTO t1
SELECT * FROM (SELECT c, c+d AS e FROM t2) AS dt
ON DUPLICATE KEY UPDATE b = e;
# 直接使用联合查询会报错
INSERT INTO t1 (a, b)
SELECT c, d FROM t2
UNION
SELECT e, f FROM t3
ON DUPLICATE KEY UPDATE b = b + c;
# 需要 将联合查询作为一个整体的导出表
INSERT INTO t1 (a, b)
SELECT * FROM
(SELECT c, d FROM t2
UNION
SELECT e, f FROM t3) AS dt
ON DUPLICATE KEY UPDATE b = b + c;
- 18、DELAYED 修饰符: 在 MySQL 5.6 就过时了,在 MySQL8.0 后 已经不支持了,会忽略这个关键词。其过程是:立即响应发起插入请求的客户端,但插入语句先排队,等到该表没有其他线程使用时执行插入语句。
https://dev.mysql.com/doc/refman/8.0/en/insert.html
https://dev.mysql.com/doc/refman/8.0/en/insert-select.html
https://dev.mysql.com/doc/refman/8.0/en/table.html
https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html
分类:
后端 / 后端-DB-mysql
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 我与微信审核的“相爱相杀”看个人小程序副业
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· spring官宣接入deepseek,真的太香了~