简单的MySQL SELECT失败,错误1054未知列
I did research on this question and nothing I've read in the MySQL manual, in MySQL bug reports, in Stack Overflow, or in other forums has helped so far.
I have a simple mysql select query:
SELECT * FROM `Toyota` WHERE `Toyota`.`CollisionEstimatingID` = '22028589';
This query fails with mysql error code 1054
Unknown column
Toyota.CollisionEstimatingID
in theWHERE
clause
This column DOES exist, I've checked the spelling of the database, table and column at least 30 times now. I even deleted my database and reimported. I have tried it with the backticks and without backticks, with alias' and without alias' I've tried it with explicitly named table.column syntax, and without the explicit syntax, I've tried mixing and matching all the stuff I've mentioned (backticks on the table, but not the column, on the column but not the table name), and nothing seems to work. It fails when I execute it from the mysql CLI on Ubuntu 12.04, it fails from my PHP 5.3 code, and it fails inside of phpMyAdmin. I am ready to flip a table.
When I try this though:
SELECT * FROM `Toyota`;
This works without any problems? Good god, MySQL is such a tease...
Here is the table setup as derived from show create database Toyota;
CREATE TABLE `Toyota` (
`CollisionEstimatingID` varchar(255) DEFAULT NULL,
`OE_part_number` varchar(255) DEFAULT NULL,
`Price` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
An interesting note -- I wonder if it could be character encoding problems? I did get it to work ONCE in phpMyAdmin by using the show create table Toyota
and deleting everything but the "CollisionEstimatingID" and used that to form the SELECT statement. But when I tried cutting and pasting, no dice.
I tried recreating the database and tables using the utf8 character set to see if that would help, but it didn't help. I tried copying to text out of the phpMyAdmin window and into my source code -- that produced some funky characters at the beginning of the column name -- kinda like this: >>?CollisionEstimatingID
only it was an 'i' with an umlaut + a double right chevron and an upside down question mark.
I'm stumped. Anyone want to test their programming mettle and help a brother out?
Like ""?
This is the UTF-8 BOM (en.wikipedia.org/wiki/Byte_order_mark).
You probably need to clean the input files first.
Your codes doesn't have any problem as i have did some testing with my SQL Server. May be try this
SELECT * FROM Toyota WHERE Toyota.CollisionEstimatingID = '22028589'