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:
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:
If we want to get the student code and phone numbers whose name is Joseph, execute the below statement:
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 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.
If we want to get the student name, subject, and marks whose marks > +80, execute the below statement:
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:
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:
It will give the following outputs where we can see that the format of the date will be changed.
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:
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.
The following example explains it more clearly:
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:
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:
The below example help to understand it clearly:
It represents no data. It is not case-sensitive means we can write null literal in any language.