目录

使用function实现类似oracle里的sequence序列

1.登录mysql进入具体的数据库下

用工具登录，或者linux下脚本：mysql -u username -p

输入密码，再use database

2.创建function

DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `_nextval`(n varchar(50)) RETURNS int(11)
begin declare _cur int; 
set _cur=(select current_value from sequence where name= n); 
update sequence set current_value = _cur + increment_num where name=n; 
return _cur;
end
;;
DELIMITER ;

如果这时候报错 

ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

，有可能是一个系统变量没有打开，执行以下命令，再重新创建function即可 

show variables like 'log_bin_trust_function_creators';#OFF
set global log_bin_trust_function_creators=1;#修改mysql环境变量
show variables like 'log_bin_trust_function_creators';#ON

3.创建sequence表

CREATE TABLE `sequence` (
  `NAME` varchar(50) NOT NULL,
  `CURRENT_VALUE` int(10) unsigned NOT NULL,
  `INCREMENT_NUM` int(10) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='序列表';

4.插入sequence数据

每条记录指向一张表的序列，name为表名 

insert into sequence(name,current_value,increment_num)
values('be_acco_capital_mode',1,1);

5.查询序列(自增)

select * from sequence;#查看当前值，为1
select _nextval('be_acco_capital_mode') from dual;#查询表名对应的序列，_nextval的参数为对应的表名
select * from sequence;#查看下一个值，为2