存储过程生成主键 mysql delimiter $$create procedure generatekeys(in pm_name varchar(20))begin declare curr_key varchar(20); declare next_key varchar(20); declare prefix_value varchar(5); declare suffix_value int; select x.nextvalue,x.defaultprefix,x.suffixvalue into curr_key,prefix_value,suffix_value from pm_tableprimaryinfo x where x.table_name=pm_name; if (curr_key is null or curr_key='') then set suffix_value='0'; else set suffix_value = suffix_value+1; end if; set curr_key = concat(prefix_value, suffix_value); update pm_tableprimaryinfo x set x.nextvalue=curr_key, x.suffixvalue=suffix_value where x.table_name=pm_name; select curr_key; end
call generatekeys(tablename);
create table `pm_tableprimaryinfo` ( `id` int(11) not null auto_increment, `table_name` varchar(30) null default null, `primaryname` varchar(30) null default null, `nextvalue` varchar(50) null default null, `defaultprefix` varchar(30) null default null, `suffixvalue` int(11) null default null, primary key (`id`))