MySQL Data Types

MySQL knows about several data types—that is, general categories in which values can be represented.

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 may be specified in decimal or hexadecimal format.

An integer consists of a sequence of digits. An integer specified in hexadecimal form 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-numeric hex digits may 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.

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 may be empty, but not both.

MySQL understands cscientific 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. On the other hand, 1.34E12 is not legal because the sign character is missing before the exponent. 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.

Numbers may 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.

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, NULL is the absence of a value.

Table 2.1. String Escape Sequences

Sequence	Meaning
	NUL (ASCII 0)
'	Single quote
"	Double quote
	Backspace
	Newline
	Carriage return
	Tab
\	Backslash

To include a 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."'
  

In string contexts, hexadecimal constants may be used to specify string values. The syntax is as described earlier for numeric values, but pairs of hexadecimal digits are interpreted as ASCII codes and converted to characters The result is used as a string. For example, when interpreted as a string, 0x616263 is "abc".

Date and Time (Temporal) Values

Dates and times are values such as "1999-06-17" or "12:30:43". MySQL also understands combined date/time values, such as "1999-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. You can display date values any way you like 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," "none of the above," and so forth. You can insert NULL values into tables, retrieve them from tables, and test whether or not a value is NULL. You can't perform arithmetic on NULL values. (If you try, the result is NULL.)