MySql序列實現

小結一下：mysql 實現 oracle中序列的功能

建立一張用於記錄序列值的表：

drop table if exists dpf_risk.seq_record;
create table dpf_risk.seq_record
(
seq_name varchar(100) comment '序列名'
,val int(11) not null default 0 comment '序列值'
,primary key (seq_name)
) comment '此表用於記錄序列的值'
;
/*初始化序列的其實值：insert into dpf_risk.seq_record(seq_name,val) values('rdm_lkp_uid_rid_mobile_emal_uid',989719);
update dpf_risk.seq_record 
set val = 989719
*/

建立取序列值的函式 （此為 next_val()的實現，current_val()同理，步長可以自己定義，很方便）
drop function if exists dpf_risk.seq; 
DELIMITER $$
create function dpf_risk.seq(seq_name varchar(100)) returns int(11) 
not DETERMINISTIC
begin 
 declare res int default 0;
 update dpf_risk.seq_record set val = last_insert_id(val+1) where seq_name = seq_name ;  
 
 select last_insert_id(val) into res from dpf_risk.seq_record where seq_name = seq_name ;  
 
 return res;  
end$$
DELIMITER ;

測試一下：

mysql> select dpf_risk.seq('rdm_lkp_uid_rid_mobile_emal_uid');
+-------------------------------------------------+
| dpf_risk.seq('rdm_lkp_uid_rid_mobile_emal_uid') |
+-------------------------------------------------+
|                                          989721 |
+-------------------------------------------------+
1 row in set (0.00 sec)


mysql> select dpf_risk.seq('rdm_lkp_uid_rid_mobile_emal_uid');
+-------------------------------------------------+
| dpf_risk.seq('rdm_lkp_uid_rid_mobile_emal_uid') |
+-------------------------------------------------+
|                                          989722 |
+-------------------------------------------------+
1 row in set (0.00 sec)


功能實現！