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:

MySQL Programming Literals

Example

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

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.

MySQL Programming Literals

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

Escape Sequence Character Represented by Sequence
\0 It represents ASCII NULL character.
\b It represents a backspace character.
\n It represents a newline character.
\r It represents carriage return character.
\t It represents tab character.
\\ It represents a backslash (\) character.
\% It represents a % character.
\_ It represents a backslash character.

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.

Number Literals Descriptions
Integer It is represented as a sequence of digits without any fractional parts. If the number preceded by - sign, it is a negative integer. If the number is preceded by + sign, it is a positive integer. If the number does not have any sign, it assumes a positive integer. For example, 55, +55, -55 are all integer numbers.
Decimal It is represented as a sequence of digits with fractional parts. In other words, it contains a whole number plus fractional part, which is separated by dot(.) operator or decimal point. It can be integer and non-integer both. It produces the calculation in exact numeric form. For example, 325.90, 355.6 are decimal numbers.
Floating-Point It is a number that contains a floating decimal point. It means there are no fixed digits before and after the decimal point. It contains two kinds of data types named float and double that produce an approximate value. For example, 2.36E0, 0.005, and -2,328.679 are all floating-point numbers.

Example

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

Output

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

MySQL Programming Literals

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:

Date Format Descriptions
'YYYY-MM-DD' or 'YY-MM-DD' It represents a date in string format with punctuations that can be used as a delimiter between the date parts. For example, '2020-03-31', '2020/03/31', and '2020^03^31' all are same date values.
'YYYYMMDD' or 'YYMMDD' It represents a date in the string format without any punctuations or delimiter between the date parts. For example, '20200422 and '200522' are interpreted as '2007-05-23', but '071342' is illegal and becomes '0000-00-00' date value.
YYYYMMDD or YYMMDD It represents a date in the number format. For example, 20200305 and 200305 are interpreted as '2020-03-05' date value.

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

Time Format Descriptions
'D hh:mm:ss', 'hh:mm:ss', 'hh:mm', 'D hh:mm', 'D hh', or 'ss' It represents a time in string format with punctuations that can be used as a delimiter between the time parts. Here, D for days that have value from 0 to 34. For example, '22 10:11:12', '10:11:12' time value.
'hhmmss' It represents a time in the string format without any punctuations or delimiter between the time parts. For example, '101211' is interpreted as '10:12:11', but '109813' is illegal and becomes '00:00:00' time value.
hhmmss, ss, or mmss, It represents a time in the number format. For example, 101211 is interpreted as '10:12:11' time value.

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

DateTime Format Descriptions
'YYYY-MM-DD hh:mm:ss' or 'YY-MM-DD hh:mm:ss' It represents a date and time in string format with punctuations that can be used as a delimiter between the date and time parts. For example, '2020-05-31 12:30:45', '2020/05/31 12*30*45', and '2020@05@31 12^30^45' all are same values.
'YYYYMMDDhhmmss' or 'YYMMDDhhmmss' It represents a date in the string format without any punctuations or delimiter between the date and time parts. For example, '20070523091528' and '070523091528' are interpreted as '2007-05-23 09:15:28', but '071122129015' is illegal and becomes '0000-00-00 00:00:00' date and time value.
YYYYMMDDhhmmss or YYMMDDhhmmss It represents a date and time in the number format. For example, 20200105142500 and 200105142500 are interpreted as '2020-01-05 14:25:00' date and time value.

Example 1

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

MySQL Programming Literals

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

Output

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

MySQL Programming Literals

Example 2

Output

MySQL Programming Literals

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:

Legal Illegal
x'01BF'
x'01bf'
X'01BF'
X'01bf'
0x01BF
0x01bf
X'0H' (Because H does not a hexadecimal digit)
0X0BAF (Because 0X should be written as 0x)

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:

Output

MySQL Programming Literals

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:

Output

MySQL Programming Literals

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:

Legal Illegal
b'0011'
B'0011'
0b0011
b'3' (3 is not a binary digit)
0B11 (0B should be written as 0b)

Example

The below example help to understand it clearly:

Output

MySQL Programming Literals

Null Values

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


Next TopicMySQL Date & Time




Contact US

Email:[email protected]

MySQL Literals(Constants)
10/30