Maison >base de données >tutoriel mysql >存储过程生成主键
存储过程生成主键 MySQL delimiter $$CREATE PROCEDURE generateKeys(in pm_name varchar(20))begindeclare curr_Key varchar(20);declare next_Key varchar(20);declare prefix_Value varchar(5);declare suffix_value int;select x.nextValue,x.DefaultPref
存储过程生成主键 MySQLdelimiter $$ 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`) )