GNU Info

(mysql.info)Column types

---

Next: Functions Prev: Language Structure Up: Reference

---

Enter node , (file) or (file)node

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'.

     Some things you should be aware about `BIGINT' columns:

        * As 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 the `BIGINT'
          to a `DOUBLE'.

        * You can always store an exact integer value in a `BIGINT'
          column by storing it as a string, as there is in this case
          there will be no intermediate double representation.

        * `-', `+', 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 when the
          result is larger than `9223372036854775807'.

`FLOAT(precision) [ZEROFILL]'
     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)' has the same range as the corresponding `FLOAT'
     and `DOUBLE' types, but the display size and number of decimals is
     undefined.

     In MySQL Version 3.23, this is a true floating-point value.  In
     earlier MySQL versions, `FLOAT(precision)' always has 2 decimals.

     Note that using `FLOAT' may give you some unexpected problems as
     all calculation in MySQL is done with double precision.  Note: No
     matching rows.

     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, are not
     counted in M (but space for these are 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 `D' is left out it's set to 0. If `M' is left out it's set to
     10.

     Note that in MySQL Version 3.22 the `M' argument had to includes
     the space needed for 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. Note: DATETIME.

`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.
     Note: DATETIME.

`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.

     A `TIMESTAMP' is always stored in 4 bytes.  The `M' argument only
     affects how the `TIMESTAMP' column is displayed.

     Note that `TIMESTAMP(X)' columns where X is 8 or 14 are reported to
     be numbers while other `TIMESTAMP(X)' columns are reported to be
     strings.  This is just to ensure that one can reliably dump and
     restore the table with these types!  Note: DATETIME.

`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.
     Note: TIME.

`YEAR[(2|4)]'
     A year in 2- or 4-digit format (default is 4-digit).  The
     allowable values are `1901' to `2155', `0000' in the 4-digit 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 Version 3.22.). Note: YEAR.

`[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 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
     the 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 the existence of a column but that do
     not actually use 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 `""'. Note: CHAR.

`BIT'
`BOOL'
`CHAR'
     These three are synonyms 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 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'.  Note: CHAR.

`TINYBLOB'
`TINYTEXT'
     A `BLOB' or `TEXT' column with a maximum length of 255 (2^8 - 1)
     characters. Note: Silent column changes. Note: BLOB.

`BLOB'
`TEXT'
     A `BLOB' or `TEXT' column with a maximum length of 65535 (2^16 - 1)
     characters. Note: Silent column changes. Note: BLOB.

`MEDIUMBLOB'
`MEDIUMTEXT'
     A `BLOB' or `TEXT' column with a maximum length of 16777215 (2^24
     - 1) characters. Note: Silent column changes. Note: BLOB.

`LONGBLOB'
`LONGTEXT'
     A `BLOB' or `TEXT' column with a maximum length of 4294967295
     (2^32 - 1) characters. Note: Silent column changes.  Note that
     because the server/client protocol and MyISAM tables has currently
     a limit of 16M per communication packet / table row, you can't yet
     use this the whole range of this type. Note: BLOB.

`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' can have a
     maximum of 65535 distinct values. Note: ENUM.

`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. Note:
     SET.

Numeric types

Numeric types

Date and time types

Date and time types

String types

String types

Choosing types

Choosing the right type for a column

Other-vendor column types

Using column types from other database engines

Storage requirements

Column type storage requirements