Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

sql
mysql
datatypes
communitycreator

Numeric data types in SQL

harris910

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
  -- Integer
	DECLARE 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 places
  DECLARE F FLOAT(7,2) DEFAULT 12345.78909; 
  -- total digits 8 including decimals and upto 3 decimal places
  DECLARE G DOUBLE(8,3) DEFAULT 12345.78909;
  -- total digits 7 including decimals and upto 3 decimal places
  DECLARE H DECIMAL(7,3) DEFAULT 123.45;

  -- bool
  DECLARE I BOOL DEFAULT False;

  -- bit
  DECLARE J BIT DEFAULT 0;

  -- miscellaneous
  -- storing float value to int
  -- it will be rounded to integer
  DECLARE 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 Persons 
VALUES (27, 75.6, False);

SELECT * from Persons;

RELATED TAGS

sql
mysql
datatypes
communitycreator
RELATED COURSES

View all Courses

Keep Exploring