MySQL 8.0: New reserved words

Post en français ici.

TL;DR: MySQL 8.0 introduced new reserved words, that can lead to rare errors on client side.

---

Brace yourself, MySQL 8.0 is coming soon on the databases delivered with your web hostings, also called "SharedSQL" (more info soon). Waiting for that, get prepared.

**Reserved keywords**

select `col1` from `mytable`;

When a DBMS like MySQL receives this simple SQL query, it parses it to know what to do.

One of the steps of this parsing is to distinguish the reserved words (a word having a meaning in SQL) from the identifiers (the name you gave to an object, like a table or a column).

1. If a word is between backquotes "`", then that's an identifier.
2. Else, if it is in the hardcoded list of reserved words ("select", "from", "database", "table"…), then that's a reserved word.
3. Else, that's an identifier.

In the below query, "col1" and "mytable" are not reserved words. So it's not mandatory to put them between "`", and the SQL query can be written like this:

select col1 from mytable;

But, if the name of your table is "select":

1. That's weird 😅
2. You HAVE to use "`" if you want to use this name as an identifier

select * from `select`;
=> OK

select * from select;
=> That's a syntax error

**Best practice: ORM**

Front developers don't HAVE to be experts of SQL and DBMS. They have lots of more interesting things to do. If you want to get rid of the SQL and DBMS specificities, move easily from a DBMS version (like MySQL 5.7) to another (like MySQL 8.0), or from a DBMS (like MySQL) to an other one (like PostgreSQL), then use an ORM. You'll then have to manipulate objects, and that's the ORM mission to put the "`" where it has to.

**Best practice: backquotes**

If you don't want to use an ORM (that's a pity), at least use backquotes.

"If your identifier is a reserved word, use backquotes". OK, make this easier. Just use backquotes for your identifiers.

**New reserved words introduced in MySQL 8.0**

MySQL 8.0 introduced 23 reserved words (they were not reserved words in MySQL 5.7):

* CUME_DIST
* DENSE_RANK
* EMPTY
* EXCEPT
* FIRST_VALUE
* GROUPING
* GROUPS
* INTERSECT
* JSON_TABLE
* LAG
* LAST_VALUE
* LATERAL
* LEAD
* NTH_VALUE
* NTILE
* OF
* OVER
* PERCENT_RANK
* RANK
* RECURSIVE
* ROW_NUMBER
* SYSTEM
* WINDOW

So, after the MySQL 5.7 to 8.0 upgrade, you may encounter a rare issue in the following case:

* One of the 23 below keywords is used as identifiers (like the name of a table or a column)
* And you do not follow any of both below good practices

In that case, a query that was OK on MySQL 5.7:

select * from groups;
=> OK

Becomes a syntax error on MySQL 8.0:

select * from groups;
=> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups' at line 1

**Solution**

Use one of the 2 best practices listed below.

More information (among others: what's a "keyword"?): https://dev.mysql.com/doc/refman/8.0/en/keywords.html

Mikaël