Mysql创造自定义序列_mysql

/*创建序列方法*/
DROP FUNCTION IF EXISTS _nextval2;
create function _nextval2(
 in_nextname varchar(255)
)
RETURNS VARCHAR(255)
begin 
declare _cur int;
declare _max_value int;
declare _min_value int;

set _cur=(select current_value from t_sequence where name= in_nextname);
set _max_value=(select max_value from t_sequence where name= in_nextname); 
set _min_value=(select min_value from t_sequence where name= in_nextname); 

if _cur > _max_value  then
 set _cur=_min_value;
end if;

update t_sequence 
 set current_value = _cur + _increment 
 where name=in_nextname; 
return _cur; 

end

/*mysql*/

SET FOREIGN_KEY_CHECKS=0;
— —————————-
— Table structure for `t_sequence`
— —————————-
DROP TABLE IF EXISTS `t_sequence`;
CREATE TABLE `t_sequence` (
  `name` varchar(50) COLLATE utf8_bin NOT NULL DEFAULT ”,
  `current_value` bigint(11) DEFAULT NULL,
  `_increment` int(11) DEFAULT NULL,
  `max_value` bigint(11) DEFAULT NULL,
  `min_value` bigint(11) DEFAULT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

— —————————-
— Records of t_sequence
— —————————-
INSERT INTO `t_sequence` VALUES (‘SEQ_COMMODITY_CODE’, ‘1000’, ‘1’, ‘9999’, ‘1000’);
INSERT INTO `t_sequence` VALUES (‘SEQ_DEPARTMENT’, ’20’, ‘1’, ‘999’, ’20’);
INSERT INTO `t_sequence` VALUES (‘SEQ_USER_ORDER_CODE’, ‘1000000’, ‘1’, ‘9999999’, ‘1000000’);
INSERT INTO `t_sequence` VALUES (‘SYSUSER_INCREMENT’, ‘1001’, ‘1’, ‘9999’, ‘1001’);
— —————————-
— Records of t_sequence
— —————————-