CODEWITHSUNDEEP
phpmysqlreserved
Kannika
Kannika

Reputation: 2558

What are the risks of using the same keyword of MySQL as table column?

I already deplay a project so far with the same column field and keyword of MySQL using PHP. I just got the message warning as below when I use PhpMyAdmin to manage the database again today.

The column name 'order' is a MySQL reserved keyword.

My project is still working with the keyword order as the column name.

My question is what are the risks or ... when we're using the same keyword of MySQL as table column?

Upvotes: 1

Views: 108

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Risks? You have experienced the risk: the query doesn't run so you have to fix it.

For most keywords, there is little chance of a keyword being confused with an identifier and still producing a valid query. Of course, you could come up with some arcane examples.

The fix is to escape the identifiers. MySQL uses backticks for escaping. Other databases use double quotes or square braces. So, there is another risk: the more you use backticks, the less portable your code is.

For me, the major reason is writability and readability. The backtick key is, on my keyboard, way off on the upper left where my little pinky has to reach far for it. I find reading the resulting code more cumbersome with lots of arcane characters floating around. For clarity, avoid escaping identifiers. Avoiding reserved words and using only letters, numbers, and underscore is quite sufficient.

There is another risk . . . that you will confuse single quotes with backticks. Then your query might have a constant where you think it has a column value. This confusion is one of the most common MySQL problems in this community.

Upvotes: 3

Raging Bull
Raging Bull

Reputation: 18747

Use backticks(`) around order in your query.

Example:

SELECT *
FROM TableName
WHERE `order`='someval'

SideNote:

Back ticks are to be used for table and column identifiers, but are only necessary when the identifier is a MySQL reserved keyword, or when the identifier contains whitespace characters or characters beyond a limited set it is often recommended to avoid using reserved keywords as column or table identifiers when possible, avoiding the quoting issue.

Back ticks are necessary for situations like the following:

SELECT id, `my name`, `another field` , `field,with,comma`

Upvotes: 0

Related Questions