MySQL-编号生成器

• 创建表，存储前缀和当天的序号

CREATE TABLE `s_seq` (
  `prefix` varchar(20) NOT NULL DEFAULT '',
  `max_cnt_per_day` int(11) NOT NULL,
  `val` int(10) unsigned NOT NULL,
  `date` varchar(10) NOT NULL DEFAULT '',
  `desc` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`prefix`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

delimiter ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `get_serial_number`(inprefix varchar(10)) RETURNS varchar(30) CHARSET utf8
begin 
 DECLARE serialNumber varCHAR (30) ;
 DECLARE today VARCHAR(16);        
 
 SET today = DATE_FORMAT(NOW(),'%y%m%d');

 UPDATE s_seq SET val = 0 , `date` = today WHERE prefix = inprefix AND `date`!= today;
 UPDATE s_seq SET val=last_insert_id(val+1)  WHERE prefix = inprefix;   
 SET serialNumber = CONCAT(inprefix, today, LPAD(last_insert_id(),6,'0'));
 
 RETURN serialNumber;
end
 ;;
delimiter ;