MySQL之生成唯一的序列号和删除重复数据

2021年11月14日 4点热度 0条评论 来源: 更好的自己520

命令行:
begin 启动事务
insert into 表名 values(); 向表中插入数据
select * from 表名
rollback 回滚事务

生成唯一序列号
需要用到唯一序列号的场景:数据库主键,业务序列号如发票号,车票号,订单号等。

如何选择生成序列号的方式
1:优先选择系统提供的序列号生成方式
2:在特殊的情况下可以使用SQL方法生成序列号

MySQL: AUTO_INCREMENT
SQLServer:IDENTITY/SEQUENCE
Oracle:SEQUENCE
PgSQL:SEQUENCE

注意:mysql的auto_increment的方式如果使用事务回滚会产生空洞情况

coalesce  when  XXX  then OOO,when XXX2 then  OOO2,case  when b then B,case  where  c  then C);
case  when:相当于if else
coalesce:获取第一个有值的字段

需求:
由SQL来生成特殊的序列号,生成订单序列号,并且订单号的公式如下
YYYYMMDDNNNNNNN。如20105120000003

DECLARE v_cnt INT;
DECLARE v_timestr INT;
DECLARE rowcount BIGINT;
SET v_timestr = DATE_FORMAT(NOW(),'%Y%m%d');
SELECT ROUND(RAND()*100,0)+1 INTO v_cnt;
START TRANSACTION;
UPDATE order_seq SET order_sn = order_sn + v_cnt WHERE timestr = v_timestr;
IF ROW_COUNT() = 0 THEN
INSERT INTO order_seq(timestr,order_sn) VALUES(v_timestr,v_cnt);
END IF;
SELECT CONCAT(v_timestr,LPAD(order_sn,7,0))AS order_sn FROM order_seq WHERE timestr = v_timestr;
COMMIT;

通过存储过程和事务等确保生成唯一序列号,可以通过创建一张表,记录某天的流水号最大是什么,然后通过某一个数可以叠加起来。

完善一下存储过程,添加表
唯一序列号 获取当前年月日+3位流水号
用orser_seq判断插入最大值

  DROP PROCEDURE IF EXISTS usp_seqnum;
  CREATE PROCEDURE usp_seqnum() BEGIN 定义变量并获取相关值 DECLARE v_cnt INT;
      DECLARE v_timestr INT;
      SET v_timestr = DATE_FORMAT(NOW(),'%Y%m%d');
      SELECT ROUND(RAND()*100,0)+1 INTO v_cnt;
      新建表
      DROP TABLE IF EXISTS im_orderseq;
      CREATE TABLE im_orderseq( timestr NVARCHAR(8) NOT NULL , ordersn INT(3) );
      START TRANSACTION;
      更新表的最值
      UPDATE im_orderseq SET ordersn = ordersn + v_cnt WHERE timestr = v_timestr;
      IF ROW_COUNT() = 0 THEN
        插入数据
        INSERT INTO im_orderseq(timestr,ordersn) VALUES(v_timestr,v_cnt);
      END IF;
      SELECT CONCAT(v_timestr,LPAD(ordersn,7,0))AS ordersn FROM im_orderseq WHERE timestr = v_timestr;
      COMMIT;
    END;

  CALL usp_seqnum();
  SELECT * FROM im_orderseq;

以DECLARE关键字声明的变量,只能在存储过程中使用,称为存储过程变量。

LPAD(str,len,padstr)
用字符串padstr对str进行左边填补直至它的长度达到len个字符长度,然后返回str。如果str的长度长于len,那么它将被截除到len个字符。

问题:
如何删除重复的数据?
利用主键来删除重复的数据

使用Group by和having从句来判断是否存在重复数据

1:判断数据是否重复

SELECT user_name , over, count(*) FROM user1_test GROUP BY user_name, over HAVING count(*)>1

2:删除重复数据,对于相同数据保留ID最大的

DELETE a 
FROM user1_test a JOIN(
SELECT user_name,COUNT(*),MAX(id) AS id FROM user1_test GROUP BY user_name HAVING COUNT(*) > 1
) b ON a.user_name = b.user_name
WHERE a.id < b.id

3:将表中查询的数据再次插入到表中

INSERT INTO xmss_wh(XM_ID,WH_ID,YJ,SH,SHR,FJ_NAME,FJ_ID) SELECT XM_ID,WH_ID,YJ,SH,SHR,FJ_NAME,FJ_ID FROM xmss_wh WHERE ID=28;

sql中的group by用法解析:
Group by语句从英文的字面意义上理解就是(根据by一定的规则进行分组 group)
它的作用是通过一定的规则将一个数据集划分成若干个小的区域,然后针对若干个小区域进行数据处理。

注意:group by是先排序后分组的。
举例子说明:如果要用到group by一般用到的就是 “每这个字”,例如说明现在有一个这样的表:每个部门有多少人就要用到分组的技术

select DepartmentID as ‘部门名称’; count(*) as ‘个数’ from BasicDepartment group by DepartmentID

这个就是使用了group by+字段进行了分组,其中我们就可以理解为我们按照了部门的名称ID ,DepartmentID将数据集进行了分组,然后再进行了各个组的统计数据分别有多少;

如果不适用count(*)等类似的函数,select DepartmentID,DepartmentName from BasicDepartment group by DepartmentID 就会报错。

判断数据是否重复:Group by column_name having count(*)>=2;
通过多表删除,Group by后,相当于形成了一个个单独的表。

利用group by和having从句处理查询数据是否重复

select  col1_name,count(*) from table1 group by col1_name having count(*)>1

参考视频:http://www.imooc.com/learn/427

    原文作者:更好的自己520
    原文地址: https://blog.csdn.net/a3060858469/article/details/78408755
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系管理员进行删除。