Literals (Constants)

Literals are the notations or idea to represent/express a non-changing value. In MySQL, literals are similar to the constant. We can use a literal while declaring a variable or executing the queries.

In this section, we are going to describe the different types of literals and how they can be used in MySQL statements.

The following are the types of literal:

1. String Literals
2. Numeric Literals
3. Date and Time Literals
4. Hexadecimal Literals
5. Bit-Value Literals
6. Boolean Literals
7. NULL Values

String Literal

The string in MySQL is a sequence of characters or bytes that are enclosed in single quotes (') or double quotes ("). For example, 'first string' and "second string" both are the same. The string of bytes is called a binary string. Each binary string contains a character set and a collation.

Let us understand it with the help of an example. First, we are going to create a table named "student_info" that contains the following data:

Example

If we want to get the student code and phone numbers whose name is Joseph, execute the below statement:

mysql> SELECT stud_code, phone FROM student_info WHERE stud_name = 'Joseph';
OR,
mysql> SELECT stud_code, phone FROM student_info WHERE stud_name = "Joseph";

Output

It will give the following outputs where we can see that both queries give the same result, either uses single quotes or double-quotes.

A string literals can also be used with special characters escape sequences. These special characters are summarised in a tabular form below:

Numeric Literals

Numeric literals in MySQL are used to specify the two types of literal values: the exact-value (integer and decimal), and the approximate value (floating-point) literals. It can be positive or negative values. The exact-value can have an integer, fraction, or both. An approximate-value is mainly used for scientific notations that contain mantissa and exponent.

Example

If we want to get the student name, subject, and marks whose marks > +80, execute the below statement:

mysql> SELECT stud_name, subject, marks FROM student_info WHERE marks > +80;

Output

It will give the following outputs where we can see all student names whose marks > +80.

Date and Time Literals

Date and Time values in MySQL can be represented either in the quoted strings or numbers format, which depends on the exact value and some factors. For example, MySQL interprets any of this '2020-09-22', '20200922', and 20200922 as a valid date.

The following table explains the format of date values in MySQL:

The following table explains the format of time values in MySQL:

The following table explains the format of datetime and timestamp values in MySQL:

Example 1

Suppose we have a table named "orders" having the following data:

If we want to get the Order_ID, Product_Name, in the different date format, execute the below statement:

mysql> SELECT Order_ID, Product_Name, DATE_FORMAT(Order_Date,'%d--%m--%y') as new_date_formate FROM orders;

Output

It will give the following outputs where we can see that the format of the date will be changed.

Example 2

mysql> SELECT Order_ID, Product_Name, DATE_FORMAT(Order_Date,'%d%m%y 11:30:45') as new_date_formate FROM orders;

Output

Hexadecimal Literals

In the numbering system, hexadecimal can be represented as a whole number whose base is 16.  Hexadecimal literal values can be written in the following term:

• x'val'
• X'val'
• 0xval

Here, val contains the hexadecimal digits in the range of (0..9 and A..F). In 0xval, leading 0x is case sensitive; therefore we cannot write it as 0X'val'. However, in the case of lettercase of the digits, the leading X or 0x does not matter for particular case-sensitive.

The below examples explain the legal and illegal hexadecimal literals:

It makes sure that the notation X'val' or x'val' should contain an even no of digits. Otherwise, we will get a syntax error. This type of error can be avoided by padding the zero digit at the beginning of the string.

Example

The following example explains it more clearly:

mysql> SELECT 0xD6+0;
mysql> SELECT HEX('TheDeveloperBlog');

Output

Boolean Literals

Boolean literals in MySQL always evaluate in 1 or 0 values. Here, 1 represents true, and 0 represents false constants.

Let us understand it with the below example:

mysql> SELECT TRUE, true, FALSE, false;

Output

Bit-Value Literals

Bit-value literals in MySQL can be written as b'val' or 0bval notation. Here, val is a binary value that contains zeros and ones, and the lettercase of any leading value of b does not matter. A leading 0b value is case sensitive, so we cannot write it as 0B.

The below examples explain the legal and illegal bit-value literals:

Example

The below example help to understand it clearly:

mysql> SET @v1 = b'1100011', @v2 = b'1100001'+0, @v3 = CAST(b'1100001' AS UNSIGNED);

Output

Null Values

It represents no data. It is not case-sensitive means we can write null literal in any language.