MySQL:"where子句不明确的列'column_name'" [英] MySQL: "Column 'column_name' in where clause is ambiguous"

问题描述

我加入了2张桌子

例如

table_A
+---------+-----------+-----------+
| user_id | ticket_id | user_name |
+---------+-----------+-----------+

table_B
+-----------+-------------+
| ticket_id | ticket_name |
+-----------+-------------+

如果我运行以下查询:

SELECT
  table_A.user_id
, table_A.user_name
, table_B.ticket_name
FROM table_A
LEFT JOIN table_B ON table_B.ticket_id = table_A.ticket_id
WHERE ticket_id = '1';

在实时服务器上，我们收到错误:"Column 'ticket_id' in where clause is ambiguous"
在测试服务器上，查询被接受.

On the live server we get the error: "Column 'ticket_id' in where clause is ambiguous"
On the the test server the query is accepted.

我知道如何解决错误，这不是问题.

但是，如果我们的测试服务器不接受该查询并且(就像生产服务器一样)抛出错误，我将感到非常高兴.

I would however be very happy if our test server would NOT accept this query and (just like the production server) throw an error.

有人知道是否有某种设置会使测试服务器像实时服务器一样抛出错误?

Does anybody know if there is some kind of setting that would make the test server throw an error just like the live server?

P.S.
测试服务器上的MySQL版本:5.0.32-Debian_7etch5-log
实时服务器上的MySQL版本:5.0.41-community-log

P.S.
MySQL version on Test server: 5.0.32-Debian_7etch5-log
MySQL version on Live server: 5.0.41-community-log

推荐答案

MS SQLServer也存在类似的问题，其中版本2000接受一些模棱两可的查询，而2005则会引发错误.基本上，较新的版本似乎更严格.

There are a similar issues with MS SQLServer where version 2000 is accepting some ambiguous queries and the 2005 would throw an error. Basically the newer versions seem to be more strict.

作为一般规则，应该在测试服务器和生产服务器上使用相同的数据库版本，以避免这种行为，因为一段代码可以在测试计算机上工作而在生产上失败.

As a general rule you should use the same DB version both on Test server and on Production server to avoid this type of behaviour where a piece of code works on the test machine and fails on production.

这篇关于MySQL:"where子句不明确的列'column_name'"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！