MySQL 8.0 and keywords

As you know, MySQL uses some keywords and some of them are also reserved.

Let’s have a look how to deal with that:

mysql> create table WRITE (id int auto_increment primary key, varying varchar(10), than int);
 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 'WRITE (id int auto_increment primary key, 
varying varchar(10), than int)' at line 1

OK, it seems WRITE is a keyword I cannot use as table name. I’ve then two choices:

• rename the table to something else like WRITE_TBL
• use back-ticks (`) around the table like `WRITE`

Let’s use the first option:

mysql> create table WRITE_TBL (id int auto_increment primary key, varying varchar(10), than int);
 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 'varying varchar(10), than int)' at line 1

We have a second error, this time MySQL is not happy with “varying“.

Let’s modify it, using the second option:

mysql> create table WRITE_TBL (id int auto_increment primary key, `varying` varchar(10), than int);
 Query OK, 0 rows affected (2.34 sec)

It worked, however, I am sure that  “than” is also a keyword but it’s not reserved !

Of course, it’s not the most convenient to check the manual each time you want to check for keywords. Additionally, more keywords are appearing with new releases. It was the case with MySQL 8.0 were 70 new keywords were added !

That’s why, MySQL provides also an Information_Schema tables with all the keywords.

mysql> select count(*) from information_Schema.keywords; 
 +----------+
 |      679 |
 +----------+
 1 row in set (0.10 sec

And we can check the amount of reserved keywords:

mysql> select count(*) from information_schema.keywords where reserved;
 +----------+
 |      262 |
 +----------+
 1 row in set (0.01 sec

And of course, we can verify for “than“:

mysql> select * from information_schema.keywords where word like 'than';

+------+----------+

| WORD | RESERVED |

+------+----------+

| THAN |        0 |

+------+----------+

1 row in set (0.03 sec)

Indeed, it’s a keyword but not reserved.

In summary, yes there are many keywords in MySQL and almost 40% are reserved. But it’s very easy to verify them using Information_Schemaor using back-ticks (but I don’t recommend you to do so and I encourage you to avoid keywords in your schemas).