16.1 Numeric Data Types

only for RuBoard - do not distribute or recompile

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).

NUMERIC

Synonym for DECIMAL .

REAL

Synonym for DOUBLE .

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).

TINYINT

Syntax

 TINYINT[(  display_size  )] [AUTO_INCREMENT] [UNSIGNED] [ZEROFILL]

Storage

1 byte

Description

A basic whole number with a range of -128 to 127 (0 to 255 unsigned).

only for RuBoard - do not distribute or recompile