MySQL批量更新数据总结

最新推荐文章于 2024-07-29 04:00:21 发布

jerry-89

最新推荐文章于 2024-07-29 04:00:21 发布

阅读量441

点赞数

分类专栏： SQL优化

本文链接：https://blog.csdn.net/eagle89/article/details/105785624

版权

SQL优化专栏收录该内容

23 篇文章 6 订阅

订阅专栏

快速插入1亿条数据的方法

# 新建库

create database bigData;

use bigData;

#1 建表dept

CREATE TABLE dept(

id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,

dname VARCHAR(20) NOT NULL DEFAULT "",

loc VARCHAR(13) NOT NULL DEFAULT ""

) ENGINE=INNODB DEFAULT CHARSET=UTF8 ;

#2 建表emp

CREATE TABLE emp

(

id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/

ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/

job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/

mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/

hiredate DATE NOT NULL,/*入职时间*/

sal DECIMAL(7,2) NOT NULL,/*薪水*/

comm DECIMAL(7,2) NOT NULL,/*红利*/

deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/

)ENGINE=INNODB DEFAULT CHARSET=UTF8 ;

二、设置参数log_bin_trust_function_creators

　　当开启二进制日志后，如果变量log_bin_trust_function_creators为OFF，那么创建或修改存储函数就会报“ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)”这样的错误。因为二进制日志的一个重要功能是用于主从复制，而存储函数有可能导致主从的数据不一致。所以当开启二进制日志后，参数log_bin_trust_function_creators就会生效，限制存储函数的创建、修改、调用。

show variables like 'log_bin_trust_function_creators';

set global log_bin_trust_function_creators=1;

# 这样添加了参数以后，如果mysqld重启，上述参数又会消失，永久方法：

windows下my.ini[mysqld]加上log_bin_trust_function_creators=1

linux下 /etc/my.cnf下my.cnf[mysqld]加上log_bin_trust_function_creators=1

三、创建函数,保证每条数据都不同

随机产生字符串

DELIMITER $$

CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)

BEGIN ##方法开始

DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';

##声明一个字符窜长度为 100 的变量 chars_str ,默认值

DECLARE return_str VARCHAR(255) DEFAULT '';

DECLARE i INT DEFAULT 0;

##循环开始

WHILE i < n DO

SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));

##concat 连接函数，substring(a,index,length) 从index处开始截取

SET i = i + 1;

END WHILE;

RETURN return_str;

END $$

#假如要删除

#drop function rand_string;

随机产生部门编号

#用于随机产生部门编号

DELIMITER $$

CREATE FUNCTION rand_num( )

RETURNS INT(5)

BEGIN

DECLARE i INT DEFAULT 0;

SET i = FLOOR(100+RAND()*10);

RETURN i;

END $$

#假如要删除

#drop function rand_num;

四、创建存储过程

创建往emp表中插入数据的存储过程

DELIMITER $$

CREATE PROCEDURE insert_emp10000(IN START INT(10),IN max_num INT(10))

BEGIN

DECLARE i INT DEFAULT 0;

#set autocommit =0 把autocommit设置成0 ；提高执行效率

SET autocommit = 0;

REPEAT ##重复

SET i = i + 1;

INSERT INTO emp10000 (empno, ename ,job ,mgr ,hiredate ,sal ,comm ,deptno ) VALUES ((START+i) ,rand_string(6),'SALESMAN',0001,CURDATE(),FLOOR(1+RAND()*20000),FLOOR(1+RAND()*1000),rand_num());

UNTIL i = max_num ##直到上面也是一个循环

END REPEAT; ##满足条件后结束循环

COMMIT; ##执行完成后一起提交

END $$

#删除

# DELIMITER ;

# drop PROCEDURE insert_emp;

创建往dept表中插入数据的存储过程

#执行存储过程，往dept表添加随机数据

DELIMITER $$

CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))

BEGIN

DECLARE i INT DEFAULT 0;

SET autocommit = 0;

REPEAT

SET i = i + 1;

INSERT INTO dept (deptno ,dname,loc ) VALUES (START +i ,rand_string(10),rand_string(8));

UNTIL i = max_num

END REPEAT;

COMMIT;

END $$

#删除

# DELIMITER ;

# drop PROCEDURE insert_dept;

五、调用存储过程

DELIMITER ;

CALL insert_dept(100,10);

#执行存储过程，往emp表添加50万条数据

DELIMITER ; #将结束标志换回 ;

CALL insert_emp(100001,500000);

CALL insert_emp10000(100001,10000);

1、 replace into 批量更新
replace into test_tbl (id,dr) values (1,'2'),(2,'3'),...(x,'y');

replace into
MySQL替换（更新）写入 replace into
此方法具有局限性：仅当写入字段中有主键字段时，才能真正更新。

        replace into。
        MySQL中实现replace into操作方式：
        思路：通过判断插入的记录里是否存在主键索引或唯一索引冲突，来决定是插入还是更新。当出现主键索引或唯一索引冲突时则进行update操作，否则进行insert操作。
        实现：使用 replace into t(filedA, filedB...) values(value1, value2);

来看看下面具体实现过程。
一、准备数据表

