mysql使用触发器生成唯一订单号,

时间:2021-10-25 07:28:24

需求:订单号唯一,并且期望是时间格式加其他字符串,

实现:采用触发机制,在新增时根据新增id值加1作为订单生成的随机且确定唯一的数,因为id唯一;

遇到问题:新增时不能提前知道id值,

解决:取到当前表中最大值 1即为id;

业务需要研究几个小时终于实现一套可行方案;

 

代码:

建表

DROP TABLE IF EXISTS `qs_test`;
CREATE TABLE `qs_test`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_num` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `value` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

创建触发器:

 触发器主要代码

set new.order_num = concat(date_format(now(),%y%m%d%i),(select LPAD(IFNULL(max(id),0) 1,11,0) FROM qs_test))

触发器全部代码

CREATE DEFINER = `root`@`%` TRIGGER `insert_value_add` BEFORE INSERT ON `qs_test` FOR EACH ROW set new.order_num = concat(date_format(now(),%y%m%d%i),(select LPAD(IFNULL(max(id),0) 1,11,0) FROM qs_test)) ;