Chapter 11. Data Types

MySQL 5.0

previous page next page

Chapter 11. Data Types

Table of Contents

11.1. Data Type Overview
11.1.1. Overview of Numeric Types
11.1.2. Overview of Date and Time Types
11.1.3. Overview of String Types
11.1.4. Data Type Default Values
11.2. Numeric Types
11.3. Date and Time Types
11.3.1. The , , and Types
11.3.2. The Type
11.3.3. The Type
11.3.4. Y2K Issues and Date Types
11.4. String Types
11.4.1. The and Types
11.4.2. The and Types
11.4.3. The and Types
11.4.4. The Type
11.4.5. The Type
11.5. Data Type Storage Requirements
11.6. Choosing the Right Type for a Column
11.7. Using Data Types from Other Database Engines

MySQL supports a number of data types in several categories: numeric types, date and time types, and string (character) types. This chapter first gives an overview of these data types, and then provides a more detailed description of the properties of the types in each category, and a summary of the data type storage requirements. The initial overview is intentionally brief. The more detailed descriptions later in the chapter should be consulted for additional information about particular data types, such as the allowable formats in which you can specify values.

MySQL also supports extensions for handing spatial data. Chapter 16, Spatial Extensions, provides information about these data types.

Several of the data type descriptions use these conventions:

  • indicates the maximum display width for integer types. For floating-point and fixed-point types, is the total number of digits. For string types, is the maximum length. The maximum allowable value of depends on the data type.

  • 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 –2.

  • Square brackets (‘’ and ‘’) indicate optional parts of type definitions.

11.1. Data Type Overview

11.1.1. Overview of Numeric Types

A summary of the numeric data types follows. For additional information, see Section 11.2, “Numeric Types”. Storage requirements are given in Section 11.5, “Data Type Storage Requirements”.

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 Section 11.2, “Numeric Types”.

If you specify for a numeric column, MySQL automatically adds the attribute to the column.

is an alias for .

in the definition of an integer column is an alias for .

Warning: When you use subtraction between integer values where one is of type , the result is unsigned unless the SQL mode is enabled. See Section 12.8, “Cast Functions and Operators”.

  • )]

    A bit-field type. indicates the number of bits per value, from 1 to 64. The default is 1 if is omitted.

    This data type was added in MySQL 5.0.3 for , and extended in 5.0.5 to , , and . Before 5.0.3, is a synonym for .

  • )] [UNSIGNED] [ZEROFILL]

    A very small integer. The signed range is to . The unsigned range is to .

  • ,

    These types are synonyms for . A value of zero is considered false. Non-zero values are considered true: 

    mysql> 
+------------------------+
| IF(0, 'true', 'false') |
+------------------------+
| false                  |
+------------------------+

mysql> 
+------------------------+
| IF(1, 'true', 'false') |
+------------------------+
| true                   |
+------------------------+

mysql> 
+------------------------+
| IF(2, 'true', 'false') |
+------------------------+
| true                   |
+------------------------+

    However, the values and are merely aliases for and , respectively, as shown here: 

    mysql> 
+--------------------------------+
| IF(0 = FALSE, 'true', 'false') |
+--------------------------------+
| true                           |
+--------------------------------+

mysql> 
+-------------------------------+
| IF(1 = TRUE, 'true', 'false') |
+-------------------------------+
| true                          |
+-------------------------------+

mysql> 
+-------------------------------+
| IF(2 = TRUE, 'true', 'false') |
+-------------------------------+
| false                         |
+-------------------------------+

