mysql#カスタムシーケンス

テキスト
mysqlプライマリキーが自増数字を使わない場合は、以下の方法を参考にして、私が写しました.

--         
DROP TABLE IF EXISTS t_common_sequence;
CREATE TABLE t_common_sequence (
  seq_name VARCHAR(100) NOT NULL COMMENT '    ',
  current_val INT NOT NULL COMMENT '   ',
  increment_val INT NOT NULL DEFAULT '1' COMMENT '      1',
  PRIMARY KEY(seq_name)
)
ENGINE = InnoDB
CHARACTER SET = utf8
COMMENT='      ';
 
--     ，             
DELIMITER //
DROP FUNCTION IF EXISTS f_currval;
CREATE FUNCTION f_currval(v_seq_name VARCHAR(100))
RETURNS INT
BEGIN
  DECLARE value INT;
  SET value = 0;
  SELECT current_val into value 
  FROM t_common_sequence
  WHERE seq_name = v_seq_name;
  RETURN value;
END;
//
DELIMITER ;
 
--     ，              
DELIMITER //
DROP FUNCTION IF EXISTS f_nextval;
CREATE FUNCTION f_nextval(v_seq_name VARCHAR(100))
RETURNS INT
BEGIN
  UPDATE t_common_sequence
  SET current_val = current_val + increment_val
  WHERE seq_name = v_seq_name;
  RETURN f_currval(v_seq_name);
END;
//
DELIMITER ;
 
--     ，          ，   
DELIMITER //
DROP FUNCTION IF EXISTS f_setval;
CREATE FUNCTION f_setval(v_seq_name VARCHAR(100), v_new_val INT)
RETURNS INT
BEGIN
  UPDATE t_common_sequence
  SET current_val = v_new_val
  WHERE seq_name = v_seq_name;
  RETURN f_currval(v_seq_name);
END;
//
DELIMITER ;



insert into t_common_sequence(seq_name,current_val,increment_val) values('seq_job',1,1);
 
 
--       
select f_currval('seq_job');
 
--        
select f_nextval('seq_job');
 
--      ，   
select f_setval('seq_job',10);
 
select * from t_common_sequence;