MySQL: Unknown column in where clause error

问题:

I have a PHP script and for some reason mysql keeps treating the value to select/insert as a column. Here is an example of my sql query:

$query = mysql_query("SELECT * FROM tutorial.users WHERE (uname=`".mysql_real_escape_string($username)."`)") or die(mysql_error());

That turns into:

SELECT * FROM tutorial.users WHERE (uname=`test`)

The error was:

Unknown column 'test' in 'where clause'

I have also tried:

SELECT * FROM tutorial.users WHERE uname=`test`

回答1:

In MySql, backticks indicate that an indentifier is a column name. (Other RDBMS use brackets or double quotes for this).

So your query was, "give me all rows where the value in the column named 'uname' is equal to the value in the column named 'test'". But since there is no column named test in your table, you get the error you saw.

Replace the backticks with single quotes.

回答2:

Weird? How so? It says exactly what's wrong. There is no 'test' column in your table. Are you sure you have the right table? 'tutorial.users' ? Are you sure the table isn't named differently? Maybe you meant to do

SELECT * from users WHERE uname = 'test';

You have to reference only the table name, not the database.. assuming the database is named tutorial

回答3:

example:

$uname = $_POST['username'];
$sql="SELECT * FROM Administrators WHERE Username LIKE '$uname'"

Note the single quotes around the $uname. When you echo the query, this is the output-

SELECT * FROM Administrators WHERE Username LIKE 'thierry'

However if you miss the quote around the $uname variable in your query, this is what you'll get-

SELECT * FROM Administrators WHERE Username LIKE thierry

On MySQL server, the 2 queries are different. thierry is the input string and correctly encapsulated in quote marks, where as in the second query, it isn't, which causes an error in MySQL.

I hope this helps and excuse my englis which is not very good

回答4:

I had the same issue and it turned out to be a typo. My error message was:

Unknown column 'departure' in 'where clause'

I checked that very column in my table and it turns out that, I had spelt it as "depature" and NOT "departure" in the table, therefore throwing the error message.

I subsequently changed my query to:

Unknown column 'depature' in 'where clause' 

and it worked!

So my advise clearly is, double check that you spelt the column name properly.

I hope this helped.

回答5:

I also faced the issue of "Unknown column in where clause" when executing the following from linux (bash) command line.

mysql -u support -pabc123 -e 'select * from test.sku where dispsku='test01' ; '

This is what I got

ERROR 1054 (42S22) at line 1: Unknown column 'test01' in 'where clause'

I had to replace the single quotes 'test01' with double quotes "test01" . It worked for me. There's a difference how and the way you're executing sql queries.

When assigning a value to a variable in a script and later, using that variable in a sql statement that has to be executed by the script, there's slight difference.

If suppose variable is

var=testing

and you want to pass this value from within script to mysql, then single quotes work.

select '$var'

So different engines might evaluate backticks and quotes differently.

This is my query that worked from linux command line.

mysql -u support -pabc123 -e 'select * from test.sku where dispsku="test01" ; '