For some reason, the developers at a new company I'm working for decided to name their columns "ignore" and "exists". Now when I run MySQL queries with those words in the where clause, I get a syntax error; however, I can't seem to figure out how to reference those columns without running into an error. I tried setting them as strings, but that doesn't make any sense.
Help?
Also, is there a term for this kind of mismatch?
put the names in backticks:
`ignore`, `exists`
If you're working across multiple tables or databases you need to escape the database name, table name, and field name separately (if each matches a keyword):
SELECT * FROM `db1`.`table1`
LEFT JOIN `db2`.`table2` on `db1`.`table1`.`field1`=`db2`.`table2`.`field2`
Only the portions that actually match a keyword have to be escaped, so things like:
select * from `db1`.table
are ok too.
The official term is "idiocy" :-) You can put backticks around the names such as
`ignore`
but I would give serious consideration to changing the names if possible. Backticks are not standard SQL, and I prefer my column names to be a little more expressive. For example, ignoreThisUser or orderExists (the general rule I try to follow is to have a noun and a verb in there somewhere).
Interestingly, some DBMS' can figure out not to treat it as a reserved word based on context. For example, DB2/z allows the rather hideous:
> CREATE TABLE SELECT ( SELECT VARCHAR(10) );
> INSERT INTO SELECT VALUES ('HELLO');
> SELECT SELECT FROM SELECT;
SELECT
---------+---------+---------+--------
HELLO
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