mysql> 
+--------------------------------+
| IF(2 = FALSE, 'true', 'false') |
+--------------------------------+
| false                          |
+--------------------------------+

    The last two statements display the results shown because is equal to neither nor .

    We intend to implement full boolean type handling, in accordance with standard SQL, in a future MySQL release.

  • )] [UNSIGNED] [ZEROFILL]

    A small integer. The signed range is to . The unsigned range is to .

  • )] [UNSIGNED] [ZEROFILL]

    A medium-sized integer. The signed range is to . The unsigned range is to .

  • )] [UNSIGNED] [ZEROFILL]

    A normal-size integer. The signed range is to . The unsigned range is to .

  • )] [UNSIGNED] [ZEROFILL]

    This type is a synonym for .

  • )] [UNSIGNED] [ZEROFILL]

    A large integer. The signed range is to . The unsigned range is to .

    Some things you should be aware of with respect to columns:

    • All arithmetic is done using signed or values, so you should not use unsigned big integers larger than (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 value to a .

      MySQL can handle in the following cases:

      • When using integers to store large unsigned values in a column.

      • In ) or ), where refers to a column.

      • When using operators (, , , and so on) where both operands are integers.

    • You can always store an exact integer value in a 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 use 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 .

  • ,)] [UNSIGNED] [ZEROFILL]

    A small (single-precision) floating-point number. Allowable values are to , , and to . These are the theoretical limits, based on the IEEE standard. The actual range might be slightly smaller depending on your hardware or operating system.

    is the total number of decimal digits and is the number of digits following the decimal point. If and are omitted, values are stored to the limits allowed by the hardware. A single-precision floating-point number is accurate to approximately 7 decimal places.

    , if specified, disallows negative values.

    Using might give you some unexpected problems because all calculations in MySQL are done with double precision. See Section A.5.7, “Solving Problems with No Matching Rows”.

  • ,)] [UNSIGNED] [ZEROFILL]

    A normal-size (double-precision) floating-point number. Allowable values are to , , and to . These are the theoretical limits, based on the IEEE standard. The actual range might be slightly smaller depending on your hardware or operating system.

    is the total number of decimal digits and is the number of digits following the decimal point. If and are omitted, values are stored to the limits allowed by the hardware. A double-precision floating-point number is accurate to approximately 15 decimal places.

    , if specified, disallows negative values.

  • ,)] [UNSIGNED] [ZEROFILL], ,)] [UNSIGNED] [ZEROFILL]

    These types are synonyms for . Exception: If the SQL mode is enabled, is a synonym for rather than .

  • ) [UNSIGNED] [ZEROFILL]

    A floating-point number. represents the precision in bits, but MySQL uses this value only to determine whether to use or for the resulting data type. If is from 0 to 24, the data type becomes with no or values. If is from 25 to 53, the data type becomes with no or values. The range of the resulting column is the same as for the single-precision or double-precision data types described earlier in this section.

    ) syntax is provided for ODBC compatibility.

  • [,])] [UNSIGNED] [ZEROFILL]

    For MySQL 5.0.3 and above:

    A packed “exact” fixed-point number. is the total number of decimal digits (the precision) and is the number of digits after the decimal point (the scale). The decimal point and (for negative numbers) the ‘’ sign are not counted in . If is 0, values have no decimal point or fractional part. The maximum number of digits () for is 65 (64 from 5.0.3 to 5.0.5). The maximum number of supported decimals () is 30. If is omitted, the default is 0. If is omitted, the default is 10.

    , if specified, disallows negative values.

    All basic calculations () with columns are done with a precision of 65 digits.

    Before MySQL 5.0.3:

    An unpacked fixed-point number. Behaves like a column; “unpacked” means the number is stored as a string, using one character for each digit of the value. is the total number of digits and is the number of digits after the decimal point. The decimal point and (for negative numbers) the ‘’ sign are not counted in , although space for them is reserved. If is 0, values have no decimal point or fractional part. The maximum range of values is the same as for , but the actual range for a given column may be constrained by the choice of and . If is omitted, the default is 0. If is omitted, the default is 10.

    , if specified, disallows negative values.

    The behavior used by the server for columns in a table depends on the version of MySQL used to create the table. If your server is from MySQL 5.0.3 or higher, but you have columns in tables that were created before 5.0.3, the old behavior still applies to those columns. To convert the tables to the newer format, dump them with mysqldump and reload them.

  • [,])] [UNSIGNED] [ZEROFILL], [,])] [UNSIGNED] [ZEROFILL], [,])] [UNSIGNED] [ZEROFILL]

    These types are synonyms for . The synonym is available for compatibility with other database systems.

11.1.2. Overview of Date and Time Types

A summary of the temporal data types follows. For additional information, see Section 11.3, “Date and Time Types”. Storage requirements are given in Section 11.5, “Data Type Storage Requirements”.

For the and range descriptions, “supported” means that although earlier values might work, there is no guarantee.

The and aggregate functions do not work with temporal values. (They convert the values to numbers, which loses the part after the first non-numeric character.) To work around this problem, you can convert to numeric units, perform the aggregate operation, and convert back to a temporal value. Examples: 

