MySQL Numeric Data Type

What Are Numeric Data Types and Why Do We Use Them?

Numeric data types are used to store number-based values in MySQL, including whole numbers and decimal numbers such as 65, 1000, 4.5, and 99.99. When you create a table in MySQL, you define the numeric data type (such as INTEGER, DECIMAL, FLOAT, etc.) for a column so that it can store numeric values of the specified type.

In MySQL, we choose numeric data types when creating a table so that a column is designed to store numeric values rather than other types of data, such as strings. Numeric data types help MySQL understand what type of values a particular column can store and how those values should be processed. They also help MySQL determine how much storage space is required for storing that data.

Syntax:

CREATE TABLE table_name (
    column_name INT,
    column_name2 DECIMAL(precision, scale),
    column_name3 FLOAT
);

Example:

Imagine you want to create a table to keep product information, where product_id stores whole numbers, price stores decimal values and rating stores floating-point numbers. In this case, you use numeric data types.

Query:

CREATE TABLE products (
    product_id INT,              -- whole number (e.g., 101, 102…)
    price DECIMAL(8,2),          -- decimal number (e.g., 499.99, 1200.50…)
    rating FLOAT                 -- floating number (e.g., 4.3, 3.75…)
);

Insert Values into the Table:

INSERT INTO products (product_id, price, rating)
VALUES (101, 499.99, 4.3);

Output:

product_id | price    | rating
-------------------------------
101        | 499.99   | 4.3

Explanation:

• product_id will only take integer values.
• price will take decimal values with up to 8 digits total and 2 digits after the decimal point.
• rating stores fractional or decimal values as floating-point numbers.



How MySQL Internally Processes and Stores Numeric Data Types

When you create a table in MySQL, you specify all the information about the column. In that information, you specify the data type and the type of values it will store. You choose a specific column in the table and define a numeric data type such as INT, DECIMAL, FLOAT, or DOUBLE for that column.

When you insert a value into the column, MySQL checks whether the value is correct or not according to the selected data type. It also checks whether the value is within the range and determines how much storage space is needed to store that value.

For example, if a column is defined as INT and you insert the value 24, MySQL understands it is a whole number. It checks whether the value is valid for the INT data type, converts it to an internal integer format, and stores it in the database storage system.




Classification of Numeric Data Types

MySQL divides numeric data types into the following categories:

1. Integer Types (Whole Numbers)

   This data type stores numbers that do not have decimal values. It is called the Integer data type.

   For example:

   INT → 25
   

   Where,

   • INT = Data Type
   • 25 = Integer Value

2. Fixed-Point Types (Exact Decimal Numbers):

   This data type stores numbers that have a fixed number of decimal places. It is called the Fixed-Point data type.

   For example:

   DECIMAL → 99.99
   

   Where,

   • DECIMAL = Data Type
   • 99.99 = Decimal Value

3. Floating-Point Types (Decimal Numbers)

   This data type stores numbers with decimal values. It is used when a large range of values is required. The values stored in this data type are approximate to the original values.

   For example:

   FLOAT → 3.14159
   

   Where,

   • FLOAT = Data Type
   • 3.14159 = Floating-Point Value

4. Signed and Unsigned Numeric Types

   MySQL has two types of numeric data types: Signed and Unsigned.

   • Signed numeric data types can store both positive and negative values.

   For example:

   INT → -100
   

   Where,

   INT = Signed Data Typ
   -100 = Negative Integer Value

   • Unsigned numeric data types store only positive values and do not store negative values.

   For example:

   INT UNSIGNED → 100
   

   Where,

   INT UNSIGNED = Unsigned Data Type
   100 = Positive Integer Value

   
   

   Common Mistakes to Avoid When Choosing Numeric Data Types

   You should avoid mistakes when choosing numeric data types; keep the following points in mind:

   1. You should avoid using the INT data type to store decimal values.
   2. When you store data such as money, salary, or banking data, you should not use the FLOAT or DOUBLEdata types because they store approximate values.
   3. You should carefully check your data type. If your data values may become larger in the future, you should choose an appropriate data type that can support a larger range of values.
   4. You should always make sure that the value you want to store falls within the allowed range of the selected data type.
   5. You should not choose a larger data type if your data values are small because it can waste storage space.
   6. If your data values are positive and a table column stores only positive numbers, you should use the UNSIGNED attribute.
   7. You can choose FLOAT and DOUBLE when your decimal values are greater or approximate, such as FLOAT/DOUBLE = 2.345677, 2.344489652334.
   
   

   How Do You Choose the Right Numeric Data Type in MySQL?

   The following points cover how to choose the right numeric data type if you create a table in MySQL:

   1. Check the Value Type:

   You should choose the data type according to the type of value you need to store, such as whole numbers or decimal numbers. Whole numbers are stored in integer data types, and decimal numbers are stored in decimal data types.

   Example:

   INT → age → 25
   DECIMAL → price → 99.99
   

   Here,

   The variable age uses the INT data type because it stores a whole number value.

   The variable price uses the DECIMAL data type because it stores a decimal value.

   2. Check the Value Range:

   You should choose a data type that can store the minimum and maximum values your column may contain.

   [Signed and Unsigned Integer Data Types :Storage Size and Range]

   Data Type	Storage Size	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,483,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

   
   

   3. Check the Accuracy Requirement:

   You can choose DECIMAL when you need exact decimal values. You can choose FLOAT or DOUBLE when approximate decimal values are acceptable.

   Example:

   DECIMAL → salary → 25000.50
   FLOAT → measurement → 2.345677
   DOUBLE → scientific_value → 2.344489652334
   

   Here,

   The variable salary uses the DECIMAL data type because it stores exact decimal values.

   The variable measurement uses the FLOAT data type because it stores approximate decimal values. The variable scientific_value uses the DOUBLE data type because it provides higher precision than FLOAT.

   4. Consider Storage Space:

   You should choose the appropriate data type so that storage space is not wasted. If your data values are small, you can choose a smaller storage data type. If your data values are large, you can choose a larger storage data type.

   Example:

   TINYINT → age → 120
   SMALLINT → student_count → 30000
   INT → population → 150000
   

   Here,

   The variable age uses the TINYINT data type because its value is within the TINYINT range.

   The variable student_count uses the SMALLINT data type because its value is larger than the TINYINT range.

   The variable population uses the INT data type because it stores a larger value.