获取数据表字段列表的存储过程:
DELIMITER $$
DROP PROCEDURE IF EXISTS VAR$$ CREATE DEFINER='root'@'localhost' PROCEDURE VAR(IN tablename VARCHAR(50))
BEGIN
SET @sql=CONCAT('select column_name from information_schema.columns where
table_name=',tablename);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt; END$$ DELIMITER ; 调用时CALL VAR('fruits');
总显示unkonwn column 'fruits' in 'where clause' 求解。
DELIMITER $$
DROP PROCEDURE IF EXISTS VAR$$ CREATE DEFINER='root'@'localhost' PROCEDURE VAR(IN tablename VARCHAR(50))
BEGIN
SET @sql=CONCAT('select column_name from information_schema.columns where
table_name=',tablename);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt; END$$ DELIMITER ; 调用时CALL VAR('fruits');
总显示unkonwn column 'fruits' in 'where clause' 求解。
DROP PROCEDURE IF EXISTS VAR$$ CREATE DEFINER='root'@'localhost' PROCEDURE VAR(IN tablename VARCHAR(50))
BEGIN
SET @SQL=CONCAT('select column_name from information_schema.columns where
table_name=\'',tablename,'\'');
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt; END$$ DELIMITER ;
CREATE DEFINER='root'@'localhost' PROCEDURE VAR(IN tablename VARCHAR(50))
BEGIN
SET @sql=CONCAT('select column_name from information_schema.columns where
table_name=',tablename);
select @sql
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt; END$$ DELIMI
输出你的@sql的内容你就明白了。
就得多加单引号,而且还要用转义符\呢?
而SET @SQL=CONCAT('select .... where table_name=',tablename,'') 为 select .... where table_name= table1 显然不正确,你需要的是 select .... where table_name= 'table1'