MySQL: Literals

This MySQL tutorial explains how to use literals (string, number, date, time, and boolean literals) in MySQL with examples.

Description

In MySQL, a literal is the same as a constant. We'll cover several types of literals - string literals, number literals, date and time literals and boolean literals.

String Literals

String literals are always surrounded by either single quotes (') or double quotes ("). For example:

Example

Explanation

'techonthenet.com'

String literal with single quotes

"techonthenet.com"

String literal with double quotes

'Tech on the Net'

String literal with single quotes

"Tech on the Net"

String literal with double quotes

Number Literals

Number literals can be either positive or negative numbers that are exact or floating point values. If you do not specify a sign, then a positive number is assumed. Here are some examples of valid number literals:

Example

Explanation

25

Integer literal with no sign (positive sign is assumed)

+25

Integer literal with positive sign

-25

Integer literal with negative sign

25e-04

Floating point literal

25.607

Decimal literal

Date and Time Literals

Date and time literals can be expressed as either strings or numbers. Here are some examples of valid date and time literals:

Example

Explanation

'2014-04-13'

Date literal formatted as 'YYYY-MM-DD'

'20140413'

Date literal formatted as 'YYYYMMDD'

20140413

Date literal formatted as YYYYMMDD

'14-04-13'

Date literal formatted as 'YY-MM-DD'

'140413'

Date literal formatted as 'YYMMDD'

140413

Date literal formatted as YYMMDD

'2014-04-13 11:49:36'

Datetime literal formatted as 'YYYY-MM-DD HH:MM:SS'

'20140413114936'

Datetime literal formatted as 'YYYYMMDDHHMMSS'

20140413114936

Datetime literal formatted as YYYYMMDDHHMMSS

'14-04-13 11:49:36'

Datetime literal formatted as 'YY-MM-DD HH:MM:SS'

'140413114936'

Datetime literal formatted as 'YYMMDDHHMMSS'

140413114936

Datetime literal formatted as YYMMDDHHMMSS

'0 11:49:36'

Time literal formatted as 'D HH:MM:SS' where D can be a day value between 0 and 34

'11:49:36'

Time literal formatted as 'HH:MM:SS'

'11:49'

Time literal formatted as 'HH:MM'

'0 11:49'

Time literal formatted as 'D HH:MM' where D can be a day value between 0 and 34

'0 11'

Time literal formatted as 'D HH' where D can be a day value between 0 and 34

'36'

Time literal formatted as 'SS'

114936

Time literal formatted as HHMMSS

4936

Time literal formatted as MMSS

36

Time literal formatted as SS

Boolean Literals

Boolean literals are values that evaluate to either 1 or 0. Here are some examples of valid boolean literals:

Example

Explanation

1

Evaluates to 1

TRUE

Evaluates to 1

true

Evaluates to 1

0

Evaluates to 0

FALSE

Evaluates to 0

false

Evaluates to 0