MySQL in a Nutshell, 2nd Edition

Appendix A. Data Types

When a table is created using the CREATE TABLE statement, every column in a table must be declared as one of the data types supported by MySQL. A column in a table can be added or changed using the ALTER TABLE statement. Data types can be organized in three basic groups: numeric, date and time, and string. This appendix provides a listing of data types along with their limitations.

Numeric Data Types

Standard SQL numeric data types are allowed: accurate numeric data types (i.e., BIGINT, DECIMAL, INTEGER, MEDIUMINT, NUMERIC, SMALLINT, and TINYINT) and approximate numeric data types (i.e., DOUBLE and FLOAT). For all numeric data types, you can use the UNSIGNED and ZEROFILL flags depending on your needs. If UNSIGNED is omitted, SIGNED is assumed. A numeric data type has different allowable ranges based on whether it’s SIGNED or UNSIGNED. The ZEROFILL flag instructs MySQL to pad the unused spaces to the left of a number with zeros. For example, a column with a data type set to INT(10) using ZEROFILL will display the number 5 as 0000000005. If the ZEROFILL flag is used, UNSIGNED is assumed for the column. When subtracting values where one is UNSIGNED, the results will become UNSIGNED.

For several of the numeric data types, you can specify a width for displaying. This number cannot exceed 255. The display width is a factor only when ZEROFILL is used for the column. You may also specify the number of digits allowed for the decimals, including the decimal point.

Approximate ...