MySQL Data Types

MySQL knows about several data types that is, general categories in which values can be represented. These include numbers, string values, temporal values such as dates and times, and the NULL value.

Numeric Values

Numbers are values such as 48 or 193.62. MySQL understands numbers specified as integers (with no fractional part) or floating-point values (with a fractional part). Integers can be specified in decimal or hexadecimal format.

An integer consists of a sequence of digits with no decimal point. In numeric contexts, an integer can be specified as a hexadecimal constant and is treated as a 64-bit integer. The syntax for specifying hexadecimal values is given in the next section, "String (Character) Values," because they are treated as strings by default.

A floating-point number consists of a sequence of digits, a decimal point, and another sequence of digits. One sequence of digits or the other can be empty, but not both.

MySQL understands scientific notation. This is indicated by immediately following an integer or floating-point number with 'e' or 'E', a sign character ('+' or '-'), and an integer exponent. 1.34E+12 and 43.27e-1 are numbers in legal scientific notation. The number 1.34E12 is also legal even though it is missing a sign character before the exponent, but only as of MySQL 3.23.26. Prior to that version, a sign character is required.

Hexadecimal numbers cannot be used in scientific notation: The 'e' that begins the exponent part is also a legal hex digit and thus would be ambiguous.

Any number can be preceded by a minus sign ('-') to indicate a negative value.

String (Character) Values

Strings are values, such as 'Madison, Wisconsin', or 'patient shows improvement'. You can use either single or double quotes to surround a string value. The ANSI SQL standard specifies single quotes, so statements written using them are more portable to other database engines.

Several escape sequences are recognized within strings and can be used to indicate special characters, as shown in Table 2.1. Each sequence begins with a backslash character ('\') to signify a temporary escape from the usual rules for character interpretation. Note that a NUL byte is not the same as the NULL value; NUL is a zero-valued byte, whereas NULL is the absence of a value.

Table 2.1. String Escape Sequences

Sequence	Meaning
\0	NUL (ASCII 0)
\'	Single quote
\"	Double quote
\b	Backspace
\n	Newline (linefeed)
\r	Carriage return
\t	Tab
\\	Backslash
\Z	Ctrl-Z (Windows EOF character)

To include either kind of quote character within a string, you can do one of three things:

• Double the quote character if the string is quoted using the same character:

   'I can''t'  "He said, ""I told you so.""" 

• Quote the string with the other quote character; in this case, you do not double the quote characters within the string:

   "I can't"  'He said, "I told you so."' 

• Escape the quote character with a backslash; this works regardless of the quote characters used to quote the string:

   'I can\'t'  "I can\'t" "He said, \"I told you so.\"" 'He said, \"I told you so.\"' 

Hexadecimal constants can be used to specify string values. There are two different syntaxes for such constants. The first consists of '0x' followed by one or more hexadecimal digits ('0' through '9' and 'a' through 'f'). For example, 0x0a is 10 decimal, and 0xffff is 65535 decimal. Non-decimal hex digits can be specified in uppercase or lowercase, but the leading '0x' cannot be given as '0X'. That is, 0x0a and 0x0A are legal, but 0X0a and 0X0A are not. In string context, pairs of hexadecimal digits are interpreted as ASCII codes, converted to characters, and the result is used as a string. In numeric context, a hexadecimal constant is treated as a number. The following statement illustrates both uses:

 mysql> SELECT 0x616263, 0x616263+0;  +----------+------------+ | 0x616263 | 0x616263+0 | +----------+------------+ | abc      |    6382179 | +----------+------------+ 

As of MySQL 4.0, string values can also be specified using the ANSI SQL notation X'val', where val consists of pairs of hexadecimal digits. As with 0x notation, such values are interpreted as strings but can be used as numbers in a numeric context:

 mysql> SELECT X'616263', X'616263'+0;  +-----------+-------------+ | X'616263' | X'616263'+0 | +-----------+-------------+ | abc       |     6382179 | +-----------+-------------+ 

Unlike 0x notation, the leading 'X' is not case sensitive:

 mysql> SELECT X'61', x'61';  +-------+-------+ | X'61' | x'61' | +-------+-------+ | a     | a     | +-------+-------+ 

From MySQL 4.1 and later, string values can be specified to lie within a particular character set. Before that, string values are interpreted using the server's default character set. The "Character Set Support" section later in this chapter discusses issues related to character sets in more detail.

Date and Time (Temporal) Values

Dates and times are values such as '2002-06-17' or '12:30:43'. MySQL also understands combined date/time values, such as '2002-06-17 12:30:43'. Take special note of the fact that MySQL represents dates in year-month-day order. This often surprises newcomers to MySQL, although this format is the ANSI SQL standard (also known as ISO 8601 format). You can display date values any way you want by using the DATE_FORMAT() function, but the default display format lists the year first, and input values must be specified with the year first.

The NULL Value

NULL is something of a "typeless" value. Generally, it's used to mean "no value," "unknown value," "missing value," "out of range," "not applicable," "none of the above," and so on. You can insert NULL values into tables, retrieve them from tables, and test whether a value is NULL. However, you cannot perform arithmetic on NULL values; if you try, the result is NULL.