 |  |
Chapter 16. MySQL Data Types
MySQL offers a wide variety of data types to support the storage of different kinds of data. This chapter lists the full range of these data types and describes their functionality, syntax, and data storage requirements. For each data type, the syntax shown uses square brackets ([]) to indicate optional parts of the syntax. The following example shows how BIGINT is explained in this chapter:
BIGINT[(display_size)]
This indicates that you can use BIGINT alone or with a display size value. The italics indicate that you do not enter display_size literally, but instead enter your own value. Thus, possible uses of BIGINT include:
BIGINT BIGINT(20)
Like the BIGINT type above, many MySQL data types support the specification of a display size. Unless otherwise specified, this value must be an integer between 1 and 255.
Table 16-1 lists the data types and categorizes them as numeric, string, date, or complex. You can find the full description of each data type later in this chapter.
Table 16-1. MySQL data types
|
Data type |
Classification |
|---|---|
|
BIGINT |
Numeric |
|
BLOB |
String |
|
CHAR |
String |
|
CHARACTER |
String |
|
CHARACTER VARYING |
String |
|
DATE |
Date |
|
DATETIME |
Date |
|
DEC |
Numeric |
|
DECIMAL |
Numeric |
|
DOUBLE |
Numeric |
|
DOUBLE PRECISION |
Numeric |
|
ENUM |
Complex |
|
FLOAT |
Numeric |
|
INT |
Numeric |
|
INTEGER |
Numeric |
|
LONGBLOB |
String |
|
LONGTEXT |
String |
|
MEDIUMBLOB |
String |
|
MEDIUMINT |
Numeric |
|
MEDIUMTEXT |
String |
|
NCHAR |
String |
|
NATIONAL CHAR |
String |
|
NATIONAL CHARACTER |
String |
|
NATIONAL VARCHAR |
String |
|
NUMERIC |
Numeric |
|
REAL |
Numeric |
|
SET |
Complex |
|
SMALLINT |
Numeric |
|
TEXT |
String |
|
TIME |
Date |
|
TIMESTAMP |
Date |
|
TINYBLOB |
String |
|
TINYINT |
Numeric |
|
TINYTEXT |
String |
|
VARCHAR |
String |
|
YEAR |
Date |
In the following cases, MySQL silently changes the column type you specify in your table creation to something else:
- VARCHAR -> CHAR
- When the specified VARCHAR column size is less than four characters, it is converted to CHAR.
- CHAR -> VARCHAR
- When a table has at least one column of a variable length, all CHAR columns greater than three characters in length are converted to VARCHAR.
- TIMESTAMP display sizes
- Display sizes for TIMESTAMP fields must be an even value between 2 and 14. A display size of 0 or greater than 14 will convert the field to a display size of 14. An odd-valued display size will be converted to the next highest even value.
16.1. Numeric Data Types
MySQL supports all ANSI SQL2 numeric data types. MySQL numeric types break down into two groups: integer and floating point. Within each group, the types differ by the amount of storage required for them.
Numeric types allow you to specify a display size, which affects the way MySQL displays results. The display size bears no relation to the internal storage provided by each data type. In addition, the floating types allow you to optionally specify the number of digits that follow the decimal point. In such cases, the digits value should be an integer from 0 to 30 and at most two less than the display size. If you do make the digits value greater than two less than the display size, the display size will automatically change to two more than the digits value. For instance, MySQL automatically changes FLOAT(6,5) to FLOAT(7,5).
When you insert a value into a column that requires more storage than the data type allows, it will be clipped to the minimum (negative values) or maximum (positive values) value for that data type. MySQL will issue a warning when such clipping occurs during ALTER TABLE, LOAD DATA INFILE, UPDATE, and multirow INSERT statements.
The AUTO_INCREMENT attribute may be supplied for at most one column of an integer type in a table. The UNSIGNED attribute may be used with any numeric type. An unsigned column may contain only positive integers or floating-point values. The ZEROFILL attribute indicates that the column should be left padded with zeros when displayed by MySQL. The number of zeros padded is determined by the column's display width.
| BIGINT |
Syntax
BIGINT[(display_size)] [AUTO_INCREMENT] [UNSIGNED] [ZEROFILL]
Storage
8 bytes
Description
Largest integer type, supporting range of whole numbers from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 (0 to 18,446,744,073,709,551,615 unsigned). BIGINT has some issues when you perform arithmetic on unsigned values. MySQL performs all arithmetic using signed BIGINT or DOUBLE values. You should therefore avoid performing any arithmetic operations on unsigned BIGINT values greater than 9,223,372,036,854,775,807. If you do, you may end up with imprecise results.
| DEC |
Synonym for DECIMAL.
| DECIMAL |
Syntax
DECIMAL[(precision, [scale])] [ZEROFILL]
Storage
precision + 2 bytes
Description
Stores floating-point numbers where precision is critical, such as for monetary values. DECIMAL types require you to specify the precision and scale. The precision is the number of significant digits in the value. The scale is the number of those digits that come after the decimal point. For example, a BALANCE column declared as DECIMAL(9, 2) would store numbers with nine significant digits, two of which are to the right of the decimal point. The range for this declaration would be -9,999,999.99 to 9,999,999.99. If you specify a number with more decimal points, it is rounded to fit the proper scale. Values beyond the range of the DECIMAL are clipped to fit within the range.
MySQL actually stores DECIMAL values as strings, not as floating-point numbers. It uses one character for each digit, one character for the decimal points when the scale is greater than 0, and one character for the sign of negative numbers. When the scale is 0, the value contains no fractional part. Prior to MySQL 3.23, the precision actually had to include space for the decimal and sign. This requirement is no longer in place, in accordance with the ANSI specification.
ANSI SQL supports the omission of precision and/or scale where the omission of scale creates a default scale of zero and the omission of precision defaults to an implementation-specific value. In the case of MySQL, the default precision is 10.
| DOUBLE |
Syntax
DOUBLE[(display_size, digits)] [ZEROFILL]
Storage
8 bytes
Description
A double-precision floating-point number. This type stores large floating-point values. DOUBLE columns can store negative values between -1.7976931348623157E+308 and -2.2250738585072014E-308, 0, and positive numbers between 2.2250738585072014E-308 and 1.7976931348623157E+308.
| DOUBLE PRECISION |
Synonym for DOUBLE.
| FLOAT |
Syntax
FLOAT[(display_size, digits)] [ZEROFILL]
Storage
4 bytes
Description
A single-precision floating-point number. This type is used to store small floating-point numbers. FLOAT columns can store negative values between -3.402823466E+38 and -1.175494351E-38, 0, and positive values between 1.175494351E-38 and 3.402823466E+38.
| INT |
Syntax
INT[(display_size)] [AUTO_INCREMENT] [UNSIGNED] [ZEROFILL]
Storage
4 bytes
Description
A basic whole number with a range of -2,147,483,648 to 2,147,483,647 (0 to 4,294,967,295 unsigned).
| INTEGER |
Synonym for INT.
| MEDIUMINT |
Syntax
MEDIUMINT[(display_size)] [AUTO_INCREMENT] [UNSIGNED] [ZEROFILL]
Storage
3 bytes
Description
A basic whole number with a range of -8,388,608 to 8,388,607 (0 to 16,777,215 unsigned).
| SMALLINT |
Syntax
SMALLINT[(display_size)] [AUTO_INCREMENT] [UNSIGNED] [ZEROFILL]
Storage
2 bytes
Description
A basic whole number with a range of -32,768 to 32,767 (0 to 65,535 unsigned).
|  | |
| 15.2. SQL Commands |  | 16.2. String Data Types |
Copyright © 2003 O'Reilly & Associates. All rights reserved.