检查数据表中某个变量字段是否存在。
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')出错
[解决办法]
create PROCEDURE CHECK_VAR(tablename VARCHAR(50),variable VARCHAR(50))
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)
[解决办法]
(SELECT * FROM information_schema.columns where table_name=tablename and column_name=variable)
显然，红色部分仅做一个WHERE中的变量，而不是表名和变量名。
