,,MySQL实战之Insert语句的使用心得

本文主要介绍实战中使用MySQL的Insert语句的相关经验。通过示例代码非常详细的介绍，对大家的学习或者工作都有一定的参考价值。下面让我们跟随边肖一起学习。

一、Insert的几种语法

1-1.普通插入语句

1-2.插入或更新

1-3.插入或替换

1-4.插入或忽略

第二，大量的数据插入

2-1、三种处理方法

2-1-1，单循环插入

2-1-2，修改SQL语句，批量插入

2-1-3.分批反复插入。

2-2.插入速度慢的其他优化方法

第三，替换成语法的“坑”

摘要

一、Insert的几种语法

1-1.普通插入语句

插入到表中(` a '，` b '，` c '，……)值(' a '，' b '，' c '，……)；

这里就不赘述了，注意顺序就好。不建议朋友们去掉括号前面的内容。不要问为什么，很容易被同事骂。

1-2.插入或更新

如果我们想插入一个新记录(INSERT)，但如果该记录已经存在，我们将更新它。此时，我们可以使用' insert into…on duplicate key update…'语句：

场景：该表存储用户的历史充值金额。如果用户第一次充值，会增加一条新的数据。如果用户充值，将累计历史充值金额。需要保证个人用户的数据不会重复录入。

此时，您可以使用语句' insert into…on duplicate key update…'。

注意：语句' insert into…on duplicate key update…'基于唯一索引或主键来判断它是否存在。如下面的SQL所示，需要在username字段上建立唯一索引，transId设置可以自己增加。

-用户陈哈哈充值30元买会员。

插入total_transaction (t_transId，用户名，total_amount，last_transTime，last_remark)

值(空，'陈哈哈'，30，' 2020-06-11 20: 00: 20 '，'正式会员')

On重复键update total _ amount=total _ amount 30，last _ trans time=' 2020-06-11 20:00:20 '，last _ remark='完全成员'；

-用户陈哈哈充值100元购买盲人至尊拳皮肤。

插入total_transaction (t_transId，用户名，total_amount，last_transTime，last_remark)

值(空，'陈哈哈'，100，' 2020-06-11 20: 00: 20 '，'买盲僧无上拳之皮')

论重复键更新total _ amount=total _ amount 100，last _ trans time=' 2020-06-11 21:00:00 '，last _ remark='买盲僧至尊拳的皮'；

如果username='chenhaha '的记录不存在，INSERT语句将插入一条新记录；否则，将更新当前用户名为' chenhaha '的记录，更新的字段将由update指定。

是的，重复键更新是MySQL特有的语法。比如MySQL迁移Oracle或者其他DB的时候，类似的语句要改成合并成语法，兼容性让人想被指责。但是没办法，就像用WPS写的xlsx用Office打不开一样。

1-3.插入或替换

如果我们想插入一条新记录(INSERT)，但如果该记录已经存在，则先删除原记录，然后插入新记录。

示例：此表存储每个客户的最新交易订单信息。要求保证单个用户的数据不重复录入，执行效率最高，与数据库的交互最少，支持数据库的高可用性。

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

“REPLACE INTO”语句基于唯一索引或主键来确定唯一性(是否存在)。

注意：如以下SQL所示，需要在username字段上建立唯一索引，transId设置可以自行增加。

-20点充值。

替换为last_transaction (transId，用户名，金额，交易时间，备注)

值(空，'陈哈哈'，30，' 2020-06-11 20: 00: 20 '，'会员充值')；

-21点买皮肤。

替换为last_transaction (transId，用户名，金额，交易时间，备注)

值(null，'陈哈哈'，100，' 2020-06-11 21: 00: 00 '，'买盲僧至尊拳的皮')；

如果username='chenhaha '的记录不存在，REPLACE语句会插入一条新记录(第一次充值)；否则，将删除当前用户名为' chenhaha '的记录，然后插入新记录。

不要给id一个具体的值，否则会影响SQL的执行，除非业务有特殊要求。

小贴士：

在重复键更新时：如果插入的行中有唯一索引或重复主键，将执行旧的更新；如果不会导致唯一索引或重复主键，则直接添加新行。

替换为：如果插入行中有唯一索引或重复主键，则删除旧记录并输入新记录；如果不会导致唯一索引或重复主键，则直接添加新行。

replace into和insert on deplicate udpate的比较：

1.当没有主键或唯一索引重复时，replace into与insert on deplicate udpate相同。

2.当主键或唯一索引重复时，替换删除旧记录并输入新记录，这样所有原始记录将被清除。此时，如果replace语句的字段不完整，一些原始值如C字段将自动填充为默认值(如Null)。

3.细心的朋友会发现，在de placed update上insert只影响一行，而REPLACE INTO可能会影响多行。为什么？写在文章的最后一节~

1-4.插入或忽略

如果我们想插入一个新记录(INSERT)，但如果该记录已经存在，我们将什么也不做，只是忽略它。这时候可以用INSERT IGNORE INTO …语句：场景很多，就不举例重复了。

注意：如上所述，' INSERT IGNORE INTO …'语句是基于唯一索引或主键来判断唯一性(是否存在)。需要在用户名字段上建立唯一索引(unique)，transId设置可以自行增加。

-第一次由用户添加。

将忽略插入用户信息(id，用户名，性别，年龄，余额，创建时间)

值(null，'陈哈哈'，'男'，26，0，' 2020-06-11 20:00:20 ')；

-二次加法，直接忽略。

将忽略插入用户信息(id，用户名，性别，年龄，余额，创建时间)

值(null，'陈哈哈'，'男'，26，0，' 2020-06-11 21:00:20 ')；

二、大量数据插入

2-1、三种处理方式

2-1-1，单循环插入

