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.

In MySQL 5 Certification Study Guide, the BIT data type is described as below:The BIT data type represents bit-field values. BIT column specifications take a width indicating the number of bits per value ...

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

MySQL\'s default Character Set is latin1 that is also known as ISO-8859-1. The latin1 Character Set uses one byte per character. To get the current database character set, you may run the following query i ...

In MySQL 5 Certification Study Guide, integer data types are described as below:For storing numeric data, MySQL provides integer data types. ...

In MySQL 5 Certification Study Guide, Numeric data types are described as below:The floating-point data types include FLOAT and DOUBLE. Each of these types may be used to ...

In MySQL 5 Certification Study Guide, Numeric data types are described as below:The fixed-point data type is DECIMAL. It is used to represent exact-value numbers that have an integer part, a fra ...

Some text from \"MySQL 5 Certification Study Guide\" about binary and non-binary string data types is as below:
Three data types store non-binary strings: CHAR, VARCHAR, and ...

In MySQL 5 Certification Study Guide, the ENUM and SET Data Types are well described as below:
The ENUM and SET string data types are used when the values to be stor ...

The CHAR and VARCHAR data types store non-binary strings (that is, strings of characters that have a character set and collation). These types differ in terms of their maximum allowable length and in how tr ...

The string data types include binary and non-binary string data.The CHAR, VARCHAR, and TEXT data types store non-binary strings (that is, strings of characters that have a character set and collation). ...

You can reformat date values into other display formats using the MySQL DATE_FORMAT() function. Take Example:SELECT DATE_FORMAT(\'1997-10-04 22:23:00\', \'%W %M %Y\'); -> \'Saturday Octo ...

You can reformat time values into other display formats using the TIME_FORMAT() function in MySQL. Take examples:TIME_FORMAT(time_expression, format_string)SELECT TIME_FORMAT( CURRENT_TIME, \'%f\' ...

MySQL provides data types for storing different kinds of temporal information. The following table summarizes the storage requirements and ranges for the date and time data types. In the following descr ...

The DATE data type represents date values in \'YYYY-MM-DD\' format. The supported range of DATE values is \'1000-01-01\' to \'9999-12-31\'. You might be able to ...

In MySQL 5 Certification Study Guide, the TIMESTAMP data type is described in detailed. Some useful text is as below:The TIMESTAMP type, like DATETIME, stores date-and-time values, but has a different range a ...