SELECT SEC_TO_TIME(SUM(TIME_TO_SEC())) FROM ;
SELECT FROM_DAYS(SUM(TO_DAYS())) FROM ;

  • A date. The supported range is to . MySQL displays values in format, but allows you to assign values to columns using either strings or numbers.

  • A date and time combination. The supported range is to . MySQL displays values in format, but allows you to assign values to columns using either strings or numbers.

  • A timestamp. The range is to partway through the year .

    A column is useful for recording the date and time of an or operation. By default, the first column in a table is automatically set to the date and time of the most recent operation if you do not assign it a value yourself. You can also set any column to the current date and time by assigning it a value. Variations on automatic initialization and update properties are described in Section 11.3.1.1, “ Properties as of MySQL 4.1”.

    A value is returned as a string in the format with a display width fixed at 19 characters. To obtain the value as a number, you should add to the timestamp column.

    Note: The format that was used prior to MySQL 4.1 is not supported in MySQL 5.0; see MySQL 3.23, 4.0, 4.1 Reference Manual for information regarding the old format.

  • A time. The range is to . MySQL displays values in format, but allows you to assign values to 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 to , and . In two-digit format, the allowable values are to , representing years from 1970 to 2069. MySQL displays values in format, but allows you to assign values to columns using either strings or numbers.

11.1.3. Overview of String Types

A summary of the string data types follows. For additional information, see Section 11.4, “String Types”. Storage requirements are given in Section 11.5, “Data Type Storage Requirements”.

In some cases, MySQL may change a string column to a type different from that given in a or statement. See Section 13.1.5.1, “Silent Column Specification Changes”.

In MySQL 4.1 and up, string data types include some features that you may not have encountered in working with versions of MySQL prior to 4.1:

  • MySQL interprets length specifications in character column definitions in character units. (Before MySQL 4.1, column lengths were interpreted in bytes.) This applies to , , and the types.

  • Column definitions for many string data types can include attributes that specify the character set or collation of the column. These attributes apply to the , , the types, , and data types:

    • The attribute specifies the character set, and the attribute specifies a collation for the the character set. For example: 

      CREATE TABLE t
(
    c1 VARCHAR(20) CHARACTER SET utf8,
    c2 TEXT CHARACTER SET latin1 COLLATE latin1_general_cs
);

      This table definition creates a column named that has a character set of with the default collation for that character set, and a column named that has a character set of and a case-sensitive collation.

      is a synonym for .

    • The attribute is shorthand for .

    • The attribute is shorthand for .

    • The attribute is shorthand for specifying the binary collation of the column character set. In this case, sorting and comparison are based on numeric character values. (Before MySQL 4.1, caused a column to store binary strings and sorting and comparison were based on numeric byte values. This is the same as using character values for single-byte character sets, but not for multi-byte character sets.)

  • Character column sorting and comparison are based on the character set assigned to the column. (Before MySQL 4.1, sorting and comparison were based on the collation of the server character set.) For the , , , , and data types, you can declare a column with a binary collation or the attribute to cause sorting and comparison to use the underlying character code values rather than a lexical ordering.

Chapter 10, Character Set Support, provides additional information about use of character sets in MySQL.

  • ) [CHARACTER SET ] [COLLATE ]

    A fixed-length string that is always right-padded with spaces to the specified length when stored. represents the column length. The range of is 0 to 255 characters.

    Note: Trailing spaces are removed when values are retrieved.

    Before MySQL 5.0.3, a column with a length specification greater than 255 is converted to the smallest type that can hold values of the given length. For example, is converted to , and is converted to . This is a compatibility feature. However, this conversion causes the column to become a variable-length column, and also affects trailing-space removal.

    In MySQL 5.0.3 and later, if you attempt to set the length of a greater than 255, the or statement in which this is done fails with an error: 

    mysql> 
