Numeric data types in SQL
The numeric data types in SQL are described below.
- Exact numeric data types are used to store exact values and perform accurate calculations. Values are stored as literal representations of a number’s value. For example, INT, TINYINT, SMALLINT, MEDIUMINT, NUMERIC, etc.
- Approximate numeric data types are used to save up storage cost and perform fast calculations. For example, REAL, DOUBLE, etc.
Integer
Type | Description |
TINYINT | If signed, -128 to 127 is the allowable range. If unsigned, 0 to 255 is the allowable range. (1-byte storage) Example: Can be used in a bank database. |
SMALLINT | If signed, -128 to 127 is the allowable range. If unsigned, 0 to 255 is the allowable range. Can be used in a bank database. (1-byte storage) Example: Can be used in a bank database. |
MEDIUMINT | If signed, -8388608 to 8388607 is the allowable range. If unsigned, 0 to 16777215 is the allowable range. (3-byte storage) Example: Can be used in a bank database. |
INT | If signed, -2147483648 to 2147483647 is the allowable range. If unsigned, 0 to 4294967295 is the allowable range. (4-byte storage) Example: Can be used in a bank database. |
BIGINT | If signed, -9223372036854775808 to 9223372036854775807 is the allowable range. If unsigned, 0 to 18446744073709551615 is the allowable range. (8-byte storage) Example: Can be used in a bank database. |
Floating point
Type | Description |
FLOAT(m,d) | m: Total display length (default value = 10) d: Number of decimal places (default value = 2) The floating-point number cannot be unsigned and decimal precision can go to 24 places. (2-byte storage) Example: Can be used in a scientific research database. |
DOUBLE(m,d) or REAL(m,d) | m: Total display length (default value = 16) d: Number of decimal places (default value = 4) The double-precision floating-point number cannot be unsigned and decimal precision can go to 53 places. (8-byte storage) Example: Can be used in a scientific research database. |
DECIMAL(m,d) or NUMERIC(m,d) | m: Total display length d: Number of decimal places The unpacked floating-point number cannot be unsigned and in this, each decimal corresponds to one byte. Example: Can be used in a scientific research database. |
- If a number = 12345.6789, m = 8, and d = 3, then any of the above will output the following: 12345.679.
- If a floating-point value is saved in an integer, then the number is simply rounded off. For example, 12345.78909 will be stored as 12346 and its type will be an integer.
Boolean
Type | Description |
BOOL OR BOOLEAN | It is used to store true or false only. 1: True 0: False |
Bit
Type | Description |
BIT(m) | It is used to store bit values. m: number of bits per value that has a range of 1 to 64 |
Code
The first example shows how we can declare different data types in stored procedures, and the second example shows how we incorporate different data types while creating a table.
DELIMITER &&CREATE PROCEDURE numeric_datatypes()BEGIN-- IntegerDECLARE A TINYINT DEFAULT 122;DECLARE B SMALLINT DEFAULT 31767;DECLARE C MEDIUMINT DEFAULT -8288607;DECLARE D INT DEFAULT -2146483648;DECLARE E BIGINT DEFAULT 9223372036854765807;-- Floating point-- total digits 7 including decimals and upto 2 decimal placesDECLARE F FLOAT(7,2) DEFAULT 12345.78909;-- total digits 8 including decimals and upto 3 decimal placesDECLARE G DOUBLE(8,3) DEFAULT 12345.78909;-- total digits 7 including decimals and upto 3 decimal placesDECLARE H DECIMAL(7,3) DEFAULT 123.45;-- boolDECLARE I BOOL DEFAULT False;-- bitDECLARE J BIT DEFAULT 0;-- miscellaneous-- storing float value to int-- it will be rounded to integerDECLARE K INT DEFAULT 12345.78909;SELECT A;SELECT B;SELECT C;SELECT D;SELECT E;SELECT F;SELECT G;SELECT H;SELECT I;SELECT J;SELECT K;END&&DELIMITER ;call numeric_datatypes()
CREATE TABLE Persons (Age int,Weight float,USCitizen bool);INSERT INTO PersonsVALUES (27, 75.6, False);SELECT * from Persons;