insert语句用法(sql数据库insert用法)-昊阳知识网

前言：

提到MySQL的Insert语句，大家肯定不陌生，可以张口就来：不就是insert into table values(xxx,xxx,xxx)嘛！

没错，但在实战中，根据不同的需求场景，插入操作在语法、执行方式上的用法多种多样；今天，来给小伙伴们从这两方面分享一下搬砖心得；

这里不再赘述，注意顺序即可，不建议小伙伴们去掉前面括号的内容，别问为什么，容易被同事骂。

2、插入或更新：

如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就更新该记录；

此时，可以使用 “INSERT INTO … ON DUPLICATE KEY UPDATE …” 语句：

情景示例：这张表存了用户历史充值金额，如果第一次充值就新增一条数据，如果该用户充值过就累加历史充值金额，需要保证单个用户数据不重复录入。

这时可以使用 “INSERT INTO … ON DUPLICATE KEY UPDATE …” 语句。

注意事项：”INSERT INTO … ON DUPLICATE KEY UPDATE …”语句是基于唯一索引或主键来判断唯一（是否存在）的。

如下SQL所示，需要在username字段上建立唯一索引（Unique），transId设置自增即可。

–用户陈哈哈充值了30元买会员INSERTINTOtotal_transaction(t_transId,username,total_amount,last_transTime,last_remark)VALUES(null,’chenhaha’,30,’2020-06-1120:00:20′,’充会员’)ONDUPLICATEKEYUPDATEtotal_amount=total_amount 30,last_transTime=’2020-06-1120:00:20′,last_remark=’充会员’;–用户陈哈哈充值了100元买瞎子至高之拳皮肤INSERTINTOtotal_transaction(t_transId,username,total_amount,last_transTime,last_remark)VALUES(null,’chenhaha’,100,’2020-06-1120:00:20′,’购买盲僧至高之拳皮肤’)ONDUPLICATEKEYUPDATEtotal_amount=total_amount 100,last_transTime=’2020-06-1121:00:00′,last_remark=’购买盲僧至高之拳皮肤’;

若username=’chenhaha’的记录不存在，INSERT语句将插入新记录，否则，当前username=’chenhaha’的记录将被更新，更新的字段由UPDATE指定。

对了，ON DUPLICATE KEY UPDATE 为 MySQL特有语法，比如在MySQL迁移Oracle或其他DB时，类似的语句要改为 MERGE INTO 语法，兼容性让人想骂街。但没办法，就像用WPS写的xlsx用Office无法打开一样。

3、插入或替换：

如果我们想插入一条新记录（INSERT），但如果记录已经存在，就先删除原记录，再插入新记录。

情景示例：这张表存的每个客户最近一次交易订单信息，要求保证单个用户数据不重复录入，且执行效率最高，与数据库交互最少，支撑数据库的高可用。

此时，可以使用”REPLACE INTO”语句，这样就不必先查询，再决定是否先删除再插入。

重要的事情说三遍：

“REPLACE INTO”语句是基于唯一索引或主键来判断唯一（是否存在）的。”REPLACE INTO”语句是基于唯一索引或主键来判断唯一（是否存在）的。”REPLACE INTO”语句是基于唯一索引或主键来判断唯一（是否存在）的。

注意事项：如下SQL所示，需要在username字段上建立唯一索引（Unique），transId设置自增即可。

–20点充值REPLACEINTOlast_transaction(transId,username,amount,trans_time,remark)VALUES(null,’chenhaha’,30,’2020-06-1120:00:20′,’会员充值’);–21点买皮肤REPLACEINTOlast_transaction(transId,username,amount,trans_time,remark)VALUES(null,’chenhaha’,100,’2020-06-1121:00:00′,’购买盲僧至高之拳皮肤’);

若username=’chenhaha’的记录不存在，REPLACE语句将插入新记录（首次充值），否则，当前username=’chenhaha’的记录将被删除，然后再插入新记录。

id不要给具体值，不然会影响SQL执行，业务有特殊需求除外。

小tips：

ON DUPLICATE KEY UPDATE：如果插入行出现唯一索引或者主键重复时，则执行旧的update；如果不会导致唯一索引或者主键重复时，就直接添加新行。

REPLACE INTO：如果插入行出现唯一索引或者主键重复时，则delete老记录，而录入新的记录；如果不会导致唯一索引或者主键重复时，就直接添加新行。

replace into 与 insert on deplicate udpate 比较：

在没有主键或者唯一索引重复时，replace into 与 insert on deplicate udpate 相同。在主键或者唯一索引重复时，replace是delete老记录，而录入新的记录，所以原有的所有记录会被清除，这个时候，如果replace语句的字段不全的话，有些原有的比如c字段的值会被自动填充为默认值（如Null）。细心地朋友们会发现，insert on deplicate udpate只是影响一行，而REPLACE INTO可能影响多行，为什么呢？写在文章最后一节咯~4、插入或忽略：

如果我们希望插入一条新记录（INSERT），但如果记录已经存在，就啥事也不干直接忽略，此时，可以使用INSERT IGNORE INTO …语句：情景很多，不再举例赘述。

注意事项：同上，”INSERT IGNORE INTO …”语句是基于唯一索引或主键来判断唯一（是否存在）的，需要在username字段上建立唯一索引（Unique），transId设置自增即可。

–用户首次添加INSERTIGNOREINTOusers_info(id,username,sex,age,balance,create_time)VALUES(null,’chenhaha’,’男’,26,0,’2020-06-1120:00:20′);–二次添加，直接忽略INSERTIGNOREINTOusers_info(id,username,sex,age,balance,create_time)VALUES(null,’chenhaha’,’男’,26,0,’2020-06-1121:00:20′); 二、大量数据插入的实现方式： 1、单条循环插入：

