MySQL “'字段列表'中的未知列”，但该列确实存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15989529/
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
"Unknown column in 'field list'", but column does exist
提问by Rits
DROP TABLE IF EXISTS `transactions`;
CREATE TABLE `transactions` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`purchase_date` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `transactions` (`purchase_date`) VALUES (NULL)
I've isolated my problem in this code. When I run it, I get the error:
我在这段代码中隔离了我的问题。当我运行它时，我收到错误：
[ERROR in query 3] Unknown column 'purchase_date' in 'field list'
[查询 3 中的错误]“字段列表”中的未知列“purchase_date”
Anyone an idea?
任何人的想法？
回答by whetstone
There is an unprintable character 30 (RecordSeparator) inserted between purchase_dateand the 'in the INSERTstatement. Just remove the text ('purchase_date')and rewrite it by hand it should be fine.
有插入之间的不可打印字符30（RecordSeparator）purchase_date的和'在INSERT发言。只需删除文本('purchase_date')并手动重写它应该没问题。
回答by Lewis
Nery niche solution when I got this error.
当我收到此错误时，Nery 利基解决方案。
I had a BEFORE INSERTtrigger on my table that did something with NEW.`field_mysql_doesnt_think_exists`
and if I didn't pass that field to an insert statement then I would get
我的BEFORE INSERT桌子上有一个触发器，它做了一些事情，NEW.`field_mysql_doesnt_think_exists`
如果我没有将该字段传递给插入语句，那么我会得到
[ERROR in query 3] Unknown column 'field_mysql_doesnt_think_exists' in 'field list'
[ERROR in query 3] Unknown column 'field_mysql_doesnt_think_exists' in 'field list'
回答by prokaryote
I just spent the better part of a day figuring this out. My problem was the same: invisible characters kiboshing the query and returning the "unknown column" error.
我只是花了一天的大部分时间来解决这个问题。我的问题是一样的：不可见字符 kiboshing 查询并返回“未知列”错误。
I solved it by wading back into Windows and removing the garbage using NotePad++.
我通过回到 Windows 并使用 NotePad++ 删除垃圾来解决它。
How did the garbage get in there in the first place? I think it was because I made the mistake of copying some long complex queries into LibreOffice Writer (my functional specs document) instead of just bookmarking them in phpMyAdmin or saving them in a text editor. Pasting them from LibreOffice into the query window is where (I think) the garbage originated.
垃圾最初是怎么进来的？我认为这是因为我犯了一个错误，将一些冗长的复杂查询复制到 LibreOffice Writer（我的功能规范文档）中，而不是仅仅在 phpMyAdmin 中为它们添加书签或将它们保存在文本编辑器中。将它们从 LibreOffice 粘贴到查询窗口是（我认为）垃圾的来源。
Once there, it persisted like Malaria. I couldn't even get rid of it by hand-retyping the whole query -- I had to put it into NotePad++ (Encoding menu) and show ANSI and the UTF8 combos and then remove the garbage by hand.
一旦到达那里，它就会像疟疾一样持续存在。我什至无法通过手动重新输入整个查询来摆脱它——我不得不将它放入 NotePad++（编码菜单）并显示 ANSI 和 UTF8 组合，然后手动删除垃圾。
Once that was done, the query worked.
一旦完成，查询就起作用了。
回答by designgroop
This can also happen if you paste a column name when building the table structure. Same error - but the unprintable/invisible characters are in the table structure, not the query.
如果在构建表结构时粘贴列名，也会发生这种情况。同样的错误 - 但不可打印/不可见的字符在表结构中，而不是查询中。
回答by cssyphus
This might not help anyone else, but adding this "just in case" it helps someone.
这可能对其他人没有帮助，但添加这个“以防万一”对某人有帮助。
In my situation it was a different solution.
在我的情况下，这是一个不同的解决方案。
I receive large datasets as Excel CSV files and use a (WIL) script to convert the .csv file into an importable .sql file. I had an error in my script whereby these two lines did not reference the same table name (I had hard-coded the first location and forgot to update it):
我以 Excel CSV 文件的形式接收大型数据集，并使用 (WIL) 脚本将 .csv 文件转换为可导入的 .sql 文件。我的脚本中有一个错误，这两行没有引用相同的表名（我硬编码了第一个位置并忘记更新它）：
* "INSERT INTO `old_table_name` (`cid`, `date`, etc etc"
* "CREATE TABLE IF NOT EXISTS `":_dbName:"` (etc etc "
I just changed the first line to also get the table name from the variable, and voila!
我刚刚更改了第一行以从变量中获取表名，瞧！
* "INSERT INTO `":_dbName:"` (`cid`, `date`, etc etc"
So check those two lines in your import SQL file.
因此，请检查导入 SQL 文件中的这两行。
回答by Manohar Reddy Poreddy
Same error in a different scenario:
不同场景中的相同错误：
This also happens when you miss @ symbol for a variable.
当您错过变量的 @ 符号时，也会发生这种情况。
SET @myVar1=1; SELECT @myVar1; -- GOOD, correctly prints: 1
SET @myVar1=1; SELECT myVar1; -- BAD, prints: Unknown column 'myVar1' in 'field list'
