DELIMITER $$
DROP PROCEDURE IF EXISTS CHECK_VAR$$ CREATE DEFINER='root'@'localhost' PROCEDURE CHECK_VAR(IN tablename VARCHAR(50),IN variable VARCHAR(50))BEGIN
SET @SQL=CONCAT('IF EXISTS (SELECT * FROM information_schema.columns where table_name=\'',tablename,'\' and column_name=\'',variable,'\') THEN RETURN "YES" ELSE RETURN "NO"');
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$DELIMITER ;CALL CHECK_VAR('fruits','f_id')出错
DROP PROCEDURE IF EXISTS CHECK_VAR$$ CREATE DEFINER='root'@'localhost' PROCEDURE CHECK_VAR(IN tablename VARCHAR(50),IN variable VARCHAR(50))BEGIN
SET @SQL=CONCAT('IF EXISTS (SELECT * FROM information_schema.columns where table_name=\'',tablename,'\' and column_name=\'',variable,'\') THEN RETURN "YES" ELSE RETURN "NO"');
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END$$DELIMITER ;CALL CHECK_VAR('fruits','f_id')出错
BEGIN
if EXISTS (SELECT * FROM information_schema.columns where table_name=tablename and column_name=variable)
then
select "YES";
else
select "NO";
end if;
end$mysql> call CHECK_VAR('test','a');
-> $
+----+
| NO |
+----+
| NO |
+----+
1 row in set (0.00 sec)
什么时候直接就用sql语句
显然,红色部分仅做一个WHERE中的变量,而不是表名和变量名。