Web Directory: mysql docs, manual Column types.php
Search:  
MySQL Reference Manual for version 5.0.0-alpha. - 12 Column Types Go to the first, previous, next, last section, table of contents.


12 Column Types

MySQL supports a number of column types that may be grouped into 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. 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.

In addition, MySQL 4.1 and up supports extensions for handing spatial data. Information about spatial types is provided in section 18 Spatial Extensions in MySQL.

The column types supported by MySQL follow. Several of the type descriptions use these conventions:

M
Indicates the maximum display size. The maximum legal display size is 255.
D
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.

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! See section 13.7 Cast Functions.

12.1 Overview of Numeric Types

TINYINT[(M)] [UNSIGNED] [ZEROFILL]
A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255.
BIT
BOOL
BOOLEAN
These are synonyms for TINYINT(1). The BOOLEAN synonym was added in version 4.1.0 In the future, full boolean type handling will be introduced in accordance with standard SQL.
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 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) and 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 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) [UNSIGNED] [ZEROFILL]
A floating-point number. precision 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(precision) has the same range as the corresponding FLOAT and DOUBLE types, but the display size and number of decimals are undefined. As of 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 might give you some unexpected problems because all calculations in MySQL are done with double precision. See section A.5.6 Solving Problems with No Matching Rows. This syntax is provided for ODBC compatibility.
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. M is the display width and D is the number of decimals. FLOAT without arguments or FLOAT(precision) (where precision is in the range from 0 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. M is the display width and D is the number of decimals. DOUBLE without arguments or FLOAT(precision) (where precision is in the range from 25 to 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 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. 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 Version 3.23, the M argument must be large enough to include the space needed for the sign and the decimal point.
DEC[(M[,D])] [UNSIGNED] [ZEROFILL]
NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL]
FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]
These are synonyms for DECIMAL. The FIXED alias was added in version 4.1.0 for compatibility with other servers.

12.2 Overview of Date and Time Types

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. See section 12.5.1 The DATETIME, DATE, and TIMESTAMP Types.
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. See section 12.5.1 The DATETIME, DATE, and TIMESTAMP Types.
TIMESTAMP[(M)]
A timestamp. The range is '1970-01-01 00:00:00' to sometime in 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. See section 12.5 Date and Time Types. 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 4 bytes each. From version 4.0.12, the --new option can be used to make the server behave as in version 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! See section 12.5.1 The DATETIME, DATE, and TIMESTAMP 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. See section 12.5.2 The TIME Type.
YEAR[(2|4)]
A year in two-digit or four-digit format. The default is four-digit. The allowable values are 1901 to 2155, 0000 in the four-digit year format, and 70 to 69 representing years from 1970 to 2069 in two-digit format. 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 Version 3.22. See section 12.5.3 The YEAR Type.

12.3 Overview of String Types

[NATIONAL] CHAR(M) [BINARY | ASCII | UNICODE]
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 Version 3.23). 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. See section 14.2.5.1 Silent Column Specification Changes. From version 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. Note that this conversion causes the column to become a variable-length column. 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. From version 4.1.0 on, the ASCII attribute can be specified. It assigns the latin1 character set to a CHAR column. From version 4.1.1, 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). See section 12.6.1 The CHAR and VARCHAR Types.
CHAR
This is a synonym for CHAR(1).
[NATIONAL] VARCHAR(M) [BINARY]
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 Version 4.0.2). Note: Trailing spaces are removed when the value is stored, which differs from the standard SQL specification. VARCHAR values are sorted and compared in case-insensitive fashion unless the BINARY keyword is given. See section 14.2.5.1 Silent Column Specification Changes. From version 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. VARCHAR is shorthand for CHARACTER VARYING. See section 12.6.1 The CHAR and VARCHAR Types.
TINYBLOB
TINYTEXT
A BLOB or TEXT column with a maximum length of 255 (2^8 - 1) characters. See section 14.2.5.1 Silent Column Specification Changes. See section 12.6.2 The BLOB and TEXT Types.
BLOB
TEXT
A BLOB or TEXT column with a maximum length of 65535 (2^16 - 1) characters. See section 14.2.5.1 Silent Column Specification Changes. See section 12.6.2 The BLOB and TEXT Types.
MEDIUMBLOB
MEDIUMTEXT
A BLOB or TEXT column with a maximum length of 16777215 (2^24 - 1) characters. See section 14.2.5.1 Silent Column Specification Changes. See section 12.6.2 The BLOB and TEXT Types.
LONGBLOB
LONGTEXT
A BLOB or TEXT column with a maximum length of 4294967295 or 4GB (2^32 - 1) characters. See section 14.2.5.1 Silent Column Specification Changes. Up to MySQL version 3.23, the client/server protocol and MyISAM tables had a limit of 16MB per communication packet / table row. From version 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. See section 12.6.2 The BLOB and TEXT Types.
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 65,535 distinct values. ENUM values are represented internally as integers. See section 12.6.3 The ENUM Type.
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. SET values are represented internally as integers. See section 12.6.4 The SET Type.

12.4 Numeric Types

MySQL supports all of the standard SQL numeric datatypes. These types include the exact numeric datatypes (INTEGER, SMALLINT, DECIMAL, and NUMERIC), as well as the approximate numeric datatypes (FLOAT, REAL, and DOUBLE PRECISION). The keyword INT is a synonym for INTEGER, and the keyword DEC is a synonym for DECIMAL.

As an extension to the SQL standard, MySQL also supports the integer types TINYINT, MEDIUMINT, and BIGINT as listed in the following table.

