mysql 存储过程实现动态sql

mysql 5.0 以后，才支持动态sql。这里介绍两种在存储过程中的动态sql：

1、第一种方式，拼接方式

示例：
CREATE PROCEDURE `proc_demo1`(in type VARCHAR(32),in id VARCHAR(32),in username VARCHAR(500))
BEGIN
	# 定义变量
	DECLARE var_sql VARCHAR(2000);
	# 拼接sql
 	set var_sql = " select * from user where 1=1";
	if type = 0 then
		set var_sql = CONCAT(var_sql,' and id = ','\'',id,'\'');
	end if;
	if type =1 then 
		set var_sql = CONCAT(var_sql,' and username = ','\'',username,'\'');
	END IF;
	
	# select var_sql ;
	set @sql = var_sql;
	# 预处理动态sql语句
	PREPARE stmt  from @sql;	
	# 执行sql
	EXECUTE stmt ;
	# 释放prepare
	deallocate prepare stmt; 	
END

2、第二种方式，使用占位符方式

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_demo4`(in type varchar(32), in id varchar(32),in username varchar(500),in sex varchar(2000))
BEGIN
	declare var_sql varchar(500);   
	set var_sql = " select * from user where 1=1";
	 
	if type =0 then
	 set var_sql =CONCAT(var_sql, " and id = ?");
	 # 参数赋值
	 set @id_param = id;
	end if;
	
	if type =1 then 
	 set var_sql =CONCAT(var_sql, " and username = ?");
	 # 参数赋值
	 set @username_param = username;
	end if;
	
	if type =2 then 
	 set var_sql =CONCAT(var_sql, " and sex find_in_set(sex,?) ");
	 # 参数赋值
	 set @sex_param = sex;
	end if;
	 
	set @sql = var_sql;
	PREPARE stmt FROM @sql;
		 
	# 执行sql,同时使用参数
	if type = 0 then 
		EXECUTE stmt USING @id_param;
	end if;
	if type = 1 then
		EXECUTE stmt USING @username_param;
	end if;
	if type = 2 then
		EXECUTE stmt USING @sex_param;
	end if;
	
	deallocate prepare stmt;
END

关于存储过程如何使用 in 条件：

find_in_set(字段，'aaa,bbb,ccc')

转发需注明出处，欢迎交流！ 1057449102@qq.com