补全MYSQL 如何实现流水号自动增长的问题

1，建一张设置增长起点值以及增长幅度的值的表 mysql_seq

DROP TABLE IF EXISTS `mysql_seq`;
CREATE TABLE `mysql_seq`  (
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `start_value` int(11) NOT NULL,
  `increment_value` int(11) NOT NULL DEFAULT 1,
  PRIMARY KEY (`name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of mysql_seq
-- ----------------------------
INSERT INTO `mysql_seq` VALUES ('考试', 103, 1);
INSERT INTO `mysql_seq` VALUES ('问卷', 302, 1);

SET FOREIGN_KEY_CHECKS = 1;

2，编写nextval函数可以拿到下次增长后需要的值

CREATE DEFINER=`root`@`%` FUNCTION `nextval`(str varchar(50)) RETURNS int(11)
begin
	declare i int;
	  set i=(select start_value from mysql_seq where name=str);
	update mysql_seq
		set start_value=i+increment_value where name=str;
return i;
end

3，具体的表实现具体流水号逻辑的触发器

DROP TRIGGER IF EXISTS `before_insert_exam`;
delimiter ;;
CREATE TRIGGER `before_insert_exam` BEFORE INSERT ON `zqx_exam` FOR EACH ROW BEGIN
	DECLARE examid VARCHAR ( 50 ) CHARACTER SET utf8;
	DECLARE nextid INT;
	IF new.TYPE = '问卷' THEN
        set examid = 'Q';
        set nextid = nextval('问卷');
	ELSE 
	    set examid = 'T';
	    set nextid = nextval('考试');
	END IF;
	IF nextid < 10 THEN
			set examid = CONCAT(examid,'0000',nextid);
	ELSEIF nextid < 100 THEN
			set examid = CONCAT(examid,'000',nextid);
	ELSEIF nextid < 1000 THEN
			set examid = CONCAT(examid,'00',nextid);
	ELSEIF nextid < 10000 THEN
			set examid = CONCAT(examid,'0',nextid);
	ELSE 
      set examid = CONCAT(examid,nextid);
	END IF;
	set new.exam_id = examid;
	set new.create_time = NOW();
	set new.UPDATE_time = NOW();
END
;;
delimiter ;
SET FOREIGN_KEY_CHECKS = 1;

**其他博客：
oracle 实现流水号定时重置的问题

docker安装18.03.0+rancher1.6.17 的容器虚拟化部署

vue.js+iview 实现全局加载的公用方法
欢迎留言评论学习**