取10w条数据进行了一些测试，如果插入方式为程序遍历循环逐条插入。

在mysql上检测插入一条的速度在0.01s到0.03s之间。

逐条插入的平均速度是0.02*100000，也就是33分钟左右。

下面代码是测试例子：

普通循环插入100000条数据的时间测试

@TestpublicvoidinsertUsers1(){Useruser=newUser();user.setUserName(“提莫队长”);user.setPassword(“正在送命”);user.setPrice(3150);user.setHobby(“种蘑菇”);for(inti=0;i<100000;i ){user.setUserName(“提莫队长” i);//调用插入方法userMapper.insertUser(user);}}

执行速度是30分钟也就是0.018*100000的速度。可以说是很慢了

发现逐条插入优化成本太高。然后去查询优化方式。发现用批量插入的方法可以显著提高速度。

将100000条数据的插入速度提升到1-2分钟左右 ↓

2、修改SQL语句批量插入：insertintouser_info（user_id,username,password,price,hobby）values（null,’提莫队长1′,’123456′,3150,’种蘑菇’）,(null,’盖伦’,’123456′,450,’踩蘑菇’);

用批量插入插入100000条数据，测试代码如下：

@TestpublicvoidinsertUsers2(){List<User>list=newArrayList<User>();Useruser=newUser();user.setPassword(“正在送命”);user.setPrice(3150);user.setHobby(“种蘑菇”);for(inti=0;i<100000;i ){user.setUserName(“提莫队长” i);//将单个对象放入参数list中list.add(user);}userMapper.insertListUser(list);}

批量插入使用了0.046s 这相当于插入一两条数据的速度，所以用批量插入会大大提升数据插入速度，当有较大数据插入操作是用批量插入优化

批量插入的写法：

dao定义层方法:

IntegerinsertListUser(List<User>user);

mybatis Mapper中的sql写法：

<insertid=”insertListUser”parameterType=”java.util.List”>INSERTINTO`db`.`user_info`(`id`,`username`,`password`,`price`,`hobby`)values<foreachcollection=”list”item=”item”separator=”,”index=”index”>(null,#{item.userName},#{item.password},#{item.price},#{item.hobby})</foreach></insert>

这样就能进行批量插入操作：

注：但是当批量操作数据量很大的时候。例如我插入10w条数据的SQL语句要操作的数据包超过了1M，MySQL会报如下错：

报错信息：

Mysql You can change this value on the server by setting the max_allowed_packet’ variable. Packet for query is too large (6832997 > 1048576). You can change this value on the server by setting the max_allowed_packet’ variable.

解释：

用于查询的数据包太大（6832997> 1048576）。

您可以通过设置max_allowed_packet的变量来更改服务器上的这个值。

通过解释可以看到用于操作的包太大。这里要插入的SQL内容数据大小为6M 所以报错。

解决方法：

数据库是MySQL57，查了一下资料是MySQL的一个系统参数问题：

max_allowed_packet，其默认值为1048576(1M)，

查询：

showVARIABLESlike’%max_allowed_packet%’;

修改此变量的值：MySQL安装目录下的my.ini(windows)或/etc/mysql.cnf(linux) 文件中的[mysqld]段中的max_allowed_packet = 1M，如更改为20M(或更大，如果没有这行内容，增加这一行)，如下图

保存，重启MySQL服务。现在可以执行size大于1M小于20M的SQL语句了。

但是如果20M也不够呢？

3、分批量多次循环插入：

如果不方便修改数据库配置或需要插入的内容太多时，也可以通过后端代码控制，比如插入10w条数据，分100批次每次插入1000条即可，也就是几秒钟而已；当然，如果每条的内容很多的话，另说。。

4、扩展：插入速度慢的其他几种优化途径

A、通过show processlist;命令，查询是否有其他长进程或大量短进程抢占线程池资源？看能否通过把部分进程分配到备库从而减轻主库压力；或者，先把没用的进程kill掉一些？（手动挠头o_O）

B、大批量导数据，也可以先关闭索引，数据导入完后再打开索引

关闭：ALTER TABLE user_info DISABLE KEYS;
开启：ALTER TABLE user_info ENABLE KEYS;

三、REPLACE INTO语法的“坑”

上面曾提到REPLACE可能影响3条以上的记录，这是因为在表中有超过一个的唯一索引。

在这种情况下，REPLACE将考虑每一个唯一索引，并对每一个索引对应的重复记录都删除，然后插入这条新记录。

假设有一个table1表，有3个字段a, b, c。它们都有一个唯一索引，会怎么样呢？我们做一些数据测试一下。

–测试表创建，a,b,c三个字段均有唯一索引CREATETABLEtable1(aINTNOTNULLUNIQUE,bINTNOTNULLUNIQUE,cINTNOTNULLUNIQUE);–插入三条测试数据INSERTintotable1VALUES(1,1,1);INSERTintotable1VALUES(2,2,2);INSERTintotable1VALUES(3,3,3);

此时table1中已经有了3条记录，a,b,c三个字段都是唯一（UNIQUE）索引：

mysql>select*fromtable1; — — — |a|b|c| — — — |1|1|1||2|2|2||3|3|3| — — — 3rowsinset(0.00sec)

下面我们使用REPLACE语句向table1中插入一条记录。

REPLACE INTO table1(a, b, c) VALUES(1,2,3);

mysql>REPLACEINTOtable1(a,b,c)VALUES(1,2,3);QueryOK,4rowsaffected(0.04sec)

此时查询table1中的记录如下，只剩一条数据了~

mysql>select*fromtable1; — — — |a|b|c| — — — |1|2|3| — — — 1rowinset(0.00sec)

（老板：插入前10w数据，插入5w数据后还剩8w数据？？，咱们家数据让你喂狗了吗！！）