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

posted @ 2022-02-16 20:06 zhanglw 阅读(5267) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

zhanglw

MySQL insert 语法

公告