A.3 Column Types

MySQL supports a number of column types, which may be grouped into three categories: numeric types, date and time types, and string (character) types. This section first gives an overview of the types available and summarizes the storage requirements for each column type, and then provides a more detailed description of the properties of the types in each category. The overview is intentionally brief. More detailed descriptions should be consulted for additional information about particular column types, such as the allowable formats in which you can specify values.

The column types supported by MySQL follow. The following code letters are used in the descriptions:

• M: Indicates the maximum display size. The maximum legal display size is 255.

• D: Applies to floating-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.

Note that if you specify ZEROFILL for a 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.

• TINYINT[(M)] [UNSIGNED] [ZEROFILL]: A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255.

• BIT, BOOL: These are synonyms for TINYINT(1).

• SMALLINT[(M)] [UNSIGNED] [ZEROFILL]: A small integer. The signed range is -32768 to 32767. The unsigned range is 0 to 65535.

• MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]: A medium-size integer. The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215.

• INT[(M)] [UNSIGNED] [ZEROFILL]: A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.

• INTEGER[(M)] [UNSIGNED] [ZEROFILL]: This is a synonym for INT. BIGINT[(M)] [UNSIGNED] [ZEROFILL]: A large integer. The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.

• FLOAT(precision) [UNSIGNED] [ZEROFILL]: A floating-point number. precision can be less than or equal to 24 for a single-precision floating-point number and between 25 and 53 for a double-precision floating-point number. These types are like the FLOAT and DOUBLE types described immediately following. FLOAT(X) has the same range as the corresponding FLOAT and DOUBLE types, but the display size and number of decimals are undefined. Note that using FLOAT might give you some unexpected problems because all calculations in MySQL are done with double precision.

• FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]: 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. The M is the display width and D is the number of decimals. FLOAT without arguments or FLOAT(X) where X is less than or equal to 24 stands for a single-precision floating-point number.

• DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]: 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. The M is the display width and D is the number of decimals. DOUBLE without arguments or FLOAT(X) where X is between 25 and 53 stands for a double-precision floating-point number.

• DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL]: These are synonyms for DOUBLE.

• DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]: An unpacked floating-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. The decimal point and, for negative numbers, the - sign, are not counted in M (but space for these is reserved). If D is 0, values will 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.

• DEC[(M[,D])] [UNSIGNED] [ZEROFILL], NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL]: These are synonyms for DECIMAL.

• DATE: 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.

• DATETIME: 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.

• TIMESTAMP[(M)]: A timestamp. The range is '1970-01-01 00:00:00' to sometime in the year 2037.

  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.

  From MySQL 4.1, TIMESTAMP is returned as a string with the format 'YYYY-MM-DD HH:MM:SS'. If you want to have this as a number, you should add +0 to the timestamp column. Different timestamp lengths are not supported. From version 4.0.12, the --new option can be used to make the server behave as in version 4.1.

  A TIMESTAMP column is useful for recording the date and time of an INSERT or UPDATE operation because it is automatically set to the date and time of the most recent operation if you don't give it a value yourself. You can also set it to the current date and time by assigning it a NULL value. The M argument affects only how a TIMESTAMP column is displayed; its values always are stored using 4 bytes each.

• TIME: 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.

• YEAR[(2|4)]: A year in two- or four-digit format (default is four-digit). The allowable values are 1901 to 2155, 0000 in the four-digit year format, and 1970-2069 if you use the two-digit format (70-69). MySQL displays YEAR values in YYYY format, but allows you to assign values to YEAR columns using either strings or numbers.

• [NATIONAL] CHAR(M) [BINARY]: A fixed-length string that is always right-padded with spaces to the specified length when stored. The range of M is 0 to 255 characters. Trailing spaces are removed when the value is retrieved. CHAR values are sorted and compared in case-insensitive fashion according to the default character set unless the BINARY keyword is given. NATIONAL CHAR (or its equivalent short form, NCHAR) is the SQL-99 way to define that a CHAR column should use the default character set. This is the default in MySQL. CHAR is shorthand for CHARACTER. 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) that is not defined as NOT NULL will occupy only 1 bit and can take two values: NULL or '' (the empty string).

• CHAR: This is a synonym for CHAR(1).

• [NATIONAL] VARCHAR(M) [BINARY]: A variable-length string. Note: Trailing spaces are removed when the value is stored (this differs from the SQL-99 specification). The range of M is 0 to 255 characters (1 to 255 prior to MySQL Version 4.0.2). VARCHAR values are sorted and compared in case-insensitive fashion unless the BINARY keyword is given. VARCHAR is a shorthand for CHARACTER VARYING.

• TINYBLOB, TINYTEXT: A BLOB or TEXT column with a maximum length of 255 characters.

• BLOB, TEXT: A BLOB or TEXT column with a maximum length of 65535 characters.

• MEDIUMBLOB, MEDIUMTEXT: A BLOB or TEXT column with a maximum length of 16777215 characters.

• LONGBLOB, LONGTEXT: A BLOB or TEXT column with a maximum length of 4294967295 characters. The maximum allowed length of LONGBLOB or LONGTEXT columns depends on the configured maximum packet size in the client/server protocol and available memory.

• ENUM('value1','value2',…): 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 65535 distinct values.

• SET('value1','value2',…): 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.