我们取了10w条数据做了一些测试。如果插入模式是程序遍历循环，则逐个插入它们。在mysql上检测一个条的插入速度在0.01秒到0.03秒之间.

逐个插入的平均速度是0.02 * 10万，大概是33分钟。

以下代码是一个测试示例：

在普通周期中插入100，000条数据的1次测试

@测试

public void insertUsers1() {

User User=new User()；

User.setUserName('队长Teemo ')；

user . set password(' dying ')；

user . set price(3150)；

User.setHobby('种蘑菇')；

for(int I=0；我100000；i ) {

User.setUserName('队长Teemo ' I)；

//调用插入方法

userMapper.insertUser(用户)；

}

执行速度为30分钟，即0.018 * 10万。可以说是很慢了。

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

将10万条数据的插入速度提高到1-2分钟左右

2-1-2，修改SQL语句，批量插入

插入用户信息(用户标识，用户名，密码，价格，爱好)

值(null，'队长Teemo 1 '，' 123456 '，3150，'种蘑菇')，(null，'盖伦'，' 123456 '，450，'踩蘑菇')；

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

@测试

public void insertUsers2() {

ListUser list=new ArrayListUser()；

User User=new User()；

user . set password(' dying ')；

user . set price(3150)；

User.setHobby('种蘑菇')；

for(int I=0；我100000；i ) {

User.setUserName('队长Teemo ' I)；

//将单个对象放入参数列表

list.add(用户)；

}

user mapper . insertlistuser(list)；

}

批量插入需要0.046s，相当于插入一两条数据的速度。所以批量插入会大大提高数据插入的速度。当有大量数据插入操作时，批量插入会得到优化。

批量插入的编写：

Dao定义层方法：

整数insertListUser(ListUser用户)；

在mybatis Mapper中编写sql:

insert id='insertListUser '参数Type='java.util.List '

插入到`数据库'`用户信息'

(` id '，

`用户名`，

`密码`，

“价格”,

`爱好`)

价值观念

foreach collection=' list ' item=' item ' separator='，' index='index '

(空，

#{item.userName}，

#{item.password}，

#{item.price}，

#{item.hobby})

/foreach

/插入

这使得批量插入成为可能：

注意：但有大量批量操作数据时。例如，如果插入10w数据的MySQL语句要操作的数据包超过1M，MySQL会报告如下错误：

错误信息：

你可以在服务器上通过设置max_allowed_packet变量来改变这个值。用于查询的数据包太大(6832997 1048576)。您可以通过设置max_allowed_packet变量在服务器上更改该值。

解释：

用于查询的数据包太大(6832997 1048576)。您可以通过设置变量max_allowed_packet在服务器上更改该值。

从解释中可以看出，操作用的包太大了。这里要插入的SQL内容数据的大小是6M，因此报告了一个错误。

解决方法：

数据库是MySQL57。检查数据是MySQL的一个系统参数问题：

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

查询：

显示变量，如“% max _ allowed _ packet %”；

修改这个变量的值：在mysql安装目录下my.ini(windows)或/etc/mysql.cnf(linux)文件的[mysqld]部分。

Max_allowed_packet=1M，如果改成20M(或者更大，如果没有这条线，就加这条线)，如下图

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

但是20M不够怎么办？

2-1-3.分批反复插入。

如果不方便修改数据库配置或者需要插入的内容太多，也可以通过后端代码控制，比如插入10w条数据，一次100批插入1000条数据，也就是几秒钟的时间；当然，如果每篇文章内容都很多，那就说点别的。

2-2.插入速度慢的其他优化方法

a、通过show processlist命令来查询是否有其他长进程或大量短进程占用线程池资源。看看能不能通过分配一些进程给备用库来减轻主库的压力；或者，先干掉一些没用的进程？(手动抓挠o_O)

B.要批量导入数据，也可以先关闭索引，然后在数据导入后再打开它。

Close: ALTER TABLE user_info禁用键；

Open: ALTER TABLE user_info启用键；

三、REPLACE INTO语法的“坑”

上面提到，REPLACE可能会影响3个以上的记录，因为表中有多个唯一索引。在这种情况下，REPLACE将考虑每个唯一索引，删除对应于每个索引的重复记录，然后插入这个新记录。假设有一个包含3个字段A、B和c的table1表，它们都有一个唯一的索引。会发生什么？我们早点测试数据吧。

-测试表创建，A、B、C三个字段有唯一的索引。

创建表table1(a INT NOT NULL UNIQUE，b INT NOT NULL UNIQUE，c INT NOT NULL UNIQUE)；

-插入三个测试数据。

向表1中插入值(1，1，1)；

向表1中插入值(2，2，2)；

将值(3，3，3)插入表1；

此时，table1中已经有三条记录，A、B、C三个字段是唯一索引。

mysql select * from table1

- - -

| a | b | c |

- - -

| 1 | 1 | 1 |

| 2 | 2 | 2 |

| 3 | 3 | 3 |

- - -

集合中的3行(0.00秒)

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

代入表1(a，b，c)值(1，2，3)；

mysql替换成table1(a，b，c)的值(1，2，3)；

查询正常，4行受影响(0.04秒)

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

mysql select * from table1

- - -

| a | b | c |

- - -

| 1 | 2 | 3 |

- - -

集合中的1行(0.00秒)

(老板：插入前10w数据，插入5w数据后，还剩8w数据？我们家数据让你喂狗了吗？)

REPLACE INTO语法回顾：如果插入的行中存在唯一索引或重复主键，则删除旧记录并输入新记录；如果不会导致唯一索引或重复主键，则直接添加新行。

我们可以看到，使用REPLACE INTO时，每个唯一索引都会有影响，可能会导致数据被误删除。因此，建议不要在具有多个唯一索引的表中使用Replace Into。

目录

相关文章阅读