4.3.1.1. insert语法

最新推荐文章于 2021-12-17 16:15:47 发布

wangqiaowqo

最新推荐文章于 2021-12-17 16:15:47 发布

阅读量183

点赞数

分类专栏： Mysql 文章标签：数据库

本文链接：https://blog.csdn.net/wangqiaowqo/article/details/83979759

版权

Mysql 专栏收录该内容

63 篇文章 0 订阅

订阅专栏

insert语句，它的基本含义是往数据库中插入新的数据，其语法主要有三类，分别说明如下

1.insert into tbl_name values …


insert [low_priority | delayed | high_priority] [ignore]
    [into] tbl_name [(col_name,...)]
    {values | value} ({expr | default},...),(...),...
    [ on duplicate key update  col_name=expr[, col_name=expr] ... ]


INSERT INTO user_access_flow(user_id,access,add_time,os,os_version,app_id,from_touch) VALUES
(:1.userId, :1.access, :1.addTime, :1.os, :1.osVersion, :1.appId,
:1.fromTouch) ON DUPLICATE KEY UPDATE user_id=:1.userId, add_time=:1.addTime, access=:1.access, os=:1.os,os_version=:1.osVersion, app_id=:1.appId,from_touch=:1.fromTouch

首先[low_priority | delayed | high_priority] [ignore]部分是说明写入操作的优先级:

low_priority选项表示写操作会被延迟直到所有没有任何对tbl_name表的读操作，但是如果指定了这个选项，MyISAM引擎的并发写入就无法生效，即使它原本是可以并发插入的；

delayed选项表示插入操作暂时缓存在服务器端被延迟插入，客户端可以立即返回，当表处于空闲状态时，在进行插入操作。但是这个选项在第三类型的insert into … select …和insert … on duplicate key update 语句中无法生效；

high_priority选项表明写入时最高优先级，它的状态级别优先于--low-priority-updates，同样它会也让MyISAM引擎的并发写入失效。

特别注意的是，low_priority和high_priority选项只影响表级锁的存储引擎，如MyISAM、Memory和Merge三种引擎的表。如InnoDB引擎，这几个选项就无法生效。

ignore选项表示将执行insert语句返回的错误当成warning，即可以跳过错误语句，这样可以保证一个线程在批量执行SQL时，其中一个SQL错误不会导致整个处理都退出；而是跳过错误继续执行。这种错误包括duplicate-key的错误等。

接下来那个表名表示被插入的字段和具体的值，特别需要说明的是on duplicate key update选项，如果插入的值与原来主键、unique索引中的键值一样，那么将会执行更新操作，通过返回结果观察，影响的行数(即“affected-rows”)就是2，相对于插入操作影响的行数为1。

例子：我们往customer(customer_id(int)、customer_name(varchar)、customer_contact
(varchar)、customer_phone(varchar))插入一条数据，SQL语句如下：

insert into customer values (20101213, '百度在线', '海淀区上地百度大厦', '010-59928888');

若插入的数据，其customer_id字段是自增的，那么插入时则不能指定customer_id字段的值，需要在插入字段列表明确列出，如下所示：

insert into customer(customer_name, customer_contact, customer_phone) values ('百度在线', '海淀区上地百度大厦','010-59928888');

若原来可能一个id为20101213的客户，那么不确定的情况下可以采用on duplicate key update，方式如下：


insert into customer(customer_id, customer_phone) values 
(20101213, '010-59926666') on duplicate key update 
customer_phone = '010-59926666';

2.insert into tbl_name set col_name=expr...
insert [low_priority | delayed | high_priority] [ignore]
[into] tbl_name
set col_name={expr | default}, ...
[ on duplicate key update col_name=expr[, col_name=expr] ... ]
这种语法其实与上面的类似，只不过写法不一样，它把字段的指定挪到set后面，表示每个字段的插入值为多少，其它的部分说明与上面的一样。

