mysql 存储过程批量删除重复数据

news/2024/11/15 5:50:07/

mysql 存储过程批量删除重复数据 

表结构:

LOAD DATA INFILE '/usr/local/phone_imsi_12' replace INTO TABLE tbl_imsi2number_new FIELDS TERMINATED BY '\t' ENCLOSED BY '' (number,imsi);

先用SQL语句来进行去重操作:

delete from tbl_imsi2number_new where imsi in (select imsi from (select imsi from tbl_imsi2number_new group by imsi having count(imsi) > 1) a) and number not in (select * from (select min(number) from tbl_imsi2number_new group by imsi having count(imsi) > 1 ) b);

因为数据量太大(共计3亿多数据),所以:

将开发那边拿过来的数据load data infile到大表里,不进行任何去重操作,没有任何约束。然后将这些数据分成几十个小文件,再load进几十个小表中,用这几十个小表去对比大表去重。得到去重后的小表。去重以后的小表,根据字段进行hash算出后两位数字(mod(conv(right(md5(imsi),2),16,10),100))进行批量插入。

存储过程如下(去重):

DELIMITER //

/*tblname 动态控制表名*/
CREATE PROCEDURE create_imsi(IN tblname varchar(200))
begin
declare age int default 1;
declare done int(1) default 0;
declare v_imsi varchar(200);

/*定义游标*/
declare cur_l cursor for select imsi from sqlstr;

/*定义异常*/
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' set done=1;
drop view if exists sqlstr;

/*定义视图*/
set @tbl = CONCAT("create view sqlstr as select a.imsi from tbl_new a,(select imsi from phone_",tblname," group by imsi having count(imsi) > 1) b where a.imsi = b.imsi group by imsi");

/*执行视图语句*/

PREPARE stmt FROM @tbl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
OPEN cur_l;
FETCH cur_l INTO v_imsi;
while (done <> 1)
do

/*对比大表数据,删除小表中的重复数据*/
set @del = CONCAT("delete from phone_",tblname," where imsi=",v_imsi);
PREPARE stmt1 FROM @del;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
FETCH cur_l INTO v_imsi;
end while;
close cur_l;
end//

DELIMITER ;

2、根据hash算法插入新表:

DELIMITER //
CREATE PROCEDURE insert_imsi(IN tblname varchar(20))
begin
declare age int default 1;
declare done int(1) default 0;
declare done1 int(1) default 0;
declare v_imsi varchar(200);
declare v_e varchar(2000);
declare v_number varchar(3000);
declare v_ctype varchar(2000);
declare cur_l cursor for select split from sqlstr;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' set done=1;
DECLARE CONTINUE HANDLER FOR 1146 set done1=3;
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' set done1=1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '42000' set done1=2;
DECLARE CONTINUE HANDLER FOR SQLSTATE 'HY000' set done1=3;
drop view if exists sqlstx;
drop view if exists sqlstr;
set @sqlstx = CONCAT("create view sqlstr as SELECT imsi,number,ctype,mod(conv(right(md5(imsi),2),16,10),100) split from imsi_phone_",tblname);
PREPARE stmt1 FROM @sqlstx;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
OPEN cur_l;
WHILE done <> 1
DO
  FETCH cur_l INTO v_e;
  set @ins = concat("insert into imsi_",v_e,"(imsi,number,ctype) select imsi,number,ctype from sqlstr where split = '",v_e,"'");
  PREPARE stmt3 FROM @ins;
  EXECUTE stmt3;
END WHILE;
close cur_l;
end//

DELIMITER ;

 报错:1、ERROR 1243 (HY000) at line 1: Unknown prepared statement handler (stmt3) given to EXECUTE

   2、ERROR 1054 (42S22) at line 1: Unknown column '000cdc41b2a02518' in 'where clause'

由于set @dat = concat("insert into imsi_",v_e,"(imsi,number,ctype) select imsi,number,ctype from imsi_phone_",tblname," where imsi=‘’",v_imsi,“‘’”);没有在(=)那里加单引号,因为字段里有字母。

参数优化:

由于建表使用innodb引擎,所以此优化是针对innodb引擎的:

1、innodb_flush_log_at_trx_commit参数设置为1,减少刷新。
2、set sql_log_bin=0  暂时不产生二进制日志
3、sync_binlog  设置为0,减少刷新
4、innodb_buffer_pool_size    尽可能设置最大
5、set foreign_key_checks=0  去除外键检查
6、减少不必要的索引,有重复数据的话,主键是必须要的
7、innodb_change_buffer_max_size    上限为50,这里我设置为40,因为load是插入数据,所以设置插入缓冲
8、binlog_cache_size  如果必须要开启二进制日志,设置此参数尽可能大,因为sync_binlog设置为0,所以缓冲应该大
9、innodb_flush_method    刷新模式,设置为O_DIRECT
10、innodb_io_capacity    刷新脏页,根据你的硬盘设置,SAS设置800--900
11、innodb_log_buffer_size与innodb_sort_buffer_size  尽可能设置最大
12、unique_checks  设置为不检查:set unique_checks=0;
13、alter table tablename disable keys;设置表忽略索引,如果有。

14、设置自动提交为0,减少日志刷新:SET autocommit=0;

15、设置innodb_autoinc_lock_mode=2

16、设置主键,聚簇索引,按照主键顺序插入会更快

 


http://www.ppmy.cn/news/9223.html

相关文章

191:vue+openlayers 选择feature,固定按钮删除selected feature

第191个 点击查看专栏目录 本示例的目的是介绍如何在vue+openlayer中使用select来选择feature元素,通过按键来删除selected的feature。 直接复制下面的 vue+openlayers源代码,操作2分钟即可运行实现效果 文章目录 示例效果配置方式示例源代码(共95行)相关API参考专栏目标…

Qt编译CTK

文章目录一、CTK简介二、CTK下载三、CTK编译一、CTK简介 CTK是什么 CTK 为支持生物医学图像计算的公共开发包&#xff0c;其全称为 Common Toolkit CTK 提供了什么 当前&#xff0c;CTK 工作的主要范围包括&#xff1a; DICOM&#xff1a;提供了从 PACS 和本地数据库中查询和…

redis配置文件

redis主要配置项&#xff1a; bind 0.0.0.0 #监听地址&#xff0c;可以用空格隔开后多个监听IP protected-mode yes #redis3.2 之后加入的新特性&#xff0c;在没有设置bind IP和密码的时候,redis只允许访问 127.0.0.1:6379&#xff0c;远程访问将提示警告信息并拒绝远程访问…

java基础 多线程

线程(thread)是一个程序内部的一条执行路径。 多线程的实现方案一&#xff1a;继承Thread类 public Thread(String name) 可以为当前线程指定名称 public Thread(Runnable target) 封装Runnable任务对象成为线程对象 public Thread(Runnable target &#x…

CAN 通信协议

CAN 概述 CAN 是Controller Area Network 的缩写&#xff08;以下称为CAN&#xff09;&#xff0c;它的设计目标是以最小的CPU负荷来高效处理大量的报文。1986 年德国电气商BOSCH公司开发出面向汽车的CAN 通信协议。此后&#xff0c;CAN 通过ISO11898 及ISO11519 进行了标准化…

数据在内存中的存储

专栏&#xff1a;C语言 每日一句&#xff1a;立志趁早点&#xff0c;上路轻松点&#xff0c;目光放远点&#xff0c;苦累看淡点&#xff0c;努力多一点&#xff0c;奋斗勇一点&#xff0c;胜利把名点&#xff0c;祝你折桂冠&#xff0c;成功新起点&#xff0c;幸福多一点&#…

2023年1月4日:fastadminApi接口开发项目时遇到的问题

SelectPage selectpage(官方文档&#xff1a;动态下拉(SelectPage) - FastAdmin框架文档 - FastAdmin开发文档) 常规用法 下面介绍一个基础的动态下拉列表示例&#xff0c;如下 <input id"c-name" data-rule"required" data-source"category/sel…

第二章 linux常用指令

第二章 linux常用指令一、ls指令&#xff1a;查看目录内容1、作用2、语法3、示例二、pwd命令&#xff1a;查看当前位置1、作用2、语法3、示例三、cd 指令&#xff1a;进入1、作用2、语法3、常用变型四、touch指令&#xff1a;创建文件1、作用2、语法3、常用选项五、mkdir指令&a…