Your Database Doesn’t Like Your Data – Reserved Words in MySQL
By the time you’re reading this blog, chances are that you already know your way around database management systems. Be it non-relational or relational, all DBMS have something in common – all of them come with their own quirks and features, and while some of them may be well known to you as a developer or DBA, others may surprise you.
One of such features are reserved keywords – such keywords are deemed significant enough by MySQL or other database management systems to treat them differently. Such keywords include SELECT, SET, SHOW, ADD, AND, and around a hundred other different words.
How Do Reserved Keywords Look Like?
Take a look at this query:
A Basic Query with Reserved Keywords.
↓
↑ A Basic Query with Reserved Keywords.
How do you think it will execute? The answer is simple – it will not:
An Error Posed by Reserved Keywords.
↓
↑ An Error Posed by Reserved Keywords.
Take a careful look at the above error message – the first keyword that errors out is the ALTER keyword. Even though there are more keywords (spoiler alert – “int” is also one of them), MySQL doesn’t even bother to look deeper. Surprised? Don’t be. As soon as the first reserved keyword is found, MySQL will error out. As simple as that.
If you look closely, you will actually notice that DbVisualizer has the alter keyword underlined too, meaning that it’s trying to tell you that this is something you should not do and you should reconsider this part of the query.
What Are the Reserved Keywords?
Different versions of MySQL have different keywords that are reserved. Many of them are likely not to be encountered by developers, but some DBAs will certainly have difficulties working with them.
That’s not to say that MySQL is reserving a lot of keywords or it doesn’t provide any assistance to solve problems related to them – in fact, it provides both lists of reserved words and reserved words in certain versions of MySQL, but the problem is that most DBAs are not aware of the situations these words are applicable to.
Some of more interesting reserved keywords and the situations they’re applicable to are as follows:
| Reserved Keyword | Applicable Situations |
|---|---|
| SELECT | Selecting data and performing searching operations. |
| SET | On one hand, UPDATE queries, on the other, creating columns which identify what was set by the user, etc. |
| SHOW | Users use the query SHOW TABLES to show the existing tables in their database, but also create columns that indicate whether something has been shown to the user or not. |
| SMALLINT | SMALLINT is a data type. All data types are reserved keywords at the same time. |
| SQL, SQLEXCEPTION, SQLSTATE, SQLWARNING | All words that either define SQL states (exception, warning, etc.), mention them, or mention the query language (SQL) itself are reserved. |
| SSL | The word SSL (that’s the same as TLS and it refers to encrypting connections over the web) is also reserved. |
| COLUMN | The word “column” is also of a reserved nature, which some may also find quite surprising. |
| CUBE | The word is reserved as of MySQL 8.0.1 – the CUBE keyword refers to generating grouped data inside a GROUP BY query. |
| DATABASE, DATABASES | All keywords related to databases are reserved in all versions in MySQL – contrary to a popular belief, though, the keyword DATA is not. These keywords may sometimes be used as column names. |
| DAY_* (HOUR,MINUTE,SECOND,MICROSECOND) | The keyword DAY is not reserved, but the words referring to the time of day (hour, minute, second, microsecond) are. |
How to Deal with Reserved Keywords in MySQL?
Now that you know what some of the reserve keywords are and their use cases within MySQL, you may be interested in how to work with situations that require these keywords to be used.
In most cases, everything’s pretty simple – if you have the feeling that a keyword may be reserved (if it refers to data, databases, or any action related to data within them), escape the keyword with backticks (``). That means that your parts of your queries that involve keywords should look like so:
- `alter` VARCHAR(20) NOT NULL instead of alter VARCHAR(20) NOT NULL.
- `default` VARCHAR(255) NOT NULL DEFAULT ‘No Data Here’ instead of default VARCHAR(255) NOT NULL DEFAULT ‘No Data Here’
You get the point.
If you have a hard time identifying reserved keywords though (everyone does from time to time), have a look through the list of reserved keywords in the documentation, then click CTRL + F to either find a specific keyword, or enter (R) in the search field to filter reserved keywords from other keywords. Also, pay close attention to whether the documentation says that a keyword is reserved in a certain version of MySQL – it’s unlikely that MySQL 5.7.36 would have the same reserved keywords as MySQL 8.0.11.
DbVisualizer and Errors in Databases
Ask any DBA and he will confirm – errors in databases are not only related to reserved keywords. Errors are thrown for a multitude of different reasons, and if you use a CLI instead of using a trusted database client like DbVisualizer, you’re going to face problems sooner or later. The fact of the matter is that good SQL clients do not only support all kinds of database management systems (have a look through a list of DbVisualizer’s supported databases here), but also help you deal with encountered errors in a quick and unsophisticated way.
To give you an example of what we mean, we’ll ask you to launch DbVisualizer and head over to Tools and then click on Properties. You will see a window similar to this one:
The Properties of DbVisualizer. SSH Configurations.
↓
↑ The Properties of DbVisualizer. SSH Configurations.
In this part of the property window DbVisualizer will walk you through the configuration of SSH keys to allow those who want to SSH into their database to do so.
The tool properties can be used to solve a wide variety of different issues as well – for example, should you want to deny the execution of certain queries (queries will be denied execution only in DbVisualizer and not via CLI, phpMyAdmin, or other SQL clients), please head over to Permissions and set up some permissions there:
Setting Up Permissions for DbVisualizer.
↓
↑ Setting Up Permissions for DbVisualizer.
One can also set up variables to make their work using DbVisualizer easier:
Setting Up Variables within DbVisualizer.
↓
↑ Setting Up Variables within DbVisualizer.
Explore the features of DbVisualizer for yourself and we’re certain that you will find that it offers a reliable way to solve the issues encountered by your developers as well. Grab a free trial of DbVisualizer to start solving those today, and we’ll see you in the next one!
FAQs
Where Can I See the List of Reserved Keywords in MySQL?
The full list of reserved keywords can be found here.
Are Reserved Keywords the Same Across All MySQL Versions?
No – each version has different sets of reserved keywords. The list of reserved keywords can be found here, while the list relevant to the newest version of MySQL can always be seen here.
How to Deal with Reserved Keywords in MySQL?
To deal with reserved keywords in MySQL, please enclose reserved keywords with backticks (``).
Can I Evaluate DbVisualizer Before Buying It?
Yes – DbVisualizer does offer a free trial of the software and it can be accessed here.