Type Bytes Minimum Value Maximum Value
TINYINT 1 -128 127
SMALLINT 2 -32768 32767
MEDIUMINT 3 -8388608 8388607
INT 4 -2147483648 2147483647
BIGINT 8 -9223372036854775808 9223372036854775807

Another extension is supported by MySQL for optionally specifying the display width of an integer value in parentheses following the base keyword for the type (for example, INT(4)). This optional display width specification is used to left-pad the display of values having a width less than the width specified for the column. However, the display width does not constrain the range of values that can be stored in the column, or the number of digits that will be displayed for values having a width exceeding that specified for the column.

When used in conjunction with the optional extension attribute ZEROFILL, the default padding of spaces is replaced with zeros. For example, for a column declared as INT(5) ZEROFILL, a value of 4 is retrieved as 00004. Note that if you store larger values than the display width in an integer column, you may experience problems when MySQL generates temporary tables for some complicated joins, as in these cases MySQL trusts that the data did fit into the original column width.

All integer types can have an optional (non-standard) attribute UNSIGNED. Unsigned values can be used when you want to allow only non-negative numbers in a column and you need a little bigger upper numeric range for the column.

As of MySQL 4.0.2, floating-point and fixed-point types also can be UNSIGNED. As with integer types, this attribute prevents negative values from being stored in the column. However, unlike the integer types, the upper range of column values remains the same.

The DECIMAL and NUMERIC types are implemented as the same type by MySQL. They are used to store values for which it is important to preserve exact precision, for example with monetary data. When declaring a column of one of these types the precision and scale can be (and usually is) specified; for example:

salary DECIMAL(5,2)

In this example, 5 is the precision and 2 is the scale. The precision represents the number of significant decimal digits that will be stored for values, and the scale represents the number of digits that will be stored following the decimal point.

