求教mysql触发器生成流水号

用存储过程生成流水号是很常用的，这里以生成订单编号的流水号作为示例。（新的一天的流水号从1开始，如：今天的订单编号是CD2013010900014，下一个订单编号将是CD2013010900015；明天的订单编号将从CD2013011000001开始）

生成规则：2位前缀+年月日+5位流水号 或者 2位前缀+年月日时分+5位流水号 或者 2位前缀+年月日时分秒+5位流水号。

测试订单表(test_orders):

[sql] view plain copy

CREATE TABLE `test_orders` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`orderNo` varchar(25) NOT NULL DEFAULT '',

`orderName` char(10) NOT NULL DEFAULT '',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=76 DEFAULT CHARSET=utf8

生成订单编号的存储过程(generate_orderNo):

[sql] view plain copy

CREATE DEFINER=PROCEDURE `generate_orderNo`(in orderNamePre char(2), in num int, out newOrderNo varchar(25))

BEGIN

DECLARE currentDate varCHAR (15) -- 当前日期,有可能包含时分秒

DECLARE maxNo INT DEFAULT 0 -- 离现在最近的满足条件的订单编号的流水号最后5位，如：SH2013011000002的maxNo=2

-- DECLARE l_orderNo varCHAR (25) -- 新生成的订单编号

-- DECLARE oldDate DATE -- 离现在最近的满足条件的订单编号的日期

DECLARE oldOrderNo VARCHAR (25) DEFAULT '' -- 离现在最近的满足条件的订单编号

if num = 8 then -- 根据年月日生成订单编号

SELECT DATE_FORMAT(NOW(), '%Y%m%d') INTO currentDate -- 订单编号形式:前缀+年月日+流水号，如：SH2013011000002

elseif num = 14 then -- 根据年月日时分秒生成订单编号

SELECT DATE_FORMAT(NOW(), '%Y%m%d%H%i%s') INTO currentDate -- 订单编号形式：前缀+年月日时分秒+流水号，如：SH2013011010050700001,个人不推荐使用这种方法生成流水号

else -- 根据年月日时分生成订单编号

SELECT DATE_FORMAT(NOW(), '%Y%m%d%H%i') INTO currentDate -- 订单形式：前缀+年月日时分+流水号,如：SH20130110100900005

end if

SELECT IFNULL(orderNo, '') INTO oldOrderNo

FROM test_orders

WHERE SUBSTRING(orderNo, 3, num) = currentDate

AND SUBSTRING(orderNo, 1, 2) = orderNamePre

and length(orderNo) = 7 + num

ORDER BY id DESC LIMIT 1 -- 有多条时只显示离现在最近的一条

IF oldOrderNo != '' THEN

SET maxNo = CONVERT(SUBSTRING(oldOrderNo, -5), DECIMAL) -- SUBSTRING(oldOrderNo, -5)：订单编号如果不为‘‘截取订单的最后5位

END IF

SELECT

CONCAT(orderNamePre, currentDate, LPAD((maxNo + 1), 5, '0')) INTO newOrderNo -- LPAD((maxNo + 1), 5, '0')：如果不足5位，将用0填充左边

INSERT INTO test_orders (orderNo, orderName) VALUES (newOrderNo, 'testNo') -- 向订单表中插入数据

--set newOrderNo = l_orderNo

SELECT

newOrderNo

END

参数说明：orderNamePre：(输入)订单编号的前缀,这里设定为两个字符

num：(输入)将按什么规则生成流水号(生成规则有：年月日、年月日时分秒、年月日时分三种),可选的num有：8、12、14

newOrderNo：(输出)新生成的订单编号

生成中的一些说明在存储过程中已经写得很明确了，这里不再重复。

调用存储过程向表中插入数据:

[sql] view plain copy

SET @orderNo = ''

CALL `generate_orderNo`('SH', 12, @orderNo)

SELECT @orderNo

天猫订单

本数据集共收集了发生在一个月内的28010条数据(2020-02-01到2020-02-29)，包含以下：

['订单编号', '总金额', '买家实际支付金额', '收货地址', '订单创建时间', '订单付款时间 ', '退款金额']

7个字段说明：

订单编号：订单编号

总金额：订单总金额

买家实际支付金额：总金额 - 退款金额（在已付款的情况下）。金额为0（在未付款的情况下）

收货地址：各个省份

订单创建时间：下单时间

订单付款时间：付款时间

退款金额：付款后申请退款的金额。如无付过款，退款金额为0

二、数据处理

1.查看是否存在重复数据

由执行结果看出，不存在重复值，因此无需删除重复值

2.查看是否存在空值

只有订单付款时间存在空值，订单创建后不一定付款，因此合理，数据不需要对空值进行处理。

三、数据分析

1.查看哪些地区的人购买订单最多

根据统计结果发现，上海、广东、江苏、浙江、北京的购买订单在前五名，且占全部订单的43%

tableau图表展示各个省份的订单量，颜色越深，则订单量越多。几个重点省市的订单量较多，建议可以向这些重点城市的周边省市发展客户，这些省市的购买量可能已达饱和，但是其他城市仍然存在需求量。

2.查看这一个月中每一天的下单量，发现月末的下单量最多

查看这一天中哪天的放弃付款率最高，发现月中的放弃付款率最高。

tableau展示这个月每一天的创建订单量与付款量，月末的订单量显著提升。推测2020年2月初正处于复工阶段，因此下单量较低。

查看在一天之中，哪个时间段的下单量最多，总体来说，晚上处于下单高峰期

查看一天24小时内，各个时间段的提交订单量和支付订单量，发现早上放弃付款的概率稍微高一点，但是一天之内的放弃付款率几乎相同。

tableau展示各个时间的创建订单量和付款订单量，符合人们晚上比较空闲，尤其是睡前更有空挑选需要购买的东西。根据用户下单高峰时间，建议商家可以在早上10点，下午2-4点，晚上8-10点之间进行营销活动。

3.计算转化率

由结果可知，创建订单为100%，支付订单为85.99%，订单成交为67.67%，订单全额支付成交为65.84%。从转化率来看，客户退款比例较大。

tableau图表展示漏斗图。由于天猫有七天无理由退换货的政策，因此难免会存在退货退款的现象，很多用户会抱着试试看的心态购买后确认是否合适，然后会进行相应的 *** 作，因此建议商家重点关注老客户，黏性高的老客户相对于抱着试试看心态的退货概率更低。另外，可以及时联系新客户接收反馈。另外，建议商家图片描述，商品描述信息尽量齐全并且与商品实际描述一致，退货带来的成本可能会比商品不卖出的成本更高。

4.销售额在一个月中，也是月末销售额较高，基本与订单量走势一致

查看每天订单的退款/付款率，发现月初的退款率较高，推测与月初处于复工阶段，客户没有及时收到商品就退款了。

tableau展示这一个月中每天的退款率。

查看各个地区的退货率，与销售量并不一致，湖北和新疆的退款率很高，推测与疫情有关。

tableau展示各个地区的退款率。

可以的，如果你生成的订单号重复数据很多，那样肯定会报错无法插入数据的，不过这些影响还是比较小。因为数据还没进入到内存，只是在服务层。

建议你生成订单号的工具最好是那种随机数，或者是有规律递增的，那样就不会有很多重复的了

---