home | O'Reilly's CD bookshelfs | FreeBSD | Linux | Cisco | Cisco Exam  


Book HomeManaging and Using MySQLSearch this book

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.

DEC

Synonym for DECIMAL.

DOUBLE PRECISION

Synonym for DOUBLE.

INTEGER

Synonym for INT.



Library Navigation Links

Copyright © 2003 O'Reilly & Associates. All rights reserved.