为什么在存储过程中收到此MySQL错误1146（无此类表）？ [英] Why am I getting this MySQL Error 1146 (No Such Table) in my Stored Procedure?

问题描述

这是我的MySQL存储过程：

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;

MySQL是否认为 NO_EXAM_TABLE 是一个表

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';

我不明白为什么 = 运算符引起错误，并且 LIKE 运算符工作正常。

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

推荐答案

您需要将INTO放在正确的位置：

You need to put the INTO in the correct position:

select ...
into var
from table
where ...

这篇关于为什么在存储过程中收到此MySQL错误1146（无此类表）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！