SQL SQLite INNER JOIN 中的“不明确的列名”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/555246/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
"ambiguous column name" in SQLite INNER JOIN
提问by ggambett
I have two tables in a SQLite DB, INVITEM and SHOPITEM. Their shared attribute is ItemId and I want to perform an INNER JOIN. Here's the query:
我在 SQLite 数据库中有两个表，INVITEM 和 SHOPITEM。他们的共享属性是 ItemId，我想执行 INNER JOIN。这是查询：
SELECT INVITEM.CharId AS CharId,
INVITEM.ItemId AS ItemId
FROM (INVITEM as INVITEM
INNER JOIN SHOPITEM AS SHOPITEM
ON SHOPITEM.ItemId = INVITEM.ItemId)
WHERE ItemId = 3;
SQLite doesn't like it :
SQLite 不喜欢它：
SQL error: ambiguous column name: ItemId
The error goes away if I write WHERE INVITEM.ItemId = 3, but since the WHERE condition is more or less user-specified, I rather make it work without having to specify the table. NATURAL JOIN seems to solve the issue, but I'm not sure if the solution is general enough (ie I could use in this case, but I'm not sure if I can use in every case)
如果我写WHERE INVITEM.ItemId = 3，错误就会消失，但由于 WHERE 条件或多或少是用户指定的，我宁愿让它工作而不必指定表。NATURAL JOIN 似乎解决了这个问题，但我不确定该解决方案是否足够通用（即我可以在这种情况下使用，但我不确定是否可以在所有情况下使用）
Any alternate SQL syntax that would fix the problem?
任何可以解决问题的替代 SQL 语法？
采纳答案by geofftnz
I would steer clear of allowing the user to write SQL clauses directly. This is the source of SQL Injection vulnerabilities.
我会避免让用户直接编写 SQL 子句。这是 SQL 注入漏洞的来源。
If you need the query to be flexible, try parsing the user's input and adding the appropriate where clause.
如果您需要灵活的查询，请尝试解析用户的输入并添加适当的 where 子句。
Here is some C# code to show the general idea
下面是一些 C# 代码来展示总体思路
// from user input
string user_column = "ItemID";
string user_value = "3";
string sql = "SELECT INVITEM.CharId AS CharId, INVITEM.ItemId AS ItemId FROM (INVITEM as INVITEM INNER JOIN SHOPITEM AS SHOPITEM ON SHOPITEM.ItemId = INVITEM.ItemId) ";
if (user_column == "ItemID")
{
// using Int32.Parse here to prevent rubbish like "0 OR 1=1; --" being entered.
sql += string.Format("WHERE INVITEM.ItemID={0}",Int32.Parse(user_value));
}
Obviously if you're dealing with more than one clause, you'd have to substitute AND for WHERE in subsequent clauses.
显然，如果您要处理多个子句，则必须在后续子句中用 AND 替换 WHERE。
回答by Bill Karwin
I would write this query this way:
我会这样写这个查询：
SELECT i.CharId AS CharId, i.ItemId AS ItemId
FROM INVITEM as i INNER JOIN SHOPITEM AS s USING (ItemId)
WHERE i.ItemId = 3;
I'm using the USING (ItemId)syntax which is just a matter of taste. It's equivalent to ON (i.ItemID = s.ItemID).
我使用的USING (ItemId)语法只是一个品味问题。它相当于ON (i.ItemID = s.ItemID).
But I resolved the ambiguity by qualifying i.ItemIDin the WHEREclause. You would think this is unnecessary, since i.ItemID = s.ItemID. They're both equal by commutativity, so there's no semantic ambiguity. But apparently SQLite isn't smart enough to know that.
但是我通过i.ItemID在WHERE条款中进行限定来解决歧义。您会认为这是不必要的，因为i.ItemID = s.ItemID. 它们在交换性上是相等的，所以没有语义歧义。但显然 SQLite 不够聪明，无法知道这一点。
I don't like to use NATURAL JOIN. It's equivalent to an equi-join of everycolumn that exists in both tables with the same name. I don't like to use this because I don't want it to compare columns that I don't want it to, simply because they have the same name.
我不喜欢使用NATURAL JOIN. 它相当于两个表中同名的每一列的等价连接。我不喜欢使用它，因为我不希望它比较我不希望它比较的列，仅仅因为它们具有相同的名称。
回答by dave-holm
Just change your column alias to something similar, but unique (such as ITEM_ID).
只需将您的列别名更改为类似但唯一的名称（例如 ITEM_ID）。
