909422229_Mysql创建序列

--创建序列表

DROP TABLE
    IF EXISTS sequence;
CREATE TABLE
    sequence
    (
        NAME VARCHAR(50) NOT NULL,
        current_value BIGINT NOTNULL,
        increment INT NOT NULLDEFAULT 1,
        PRIMARY KEY (NAME)
    )
    ENGINE=INNODB;

--增加一个序列类型

INSERT INTO sequence VALUES ('SEQ_TRZ_MEMBER_NO',10000000000,1);

--创建查看当前序列值的函数  【注意要用cmd窗口进行创建函数，否则创建无法成功】

DELIMITER $$
DROP FUNCTION IF EXISTS currval; 
CREATE FUNCTION currval (seq_name VARCHAR(50)) 
RETURNS BIGINT 
BEGIN 
  DECLARE c_value BIGINTDEFAULT  0; 
  SET c_value = 0; 
  SELECT current_value INTO c_value
  FROM sequence 
  WHERE NAME = seq_name; 
  RETURN c_value; 
END $$

--创建查看下一个序列值的函数 【注意要用cmd窗口进行创建函数，否则创建无法成功】

DROP FUNCTION IF EXISTS nextval; 
DELIMITER $$ 
CREATE FUNCTION nextval (seq_name VARCHAR(50)) 
RETURNS BIGINT 
CONTAINS SQL 
BEGIN 
   UPDATE sequence 
   SET          current_value = current_value +increment 
   WHERE NAME = seq_name; 
   RETURN currval(seq_name); 
END $$ 

--查询当前序列的类型

SELECT         `name`,
`current_value`,
`increment`
 
FROM
`taotao`.`sequence`
LIMIT 0, 1000;

--获取当前的序列值

SELECT currval('seq_user_id')

--获取下一个序列值

SELECT nextval('seq_user_id')