mysql - #1327 - 未声明的变量 : table_name

Question

我正在将一些查询滚动到一个存储过程中，然后我遇到了 #1327 - Undeclared variable 错误......不过奇怪的是 它声称未声明的变量实际上是一个表名。

---

解决问题

所以我提取了它失败的部分过程，并尝试通过 PHPMyAdmin 直接在数据库上将其作为普通 SQL 查询运行……同样的事情。经过大量修改后，我似乎要加入另一张 table 。

如果我在单个表上运行查询，没问题，如下所示:

SET @i_channel_id = 3;
SET @i_product_id = 90;

SELECT 
`product_status_to_channel`.`status_code` INTO @s_status_code

FROM `product_status_to_channel`

WHERE `product_status_to_channel`.`channel_id` = @i_channel_id
AND `product_status_to_channel`.`product_id` = @i_product_id

ORDER BY IF(`product_status_to_channel`.`date` IS NULL, 1, 0) ASC, 
    `product_status_to_channel`.`date` DESC

LIMIT 0, 1;

SELECT @s_status_code AS status_code;

哪个输出 'LIVE' 作为 PHPMyAdmin 中的 status_code - 这很好。

---

但是，当我尝试 JOIN 到消息表以查找关联的状态消息时，我收到错误:#1327 - 未声明的变量:product_status_to_channel_lang ...但是 product_status_to_channel_lang 是一个表？!

SET @i_channel_id = 3;
SET @i_language_id = 3;
SET @i_product_id = 90;

SELECT 
`product_status_to_channel`.`status_code` INTO @s_status_code,
`product_status_to_channel_lang`.`string` INTO @s_status_message

FROM `product_status_to_channel`

LEFT JOIN `product_status_to_channel_lang`
    ON `product_status_to_channel`.`product_status_to_channel_id` = `product_status_to_channel_lang`.`product_status_to_channel_id`
    AND `product_status_to_channel_lang`.`language_id` = @i_language_id

WHERE `product_status_to_channel`.`channel_id` = @i_channel_id
AND `product_status_to_channel`.`product_id` = @i_product_id

ORDER BY IF(`product_status_to_channel`.`date` IS NULL, 1, 0) ASC, `product_status_to_channel`.`date` DESC

LIMIT 0, 1;

SELECT @s_status_code AS status_code, @s_status_message AS status_message;

它是否试图将 product_status_to_channel_lang.product_status_to_channel_id 评估为 JOIN 上的变量？

LEFT JOIN `product_status_to_channel_lang`
    ON `product_status_to_channel`.`product_status_to_channel_id` = `product_status_to_channel_lang`.`product_status_to_channel_id`

我假设我忽略了一些明显的东西？

这两个我都试过了:

• 运行 xampp 和 MySQL 5.5.27 的 Win7 机器 - MySQL Community Server (GPL)
• 运行 MySQL 5.1.73-1-log 的 Debian 机器 - (Debian)

Answer 1

没关系 - 这很明显:

SELECT 
`product_status_to_channel`.`status_code` INTO @s_status_code,
`product_status_to_channel_lang`.`string` INTO @s_status_message

应该是:

SELECT 
`product_status_to_channel`.`status_code`, 
`product_status_to_channel_lang`.`string` 

INTO 
@s_status_code, 
@s_status_message

... 一定是星期五，花了几个小时才看到。

---

... INTO @s_status_code,
`product_status_to_channel_lang`.`string` ...

^这就是它试图将 product_status_to_channel_lang 分配给要放入数据的变量的地方。