mysql触发器 非主键自增长

mysql中有1个表，流水号，非主键字段，需要发号自增长，所以决定建立1个触发器来处理

新建1张发号表

DROP TABLE IF EXISTS `sequence`;
CREATE TABLE `sequence` (
  `seqkey` varchar(32) NOT NULL,
  `seqid` bigint(38) NOT NULL,
  PRIMARY KEY (`seqkey`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、初始化发号信息

INSERT INTO `sequence` VALUES ('test', '6');

3、新建测试表

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `name` varchar(255) DEFAULT NULL,
  `id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

4、在测试表上新建触发器，id自增长，根据sequence 发号表，seqid 每次+1

-- 触发器
DROP TRIGGER IF EXISTS `test_seqid`;
DELIMITER ;;
CREATE TRIGGER `test_seqid` BEFORE INSERT ON `test` FOR EACH ROW BEGIN  
DECLARE nid iNT(10); 
 
update sequence set seqid = seqid + 1 where seqkey='test';
SELECT seqid into nid from sequence  where seqkey='test';

SET new.id = nid;
end
;;
DELIMITER ;

5、测试

insert into test(name)VALUES('测试1')

执行查询sql，结果如下