MySQL stores DECIMAL and NUMERIC values as strings, rather than as binary floating-point numbers, in order to preserve the decimal precision of those values. One character is used for each digit of the value, the decimal point (if the scale is greater than 0), and the `-' sign (for negative numbers). If the scale is 0, DECIMAL and NUMERIC values contain no decimal point or fractional part.

Standard SQL requires that the salary column be able to store any value with 5 digits and 2 decimals. In this case, therefore, the range of values that can be stored in the salary column is from -999.99 to 999.99. MySQL varies from this in two ways:

  • On the positive end of the range, MySQL actually can store numbers up to 9999.99 in this column. For positive numbers, it uses the byte reserved for the sign to extend the upper end of the range.
  • DECIMAL columns in MySQL before 3.23 are stored differently and cannot represent all the values required by standard SQL. This is because for a type of DECIMAL(M,D), the value of M includes the bytes for the sign and the decimal point. The range of the salary column before MySQL 3.23 would be -9.99 to 99.99.

In standard SQL, the syntax DECIMAL(p) is equivalent to DECIMAL(p,0). Similarly, the syntax DECIMAL is equivalent to DECIMAL(p,0), where the implementation is allowed to decide the value of p. As of MySQL 3.23.6, both of these variant forms of the DECIMAL/NUMERIC datatypes are supported. Before 3.23.6, the precision and scale both must be specified explicitly.

The maximum range of DECIMAL and NUMERIC values is the same as for DOUBLE, but the actual range for a given DECIMAL or NUMERIC column can be constrained by the precision or scale for a given column. When such a column is assigned a value with more digits following the decimal point than are allowed by the specified scale, the value is converted to that scale. (The precise behavior is operating system-specific, but generally the effect is truncation to the allowable number of digits.) When a DECIMAL or NUMERIC column is assigned a value that exceeds the range implied by the specified (or default) precision and scale, MySQL stores the value representing the corresponding end point of that range.

For floating-point column types, MySQL uses four bytes for single-precision values and eight bytes for double-precision values.

The FLOAT type is used to represent approximate numeric datatypes. The SQL standard allows an optional specification of the precision (but not the range of the exponent) in bits following the keyword FLOAT in parentheses. The MySQL implementation also supports this optional precision specification, but the precision value is used only to determine storage size. A precision from 0 to 23 results in four-byte single-precision FLOAT column. A precision from 24 to 53 results in eight-byte double-precision DOUBLE column.

When the keyword FLOAT is used for a column type without a precision specification, MySQL uses four bytes to store the values. MySQL also supports variant syntax with two numbers given in parentheses following the FLOAT keyword. The first number represents the display width and the second number specifies the number of digits to be stored and displayed following the decimal point (as with DECIMAL and NUMERIC). When MySQL is asked to store a number for such a column with more decimal digits following the decimal point than specified for the column, the value is rounded to eliminate the extra digits when the value is stored.

In standard SQL, the REAL and DOUBLE PRECISION types do not accept precision specifications. MySQL supports a variant syntax with two numbers given in parentheses following the type name. The first number represents the display width and the second number specifies the number of digits to be stored and displayed following the decimal point. As an extension to the SQL standard, MySQL recognizes DOUBLE as a synonym for the DOUBLE PRECISION type. In contrast with the standard's requirement that the precision for REAL be smaller than that used for DOUBLE PRECISION, MySQL implements both as 8-byte double-precision floating-point values (when the server SQL mode does not include the REAL_AS_FLOAT option).

For maximum portability, code requiring storage of approximate numeric data values should use FLOAT or DOUBLE PRECISION with no specification of precision or number of decimal points.

When asked to store a value in a numeric column that is outside the column type's allowable range, MySQL clips the value to the appropriate endpoint of the range and stores the resulting value instead.

For example, the range of an INT column is -2147483648 to 2147483647. If you try to insert -9999999999 into an INT column, MySQL clips the value to the lower endpoint of the range and stores -2147483648 instead. Similarly, if you try to insert 9999999999, MySQL clips the value to the upper endpoint of the range and stores 2147483647 instead.

If the INT column is UNSIGNED, the size of the column's range is the same but its endpoints shift up to 0 and 4294967295. If you try to store -9999999999 and 9999999999, the values stored in the column are 0 and 4294967296.

Conversions that occur due to clipping are reported as ``warnings'' for ALTER TABLE, LOAD DATA INFILE, UPDATE, and multiple-row INSERT statements.

12.5 Date and Time Types

The date and time types for representing temporal values are DATETIME, DATE, TIMESTAMP, TIME, and YEAR. Each temporal type has a range of legal values, as well as a ``zero'' value that is used when you specify an illegal value that MySQL cannot represent.

MySQL allows you to store certain ``not strictly legal'' date values, such as '1999-11-31'. The reason for this is that we consider date checking to be the responsibility of the application, not the SQL server. To make date checking faster, MySQL verifies only that the month is in the range from 0 to 12 and that the day is in the range from 0 to 31. These ranges are defined to include zero because MySQL allows you to store dates where the day or month and day are zero in a DATE or DATETIME column. This is extremely useful for applications that need to store a birthdate for which you don't know the exact date. In this case, you simply store the date like '1999-00-00' or '1999-01-00'. Note that if you store dates like these, you should not expect to get correct results for such as DATE_SUB() or DATE_ADD that require complete dates.

Here are some general considerations to keep in mind when working with date and time types:

  • MySQL retrieves values for a given date or time type in a standard output format, but it attempts to interpret a variety of formats for input values that you supply (for example, when you specify a value to be assigned to or compared to a date or time type). Only the formats described in the following sections are supported. It is expected that you will supply legal values, and unpredictable results may occur if you use values in other formats.
  • Dates containing two-digit year values are ambiguous because the century is unknown. MySQL interprets two-digit year values using the following rules:
    • Year values in the range 00-69 are converted to 2000-2069.
    • Year values in the range 70-99 are converted to 1970-1999.
  • Although MySQL tries to interpret values in several formats, dates always must be given in year-month-day order (for example, '98-09-04'), rather than in the month-day-year or day-month-year orders commonly used elsewhere (for example, '09-04-98', '04-09-98').
  • MySQL automatically converts a date or time type value to a number if the value is used in a numeric context, and vice versa.
  • When MySQL encounters a value for a date or time type that is out of range or otherwise illegal for the type (as described at the beginning of this section), it converts the value to the ``zero'' value for that type. The exception is that out-of-range TIME values are clipped to the appropriate endpoint of the TIME range. The following table shows the format of the ``zero'' value for each type:
    Column Type ``Zero'' Value
    DATETIME '0000-00-00 00:00:00'
    DATE '0000-00-00'
    TIMESTAMP 00000000000000
    TIME '00:00:00'
    YEAR 0000
  • The ``zero'' values are special, but you can store or refer to them explicitly using the values shown in the table. You can also do this using the values '0' or 0, which are easier to write.
  • ``Zero'' date or time values used through Connector/ODBC are converted automatically to NULL in Connector/ODBC 2.50.12 and above, because ODBC can't handle such values.

12.5.1 The DATETIME, DATE, and TIMESTAMP Types

The DATETIME, DATE, and TIMESTAMP types are related. This section describes their characteristics, how they are similar, and how they differ.

The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. (``Supported'' means that although earlier values might work, there is no guarantee that they will.)

The DATE type is used when you need only a date value, without a time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

The TIMESTAMP column type has varying properties, depending on the MySQL version and the SQL mode the server is running in. These properties are described later in this section.

You can specify DATETIME, DATE, and TIMESTAMP values using any of a common set of formats:

  • As a string in either 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS' format. A ``relaxed'' syntax is allowed--any punctuation character may be used as the delimiter between date parts or time parts. For example, '98-12-31 11:30:45', '98.12.31 11+30+45', '98/12/31 11*30*45', and '98@12@31 11^30^45' are equivalent.
  • As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A ``relaxed'' syntax is allowed here, too. For example, '98-12-31', '98.12.31', '98/12/31', and '98@12@31' are equivalent.
  • As a string with no delimiters in either 'YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS' format, provided that the string makes sense as a date. For example, '19970523091528' and '970523091528' are interpreted as '1997-05-23 09:15:28', but '971122129015' is illegal (it has a nonsensical minute part) and becomes '0000-00-00 00:00:00'.
  • As a string with no delimiters in either 'YYYYMMDD' or 'YYMMDD' format, provided that the string makes sense as a date. For example, '19970523' and '970523' are interpreted as '1997-05-23', but '971332' is illegal (it has nonsensical month and day parts) and becomes '0000-00-00'.
  • As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format, provided that the number makes sense as a date. For example, 19830905132800 and 830905132800 are interpreted as '1983-09-05 13:28:00'.
  • As a number in either YYYYMMDD or YYMMDD format, provided that the number makes sense as a date. For example, 19830905 and 830905 are interpreted as '1983-09-05'.
  • As the result of a function that returns a value that is acceptable in a DATETIME, DATE, or TIMESTAMP context, such as NOW() or CURRENT_DATE.

Illegal DATETIME, DATE, or TIMESTAMP values are converted to the ``zero'' value of the appropriate type ('0000-00-00 00:00:00', '0000-00-00', or 00000000000000).

For values specified as strings that include date part delimiters, it is not necessary to specify two digits for month or day values that are less than 10. '1979-6-9' is the same as '1979-06-09'. Similarly, for values specified as strings that include time part delimiters, it is not necessary to specify two digits for hour, minute, or second values that are less than 10. '1979-10-30 1:2:3' is the same as '1979-10-30 01:02:03'.

Values specified as numbers should be 6, 8, 12, or 14 digits long. If a number is 8 or 14 digits long, it is assumed to be in YYYYMMDD or YYYYMMDDHHMMSS format and that the year is given by the first 4 digits. If the number is 6 or 12 digits long, it is assumed to be in YYMMDD or YYMMDDHHMMSS format and that the year is given by the first 2 digits. Numbers that are not one of these lengths are interpreted as though padded with leading zeros to the closest length.

Values specified as non-delimited strings are interpreted using their length as given. If the string is 8 or 14 characters long, the year is assumed to be given by the first 4 characters. Otherwise, the year is assumed to be given by the first 2 characters. The string is interpreted from left to right to find year, month, day, hour, minute, and second values, for as many parts as are present in the string. This means you should not use strings that have fewer than 6 characters. For example, if you specify '9903', thinking that will represent March, 1999, you will find that MySQL inserts a ``zero'' date into your table. This is because the year and month values are 99 and 03, but the day part is completely missing, so the value is not a legal date. However, as of MySQL 3.23, you can explicitly specify a value of zero to represent missing month or day parts. For example, you can use '990300' to insert the value '1999-03-00'.

You can to some extent assign values of one date type to an object of a different date type. However, there may be some alteration of the value or loss of information:

  • If you assign a DATE value to a DATETIME or TIMESTAMP object, the time part of the resulting value is set to '00:00:00' because the DATE value contains no time information.
  • If you assign a DATETIME or TIMESTAMP value to a DATE object, the time part of the resulting value is deleted because the DATE type stores no time information.
  • Remember that although DATETIME, DATE, and TIMESTAMP values all can be specified using the same set of formats, the types do not all have the same range of values. For example, TIMESTAMP values cannot be earlier than 1970 or later than 2037. This means that a date such as '1968-01-01', while legal as a DATETIME or DATE value, is not a valid TIMESTAMP value and will be converted to 0 if assigned to such an object.

Be aware of certain pitfalls when specifying date values:

  • The relaxed format allowed for values specified as strings can be deceiving. For example, a value such as '10:11:12' might look like a time value because of the `:' delimiter, but if used in a date context will be interpreted as the year '2010-11-12'. The value '10:45:15' will be converted to '0000-00-00' because '45' is not a legal month.
  • The MySQL server performs only basic checking on the validity of a date: The ranges for year, month, and day are 1000 to 9999, 00 to 12, and 00 to 31. Any date containing parts not within these ranges is subject to conversion to '0000-00-00'. Please note that this still allows you to store invalid dates such as '2002-04-31'. To ensure a date is valid, perform a check in your application.
  • Dates containing two-digit year values are ambiguous because the century is unknown. MySQL interprets two-digit year values using the following rules:
    • Year values in the range 00-69 are converted to 2000-2069.
    • Year values in the range 70-99 are converted to 1970-1999.

