MySQL - add leading underscore on reserved keywords for columns?

Question

Is it safe to add leading underscores on reserved keywords for columns?

For instance: _key

There too many reserved keywords and it is hard to avoid.

For instance currently I am using code to replace key to avoid collision.

But what happens if one day they decide to add code into the reserved keywords? Then I have to find new keyword again or adding backticks - which is a lot do to maintain!

IMO `_key` is way more error prone than just using backticks. What is the content of your column? — juergen d, Sep 13 '16 at 11:28
`What is the content of your column?` for instance: `group_name_1` it can be `longtext` or `char` or `int`. — Run, Sep 13 '16 at 11:30
I mean wouldn't `id` or `name` be a better name for the column? — juergen d, Sep 13 '16 at 11:34
i have id and name in the table already. need key/ code in it that's all. — Run, Sep 13 '16 at 11:35
Side note: don't forget that not all keywords are *reserved* keywords. — Álvaro González, Sep 13 '16 at 11:42

score 1 · Answer 1 · edited Jan 13 '22 at 04:53

1

There are tens or hundreds of thousands of words in the English language. There are a few hundred reserved words in SQL. It should not be hard to avoid the reserved words, particularly because you should strive for descriptive column names rather than short column names.

So, if you have something in your business that you call a key, is it a key for a customer (CustomerKey)? For a location (LocationKey)? For a door (DoorKey)? You get the idea.

In addition, I normally follow these naming conventions:

Tables are names in the plural (few keywords are plural, although one is values).
The id for the table is the singular of the table name followed by id or _id.
Foreign key references -- where possible -- have the name of the primary key of the table they are referring to.

edited Jan 13 '22 at 04:53

Nimantha

6,405
6
28
69

answered Sep 13 '16 at 11:35

Gordon Linoff

1,242,037
58
646
786

Where does this naming conventions come from? – Run Sep 13 '16 at 11:37
`Tables are names in the plural (few keywords are plural, although one is values)` not sure about this. have looked around and some suggest using singular as convention... – Run Sep 13 '16 at 11:52
1

@teelou . . . In my books, I recommend plurals for the names of tables. – Gordon Linoff Sep 13 '16 at 22:32

Bill Karwin · Answer 2 · 2022-01-13T05:19:05.840

Using a leading _ character currently has no conflicts with MySQL's reserved keywords, but in theory they could start using that character in the future. The chances of it are quite low, but that's not a strict guarantee.

It's true that each new version of MySQL may introduce a few new keywords. If you want to prepare for any new reserved keywords, the only sure solution is to develop a habit of using back-ticks on all your identifiers.

The same risk exists in any other programming language, by the way. This is inconvenient for anyone using those words as names of functions, classes, or constants.

PHP 7.1 introduced reserved words void and iterable, and object was added in PHP 7.2.
Java 4 added assert, Java 5 added enum, and Java 9 added _ as reserved words.
JavaScript/ECMAScript has a complex history of adding and removing reserved words. See https://mathiasbynens.be/notes/reserved-keywords
Python 3 added reserved words False, True, None, __peg_parser__, async, await, nonlocal, and removed as keywords exec and print.

Ultimately, you just need to accept that when you upgrade a major version of some software, you have to do some work to accommodate its changes.

MySQL - add leading underscore on reserved keywords for columns?

2 Answers2