mysql创建表报错1146,为什么在存储过程中收到此MySQL错误1146（无此类表）？

Here is my MySQL stored procedure:

CREATE PROCEDURE myStoredProc(DB_NAME varchar(30))

BEGIN

DECLARE NO_EXAM_TABLE BOOLEAN;

/*This one throws 'No Such Table' exception*/

SELECT IF(count(*) = 0, TRUE, FALSE)

FROM information_schema.COLUMNS cols

WHERE cols.TABLE_SCHEMA = DB_NAME

AND cols.TABLE_NAME = 'exam'

INTO NO_EXAM_TABLE;

/*But this one works fine (removed the INTO clause)*/

SELECT IF(count(*) = 0, TRUE, FALSE)

FROM information_schema.COLUMNS cols

WHERE cols.TABLE_SCHEMA = DB_NAME

AND cols.TABLE_NAME = 'exam';

END;

Does MySQL think NO_EXAM_TABLE is a table instead of a variable?

EDIT

This is what I'm using to catch the error:

DECLARE EXIT HANDLER FOR 1146 BEGIN SELECT "42S02 (ER_NO_SUCH_TABLE) Table doesn't exist" as 'ERROR_NO SQLSTATE'; ROLLBACK; END;

EDIT2

I've narrowed the problem down to this clause:

AND cols.TABLE_NAME = 'exam';

If I change it to this, it works:

AND cols.TABLE_NAME LIKE 'exam';

I don't understand why the = operator is causing an error and the LIKE operator is working fine.

解决方案

You need to put the INTO in the correct position:

select ...

into var

from table

where ...