12.5.1.1 TIMESTAMP Properties Prior to MySQL 4.1

The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns in a table, only the first one is updated automatically.

Automatic updating of the first TIMESTAMP column in a table occurs under any of the following conditions:

  • You explicitly set the column to NULL.
  • The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement.
  • The column is not specified explicitly in an UPDATE statement and some other column changes value. An UPDATE that sets a column to the value it already has does not cause the TIMESTAMP column to be updated; if you set a column to its current value, MySQL ignores the update for efficiency.

TIMESTAMP columns other than the first can also be set to the current date and time. Just set the column to NULL or to NOW().

You can set any TIMESTAMP column to a value different from the current date and time by setting it explicitly to the desired value. This is true even for the first TIMESTAMP column. You can use this property if, for example, you want a TIMESTAMP to be set to the current date and time when you create a row, but not to be changed whenever the row is updated later:

  • Let MySQL set the column when the row is created. This initializes it to the current date and time.
  • When you perform subsequent updates to other columns in the row, set the TIMESTAMP column explicitly to its current value:
    UPDATE tbl_name
    SET timestamp_col = timestamp_col,
    	other_col1 = new_value1,
    	other_col2 = new_value2, ...
    

Another way to maintain a column that records row-creation time is to use a DATETIME column that you initialize to NOW() when the row is created and leave alone for subsequent updates.

TIMESTAMP values may range from the beginning of 1970 to sometime in the year 2037, with a resolution of one second. Values are displayed as numbers.

The format in which MySQL retrieves and displays TIMESTAMP values depends on the display size, as illustrated by the following table. The ``full'' TIMESTAMP format is 14 digits, but TIMESTAMP columns may be created with shorter display sizes:

Column Type Display Format
TIMESTAMP(14) YYYYMMDDHHMMSS
TIMESTAMP(12) YYMMDDHHMMSS
TIMESTAMP(10) YYMMDDHHMM
TIMESTAMP(8) YYYYMMDD
TIMESTAMP(6) YYMMDD
TIMESTAMP(4) YYMM
TIMESTAMP(2) YY

All TIMESTAMP columns have the same storage size, regardless of display size. The most common display sizes are 6, 8, 12, and 14. You can specify an arbitrary display size at table creation time, but values of 0 or greater than 14 are coerced to 14. Odd-valued sizes in the range from 1 to 13 are coerced to the next higher even number.

