[数据库技巧001] MYSQL生成OS+日期+流水号::类似OARCLE的SEQUENCE
ORACLE序列
1. http://gghhgame51333.blog.51cto.com/138362/41210
MYSQL实现ORACEL的SEQUENCE
2. http://meetrice.iteye.com/blog/89426
实现OS20130422000008,,此类编号的序列方法:OS + 日期 + 自动补全流水号:
-- 下面就是CURRRVAL的实现方案:
DROP TABLE IF EXISTS sequence_varchar;
CREATE TABLE sequence_varchar (
tabname VARCHAR(100) NOT NULL,
current_value VARCHAR(100) NOT NULL,
increment INT NOT NULL DEFAULT 1,
PRIMARY KEY (tabname)
) ENGINE=InnoDB;
INSERT INTO sequence_varchar VALUES ('testcodeseq','VM20130423000001',2);
select * from sequence_varchar;
-- sequence 语句
DROP FUNCTION IF EXISTS currval_varchar;
DELIMITER $
CREATE FUNCTION currval_varchar (seq_name VARCHAR(100))
RETURNS varchar(100)
CONTAINS SQL
BEGIN
DECLARE value2 varchar(100);
SET value2 = null;
SELECT current_value INTO value2
FROM sequence_varchar
WHERE tabname = seq_name;
RETURN value2;
END$
DELIMITER ;
-- 取得自增长的 incre
DROP FUNCTION IF EXISTS currval_incre_int;
DELIMITER $
CREATE FUNCTION currval_incre_int (seq_name VARCHAR(100))
RETURNS int
CONTAINS SQL
BEGIN
DECLARE value3 int;
SET value3 = 0;
SELECT increment INTO value3
FROM sequence_varchar
WHERE tabname = seq_name;
RETURN value3;
END$
DELIMITER ;
select * from sequence_varchar;
SELECT currval_varchar('testcodeseq');
SELECT currval_varchar('x');
show WARNINGS ;
-- nextval
DROP FUNCTION
IF EXISTS nextval_varchar;
DELIMITER $
CREATE FUNCTION nextval_varchar (seq_name VARCHAR(100)) RETURNS VARCHAR (100) CONTAINS SQL
-- OS + 日期 + 6位流水号,不足补0
BEGIN
DECLARE value2 VARCHAR (100) ;
DECLARE datecur VARCHAR (8) ;
DECLARE node VARCHAR (6) ;
DECLARE os VARCHAR (2) ;
DECLARE datenow VARCHAR (8) ;
DECLARE cur INT ;
SET value2 = currval_varchar (seq_name) ; -- 取得当前的号码
SET os = SUBSTRING(value2, 1, 2) ; -- 分解出前缀OS
SET datecur = SUBSTRING(value2, 3, 8) ; -- 当年记录的日期
SET datenow = DATE_FORMAT(CURRENT_DATE(), '%Y%m%d') ;-- 现在的时间
SET node = SUBSTRING(value2, 11, 6) ; -- 6位流水号
SET cur = node ; -- 变成INT型
IF datecur = datenow THEN
SET cur = cur + currval_incre_int(seq_name);
ELSE
SET cur = 1 ;
END
IF ;
SET node = lpad(cur, 6, '0') ;
SET value2 = concat(os, datenow, node) ;
UPDATE sequence_varchar
SET current_value = value2
WHERE tabname = seq_name;
RETURN value2 ;
END$
DELIMITER ;
select nextval_varchar('testcodeseq');
select * from sequence_varchar;
-- setval
DROP FUNCTION IF EXISTS setval_varchar;
DELIMITER $
CREATE FUNCTION setval_varchar (seq_name VARCHAR(50), value varchar(100))
RETURNS varchar(100)
CONTAINS SQL
BEGIN
UPDATE sequence_varchar
SET current_value = value
WHERE tabname = seq_name;
RETURN currval_varchar(seq_name);
END$
DELIMITER ;
-- 完结部分
-- 建表,测试功能用的表;日期编号+数字的组合
drop table if exists testcode;
CREATE TABLE testcode(
id INT NOT NULL AUTO_INCREMENT,
namecode VARCHAR(100) NOT NULL,
released YEAR NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
INSERT INTO testcode (namecode,released) VALUES (nextval_varchar('testcodeseq'),2000);
INSERT INTO testcode (id,namecode,released) VALUES (NULL,nextval_varchar('testcodeseq'),1998);
select * from testcode;
select * from sequence_varchar;
-- 通过在上面的表注入表的名称,和起始编号,自增长 步长;
INSERT INTO sequence_varchar VALUES ('testcodeseq','OS20130423000001',2);
-- 修改对应表记录的编号
select setval_varchar('testcodeseq','OS201103020000005');
-- 查询对应表 下一步的 流水号
select nextval_varchar('testcodeseq');
-- 取得对应表 记录的编号 与 步长
select currval_varchar('testcodeseq');
select currval_incre_int('testcodeseq');
-- 测试备注:-----
select SUBSTRING('os20130423000001',1,2),SUBSTRING('os20130423000001',3,8),SUBSTRING('os20130423000001',11,6),
DATE_FORMAT(CURRENT_DATE(),'%Y%m%d'),lpad(98,6,'0')
效果图:
