phpmysqlinsert变量技巧_MySQL实战中Insert语句的运用心得总结

1-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元买会员INSERT INTO total_transaction (t_transId,username,total_amount,last_transTime,last_remark) VALUES (null, 'chenhaha', 30, '2020-06-11 20:00:20', '充会员') ON DUPLICATE KEY UPDATE total_amount=total_amount + 30, last_transTime='2020-06-11 20:00:20', last_remark ='充会员'; -- 用户陈哈哈充值了100元买瞎子至高之拳皮肤INSERT INTO total_transaction (t_transId,username,total_amount,last_transTime,last_remark) VALUES (null, 'chenhaha', 100, '2020-06-11 20:00:20', '购买盲僧至高之拳皮肤') ON DUPLICATE KEY UPDATE total_amount=total_amount + 100, last_transTime='2020-06-11 21:00:00', last_remark ='购买盲僧至高之拳皮肤';

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

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

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

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

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

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

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

-- 20点充值REPLACE INTO last_transaction (transId,username,amount,trans_time,remark) VALUES (null, 'chenhaha', 30, '2020-06-11 20:00:20', '会员充值'); -- 21点买皮肤REPLACE INTO last_transaction (transId,username,amount,trans_time,remark) VALUES (null, 'chenhaha', 100, '2020-06-11 21:00:00', '购买盲僧至高之拳皮肤');

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

id不要给详细值，不然会影响SQL实行，业务有分外需求除外。

小tips：ON DUPLICATE KEY UPDATE：如果插入行涌现唯一索引或者主键重复时，则实行旧的update；如果不会导致唯一索引或者主键重复时，就直接添加新行。
REPLACE INTO：如果插入行涌现唯一索引或者主键重复时，则delete老记录，而录入新的记录；如果不会导致唯一索引或者主键重复时，就直接添加新行。

replace into 与 insert on deplicate udpate 比较：

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

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

-- 用户首次添加INSERT IGNORE INTO users_info (id, username, sex, age ,balance, create_time) VALUES (null, 'chenhaha', '男', 26, 0, '2020-06-11 20:00:20'); -- 二次添加，直接忽略INSERT IGNORE INTO users_info (id, username, sex, age ,balance, create_time) VALUES (null, 'chenhaha', '男', 26, 0, '2020-06-11 21:00:20');二、大量数据插入2-1、三种处理办法2-1-1、单条循环插入

我们取10w条数据进行了一些测试，如果插入办法为程序遍历循环逐条插入。
在mysql上检测插入一条的速率在0.01s到0.03s之间。

逐条插入的均匀速率是0.02100000，也便是33分钟旁边。

下面代码是测试例子：1普通循环插入100000条数据的韶光测试

@Test public void insertUsers1() { User user = new User(); user.setUserName("提莫队长"); user.setPassword("正在送命"); user.setPrice(3150); user.setHobby("种蘑菇"); for (int i = 0; i < 100000; i++) { user.setUserName("提莫队长" + i); // 调用插入方法 userMapper.insertUser(user); } }

实行速率是30分钟也便是0.018100000的速率。
可以说是很慢了

创造逐条插入优化本钱太高。
然后去查询优化办法。
创造用批量插入的方法可以显著提高速率。

将100000条数据的插入速率提升到1-2分钟旁边↓

insert into user_info （user_id,username,password,price,hobby） values （null,'提莫队长1','123456',3150,'种蘑菇'）,(null,'盖伦','123456',450,'踩蘑菇');

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

@Test public void insertUsers2() { List<User> list= new ArrayList<User>(); User user = new User(); user.setPassword("正在送命"); user.setPrice(3150); user.setHobby("种蘑菇"); for (int i = 0; i < 100000; i++) { user.setUserName("提莫队长" + i); // 将单个工具放入参数list中 list.add(user); } userMapper.insertListUser(list); }

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

批量插入的写法：

dao定义层方法:

Integer insertListUser(List<User> user);

mybatis Mapper中的sql写法：

<insert id="insertListUser" parameterType="java.util.List"> INSERT INTO `db`.`user_info` ( `id`, `username`, `password`, `price`, `hobby`) values <foreach collection="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)，

查询：

show VARIABLES like '%max_allowed_packet%';

修正此变量的值：MySQL安装目录下的my.ini(windows)或/etc/mysql.cnf(linux) 文件中的[mysqld]段中的

max_allowed_packet = 1M，如变动为20M(或更大，如果没有这行内容，增加这一行)，如下图

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

但是如果20M也不足呢？

如果未便利修正数据库配置或须要插入的内容太多时，也可以通过后端代码掌握，比如插入10w条数据，分100批次每次插入1000条即可，也便是几秒钟而已；当然，如果每条的内允许多的话，另说。
。

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

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

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

上面曾提到REPLACE可能影响3条以上的记录，这是由于在表中有超过一个的唯一索引。
在这种情形下，REPLACE将考虑每一个唯一索引，并对每一个索引对应的重复记录都删除，然后插入这条新记录。
假设有一个table1表，有3个字段a, b, c。
它们都有一个唯一索引，会怎么样呢？我们早一些数据测试一下。

-- 测试表创建，a,b,c三个字段均有唯一索引CREATE TABLE table1(a INT NOT NULL UNIQUE,b INT NOT NULL UNIQUE,c INT NOT NULL UNIQUE);-- 插入三条测试数据INSERT into table1 VALUES(1,1,1);INSERT into table1 VALUES(2,2,2);INSERT into table1 VALUES(3,3,3);

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

mysql> select from table1;+---+---+---+| a | b | c |+---+---+---+| 1 | 1 | 1 || 2 | 2 | 2 || 3 | 3 | 3 |+---+---+---+3 rows in set (0.00 sec)

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

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

mysql> REPLACE INTO table1(a, b, c) VALUES(1,2,3);Query OK, 4 rows affected (0.04 sec)

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

mysql> select from table1;+---+---+---+| a | b | c |+---+---+---+| 1 | 2 | 3 |+---+---+---+1 row in set (0.00 sec)

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

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

我们可以看到，在用REPLACE INTO时每个唯一索引都会有影响的，可能会造成误删数据的情形，因此建议不要在多唯一索引的表中利用REPLACE INTO；