Appendix B. Column Type Reference

This appendix describes each column type provided by MySQL. More information on the use of each type is available in Chapter 2, "Working with Data in MySQL." Unless otherwise indicated, the types listed have been present in MySQL at least as far back as MySQL 3.21.0.

Type name specifications are written using the following conventions:

• Square brackets ([]). Optional information.

• M. The maximum display width; unless otherwise specified, M should be an integer from 1 to 255.

• D. The number of digits following the decimal point for types that have a fractional part; D should be an integer from 0 to 30. D should also be no greater than M–2. Otherwise, the value of M is adjusted to be D+2.

In ODBC terminology, M and D correspond to "precision" and "scale."

For each type description, one or more of the following kinds of information are provided:

• Meaning. A short description of the type.

• Allowable attributes. Optional attribute keywords that may be associated with the column 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 D, "SQL Syntax Reference," for the syntax of those statements. The attributes listed in each column type description are in addition to the global attributes listed below.

• Allowable length. The maximum allowable length of column values for string types.

• Range. For numeric or date and time types, the range of values 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 date and time 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 given in the type specification.

• Storage required. The number of bytes required to store values of the type. For some types, this value is fixed. The amount varies for other types depending on the length of the value stored in the column.

• Comparisons. Whether or not comparisons are case sensitive for string types. This applies to sorting and indexing too because those operations are based on comparisons.

• Synonyms. Any synonyms for the type name.

• Notes. Any miscellaneous observations about the type.

• Global attributes. Some attributes apply to all or almost all column types; they 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 for all types except BLOB and TEXT types.