Appendix B. Data Type Reference

This appendix describes the data types provided by MySQL. More information on the use of each type is given in Chapter 3, "Working with Data in MySQL." All types listed here except BIT have been present in MySQL at least as early as MySQL 4.1.0, and most except the spatial types since well before that. Any changes in behavior since 4.1.0 are indicated in the descriptions for individual types.

Type name specifications are written using the following conventions:

Square brackets ([]) in syntax descriptions indicate optional information.
M represents the maximum display width for integer types, the precision for floating-point and fixed-point types (the width of the integer component), the number of bits for BIT, and the maximum length for string types.
D represents the number of digits following the decimal point for types that have a fractional part; this is also known as the scale. D should be less than M. Otherwise, the value of M is adjusted to be D+1.

Each type description includes one or more of the following kinds of information:

Meaning. A short description of the type.
Allowable attributes. Optional attribute keywords that may be associated with the data type in CREATE TABLE or ALTER TABLE statements. Attributes are listed in alphabetical order, but this does not necessarily correspond to the order imposed by the syntax of CREATE TABLE or ALTER TABLE. See Appendix E, "SQL Syntax Reference," for the syntax of those statements. The attributes listed in each data type description are in addition to the global attributes that apply to all or almost all data types. These global attributes are listed here rather than in each type description:
- NULL or NOT NULL may be specified for every type.
- DEFAULT default_value may be specified in all column definitions except for integer columns that have the AUTO_INCREMENT attribute, BLOB and TEXT columns, and spatial columns. With the exception of the TIMESTAMP type, default values must be constants. For example, you cannot specify DEFAULT CURDATE() for a DATE column.
Allowable length. For string types, the maximum allowable length of column values.
Range. For numeric or temporal (date and time) types, the range of values that the type can represent. For integer numeric types, two ranges are given because integer columns can be signed or unsigned, and the ranges are different for each case.
Zero value. For temporal types, the "zero" value that is stored if an illegal value is inserted into the column.
Default value. The default value if no explicit DEFAULT attribute is present in the type specification. This applies only when strict mode is not enabled. If no DEFAULT clause is given in strict mode, the column is defined with a default of NULL if it can take NULL values, and with no default value otherwise. For further information on strict mode, see "Specifying Column Default Values," in Chapter 3.
Storage required. The number of bytes or characters required to store values of the type. For some types, this value is fixed. For other types, the number varies depending on the length of the value stored in the column.
Comparisons. For string types, this value specifies how comparisons are performed. It applies to grouping, sorting, and indexing as well, because those operations are based on comparisons. Binary string types are compared byte by byte using the numeric value of each byte. Non-binary string types are compared character by character based on the character set collating sequence.
Synonyms. Any synonyms for the type name.
Note. Any miscellaneous observations about the type.

Here's a general tip that's useful if you're not sure how your version of MySQL will treat a given column definition. Create a table that contains a column defined the way you're wondering about, and then use SHOW CREATE TABLE or DESCRIBE to see how MySQL reports the definition. For example, if you can't remember the effect of the UNICODE character type attribute or SERIAL shorthand data type, create a table that uses them and then tell MySQL to display the resulting table definition:

mysql> CREATE TABLE t (c CHAR(10) UNICODE, s SERIAL);
mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `c` char(10) character set ucs2 default NULL,
  `s` bigint(20) unsigned NOT NULL auto_increment,
  UNIQUE KEY `s` (`s`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1