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.