MySQL 错误代码 1166。列名不正确
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9914995/
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
MySQL Error Code 1166. Incorrect column name
提问by Travis P
When I try to run a routine I get the following error:
当我尝试运行例程时，出现以下错误：
Error Code: 1166. Incorrect column name 'School.`School Name` = case when School.`Web School Name` is null then School.`School Name` els'
错误代码：1166。不正确的列名“School.`School Name` = case when School.`Web School Name` 为 null then School.`School Name` els'
I have double checked the column name and there is in fact a School.School Name. There are no leading or trailing spaces. Here is the beginning of the routine that the error is referring to:
我已经仔细检查了列名，实际上有一个School.School Name. 没有前导或尾随空格。这是错误所指的例程的开头：
CREATE temporary TABLE tt_Step1
SELECT DISTINCT State.Code, State.Name,
School.`School Name` = case
when School.`Web School Name` is null then School.`School Name`
else School.`Web School Name`
end,
School.`School ID`
-- Into tt_Step1
FROM State LEFT JOIN School ON State.Code = School.State
Where (School.`School ID` <> ...
I recently converted this code from MSSQL to MySQL so there may be something I missed but I cannot find any errors. The original MSSQL query runs fine in SQL Server Management Studio but the converted version of it in MySQL does not.
我最近将此代码从 MSSQL 转换为 MySQL，所以可能我遗漏了一些东西，但我找不到任何错误。原始 MSSQL 查询在 SQL Server Management Studio 中运行良好，但它在 MySQL 中的转换版本却没有。
采纳答案by robertvoliva
Give this a shot:
试一试：
CREATE temporary TABLE tt_Step1
SELECT DISTINCT State.Code, State.Name,
coalesce(School.`Web School Name`, School.`School Name`),
School.`School ID`
-- Into tt_Step1
FROM State LEFT JOIN School ON State.Code = School.State
Where (School.`School ID` <> ...
回答by mellamokb
You have a couple of issues.
你有几个问题。
- You cannot alias a column with a multi-part name like
School.School Name. - Aliasing in MySql is done with
blah as ColumnName, notColumnName = blah.
- 您不能使用多部分名称为列设置别名，例如
School.School Name. - MySql 中的别名是用 完成的
blah as ColumnName，而不是ColumnName = blah.
回答by Aminah Nuraini
In my case, it was caused by an unseen extra space after the column name. I wrote promotion-idsinstead of promotion-id.
在我的例子中，它是由列名后一个看不见的额外空格引起的。我写的promotion-ids而不是promotion-id.
回答by sealamb
I had a similar problem. My problem was resolved by adding a column name to the case statement.
我有一个类似的问题。通过在 case 语句中添加列名解决了我的问题。
Example:
例子：
Case
when val <.5 then "low"
when val >1 then "high"
end as valTest
without as valTestI would get the 1166 error.
没有as valTest我会得到 1166 错误。
