MySQL实现ORACLE序列（Sequence）的解决方案_MySQL

drop table if exists tb_sequence; create table tb_sequence ( seq_name VARCHAR(50) NOT NULL, -- 序列名称 current_val INT NOT NULL, -- 当前值 increment_val INT NOT NULL DEFAULT 1, -- 步长(跨度) PRIMARY KEY (seq_name) );

INSERT INTO tb_sequence VALUES ('seq_test1_num1', '0', '1'); INSERT INTO tb_sequence VALUES ('seq_test1_num2', '0', '2');

create function currval(v_seq_name VARCHAR(50)) returns integer begin declare value integer; set value = 0; select current_val into value from tb_sequence where seq_name = v_seq_name; return value; end;

select currval('seq_test1_num1');

create function nextval (v_seq_name VARCHAR(50)) returns integer begin update tb_sequence set current_val = current_val + increment_val where seq_name = v_seq_name; return currval(v_seq_name); end;

select nextval('seq_test1_num1');

DROP TABLE IF EXISTS `test1`; CREATE TABLE `test1` ( `name` varchar(255) NOT NULL, `value` double(255,0) DEFAULT NULL, `num1` int(11) DEFAULT NULL, `num2` int(11) DEFAULT NULL, PRIMARY KEY (`name`) );

CREATE TRIGGER `TRI_test1_num1` BEFORE INSERT ON `test1` FOR EACH ROW BEGIN set NEW.num1 = nextval('seq_test1_num1'); set NEW.num2 = nextval('seq_test1_num2'); END

INSERT INTO test1 (name, value) VALUES ('1', '111'); INSERT INTO test1 (name, value) VALUES ('2', '222'); INSERT INTO test1 (name, value) VALUES ('3', '333'); INSERT INTO test1 (name, value) VALUES ('4', '444');

SELECT * FROM test1;