MySQL 运用存储过程实现主键生成


写了一个根据当天日期生成序列号主键的一个procedure，规则为：当天的日期加上序列号，如20120604002，表明是2012年06月04号的第2单。

具体的时间方式是通过表的策略来生成的。

生成一张管理表（用于多种主键生成）

  1. create table sysOption (  
  2.   keyName varchar(255),  
  3.   value varchar(255),  
  4.   time timestamp  
  5. );  

然后就是使用存储过程（结合游标的方式) 来生成主键了，如下 ：

  1. drop procedure if exists genRecordNum;    
  2.   
  3. delimiter //  
  4. CREATE PROCEDURE genRecordNum()  
  5. BEGIN  
  6.   declare rn varchar(255) default null;  
  7.   declare v_value varchar(255) default null;  
  8.   declare v_time timestamp default null;  
  9.     
  10.   DECLARE hasResult INTEGER DEFAULT 1;  
  11.     
  12.   declare genCursor CURSOR FOR select value, time from sysOption where keyName='genRecordNum';   
  13.   declare CONTINUE HANDLER FOR SQLSTATE '02000' SET hasResult = 0;   
  14.     
  15.   OPEN genCursor;  
  16.   FETCH genCursor INTO v_value, v_time;    
  17.   CLOSE genCursor;  
  18.   
  19.     
  20.   if hasResult=0 then   
  21.        insert into sysOption values('genRecordNum', '1', now());  
  22.        set rn = CONCAT(date_format(now(), '%Y%m%d'), lpad('1', 3, '0')) ;  
  23.   else  
  24.         if date_format(v_time, '%Y%m%d')!=date_format(now(), '%Y%m%d') then  
  25.             update sysOption set value='1', time = now();  
  26.             set rn = CONCAT(date_format(now(), '%Y%m%d'), lpad('1', 3, '0')) ;  
  27.         else  
  28.             update sysOption set value=1+v_value;  
  29.             set rn = CONCAT(date_format(now(), '%Y%m%d'), lpad(1+v_value, 3, '0')) ;  
  30.         end if;  
  31.   end if;     
  32.     
  33.   select rn recordNum;  
  34. END;//  
  35. delimiter ;  
存储过程的理解：

1. 创建游标，用于查询表中相应的记录。hasResult表示表中是否有记录（如果没有查到记录，数据库抛出“02000”号错误，这是设置hasResult为0)

  1. declare genCursor CURSOR FOR select value, time from sysOption where keyName='genRecordNum';   
  2. declare CONTINUE HANDLER FOR SQLSTATE '02000' SET hasResult = 0;   

2. 执行数据库操作，如果没有记录，则直接插入数据库，并返回当前编号，如20120604001；

  1. if hasResult=0 then   
  2.    insert into sysOption values('genRecordNum', '1', now());  
  3.    set rn = CONCAT(date_format(now(), '%Y%m%d'), lpad('1', 3, '0')) ;  
3. 如果数据库有记录，则判断日期是否为今天，如果不是今天的，则更新时间为今天，并返回今天的第一个编号，如20120604001；
  1. if date_format(v_time, '%Y%m%d')!=date_format(now(), '%Y%m%d') then  
  2.             update sysOption set value='1', time = now();  
  3.             set rn = CONCAT(date_format(now(), '%Y%m%d'), lpad('1', 3, '0')) ;  
4. 如果今天已经有编号生成过了，那直接在原编号的基础上加1，更新数据库，然后返回编号，如20120604002：
  1. else  
  2.             update sysOption set value=1+v_value;  
  3.             set rn = CONCAT(date_format(now(), '%Y%m%d'), lpad(1+v_value, 3, '0')) ;  

下面是使用的结果：

相关内容