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，结果如下