TIMESTAMP columns store legal values using the full precision with which the value was specified, regardless of the display size. This has several implications:

  • Always specify year, month, and day, even if your column types are TIMESTAMP(4) or TIMESTAMP(2). Otherwise, the value will not be a legal date and 0 will be stored.
  • If you use ALTER TABLE to widen a narrow TIMESTAMP column, information will be displayed that previously was ``hidden''.
  • Similarly, narrowing a TIMESTAMP column does not cause information to be lost, except in the sense that less information is shown when the values are displayed.
  • Although TIMESTAMP values are stored to full precision, the only function that operates directly on the underlying stored value is UNIX_TIMESTAMP(). Other functions operate on the formatted retrieved value. This means you cannot use functions such as HOUR() or SECOND() unless the relevant part of the TIMESTAMP value is included in the formatted value. For example, the HH part of a TIMESTAMP column is not displayed unless the display size is at least 10, so trying to use HOUR() on shorter TIMESTAMP values produces a meaningless result.

12.5.1.2 TIMESTAMP Properties as of MySQL 4.1

From MySQL 4.1.0 on, TIMESTAMP properties differ from those of earlier MySQL releases:

  • TIMESTAMP columns are displayed in the same format as DATETIME columns.
  • Display widths are not supported in the ways described in the preceding section. In other words, you cannot use TIMESTAMP(2), TIMESTAMP(4), and so on.

In addition, if the MySQL server is running in MAXDB mode, TIMESTAMP is identical with DATETIME. That is, if the server is running in MAXDB mode at the time that a table is created, any TIMESTAMP columns are created as DATETIME columns. As a result, such columns use DATETIME display format, have the same range of values, and no automatic updating occurs.

MySQL can be run in MAXDB mode as of version 4.1.1. To enable this mode, set the server SQL mode to MAXDB at startup using the --sql-mode=MAXDB server option or by setting the global sql_mode variable at runtime:

mysql> SET GLOBAL sql_mode=MAXDB;

A client can cause the server to run in MAXDB mode for its own connection as follows:

mysql> SET SESSION sql_mode=MAXDB;

12.5.2 The TIME Type

MySQL retrieves and displays TIME values in 'HH:MM:SS' format (or 'HHH:MM:SS' format for large hours values). TIME values may range from '-838:59:59' to '838:59:59'. The reason the hours part may be so large is that the TIME type may be used not only to represent a time of day (which must be less than 24 hours), but also elapsed time or a time interval between two events (which may be much greater than 24 hours, or even negative).

You can specify TIME values in a variety of formats:

  • As a string in 'D HH:MM:SS.fraction' format. You can also use one of the following ``relaxed'' syntaxes: 'HH:MM:SS.fraction', 'HH:MM:SS', 'HH:MM', 'D HH:MM:SS', 'D HH:MM', 'D HH', or 'SS'. Here D represents days and can have a value from 0 to 33. Note that MySQL doesn't yet store the fraction part.
  • As a string with no delimiters in 'HHMMSS' format, provided that it makes sense as a time. For example, '101112' is understood as '10:11:12', but '109712' is illegal (it has a nonsensical minute part) and becomes '00:00:00'.
  • As a number in HHMMSS format, provided that it makes sense as a time. For example, 101112 is understood as '10:11:12'. The following alternative formats are also understood: SS, MMSS, HHMMSS, HHMMSS.fraction. Note that MySQL doesn't yet store the fraction part.
  • As the result of a function that returns a value that is acceptable in a TIME context, such as CURRENT_TIME.

For TIME values specified as strings that include a time part delimiter, it is not necessary to specify two digits for hours, minutes, or seconds values that are less than 10. '8:3:2' is the same as '08:03:02'.

