mysql创建存储过程，set动态赋值

1.表结构

CREATE TABLE `tb_course_version` (
  `COURSE_ID` varchar(32) NOT NULL COMMENT '',
  `BASE_ID` varchar(32) NOT NULL COMMENT '',
  `VERSION` int(6) NOT NULL DEFAULT '1' COMMENT '版本号序号',
  PRIMARY KEY (`COURSE_ID`),
  KEY `BASE_ID_INDEX` (`BASE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.创建存储过程

DROP PROCEDURE IF EXISTS `create_tb_course_version`;
DELIMITER $$
CREATE PROCEDURE `create_tb_course_version` (IN courseId VARCHAR (32),IN baseId VARCHAR (32))
BEGIN
	# 设置版本序号
	SET @version = (SELECT (IFNULL(MAX(version),0) +1) FROM tb_course_version WHERE base_id = baseId);
	# 插入课程版本数据
	INSERT INTO tb_course_version(`COURSE_ID`,`BASE_ID`,`VERSION`) VALUES (courseId,baseId,@version);
END $$
DELIMITER ;