MySQL
RESERVED WORDS

Your Database Doesn’t Like Your Data – Reserved Words in MySQL

intro

In this blog, we’re walking you through some of the most important reserved words in MySQL’s infrastructure. These keywords require special treatment – we are going to tell you everything you need to know in this blog.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
MySQL logo MySQL
THE MYSQL DATABASE

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 SELECTSETSHOWADDAND, 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 KeywordApplicable Situations
SELECTSelecting data and performing searching operations.
SETOn one hand, UPDATE queries, on the other, creating columns which identify what was set by the user, etc.
SHOWUsers 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.
SMALLINTSMALLINT is a data type. All data types are reserved keywords at the same time.
SQL, SQLEXCEPTION, SQLSTATE, SQLWARNINGAll words that either define SQL states (exception, warning, etc.), mention them, or mention the query language (SQL) itself are reserved.
SSLThe word SSL (that’s the same as TLS and it refers to encrypting connections over the web) is also reserved.
COLUMNThe word “column” is also of a reserved nature, which some may also find quite surprising.
CUBEThe word is reserved as of MySQL 8.0.1 – the CUBE keyword refers to generating grouped data inside a GROUP BY query.
DATABASE, DATABASESAll 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.

Dbvis download link img
About the author
LukasVileikisPhoto
Lukas Vileikis
Lukas Vileikis is an ethical hacker and a frequent conference speaker. He runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com, frequently speaks at conferences and blogs in multiple places including his blog over at lukasvileikis.com.
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

Counter in MySQL: Counting Rows with Ease

author Lukas Vileikis tags MySQL SQL 8 min 2024-10-03
title

SQL OFFSET: Skipping Rows in a Query

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-09-30
title

The MySQL RENAME COLUMN Statement: How, What, and Why

author Lukas Vileikis tags MySQL SQL 6 min 2024-09-26
title

SQL FETCH: Retrieving Data In Database Cursors

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 8 min 2024-09-24
title

MySQL Binary Logs – Walkthrough

author Lukas Vileikis tags Binary Log MySQL 6 min 2024-09-18
title

MySQL SHOW TABLES Statement: What it is, How It Works, What It Means for You

author Lukas Vileikis tags MySQL 5 min 2024-09-16
title

Distributing Data in a Database: A Guide to Database Sharding

author Lukas Vileikis tags MySQL OPTIMIZATION SQL 7 min 2024-09-09
title

MariaDB Docker: Server Setup Guide

author Lukas Vileikis tags MySQL SQL 5 min 2024-08-29
title

Everything You Need to Know About MySQL Full-Text Search

author Lukas Vileikis tags Full text search MySQL 6 min 2024-08-28
title

MySQL Error #1045 Explained: Everything You Need to Know

author Lukas Vileikis tags MySQL SQL 5 min 2024-08-26

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