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.
||–128 to 127
||0 to 255|
||–32,768 to 32,767
||0 to 65,535|
||–8,388,608 to 8,388,607
||0 to 16,777,215|
||–2,147,683,648 to 2,147,483,647
||0 to 4,294,967,295|
||–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:
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:
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:
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:
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:
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.