MySQL Database learning



Home / Database Structure / Data Types / What are numeric data types in MySQL?
Share this job:


What are numeric data types in MySQL?


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.





Data Types related other questions

What is BIT Data Type in MySQL?

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

What are numeric data types in MySQL?

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

What is MySQL default character set?

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

What are integer data types in MySQL?

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

What are float data types in MySQL?

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

What is fixed point and decimal data type in MySQL?

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

What is difference between binary and non-binary string data types?

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

What are ENUM and SET Data Types in MySQL?

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

What is difference between CHAR AND VARCHAR data types?

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

What are string data types in MySQL 5?

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

How to reformat date values in MySQL?

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

How to reformat Time values in MySQL?

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

What are Temporal Data Types in MySQL?

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

What are DATE, TIME, DATETIME and YEAR Data Types in MySQL?

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

What is TIMESTAMP Data Type in MySQL?

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