Geek Answers Handbook

Column selection, which is also a keyword in MySQL

For some reason, the developers of the new company I'm working on decided to name their columns “ignore” and “exist”. Now, when I run MySQL queries with these words in the where clause, I get a syntax error; however, I cannot figure out how to refer to these columns without getting into an error. I tried setting them as strings, but that makes no sense.

reference

Also, is there a term for this kind of inconsistency?

+4
source share
2 answers

enter the names in the backlinks:

`ignore`, `exists`

If you work with several tables or databases, you need to avoid the database name, table name and field name separately (if each corresponds to a keyword):

 SELECT * FROM `db1`.`table1` LEFT JOIN `db2`.`table2` on `db1`.`table1`.`field1`=`db2`.`table2`.`field2`

Only parts that really match the keyword should be escaped, so things like:

 select * from `db1`.table

also okay.

+10
source

The official term is "idiocy" :-) You can put backlinks around names such as

 `ignore`

but I would seriously think about changing the names, if possible. Backticks are not standard SQL, and I prefer column names to be slightly more expressive. For example, ignoreThisUser or orderExists (the general rule I'm trying to follow is to have a noun and a verb somewhere).

Interestingly, some DBMSs may not expect to see this as a reserved word based on context. For example, DB2 / z allows quite disgusting:

 > CREATE TABLE SELECT ( SELECT VARCHAR(10) ); > INSERT INTO SELECT VALUES ('HELLO'); > SELECT SELECT FROM SELECT; SELECT ---------+---------+---------+-------- HELLO DSNE610I NUMBER OF ROWS DISPLAYED IS 1
+4
source

More articles:


All Articles