mysql - information_schema 在过程中未知

Question

我成功地运行了一个查询，选择了 information_schema 中一个表的列名，但是如果我在存储过程中运行相同的查询以将其与游标一起使用，那么 information_schema 是未知的。谁能告诉我原因并帮助我解决问题？谢谢这是查询

 SELECT column_name
FROM information_schema.columns
WHERE table_schema = 'database_name'
AND table_name='Table_name';

这是程序。

    DELIMITER $$

    CREATE PROCEDURE build_column_names_str (INOUT column_list varchar(4000))
    BEGIN

     DECLARE v_finished INTEGER DEFAULT 0;
            DECLARE v_column varchar(100) DEFAULT "";

    -- declare cursor for column names
    DEClARE column_cursor CURSOR FOR 
     SELECT column_name
        FROM information_schema.columns
        WHERE table_schema = 'database_name'
        AND table_name='Table_name';

    -- declare NOT FOUND handler
    DECLARE CONTINUE HANDLER 
    FOR NOT FOUND SET v_finished = 1;

     OPEN column_cursor;

     get_column: LOOP

     FETCH column_cursor INTO v_column;

     IF v_finished = 1 THEN 
     LEAVE get_column;
     END IF;

     -- build column name list
     SET column_list = CONCAT(v_column,",",column_list);

     END LOOP get_column;

     CLOSE column_cursor;

    END$$

    DELIMITER ;

    SET @column_list = "";
    CALL build_column_names_str(@column_list);
    SELECT @column_list;

谢谢

Answer 1

您的代码是正确的。

检查程序执行的权限
或用

替换程序

SELECT group_concat(column_name)
        FROM information_schema.columns
        WHERE table_schema = 'database_name'
        AND table_name='Table_name';