CREATE TABLE `demo` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`a` tinyint(1) unsigned NOT NULL DEFAULT '0',
`b` tinyint(1) unsigned NOT NULL DEFAULT '0',
`c` tinyint(1) unsigned NOT NULL DEFAULT '0',
`d` tinyint(1) unsigned NOT NULL DEFAULT '0',
`e` tinyint(1) unsigned NOT NULL DEFAULT '0',
`f` tinyint(1) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `unq_a_b_c` (`a`,`b`,`c`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

注意：表中存在两处索引,id为主键索引，a,b,c为联合唯一索引。
二、写入初始数据

insert into test.demo(a,b,c,d,e,f) values(1,2,3,1,1,1);

此时存在由abc散列组成唯一索引数据：1，2，3。
三、进一步实现

replace into into demo(a,b,c,d,e,f) values(1,2,3,2,2,2);

因为已经存在由abc三列组成唯一索引数据：1，1，1，本次又写入demo(a,b,c,d,e,f) values(1,1,1,2,2,2)，会造成唯一索引冲突。
因此，会删除掉原来记录，新增一条记录。

replace into字段有primary key字段时，会更新本条记录
replace into字段没有primark key字段，发生唯一索引冲突，会删除原来的记录，新增一条记录

    在向表中插入数据的时候，经常遇到这样的情况：
    1. 首先判断数据是否存在
    2. 如果不存在，则插入
    3. 如果存在，则更新

2、insert into ...on duplicate key update批量更新
insert into test_tbl (id,dr) values (1,'2'),(2,'3'),...(x,'y') on duplicate key update dr=values(dr);

ON DUPLICATE KEY UPDATE用法(需要利用主键索引或唯一索引冲突，来决定是插入还是更新)
        upsert(update or insert), 即更新或写入。
        MySQL中实现upsert操作方式：
        思路：通过判断插入的记录里是否存在主键索引或唯一索引冲突，来决定是插入还是更新。当出现主键索引或唯一索引冲突时则进行update操作，否则进行insert操作。
        实现：使用 ON DUPLICATE KEY UPDATE

来看看下面具体实现过程。
一、准备数据表

注意：表中存在两处索引,id为主键索引，a,b,c为联合唯一索引。
二、写入初始数据

insert into test.demo(a,b,c,d,e,f) values(1,1,1,1,1,1);

此时存在由abc散列组成唯一索引数据：1，1，1。
三、进一步实现

insert into demo(a,b,c,d,e,f) values(1,1,1,2,2,2) ON DUPLICATE KEY UPDATE a=2,b=2,c=3,d=4,e=5,f=6;

因为已经存在由abc三列组成唯一索引数据：1，1，1，本次又写入demo(a,b,c,d,e,f) values(1,1,1,2,2,2)，会造成唯一索引冲突。
因此，会触发ON DUPLICATE KEY 后面的 UPDATE a=2,b=2,c=3,d=4,e=5,f=6操作。

至此，已经实现upsert功能。请记住 ON DUPLICATE KEY UPDATE的用法。
实现mysql的批量更新

insert into statistic_customer(customer_id,current_period,period_number,client_upload_bill,update_time) values
(1,201604,100,100,1540470829),
(314,201604,100,100,1540470829),
(315,201604,100,100,1540470829),
(316,201611,100,100,1540470829)
ON DUPLICATE KEY UPDATE
customer_id=values(customer_id),
current_period=values(current_period),
period_number=values(period_number),
client_upload_bill=values(client_upload_bill),
update_time=values(update_time)

3.创建临时表，先更新临时表，然后从临时表中update

    create temporary table tmp(id int(4) primary key,dr varchar(50));
    insert into tmp values (0,'gone'), (1,'xx'),...(m,'yy');
    update test_tbl, tmp set test_tbl.dr=tmp.dr where test_tbl.id=tmp.id;

注意：这种方法需要用户有temporary 表的create 权限。
4、使用mysql 自带的语句构建批量更新(case...when...then...end)

原始功能：
        update categories SET display_order= 3,title = 'New Title 1' where id=1;
        update categories SET display_order= 4,title = 'New Title 2' where id=2;
        update categories SET display_order= 5,title = 'New Title 3' where id=3;

功能改进：
UPDATE categories SET
    display_order = CASE id
        WHEN 1 THEN 3
        WHEN 2 THEN 4
        WHEN 3 THEN 5
    END,
    title = CASE id
        WHEN 1 THEN 'New Title 1'
        WHEN 2 THEN 'New Title 2'
        WHEN 3 THEN 'New Title 3'
    END
WHERE id IN (1,2,3)

mysql 实现批量可以用点小技巧来实现:

    UPDATE yoiurtable
        SET dingdan = CASE id
            WHEN 1 THEN 3
            WHEN 2 THEN 4
            WHEN 3 THEN 5
        END
    WHERE id IN (1,2,3)

这句sql 的意思是，更新dingdan 字段，如果id=1 则dingdan 的值为3，如果id=2 则dingdan 的值为4……
where部分不影响代码的执行，但是会提高sql执行的效率。确保sql语句仅执行需要修改的行数，这里只有3条数据进行更新，而where子句确保只有3行数据执行。

如果更新多个值的话，只需要稍加修改：

    UPDATE categories
        SET dingdan = CASE id
            WHEN 1 THEN 3
            WHEN 2 THEN 4
            WHEN 3 THEN 5
        END,
        title = CASE id
            WHEN 1 THEN 'New Title 1'
            WHEN 2 THEN 'New Title 2'
            WHEN 3 THEN 'New Title 3'
        END
    WHERE id IN (1,2,3)

到这里，已经完成一条mysql语句更新多条记录了。

replace into 和 insert into on duplicate key update的不同在于：

replace into 操作本质是对重复的记录先delete 后insert，如果更新的字段不全会将缺失的字段置为缺省值，用这个要悠着点否则不小心清空大量数据可不是闹着玩的。
insert into 则是只update重复记录，不会改变其它字段。