Be careful about assigning ``short'' TIME values to a TIME column. Without colons, MySQL interprets values using the assumption that the rightmost digits represent seconds. (MySQL interprets TIME values as elapsed time rather than as time of day.) For example, you might think of '1112' and 1112 as meaning '11:12:00' (12 minutes after 11 o'clock), but MySQL interprets them as '00:11:12' (11 minutes, 12 seconds). Similarly, '12' and 12 are interpreted as '00:00:12'. TIME values with colons, by contrast, are always treated as time of the day. That is '11:12' will mean '11:12:00', not '00:11:12'.

Values that lie outside the TIME range but are otherwise legal are clipped to the closest endpoint of the range. For example, '-850:00:00' and '850:00:00' are converted to '-838:59:59' and '838:59:59'.

Illegal TIME values are converted to '00:00:00'. Note that because '00:00:00' is itself a legal TIME value, there is no way to tell, from a value of '00:00:00' stored in a table, whether the original value was specified as '00:00:00' or whether it was illegal.

12.5.3 The YEAR Type

The YEAR type is a 1-byte type used for representing years.

MySQL retrieves and displays YEAR values in YYYY format. The range is 1901 to 2155.

You can specify YEAR values in a variety of formats:

  • As a four-digit string in the range '1901' to '2155'.
  • As a four-digit number in the range 1901 to 2155.
  • As a two-digit string in the range '00' to '99'. Values in the ranges '00' to '69' and '70' to '99' are converted to YEAR values in the ranges 2000 to 2069 and 1970 to 1999.
  • As a two-digit number in the range 1 to 99. Values in the ranges 1 to 69 and 70 to 99 are converted to YEAR values in the ranges 2001 to 2069 and 1970 to 1999. Note that the range for two-digit numbers is slightly different from the range for two-digit strings, because you cannot specify zero directly as a number and have it be interpreted as 2000. You must specify it as a string '0' or '00' or it will be interpreted as 0000.
  • As the result of a function that returns a value that is acceptable in a YEAR context, such as NOW().

Illegal YEAR values are converted to 0000.

12.5.4 Y2K Issues and Date Types

MySQL itself is year 2000 (Y2K) safe (see section 1.2.5 Year 2000 Compliance), but input values presented to MySQL may not be. Any input containing two-digit year values is ambiguous, because the century is unknown. Such values must be interpreted into four-digit form because MySQL stores years internally using four digits.

For DATETIME, DATE, TIMESTAMP, and YEAR types, MySQL interprets dates with ambiguous year values using the following rules:

  • Year values in the range 00-69 are converted to 2000-2069.
  • Year values in the range 70-99 are converted to 1970-1999.

Remember that these rules provide only reasonable guesses as to what your data mean. If the heuristics used by MySQL don't produce the correct values, you should provide unambiguous input containing four-digit year values.

ORDER BY sorts two-digit YEAR/DATE/DATETIME types properly.

Note also that some functions like MIN() and MAX() will convert a TIMESTAMP/DATE to a number. This means that a timestamp with a two-digit year will not work properly with these functions. The fix in this case is to convert the TIMESTAMP/DATE to four-digit year format or use something like MIN(DATE_ADD(timestamp,INTERVAL 0 DAYS)).

12.6 String Types

The string types are CHAR, VARCHAR, BLOB, TEXT, ENUM, and SET. This section describes how these types work, their storage requirements, and how to use them in your queries.

12.6.1 The CHAR and VARCHAR Types

The CHAR and VARCHAR types are similar, but differ in the way they are stored and retrieved.

The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. (Before MySQL 3.23, the length of CHAR may be from 1 to 255.) When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed.

Values in VARCHAR columns are variable-length strings. You can declare a VARCHAR column to be any length from 0 to 255, just as for CHAR columns. (Before MySQL 4.0.2, the length of VARCHAR may be from 1 to 255.) However, in contrast to CHAR, VARCHAR values are stored using only as many characters as are needed, plus one byte to record the length. Values are not padded; instead, trailing spaces are removed when values are stored. (This space removal differs from the standard SQL specification.) No case conversion takes place during storage or retrieval.

If you assign a value to a CHAR or VARCHAR column that exceeds the column's maximum length, the value is truncated to fit.

If you need a column for which trailing spaces are not removed, consider using a BLOB or TEXT type.

The following table illustrates the differences between the two types of columns by showing the result of storing various string values into CHAR(4) and VARCHAR(4) columns:

Value CHAR(4) Storage Required VARCHAR(4) Storage Required
'' ' ' 4 bytes '' 1 byte
'ab' 'ab ' 4 bytes 'ab' 3 bytes
'abcd' 'abcd' 4 bytes 'abcd' 5 bytes
'abcdefgh' 'abcd' 4 bytes 'abcd' 5 bytes

The values retrieved from the CHAR(4) and VARCHAR(4) columns will be the same in each case, because trailing spaces are removed from CHAR columns upon retrieval.

Values in CHAR and VARCHAR columns are sorted and compared in case-insensitive fashion, unless the BINARY attribute was specified when the table was created. The BINARY attribute means that column values are sorted and compared in case-sensitive fashion using the underlying character code values rather then a lexical ordering. BINARY doesn't affect how the column is stored or retrieved.

From version 4.1.0, column type CHAR BYTE is an alias for CHAR BINARY. This is a compatibility feature.

The BINARY attribute is sticky. This means that if a column marked BINARY is used in an expression, the whole expression is treated as a BINARY value.

MySQL may silently change the type of a CHAR or VARCHAR column at table creation time. See section 14.2.5.1 Silent Column Specification Changes.

12.6.2 The BLOB and TEXT Types

A BLOB is a binary large object that can hold a variable amount of data. The four BLOB types TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB differ only in the maximum length of the values they can hold. See section 12.7 Column Type Storage Requirements.

The four TEXT types TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT correspond to the four BLOB types and have the same maximum lengths and storage requirements. The only difference between BLOB and TEXT types is that sorting and comparison is performed in case-sensitive fashion for BLOB values and case-insensitive fashion for TEXT values. In other words, a TEXT is a case-insensitive BLOB. No case conversion takes place during storage or retrieval.

If you assign a value to a BLOB or TEXT column that exceeds the column type's maximum length, the value is truncated to fit.

In most respects, you can regard a TEXT column as a VARCHAR column that can be as big as you like. Similarly, you can regard a BLOB column as a VARCHAR BINARY column. The differences are:

  • You can have indexes on BLOB and TEXT columns only as of MySQL 3.23.2. Older versions of MySQL did not support indexing these column types.
  • For indexes on BLOB and TEXT columns, you must specify an index prefix length.
  • There is no trailing-space removal for BLOB and TEXT columns when values are stored or retrieved. This differs from CHAR columns (trailing spaces are removed when values are retrieved) and from VARCHAR columns (trailing spaces are removed when values are stored).
  • BLOB and TEXT columns cannot have DEFAULT values.

From version 4.1.0, LONG and LONG VARCHAR map to the MEDIUMTEXT data type. This is a compatibility feature.

Connector/ODBC defines BLOB values as LONGVARBINARY and TEXT values as LONGVARCHAR.

Because BLOB and TEXT values may be extremely long, you may encounter some constraints in using them:

  • If you want to use GROUP BY or ORDER BY on a BLOB or TEXT column, you must convert the column value into a fixed-length object. The standard way to do this is with the SUBSTRING function. For example:
    mysql> SELECT comment FROM tbl_name,SUBSTRING(comment,20) AS substr
        ->                 ORDER BY substr;
    
    If you don't do this, only the first max_sort_length bytes of the column are used when sorting. The default value of max_sort_length is 1024; this value can be changed using the --max_sort_length option when starting the mysqld server. See section 5.2.3 Server System Variables. You can group on an expression involving BLOB or TEXT values by specifying the column position or by using an alias:
    mysql> SELECT id,SUBSTRING(blob_col,1,100)
        -> FROM tbl_name GROUP BY 2;
    mysql> SELECT id,SUBSTRING(blob_col,1,100) AS b
        -> FROM tbl_name GROUP BY b;
    
  • The maximum size of a BLOB or TEXT object is determined by its type, but the largest value you can actually transmit between the client and server is determined by the amount of available memory and the size of the communications buffers. You can change the message buffer size by changing the value of the max_allowed_packet variable, but you must do so for both the server and your client program. For example, mysql and mysqldump each allow you to change the client-side max_allowed_packet value. See section 7.5.2 Tuning Server Parameters, section 8.3 mysql, the Command-line Tool, and section 8.8 The mysqldump Database Backup Program.

Note that each BLOB or TEXT value is represented internally by a separately allocated object. This is in contrast to all other column types, for which storage is allocated once per column when the table is opened.

12.6.3 The ENUM Type

An ENUM is a string object with a value normally chosen from a list of allowed values that are enumerated explicitly in the column specification at table creation time.

The value may also be the empty string ('') or NULL under certain circumstances:

  • If you insert an invalid value into an ENUM (that is, a string not present in the list of allowed values), the empty string is inserted instead as a special error value. This string can be distinguished from a 'normal' empty string by the fact that this string has the numerical value 0. More about this later.
  • If an ENUM column is declared to allow NULL, the NULL value is a legal value for the column, and the default value is NULL. If an ENUM column is declared NOT NULL, its default value is the first element of the list of allowed values.

Each enumeration value has an index:

  • Values from the list of allowable elements in the column specification are numbered beginning with 1.
  • The index value of the empty string error value is 0. This means that you can use the following SELECT statement to find rows into which invalid ENUM values were assigned:
    mysql> SELECT * FROM tbl_name WHERE enum_col=0;
    
  • The index of the NULL value is NULL.

For example, a column specified as ENUM('one', 'two', 'three') can have any of the values shown here. The index of each value is also shown:

Value Index
NULL NULL
'' 0
'one' 1
'two' 2
'three' 3

An enumeration can have a maximum of 65,535 elements.

Starting from MySQL 3.23.51, trailing spaces are automatically deleted from ENUM values when the table is created.

Lettercase is irrelevant when you assign values to an ENUM column. However, values retrieved from the column later are displayed using the lettercase that was used in the column definition.

If you retrieve an ENUM value in a numeric context, the column value's index is returned. For example, you can retrieve numeric values from an ENUM column like this:

mysql> SELECT enum_col+0 FROM tbl_name;

If you store a number into an ENUM column, the number is treated as an index, and the value stored is the enumeration member with that index. (However, this will not work with LOAD DATA, which treats all input as strings.) It's not advisable to define an ENUM column with enumeration values that look like numbers, because this can easily become confusing. For example, the following column has enumeration members with string values of '0', '1', and '2', but numeric index values of 1, 2, and 3:

numbers ENUM('0','1','2')

ENUM values are sorted according to the order in which the enumeration members were listed in the column specification. (In other words, ENUM values are sorted according to their index numbers.) For example, 'a' sorts before 'b' for ENUM('a', 'b'), but 'b' sorts before 'a' for ENUM('b', 'a'). The empty string sorts before non-empty strings, and NULL values sort before all other enumeration values. To prevent unexpected results, specify the ENUM list in alphabetical order. You can also use GROUP BY CAST(col AS VARCHAR) or GROUP BY CONCAT(col) to make sure the column is sorted lexically rather than by index number.

If you want to get all possible values for an ENUM column, you should use SHOW COLUMNS FROM tbl_name LIKE enum_col_name and parse the ENUM definition in the second column of the output.

12.6.4 The SET Type

A SET is a string object that can have zero or more values, each of which must be chosen from a list of allowed values specified when the table is created. SET column values that consist of multiple set members are specified with members separated by commas (`,'). A consequence of this is that SET member values cannot themselves contain commas.

For example, a column specified as SET('one', 'two') NOT NULL can have any of these values:

''
'one'
'two'
'one,two'

A SET can have a maximum of 64 different members.

Starting from 3.23.51 trailing spaces are automatically deleted from SET values when the table is created.

MySQL stores SET values numerically, with the low-order bit of the stored value corresponding to the first set member. If you retrieve a SET value in a numeric context, the value retrieved has bits set corresponding to the set members that make up the column value. For example, you can retrieve numeric values from a SET column like this:

mysql> SELECT set_col+0 FROM tbl_name;

If a number is stored into a SET column, the bits that are set in the binary representation of the number determine the set members in the column value. For a column specified as SET('a','b','c','d'), the the members have the following decimal and binary values:

SET Member Decimal Value Binary Value
'a' 1 0001
'b' 2 0010
'c' 4 0100
'd' 8 1000

If you assign a value of 9 to this column, that is 1001 in binary, so the first and fourth SET value members 'a' and 'd' are selected and the resulting value is 'a,d'.

For a value containing more than one SET element, it does not matter what order the elements are listed in when you insert the value. It also does not matter how many times a given element is listed in the value. When the value is retrieved later, each element in the value will appear once, with elements listed according to the order in which they were specified at table creation time. If a column is specified as SET('a','b','c','d'), then 'a,d', 'd,a', and 'd,a,a,d,d' all will appear as 'a,d' when retrieved.

If you set a SET column to an unsupported value, the value will be ignored.

SET values are sorted numerically. NULL values sort before non-NULL SET values.

Normally, you perform a SELECT on a SET column using the LIKE operator or the FIND_IN_SET() function:

mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%';
mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;

But the following will also work:

mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2';
mysql> SELECT * FROM tbl_name WHERE set_col & 1;

The first of these statements looks for an exact match. The second looks for values containing the first set member.

If you want to get all possible values for a SET column, you should use SHOW COLUMNS FROM tbl_name LIKE set_col_name and parse the SET definition in the second column of the output.

12.7 Column Type Storage Requirements

The storage requirements for each of the column types supported by MySQL are listed by category.

The maximum size of a row in a MyISAM table is 65534 bytes. Each BLOB and TEXT column accounts for only 5-9 bytes toward this size.

If a table includes any variable-length column types, the record format will also be variable-length. Note that when a table is created, MySQL may, under certain conditions, change a column from a variable-length type to a fixed-length type, or vice-versa. See section 14.2.5.1 Silent Column Specification Changes.

12.7.1 Storage Requirements for Numeric Types

Column Type Storage Required
TINYINT 1 byte
SMALLINT 2 bytes
MEDIUMINT 3 bytes
INT 4 bytes
INTEGER 4 bytes
BIGINT 8 bytes
FLOAT(X) 4 if 0 <= X <= 24, 8 if 25 <= X <= 53
FLOAT 4 bytes
DOUBLE 8 bytes
DOUBLE PRECISION 8 bytes
REAL 8 bytes
DECIMAL(M,D), NUMERIC(M,D) M+2 bytes if D > 0, M+1 bytes if D = 0 (D+2, if M < D)

12.7.2 Storage Requirements for Date and Time Types

Column Type Storage Required
DATE 3 bytes
DATETIME 8 bytes
TIMESTAMP 4 bytes
TIME 3 bytes
YEAR 1 byte

12.7.3 Storage Requirements for String Types

Column Type Storage Required
CHAR(M) M bytes, 0 <= M <= 255
VARCHAR(M) L+1 bytes, where L <= M and 0 <= M <= 255
TINYBLOB, TINYTEXT L+1 bytes, where L < 2^8
BLOB, TEXT L+2 bytes, where L < 2^16
MEDIUMBLOB, MEDIUMTEXT L+3 bytes, where L < 2^24
LONGBLOB, LONGTEXT L+4 bytes, where L < 2^32
ENUM('value1','value2',...) 1 or 2 bytes, depending on the number of enumeration values (65,535 values maximum)
SET('value1','value2',...) 1, 2, 3, 4, or 8 bytes, depending on the number of set members (64 members maximum)

VARCHAR and the BLOB and TEXT types are variable-length types, for which the storage requirements depend on the actual length of column values (represented by L in the preceding table), rather than on the type's maximum possible size. For example, a VARCHAR(10) column can hold a string with a maximum length of 10 characters. The actual storage required is the length of the string (L), plus 1 byte to record the length of the string. For the string 'abcd', L is 4 and the storage requirement is 5 bytes.

The BLOB and TEXT types require 1, 2, 3, or 4 bytes to record the length of the column value, depending on the maximum possible length of the type. See section 12.6.2 The BLOB and TEXT Types.

The size of an ENUM object is determined by the number of different enumeration values. One byte is used for enumerations with up to 255 possible values. Two bytes are used for enumerations with up to 65535 values. See section 12.6.3 The ENUM Type.

The size of a SET object is determined by the number of different set members. If the set size is N, the object occupies (N+7)/8 bytes, rounded up to 1, 2, 3, 4, or 8 bytes. A SET can have a maximum of 64 members. See section 12.6.4 The SET Type.

12.8 Choosing the Right Type for a Column

For the most efficient use of storage, try to use the most precise type in all cases. For example, if an integer column will be used for values in the range from 1 to 99999, MEDIUMINT UNSIGNED is the best type. Of the types that represent all the required values, it uses the least amount of storage.

Accurate representation of monetary values is a common problem. In MySQL, you should use the DECIMAL type. This is stored as a string, so no loss of accuracy should occur. If accuracy is not too important, the DOUBLE type may also be good enough.

For high precision, you can always convert to a fixed-point type stored in a BIGINT. This allows you to do all calculations with integers and convert results back to floating-point values only when necessary.

12.9 Using Column Types from Other Database Engines

To make it easier to use code written for SQL implementations from other vendors, MySQL maps column types as shown in the following table. These mappings make it easier to import table definitions from other database engines into MySQL:

Other Vendor Type MySQL Type
BINARY(NUM) CHAR(NUM) BINARY
CHAR VARYING(NUM) VARCHAR(NUM)
FLOAT4 FLOAT
FLOAT8 DOUBLE
INT1 TINYINT
INT2 SMALLINT
INT3 MEDIUMINT
INT4 INT
INT8 BIGINT
LONG VARBINARY MEDIUMBLOB
LONG VARCHAR MEDIUMTEXT
LONG MEDIUMTEXT (MySQL 4.1.0 on)
MIDDLEINT MEDIUMINT
VARBINARY(NUM) VARCHAR(NUM) BINARY

Column type mapping occurs at table creation time. If you create a table with types used by other vendors and then issue a DESCRIBE tbl_name statement, MySQL reports the table structure using the equivalent MySQL types.


Go to the first, previous, next, last section, table of contents.