例子：针对上面的例子，采用此从句的方法如下：
insert into customer set customer_name='百度在线', customer_contact='海淀区上地百度大厦', customer_phone='010-59928888';

3.insert into tbl_name select …
insert [low_priority | high_priority] [ignore]
[into] tbl_name [(col_name,...)]
select ...
[ on duplicate key update col_name=expr[, col_name=expr] ... ]

这种语法与上面的(1)和(2)类似，主要是数据是从已知的另外一个表中获取，如数据转移应用中。
如一个统计表中，有许多统计数据，数据来源于多个数据库和表，很多数据需要使用批量更新方法，(不可能查出所有数据然后一条一条去更新吧)，因此需要使用 insert into table1 select * from table2这样的语句。

同时，由于需要多次更新table，table定义了一个unique key，因此如果第二次运行或者更新第二批数据的时候，会产生error code : 1062 duplicate entry ‘xx-yy’key for 'keyname' 错误，自然我们会想到用 on duplicate key 来解除约束，这个时候就需要用到 values 取值函数values 取值函数：values(col_name)可以引用被插入的col_name的值，注意这个函数只在 insert ... Update 语句中有意义。


insert into table1 (a, b, c) select a, b, c from table2 on duplicate key update c = values(c);

注意 values(c)中的c不需要加任何修饰，table表设有 a,b 联合unique key。

4.3.1.2. insert优化tips
insert插入一条记录花费的时间由以下几个因素决定：连接、发送查询给服务器、解析查询、插入记录、插入索引和关闭连接。

此处没有考虑初始化时打开数据表的开销，因为每次运行查询只会做一次。如果是B-tree索引，随着索引数量的增加，插入记录的速度以logN的比例下降。

可以用以下几种方法来提高插入速度：
1.如果要在同一个客户端在同一时间内插入很多记录，可以使用insert语句附带有多个values值。这种做法比使用单一值的insert语句快多了(在一些情况下比较快)。如果是往一个非空数据表增加记录，可以调整变量bulk_insert_buffer_size的值使其更快。

2.对实时性要求不高情况下，如要从不用的客户端插入大量记录，使用insert delayed语句也可以提高速度。

3.对应MyISAM，可以在select语句正在运行时插入记录，只要表中没有空洞（由删除记录引起）或者打开MySQL相关设置。

4.对于MyISAM，在进行大批量插入前可以将索引关闭，等全部插入完毕后再开启索引，进行索引更新。

5.想要将一个文本文件加载到数据表中，可以使用load data infile。速度上通常是使用大量insert语句的20倍。

6.对于InnoDB的insert，若插入上百万，建议分批进行，批量插入3000~5000后，sleep数秒钟之后进行下一次插入，可以避免同步延迟的累积。
----------------------------------------------------------
现在问题来了，如果INSERT多行记录， ON DUPLICATE KEY UPDATE后面字段的值怎么指定？要知道一条INSERT语句中只能有一个ON DUPLICATE KEY UPDATE，到底他会更新一行记录，还是更新所有需要更新的行。这个问题困扰了我很久了，其实使用VALUES()函数一切问题都解决了。

举个例子，字段a被定义为UNIQUE，并且原数据库表table中已存在记录(2,2,9)和(3,2,1)，如果插入记录的a值与原有记录重复，则更新原有记录，否则插入新行：

1
2
3
4
5
6
INSERT INTO TABLE (a,b,c) VALUES
(1,2,3),
(2,5,7),
(3,3,6),
(4,8,2)
ON DUPLICATE KEY UPDATE b=VALUES(b);

以上SQL语句的执行，发现(2,5,7)中的a与原有记录(2,2,9)发生唯一值冲突，则执行ON DUPLICATE KEY UPDATE，将原有记录(2,2,9)更新成(2,5,9)，将(3,2,1)更新成(3,3,1)，插入新记录(1,2,3)和(4,8,2)

注意：ON DUPLICATE KEY UPDATE只是MySQL的特有语法，并不是SQL标准语法！