金沙国际官网_金沙国际平台登录

因为这个金沙国际官网_金沙国际平台登录网站与很多的大型澳门赌场都有合作,金沙国际官网_金沙国际平台登录尽职尽责,高效执行,保持好奇心,不断学习,追求卓越,点击进入金沙国际官网_金沙国际平台登录马上体验吧,所以现在也正式地开始了营业。

您的位置:金沙国际官网 > 数据库 > 自增长字段值的连续递增实现金沙国际官网,M

自增长字段值的连续递增实现金沙国际官网,M

发布时间:2019-11-01 15:43编辑:数据库浏览(148)

    今天遇到一个需要对表进行去重的问题,数据量大概千万左右,第一选择就是按Oracle的思路上:

    背景

    早上同事要我写个MySQL去除重复数据的SQL,想起来上次写过一篇MySQL去除重复数据的博客,使用导入导出加唯一索引实现的,但是那种方式对业务影响较大,所以重新写一个存储过程来删重复数据,这一写就写了一个上午,这种BUG确实是很令人沮丧和浪费时间的。

    delete from table t1 where id < (select max(id) from table t2 where t1.c1=t2.c1);  --将c1值相同的记录进行去重,只留下id最大的,写成id>min(id)效果相同。
    

    在上一篇《数据库操作类SqlHelper》博文的最后,提到了一个实践运用中遇到的问题,就是数据库表中的自增长字段的赋值不受人为控制。比如数据库有一个tb_Department表,DeptNO字段为自增长主键。

    这里把流程简单的描述一下,删重复数据的逻辑很简单:

    以上相关子查询的SQL在c1上存在索引时效率不算低,但是很遗憾MySQL没有这种写法,类似的替代写法在MySQL中效率也低的令人发指,如中间表等手段。

    金沙国际官网 1

    1.根据重复判断条件找出重复记录的最小主键(一般是ID列)。

    正好在前些时间整理一些shell脚本时处理过mysql导入时出错继续执行的问题,因此测试后采用了如下办法:

    现在插入一行数据

    2.在符合重复条件的记录中,把主键大于最小主键的记录全部删掉即可。

    1.将表数据导出:

    金沙国际官网 2

    假设我有如下表,需要删除start_time和end_time都一样的重复记录。

    mysqldump -uroot -p --skip-extended-insert -t DBNAME TABLE>TABLE.sql
    
    然后记一下去重后的记录数:
    select count(*) from (select 1 from TABLE group by c1) a;
    

    啊!DeptNO字段怎么就是22了呢,不应该是从4开始吗?

    金沙国际官网 3

    2.truncate表,然后创建唯一索引

    原因:这个表之前进行过很多插入操作,数据库针对自增长字段的每次插入都会自动+1,后来删除了一部分行数据,然后重新插入的时候,数据库不会依据表中缺失的字段值进行赋值,而是在原先的基础上继续+1赋值。

    那么存储过程如下:

    truncate table TABLE;
    create unique index IX_c1 on TABLE(c1);
    

    结果:在新插入的“哈哈系”数据行之前,其实数据库已经向表里插入过21次了,只是DeptNO字段值大于3的行数据被删除了,现在要新插入行数据的话,就会在21的基础上+1,也就是第二个表中出现的22了。

    DELIMITER //
    DROP PROCEDURE IF EXISTS Del_Dup_FOR_TEST;
    CREATE PROCEDURE Del_Dup_FOR_TEST()
    BEGIN
    DECLARE min_id INT;
    DECLARE v_start_time,v_end_time DATETIME;
    DECLARE v_count INT;
    DECLARE done INT DEFAULT 0;
    DECLARE my_cur CURSOR FOR SELECT start_time,end_time,min(id),count(1) AS count FROM leo.test GROUP BY start_time,end_time HAVING count>1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    OPEN my_cur;
      myloop: LOOP
      FETCH my_cur INTO v_start_time,v_end_time,min_id,v_count;
      IF done=1 THEN
      LEAVE myloop;
      END IF;
      DELETE FROM leo.test WHERE start_time=v_start_time AND end_time=v_end_time AND id>min_id;
      COMMIT;
      END LOOP myloop;
    CLOSE my_cur;
    END;
    //
    DELIMITER ;
    

    3.最后导入数据,需要添加-f选项。

    期望:

    逻辑很清晰,就是根据重复判断条件依次删掉重复组中主键大于最小主键的记录们。

    mysql -uroot -p -f DBNAME<TABLE.sql
    
    1. 在插入新数据的时候,针对自增长字段可以人为控制;
    2. 实际运用中,其实用户并不知道数据表中自增长字段缺失的是哪些值,程序需要自动提供缺失或者缺省值。

    但是在编写过程中却遇到一个很恶心的BUG,我最初的内容是这么写的:

    -f的作用是:Continue even if an SQL error occurs.

    设计

    DELIMITER //
    DROP PROCEDURE IF EXISTS Del_Dup_FOR_TEST;
    CREATE PROCEDURE Del_Dup_FOR_TEST()
    BEGIN
    DECLARE min_id INT;
    DECLARE start_time,end_time DATETIME;
    DECLARE count INT;
    DECLARE done INT DEFAULT 0;
    DECLARE my_cur CURSOR FOR SELECT start_time,end_time,min(id),count(1) AS count FROM leo.test GROUP BY start_time,end_time HAVING count>1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    OPEN my_cur;
      myloop: LOOP
      FETCH my_cur INTO start_time,end_time,min_id,count;
      IF done=1 THEN
      LEAVE myloop;
      END IF;
      DELETE FROM leo.test WHERE start_time=start_time AND end_time=end_time AND id>min_id;
      COMMIT;
      END LOOP myloop;
    CLOSE my_cur;
    END;
    //
    DELIMITER ;
    

    这样导入时会报很多的错误,就是因为唯一约束的存在,你只需要最后检查下表的记录数时候与第一步中查到的数目一致就可以了。

    1.在插入新数据的时候,针对自增长字段可以人为控制

    不同的部分在于变量定义的名称,即:

    这种去重方式效率比较高,缺陷可能是出错时屏幕上一堆的‘Duplicate entry’报错会淹没其他的报错。

    数据库中针对自增长字段在插入时,不可以指定显式值的。

    FETCH INTO的变量名绝对不能是你定义CURSOR时SQL语句查出来的列名或者列别名,也就说你定义的变量名既不能是表中已经存在的列名,也不能是你定义游标时用过的别名(如本例中的count),只要一个条件不符合,FETCH INTO就把全部的变量赋NULL值,这点你可以尝试在FETCH INTO后加一句Select打印变量名验证。

    此外还可以写存储过程来删除重复数据,这种方式对数据库的影响较小,无需导出导入数据,存储过程写法详见:

    insert into tb_Department(DeptNO,DeptName) values(4,N'嘿嘿系')
    

    在查询到这个BUG之前去官网页面特地看了一下是否是我的语法有错误: ,确信语法没问题,但倒数第二条评论显示可能是列名的隐藏BUG,最后一条评论反驳了BUG说法,但没有办法我还是根据BUG REPORT做了以上修改,然后功能就正常了。

    这样插入数据会报错的,提示你“当Identity_Insert设置为off时,不能为表’tb_Department’中的标识列插入显式值”。很明显,第一个期望的解决方案就是将Identity_Insert设置on,然后执行显式值插入,最后关闭标识列插入开关。

    关于此BUG的BUG报告页面详见MySQL BUG:#28227 和 BUG:#5967

    set identity_insert tb_Department on
    insert into tb_Department(DeptNO,DeptName) values(4,N'嘿嘿系')
    set identity_insert tb_Department off
    

    那么再回头看一下官网文档下的最后一条评论,开始我认为最后一条反驳BUG的评论完全是扯淡,是哪个傻X说这不是个BUG的?后来仔细想了想,他俩都对,这确实也算个BUG,傻X的也是我。

    执行看看能不能插入,哇哦,成功了,棒棒哒。

    贴一下页面下最后两条评论(截止2018.08.01):

    本文由金沙国际官网发布于数据库,转载请注明出处:自增长字段值的连续递增实现金沙国际官网,M

    关键词:

上一篇:没有了

下一篇:没有了