Table of Contents
MySQL supports a number of column types in several categories: numeric types, date and time types, and string (character) types. This chapter first gives an overview of these column types, and then provides a more detailed description of the properties of the types in each category, and a summary of the column type storage requirements. The overview is intentionally brief. The more detailed descriptions should be consulted for additional information about particular column types, such as the allowable formats in which you can specify values.
MySQL versions 4.1 and up support extensions for handing spatial data. Information about spatial types is provided in Chapter 19, Spatial Extensions in MySQL.
Several of the column type descriptions use these conventions:
Indicates the maximum display width. The maximum legal display width is 255.
Applies to floating-point and fixed-point types and indicates the number of digits following the decimal point. The maximum possible value is 30, but should be no greater than M−2.
Square brackets (‘[’ and ‘]’) indicate parts of type specifiers that are optional.
A summary of the numeric column types follows. For additional information, see the section called “Numeric Types”. Column storage requirements are given in the section called “Column Type Storage Requirements”.
M indicates the maximum display width. The maximum legal display width is 255. Display width is unrelated to the storage size or range of values a type can contain, as described in the section called “Numeric Types”.
If you specify ZEROFILL for a numeric column, MySQL automatically adds the UNSIGNED attribute to the column.
Warning: You should be aware that when you use subtraction between integer values where one is of type UNSIGNED, the result will be unsigned! See the section called “Cast Functions and Operators”.
A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255.
These are synonyms for TINYINT(1). The BOOLEAN synonym was added in MySQL 4.1.0. A value of zero is considered false. Non-zero values are considered true.
In the future, full boolean type handling will be introduced in accordance with standard SQL.
A small integer. The signed range is -32768 to 32767. The unsigned range is 0 to 65535.
A medium-size integer. The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215.
A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.
A large integer. The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615. Some things you should be aware of with respect to BIGINT columns:
All arithmetic is done using signed BIGINT or DOUBLE values, so you shouldn't use unsigned big integers larger than 9223372036854775807 (63 bits) except with bit functions! If you do that, some of the last digits in the result may be wrong because of rounding errors when converting a BIGINT value to a DOUBLE.
MySQL 4.0 can handle BIGINT in the following cases:
When using integers to store big unsigned values in a BIGINT column.
In MIN(col_name) or MAX(col_name), where col_name refers to a BIGINT column.
When using operators (+, -, *, and so on) where both operands are integers.
You can always store an exact integer value in a BIGINT column by storing it using a string. In this case, MySQL performs a string-to-number conversion that involves no intermediate double-precision representation.
The -, +, and * operators will use BIGINT arithmetic when both operands are integer values! This means that if you multiply two big integers (or results from functions that return integers), you may get unexpected results when the result is larger than 9223372036854775807.
A floating-point number. p represents the precision. It can be from 0 to 24 for a single-precision floating-point number and from 25 to 53 for a double-precision floating-point number. These types are like the FLOAT and DOUBLE types described immediately following. FLOAT(p) has the same range as the corresponding FLOAT and DOUBLE types, but the display width and number of decimals are undefined.
As of MySQL 3.23, this is a true floating-point value. In earlier MySQL versions, FLOAT(p) always has two decimals.
This syntax is provided for ODBC compatibility.
Using FLOAT might give you some unexpected problems because all calculations in MySQL are done with double precision. See the section called “Solving Problems with No Matching Rows”.
A small (single-precision) floating-point number. Allowable values are -3.402823466E+38 to -1.175494351E-38, 0, and 1.175494351E-38 to 3.402823466E+38. If UNSIGNED is specified, negative values are disallowed. M is the display width and D is the number of decimals. FLOAT without arguments or FLOAT(p) (where p is in the range from 0 to 24) stands for a single-precision floating-point number.
A normal-size (double-precision) floating-point number. Allowable values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308. If UNSIGNED is specified, negative values are disallowed. M is the display width and D is the number of decimals. DOUBLE without arguments or FLOAT(p) (where p is in the range from 25 to 53) stands for a double-precision floating-point number.
These are synonyms for DOUBLE. Exception: If the server SQL mode includes the REAL_AS_FLOAT option, REAL is a synonym for FLOAT rather than DOUBLE.
An unpacked fixed-point number. Behaves like a CHAR column; “unpacked” means the number is stored as a string, using one character for each digit of the value. M is the total number of digits and D is the number of decimals. The decimal point and (for negative numbers) the ‘-’ sign are not counted in M, although space for them is reserved. If D is 0, values have no decimal point or fractional part. The maximum range of DECIMAL values is the same as for DOUBLE, but the actual range for a given DECIMAL column may be constrained by the choice of M and D. If UNSIGNED is specified, negative values are disallowed.
If D is omitted, the default is 0. If M is omitted, the default is 10.
Prior to MySQL 3.23, the M argument must be large enough to include the space needed for the sign and the decimal point.
These are synonyms for DECIMAL.
The FIXED synonym was added in MySQL 4.1.0 for compatibility with other servers.
A summary of the temporal column types follows. For additional information, see the section called “Date and Time Types”. Column storage requirements are given in the section called “Column Type Storage Requirements”.
A date. The supported range is '1000-01-01' to '9999-12-31'. MySQL displays DATE values in 'YYYY-MM-DD' format, but allows you to assign values to DATE columns using either strings or numbers.
A date and time combination. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. MySQL displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format, but allows you to assign values to DATETIME columns using either strings or numbers.
A timestamp. The range is '1970-01-01 00:00:00' to partway through the year 2037.
A TIMESTAMP column is useful for recording the date and time of an INSERT or UPDATE operation. The first TIMESTAMP column in a table is automatically set to the date and time of the most recent operation if you don't assign it a value yourself. You can also set any TIMESTAMP column to the current date and time by assigning it a NULL value.
From MySQL 4.1 on, TIMESTAMP is returned as a string with the format 'YYYY-MM-DD HH:MM:SS'. If you want to obtain the value as a number, you should add +0 to the timestamp column. Different timestamp display widths are not supported.
In MySQL 4.0 and earlier, TIMESTAMP values are displayed in YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, or YYMMDD format, depending on whether M is 14 (or missing), 12, 8, or 6, but allows you to assign values to TIMESTAMP columns using either strings or numbers. The M argument affects only how a TIMESTAMP column is displayed, not storage. Its values always are stored using four bytes each. From MySQL 4.0.12, the --new option can be used to make the server behave as in MySQL 4.1.
Note that TIMESTAMP(M) columns where M is 8 or 14 are reported to be numbers, whereas other TIMESTAMP(M) columns are reported to be strings. This is just to ensure that you can reliably dump and restore the table with these types.
A time. The range is '-838:59:59' to '838:59:59'. MySQL displays TIME values in 'HH:MM:SS' format, but allows you to assign values to TIME columns using either strings or numbers.
A year in two-digit or four-digit format. The default is four-digit format. In four-digit format, the allowable values are 1901 to 2155, and 0000. In two-digit format, the allowable values are 70 to 69, representing years from 1970 to 2069. MySQL displays YEAR values in YYYY format, but allows you to assign values to YEAR columns using either strings or numbers. The YEAR type is unavailable prior to MySQL 3.22.
A summary of the string column types follows. For additional information, see the section called “String Types”. Column storage requirements are given in the section called “Column Type Storage Requirements”.
In some cases, MySQL may change a string column to a type different from that given in a CREATE TABLE or ALTER TABLE statement. See the section called “Silent Column Specification Changes”.
A change that affects many string column types is that, as of MySQL 4.1, character column definitions can include a CHARACTER SET attribute to specify the character set and, optionally, a collation. This applies to CHAR, VARCHAR, the TEXT types, ENUM, and SET. For example:
CREATE TABLE t
(
c1 CHAR(20) CHARACTER SET utf8,
c2 CHAR(20) CHARACTER SET latin1 COLLATE latin1_bin
);
This table definition creates a column named c1 that has a character set of utf8 with the default collation for that character set, and a column named c2 that has a character set of latin1 and the binary collation for the character set. The binary collation is not case sensitive.
Character column sorting and comparison are are based on the character set assigned to the column. Before MySQL 4.1, sorting and comparison are based on the collation of the server character set. For CHAR and VARCHAR columns, you can declare the column with the BINARY attribute to cause sorting and comparison to be case sensitive using the underlying character code values rather then a lexical ordering.
For more details, see Chapter 11, Character Set Support.
Also as of 4.1, MySQL interprets length specifications in character column definitions in characters. (Earlier versions interpret them in bytes.)
A fixed-length string that is always right-padded with spaces to the specified length when stored. M represents the column length. The range of M is 0 to 255 characters (1 to 255 prior to MySQL 3.23).
Note: Trailing spaces are removed when CHAR values are retrieved.
From MySQL 4.1.0, a CHAR column with a length specification greater than 255 is converted to the smallest TEXT type that can hold values of the given length. For example, CHAR(500) is converted to TEXT, and CHAR(200000) is converted to MEDIUMTEXT. This is a compatibility feature. However, this conversion causes the column to become a variable-length column, and also affects trailing-space removal.
CHAR is shorthand for CHARACTER. NATIONAL CHAR (or its equivalent short form, NCHAR) is the standard SQL way to define that a CHAR column should use the default character set. This is the default in MySQL.
The BINARY attribute causes sorting and comparisons to be case sensitive.
From MySQL 4.1.0 on, the ASCII attribute can be specified. It assigns the latin1 character set to a CHAR column.
From MySQL 4.1.1 on, the UNICODE attribute can be specified. It assigns the ucs2 character set to a CHAR column.
MySQL allows you to create a column of type CHAR(0). This is mainly useful when you have to be compliant with some old applications that depend on the existence of a column but that do not actually use the value. This is also quite nice when you need a column that can take only two values: A CHAR(0) column that is not defined as NOT NULL occupies only one bit and can take only the values NULL and '' (the empty string).
This is a synonym for CHAR(1).
A variable-length string. M represents the maximum column length. The range of M is 0 to 255 characters (1 to 255 prior to MySQL 4.0.2).
Note: Trailing spaces are removed when VARCHAR values are stored, which differs from the standard SQL specification.
From MySQL 4.1.0 on, a VARCHAR column with a length specification greater than 255 is converted to the smallest TEXT type that can hold values of the given length. For example, VARCHAR(500) is converted to TEXT, and VARCHAR(200000) is converted to MEDIUMTEXT. This is a compatibility feature. However, this conversion affects trailing-space removal.
VARCHAR is shorthand for CHARACTER VARYING.
The BINARY attribute causes sorting and comparisons to be case sensitive.
A BLOB or TEXT column with a maximum length of 255 (2^8 − 1) characters.
A BLOB or TEXT column with a maximum length of 65,535 (2^16 −1) characters.
A BLOB or TEXT column with a maximum length of 16,777,215 (2^24 − 1) characters.
A BLOB or TEXT column with a maximum length of 4,294,967,295 or 4GB (2^32 − 1) characters. Up to MySQL 3.23, the client/server protocol and MyISAM tables had a limit of 16MB per communication packet / table row. From MySQL 4.0, the maximum allowed length of LONGBLOB or LONGTEXT columns depends on the configured maximum packet size in the client/server protocol and available memory.
An enumeration. A string object that can have only one value, chosen from the list of values 'value1', 'value2', ..., NULL or the special '' error value. An ENUM column can have a maximum of 65,535 distinct values. ENUM values are represented internally as integers.
A set. A string object that can have zero or more values, each of which must be chosen from the list of values 'value1', 'value2', ... A SET column can have a maximum of 64 members. SET values are represented internally as integers.