Home / Database Structure / Data Types / Question No: 99

In MySQL 5 Certification Study Guide, Numeric data types are described as below:

For storing numeric data, MySQL provides integer data types, floating-point types that store approximate-value numbers, a fixed-point type that stores exact-value numbers, and a BIT type for bit-field values.

Precision and scale are terms that apply to floating-point and fixed-point values, which can have both an integer part and a fractional part. Precision is the number of significant digits. Scale is the number of digits to the right of the decimal point.

Integer data types include TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT. Smaller integer types require less storage space,   but are more limited in the range of values they represent. For example, TINYINT column values take only one byte each to store, but the type has a small range (–128 to 127). INT column values require four bytes each, but the type has a much larger range (–2,147,483,648 to 2,147,483,647). The integer data types are summarized in the following table, which indicates the amount of storage per value that each type requires as well as its range. For integer values declared with the UNSIGNED attribute, negative values are not allowed, and the high end of the range shifts upward to approximately double the maximum positive value of the signed range.

Type	Storage Required	Signed Range	Unsigned Range
TINYINT	1 byte	–128 to 127	0 to 255
SMALLINT	2 bytes	–32,768 to 32,767	0 to 65,535
MEDIUMINT	3 bytes	–8,388,608 to 8,388,607	0 to 16,777,215
INT	4 bytes	–2,147,683,648 to 2,147,483,647	0 to 4,294,967,295
BIGINT	8 bytes	–9,223,372,036,854,775,808 to 9,223,372,036,854,775,807	0 to 18,446,744,073, 709,551,615

The floating-point data types include FLOAT and   DOUBLE. Each of these types may be used to represent approximate-value   numbers that have an integer part, a fractional part, or both. FLOAT   and DOUBLE data types represent values in the native binary   floating-point format used by the server host's CPU. This is a very efficient   type for storage and computation, but values are subject to rounding error.

FLOAT represents single-precision floating-point   values that require four bytes each for storage. DOUBLE represents   double-precision floating-point values that require eight bytes each for   storage.

You can specify explicit precision and scale values in the   column definition to indicate the number of significant digits and the number of   decimal places to the right of the decimal point. The following definitions   specify a single-precision column with a precision of 10 digits and scale of 3   decimals, and a double-precision column with a precision of 20 digits and scale   of 7 decimals:

weight FLOAT(10,3)
avg_score DOUBLE(20,7)

If you specify no precision or scale, MySQL represents values   stored in FLOAT and DOUBLE columns to the maximum accuracy   allowed by the hardware of the MySQL server host. The following definitions   include no explicit precision or scale:

float_col FLOAT
double_col DOUBLE    

Floating-point values are stored using mantissa/exponent   representation, which means that the precision is defined by the width of the   mantissa and the scale varies depending on the exponent value. The result of   these factors is that stored values are approximate.

The fixed-point data type is DECIMAL. It is used to   represent exact-value numbers that have an integer part, a fractional part, or   both.

DECIMAL uses a fixed-decimal storage format: All   values in a DECIMAL column have the same number of decimal places and   are stored exactly as given when possible. DECIMAL values are not   processed quite as efficiently as FLOAT or DOUBLE values   (which use the processor's native binary format), but DECIMAL values   are not subject to rounding error, so they are more accurate. In other words,   there is an accuracy versus speed tradeoff in choosing which type to use. For   example, the DECIMAL data type is a popular choice for financial   applications involving currency calculations, because accuracy is most   important.

DECIMAL columns may be declared with a precision and   scale to indicate the number of significant digits and the number of decimal   places to the right of the decimal point. For example, if you want to represent   values such as dollar-and-cents currency figures, you can do so using a   two-digit scale:

cost DECIMAL(10,2)

The precision and scale can be omitted, or just the scale. The   defaults for omitted precision and scale are 10 and 0, respectively, so the   following declarations are equivalent:

total DECIMAL
total DECIMAL(10)
total DECIMAL(10,0)

The amount of storage required for DECIMAL column   values depends on the precision and scale. Approximately four bytes are required   per nine digits on each side of the decimal point.

The NUMERIC data type in MySQL is a synonym for   DECIMAL. (If you declare a column as NUMERIC, MySQL uses   DECIMAL in the definition.) Standard SQL allows for a difference   between the two types, but in MySQL they are the same. In standard SQL, the   precision for NUMERIC must be exactly the number of digits given in the   column definition. The precision for DECIMAL must be at least that many   digits but is allowed to be more. In MySQL, the precision is exactly as given,   for both types.

The BIT data type represents bit-field values.   BIT column specifications take a width indicating the number of bits   per value, from 1 to 64 bits. The following columns store 4 and 20 bits per   value, respectively:

bit_col1 BIT(4)
bit_col2 BIT(20)    

For a BIT(n) column, the range of values is 0   to 2 n – 1, and the storage   requirement is approximately INT((n+7)/8) bytes per value.

BIT columns can be assigned values using numeric   expressions. To write literal bit values in binary format, the literal-value   notation b'val' can be   used, where val indicates a value   consisting of the binary digits 0 and 1. For example, b'1111' equals 15   and b'1000000' equals 64.