Mysql Literals

MySQL: Literals

This 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
'AODBA.com' String literal with single quotes
"AODBA.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