MySQL 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,
then provides a more detailed description of the properties of the
types in each category. 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.
The column types supported by *MySQL* are listed below. 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* will
automatically add the `UNSIGNED' attribute to the column.
`TINYINT[(M)] [UNSIGNED] [ZEROFILL]'
A very small integer. The signed range is `-128' to `127'. The
unsigned range is `0' to `255'.
`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'. Note that 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! Note that `-', `+' and `*' will use `BIGINT'
arithmetic when both arguments are `INTEGER' values! This means
that if you multiply two big integers (or results from functions
that return integers) you may get unexpected results if the result
is larger than `9223372036854775807'.
A floating-point number. Cannot be unsigned. `precision' can be
`<=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
below. `FLOAT(X)' have the same ranges as the corresponding
`FLOAT' and `DOUBLE' types, but the display size and number of
decimals is undefined.
In *MySQL* 3.23, this is a true floating point value. In earlier
*MySQL* versions, `FLOAT(precision)' always has 2 decimals.
This syntax is provided for ODBC compatibility.
`FLOAT[(M,D)] [ZEROFILL]'
A small (single-precision) floating-point number. Cannot be
unsigned. Allowable values are `-3.402823466E+38' to
`-1.175494351E-38', `0' and `1.175494351E-38' to
`3.402823466E+38'. The M is the display width and D is the number
of decimals. `FLOAT' without an argument or with an argument of
<= 24 stands for a single-precision floating point number.
`DOUBLE[(M,D)] [ZEROFILL]'
A normal-size (double-precision) floating-point number. Cannot be
unsigned. Allowable values are `-1.7976931348623157E+308' to
`-2.2250738585072014E-308', `0' and `2.2250738585072014E-308' to
`1.7976931348623157E+308'. The M is the display width and D is
the number of decimals. `DOUBLE' without an argument or
`FLOAT(X)' where 25 <= X <= 53 stands for a double-precision
floating point number.
`DOUBLE PRECISION[(M,D)] [ZEROFILL]'
`REAL[(M,D)] [ZEROFILL]'
These are synonyms for `DOUBLE'.
`DECIMAL[(M[,D])] [ZEROFILL]'
An unpacked floating-point number. Cannot be unsigned. 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 is not
counted in M. 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 `D' is left out it's set to 0. If `M' is left out it's set to
10.
Note that in *MySQL* 3.22 the `M' argument includes the sign and
the decimal point.
`NUMERIC(M,D) [ZEROFILL]'
This is a synonym 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'. *MySQL* displays `TIMESTAMP' values 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. 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. *Note Date and time types::.
`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 2- or 4- digit formats (default is 4-digit). The
allowable values are `1901' to `2155', and `0000' in the 4 year
format and 1970-2069 if you use the 2 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.
(The `YEAR' type is new in *MySQL* 3.22.)
`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 1 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' (short form `NCHAR') is the ANSI SQL way to define
that a CHAR column should use the default CHARACTER set. This is
default in `MySQL'.
`CHAR' is a 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 that a column exists but never
actually uses the value. This is also quite nice when you need a
column that only can take 2 values: A `CHAR(0)', that is not
defined as `NOT NULL', will only occupy one bit and can only take
2 values: `NULL' or `""'.
`[NATIONAL] VARCHAR(M) [BINARY]'
A variable-length string. Note: Trailing spaces are removed when
the value is stored (this differs from the ANSI SQL
specification). The range of `M' is 1 to 255 characters.
`VARCHAR' values are sorted and compared in case-insensitive
fashion unless the `BINARY' keyword is given. *Note Silent column
changes::.
`VARCHAR' is a shorthand for `CHARACTER VARYING'.
`TINYBLOB'
`TINYTEXT'
A `BLOB' or `TEXT' column with a maximum length of 255 (2^8 - 1)
characters. *Note Silent column changes::.
`BLOB'
`TEXT'
A `BLOB' or `TEXT' column with a maximum length of 65535 (2^16 - 1)
characters. *Note Silent column changes::.
`MEDIUMBLOB'
`MEDIUMTEXT'
A `BLOB' or `TEXT' column with a maximum length of 16777215 (2^24
- 1) characters. *Note Silent column changes::.
`LONGBLOB'
`LONGTEXT'
A `BLOB' or `TEXT' column with a maximum length of 4294967295
(2^32 - 1) characters. *Note Silent column changes::.
`ENUM('value1','value2',...)'
An enumeration. A string object that can have only one value,
chosen from the list of values `'value1'', `'value2'', `...', or
`NULL'. An `ENUM' 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' can have a maximum of 64 members.