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." All types listed here have been present in MySQL at least as far back as MySQL 3.22.0. Some types have undergone changes in behavior since then, as indicated in the type descriptions. 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 shortly. Allowable length. For string types, the maximum allowable length of column values. 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 comparisons are case sensitive for string types. This applies to sorting and indexing as well because those operations are based on comparisons. Synonyms. Any synonyms for the type name. Notes. Any miscellaneous observations about the type. Certain global attributes apply to all or almost all column types. They are listed here rather than in each type description: NULL or NOT NULL can be specified for every type. DEFAULT default_value can be specified for most types. Default values must be constants. For example, you cannot specify DEFAULT NOW() for a DATETIME column. A default value specification is ineffective for TIMESTAMP columns and columns with the AUTO_INCREMENT attribute, and is illegal for BLOB and TEXT columns. (Capabilities for non-constant default values and default values for BLOB and TEXT columns are scheduled for inclusion in MySQL 4.1, but are not yet present as of this writing.) |
