写了个简单的添加字段的存储过程,发现mysql存储过程中的where in是通过FIND_IN_SET(column_name,v_column_name)实现的!
调用的存储过程:
CALL `sp_add_column`('test','tiger_test_add_column',CONCAT('test1',',','test2'),'ALTER TABLE', 'tiger_test_add_column ADD test1 VARCHAR(60), ADD test2 VARCHAR(60)',@v_out);
SELECT @v_out;
部分存储过程:
CREATE DEFINER=`test`@`%` PROCEDURE `sp_test`(
IN v_schema_name VARCHAR (50),
IN v_table_name VARCHAR (50),
IN v_column_name VARCHAR (500),
IN v_str_sql1 VARCHAR (500),
IN v_str_sql2 VARCHAR (500),
OUT v_out VARCHAR(50)
)
BEGIN
DECLARE str VARCHAR (2000) ;
DECLARE v_str VARCHAR (2000) ;
-- 判断表中是否已经存在同名字段
SELECT
COUNT(*) INTO @cnt
FROM
information_schema.columns
WHERE table_schema = v_schema_name
AND table_name = v_table_name
AND FIND_IN_SET(column_name,v_column_name);
-- 执行脚本
IF @cnt <= 0