自助主键

发表于 分类于 阅读次数： 阅读次数： Changyan： 本文字数： 658 阅读时长 ≈ 1 分钟
mysql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE DEFINER=`root`@`%` FUNCTION `currval`(seq_name VARCHAR(50)) RETURNS bigint(20)
BEGIN
  DECLARE current bigint;
  SET current = 0;
  SELECT currentValue INTO current
  FROM _SEQ
  WHERE LOWER(seqname) = LOWER(seq_name);
  RETURN current;
END

CREATE DEFINER=`root`@`%` FUNCTION `nextval`(seq_name VARCHAR(50)) RETURNS bigint(20)
BEGIN
   UPDATE _SEQ
   SET currentValue = currentValue + increment
   WHERE LOWER(seqname) = LOWER(seq_name);
   RETURN currval(seq_name);
END


CREATE TABLE `_seq` (
  `seqname` varchar(50) NOT NULL,
  `currentValue` bigint(20) NOT NULL,
  `increment` bigint(20) NOT NULL DEFAULT '1',
  PRIMARY KEY (`seqname`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;