Numeric Data Type

       For storing numeric data, MySQL provides integer data  types, floating-point types that store approximate-value (real) numbers, a fixed-point type that stores exact-value (real) numbers, and a BIT type for bit-field values.  When numeric data type is chosen, consider the following factors:  

•   The range of values the data type represents  

•   The amount of storage space that column values require  

•   The column precision and scale for floating-point and fixed-point 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 Type 

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

Floating-Point Types 

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 (IEEE 754) used by the server host's CPU.  This is a very efficient type  for storage and computation, but values are subject to rounding error. 

Type	Storage Required	Signed Range	Unsigned Range
FLOAT	4 bytes	-3.402823466E+38 to -1.175494351E-38	0 and 1.175494351E-38 to 3.402823466E+38
DOUBLE	8 bytes	-1.7976931348623157E+308 to -2.2250738585072014E-308	0 and 2.2250738585072014E-308 to 1.7976931348623157E+308

Fixed-Point Types 

The fixed-point data type is DECIMAL .  It is used to represent exact-v alue 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 accur acy 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. 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 maximum range is the same as for DOUBLE; the effective range for a given  DECIMAL  column is determined by precision and scale. 

NUMERIC Data Type

The NUMERIC data type in MySQL is a synonym for DECIMAL. (If a column is declared 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

BIT 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.  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 also 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.  Rule of thumb for storage size:  n=8 takes 1 Byte.