MariaDB Reserved Words – Helpful Tutorial

In this MariaDB tutorial, we will look at various MariaDB Reserved Words and also look at several examples. There are lists of the topic that comes under discussion:

• MariaDB Reserved Words
• MariaDB Escape Reserved Words
• MariaDB 10 Reserved Words
• MariaDB Reserved Words Exception

MariaDB Reserved Words

In this section, we will know about reserved words and their examples.

In MariaDB, the implementation of SQL is made up of keywords that are used in performing SQL operations. We have to take care that these keywords should not be used while naming databases, tables, columns, or any other database objects. Thus keywords are considered reserved. This is the list of all of MariaDB’s reserved words from the official website.

In MariaDB, we can’t use reserved words during the creation of a table or any other database objects. Here is one example to use the reserved words by the following query:

RESERVED WORDS EXAMPLE:

CREATE TABLE ADD(
name VARCHAR(20),
Date_value DATE);

In the query, we tried to use the reserved words as ADD in the table_name by using the CREATE TABLE statement. This leads to an error because ADD function is normally used to add two numeric values.

So, reserved words in MariaDB can be utilized only in their specific use case. To understand this, let’s take a look at an example.

The example of the MariaDB reserved word using the IN keyword in the following query:

EXAMPLE:

SELECT * FROM LIFE
WHERE LIFE_EXPECTATION IN ('GOOD','BAD');

As we see in the above query, we have used the IN keyword in the LIFE table with the help of the SELECT statement on the LIFE_EXPECTATION column. So, as the result, it will check whether these values exist in the LIFE_EXPECTATION column or not and bring the result based on that.

Read: MariaDB ENUM – Helpful Guide

MariaDB Escape Reserved Words

In this section, we will learn how to use the escape reserved words and it is explained with an example.

In MariaDB, the Backticks (‘) is used to escape reserved terms in DDL statements. Enclose reserved keywords in double quotations (“) in SQL CREATE TABLE statements and queries on views to escape them.

First, let’s see the EMPLOYEE table by the following query:

CREATE TABLE IF NOT EXISTS music_info(
id INTEGER AUTO_INCREMENT PRIMARY KEY NOT NULL,
`key_1` TEXT UNIQUE NOT NULL,
id_value TEXT NOT NULL);  

In the first query, we have used with CREATE TABLE statement with the IF NOT EXISTS clause to create a table called music_info. In the column called key_1, we have used the escape reserved words with the data type as TEXT.

Read: MariaDB vs Postgres – Detailed Comparison

MariaDB 10 reserved words

In this sub-topic, we will know about the top 10 reserved words and which are explained with the help of an example.

Here are the top 10 reserved words as the keywords in the MariaDB are given below:

Reserved Words	DEFINITION
BETWEEN	It is used as the condition in the DDL statement to get the output in the query.
ASC	It is used to arrange in ascending order and it is used as an ASC keyword after the ORDER BY clause.
ADD	It is used as the function to add two or more values in numeric. And it is also used in the ALTER TABLE statement to add a new column with the data type.
AS	It is called an alias name of the column or function used for the temporary session.
PRIMARY	It is a key constraint used only once in the table and only one column can contain the primary key value as an integer or string with no NULL value or an empty string in it.
OR	It is a condition-based clause used after the WHERE condition and it is used to choose between condition_1 or condition_2.
DESC	It is used to arrange the column or records in descending order. It is used after the ORDER BY clause with the DESC keyword.
ELSEIF	It is used in the function or the CASE statement for the condition if it gets a true or false base and returns the value as numeric or string in the output.
RANDOM	It is a function to bring a random value or string as the result set in the query.

Read: MariaDB If Null + Examples

MariaDB Reserved Words Exception

In this section, we will talk about historic exceptions of the reserved words in the MariaDB and it is explained with the help of an example.

Some keywords in MariaDB are exceptions for historical reasons and are allowed as unquoted identifiers. These are some of them:

Exception Reserved Words	DEFINITION
BIT	It is a bit-field type and it contains values from 1 to 64. The default value is 1 if M is omitted.
DATE	It is a date and displays the date in YYYY-MM-DD format. It contains dates in loose assigned values including strings or numbers.
ENUM	It is an enumeration and it chooses a value from the list of values. The ENUM column still can contain 65,535 distinct values.
TEXT	A text column can contain a maximum value of 65,535 characters. If we want to store the bigger text we use the MEDIUM TEXT data type.
TIME	In MariaDB, we can store time value in HH:MM: SSSS
TIMESTAMP	When a record is inserted or changed, the timestamp field is automatically assigned the current DateTime. The timestamp format is YYYY-MM-DD HH:MM:SS: ffff

In MariaDB, the ENUM datatype chooses the value from the list of values in the parameters of the column. The sample example of the MariaDB Reserved Words Exception as ENUM keywords by the following query:

EXAMPLE:

CREATE TABLE LIFE(
NAME VARCHAR(20),
LIFE_EXPECTATION ENUM('GOOD','BAD','BETTER'));

INSERT INTO LIFE (NAME,LIFE_EXPECTATION)
VALUES('JOHN SMITH','GOOD'),
('MR. ANDRESON','BAD');

SELECT * FROM LIFE;

In the first query, we have created a table called LIFE under which we have used the ENUM data type on the LIFE_EXPECTATION column as GOOD, BAD, and BETTER as the value list. After that, we have inserted new records in the LIFE table and chosen the ENUM value as the value list for each row by using the INSERT INTO statement.

In the last query, we have retrieved all records from the LIFE table by using the SELECT statement.

You may also like to read the following MariaDB tutorials.

• What is MariaDB Sharding
• MariaDB Add Column With Default Value
• How to load files into MariaDB
• MariaDB Foreign Key + Examples
• MariaDB Reset Root Password
• MariaDB Between + Examples
• MariaDB Temporary Table + Examples
• MariaDB Truncate Table + Examples

In this MariaDB tutorial, we have discussed the MariaDB reserved words and also discusses some sample examples related to it. There are lists of the topic that comes under discussion:

• MariaDB reserved words
• MariaDB escape reserved words
• MariaDB 10 reserved words
• MariaDB Reserved Words Exception