ERROR 1074 (42000): Column length too big for column 'col' (max = 255);
use BLOB or TEXT instead
mysql> 
ERROR 1146 (42S02): Table 'test.c1' doesn't exist

    is shorthand for . (or its equivalent short form, ) is the standard SQL way to define that a column should use some predefined character set. MySQL 4.1 and up uses as this predefined character set. Section 10.3.6, “National Character Set”.

    The data type is an alias for the data type. This is a compatibility feature.

    MySQL allows you to create a column of type . This is useful primarily when you have to be compliant with old applications that depend on the existence of a column but that do not actually use its value. is also quite nice when you need a column that can take only two values: A column that is defined as occupies only one bit and can take only the values and (the empty string).

  • ] [COLLATE ]

    This type is a synonym for .

  • ) [CHARACTER SET ] [COLLATE ]

    A variable-length string. represents the maximum column length. In MySQL 5.0, the range of is 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in MySQL 5.0.3 and later. (The actual maximum length of a in MySQL 5.0 is determined by the maximum row size and the character set you use. The maximum effective length starting with MySQL 5.0.3 is 65,532 bytes.)

    Note: Before 5.0.3, trailing spaces were removed when values were stored, which differs from the standard SQL specification.

    Prior to MySQL 5.0.3, a column with a length specification greater than 255 was converted to the smallest type that could hold values of the given length. For example, was converted to , and was converted to . This was a compatibility feature. However, this conversion affected trailing-space removal.

    is shorthand for .

    values are stored using as many characters as are needed, plus one byte to record the length (two bytes for columns that are declared with a length longer than 255).

  • )

    The type is similar to the type, but stores binary byte strings rather than non-binary character strings.

  • )

    The type is similar to the type, but stores binary byte strings rather than non-binary character strings.

  • A column with a maximum length of 255 (28 – 1) bytes.

  • ] [COLLATE ]

    A column with a maximum length of 255 (28 – 1) characters.

  • )]

    A column with a maximum length of 65,535 (216 – 1) bytes.

    An optional length can be given for this type. If this is done, MySQL creates the column as the smallest type large enough to hold values bytes long.

  • )] [CHARACTER SET ] [COLLATE ]

    A column with a maximum length of 65,535 (216 – 1) characters.

    An optional length can be given for this type. If this is done, MySQL creates the column as the smallest type large enough to hold values characters long.

  • A column with a maximum length of 16,777,215 (224 – 1) bytes.

  • ] [COLLATE ]

    A column with a maximum length of 16,777,215 (224 – 1) characters.

  • A column with a maximum length of 4,294,967,295 or 4GB (232 – 1) bytes. The maximum effective (permitted) length of columns depends on the configured maximum packet size in the client/server protocol and available memory.

  • ] [COLLATE ]

    A column with a maximum length of 4,294,967,295 or 4GB (232 – 1) characters. The maximum effective (permitted) length of columns depends on the configured maximum packet size in the client/server protocol and available memory.

  • ','',...) [CHARACTER SET ] [COLLATE ]

    An enumeration. A string object that can have only one value, chosen from the list of values ', ', , or the special error value. An column can have a maximum of 65,535 distinct values. values are represented internally as integers.

  • ','',...) [CHARACTER SET ] [COLLATE ]

    A set. A string object that can have zero or more values, each of which must be chosen from the list of values ', ', A column can have a maximum of 64 members. values are represented internally as integers.

11.1.4. Data Type Default Values

The clause in a data type specification indicates a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as or . The exception is that you can specify as the default for a column. See Section 11.3.1.1, “ Properties as of MySQL 4.1”.

Prior to MySQL 5.0.2, if a column definition includes no explicit value, MySQL determines the default value as follows:

If the column can take as a value, the column is defined with an explicit clause.

If the column cannot take as the value, MySQL defines the column with an explicit clause, using the implicit default value for the column data type. Implicit defaults are defined as follows:

  • For numeric types other than integer types declared with the attribute, the default is . For an column, the default value is the next value in the sequence.

  • For date and time types other than , the default is the appropriate “zero” value for the type. For the first column in a table, the default value is the current date and time. See Section 11.3, “Date and Time Types”.

  • For string types other than , the default value is the empty string. For , the default is the first enumeration value.

and columns cannot be assigned a default value.

As of MySQL 5.0.2, if a column definition includes no explicit value, MySQL determines the default value as follows:

If the column can take as a value, the column is defined with an explicit clause. This is the same as before 5.0.2.

If the column cannot take as the value, MySQL defines the column with no explicit clause. For data entry, if an or statement includes no value for the column, MySQL handles the column according to the SQL mode in effect at the time:

  • If strict SQL mode is not enabled, MySQL sets the column to the implicit default value for the column data type.

  • If strict mode is enabled, an error occurs for transactional tables and the statement is rolled back. For non-transactional tables, an error occurs, but if this happens for the second or subsequent row of a multiple-row statement, the preceding rows will have been inserted.

Suppose that a table is defined as follows: 

CREATE TABLE t (i INT NOT NULL);

In this case, has no explicit default, so in strict mode each of the following statements produce an error and no row is inserted. When not using strict mode, only the third statement produces an error; the implicit default is inserted for the first two statements, but the third fails because cannot produce a value: 

INSERT INTO t VALUES();
INSERT INTO t VALUES(DEFAULT);
INSERT INTO t VALUES(DEFAULT(i));

See Section 5.2.5, “The Server SQL Mode”.

For a given table, you can use the statement to see which columns have an explicit clause.

previous page start next page