MySQL Numeric Data Type

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

Numeric data types are data types in MySQL that are used in a database table to store numbers. They can store whole numbers and decimal numbers, such as 65, 1000, 4.5, and 99.99.

When you create a table, you choose a numeric data type for a column. For example, you can use `INT`, `DECIMAL`, `FLOAT`, or `DOUBLE`.

The data type tells MySQL what type of value the column can store and how MySQL should handle that value.

Syntax:

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

Example:

Suppose you create a table to store product information. product_id stores whole numbers, price stores decimal numbers, and rating stores floating-point numbers. So, you use different numeric data types for these columns.

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 store whole numbers.
• price will store decimal numbers with up to 8 digits in total and 2 digits after the decimal point.
• rating will store decimal numbers as floating-point values.



How MySQL Internally Processes and Stores Numeric Data Types

When you create a table in MySQL, you choose a data type for each column. The data type tells MySQL what type of value the column will store. For numeric values, you can use `INT`, `DECIMAL`, `FLOAT`, or `DOUBLE`. When you insert a value into a column, MySQL checks the value according to the selected data type. It checks whether the value is valid, within the allowed range, and how much storage space is needed to store it. For example, if a column is defined as `INT` and you insert `24`, MySQL understands that `24` is a whole number. It checks whether `24` is valid for `INT` and then stores it in the database.




Classification of Numeric Data Types

The following list shows the categories of numeric data types in MySQL.

1. Integer Types (Whole Numbers)

   If you define the integer data type for a column in a query, it stores only whole numbers, not decimal values.

   For example:

   INT → 25
   

   Where,

   • INT = Data Type
   • 25 = Integer Value

2. Fixed-Point Types (Exact Decimal Numbers)

   If you define the fixed-point data type for a column in a query, it stores decimal values with a fixed number of decimal places.

   For example:

   DECIMAL → 99.99
   

   Where,

   • DECIMAL = Data Type
   • 99.99 = Decimal Value

3. Floating-Point Types (Decimal Numbers)

   If you define the floating-point data type for a column in a query, it stores decimal values. It is used when a large range of values is needed. The stored values can be 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 values: Signed and Unsigned.

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

   For example:

   INT → -100
   

   Where,

   INT = Signed Data Type
   -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. Do not choose the `INT` data type to store decimal values.
2. Do not use `FLOAT` or `DOUBLE` for money, salary, or banking data because they store approximate values.
3. Choose a data type that can store larger values if your data may become larger in the future.
4. Make sure the value you want to store is within the allowed range of the selected data type
5. Do not choose a larger data type if your values are small because it can waste storage space.
6. If a column stores only positive numbers, you can use the `UNSIGNED` attribute.
7. You can use `FLOAT` or `DOUBLE` for approximate decimal values, such as `2.345677` and `2.344489652334`.



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

In MySQL, to choose the right numeric data type, you need to consider some main important factors.

1. Choose the Data Type Based on the Data You Store:

In the time of choosing the data type, if the data is a decimal, you want to select a decimal data type, and if the data is a whole number, you want to select an integer data type.

Example:

INT → age → 25
DECIMAL → price → 99.99

Here,

The INT data type defines the age variable and stores a whole number as the data. Similarly, the DECIMAL data type defines the price variable and stores a decimal value as the output.

2. Check the Value Range:

To check the value range, you should choose the appropriate data type.

For example, in the above example:

INT → age → 25
DECIMAL → price → 99.99

Here, the data is an integer, so you choose an integer data type. If the value is small, use TINYINT or SMALLINT. If the value is large, use INT or BIGINT.

Now, see the chart below. It clearly shows which integer data type you should choose according to its factors, such as storage size and range.

[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:

Now, look carefully at the third point. When selecting a data type, you should also consider the accuracy of the value.

If you want to store a fully accurate decimal value in a column and an approximate decimal value, select the DECIMAL data type for the first and the FLOAT or DOUBLE data type for the second.

Example:

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

Here,

DECIMAL, FLOAT, and DOUBLE are three different data types used for different variables.

The DECIMAL data type is used for the salary variable, the FLOAT data type is used for the measurement variable, and the DOUBLE data type is used for the scientific_value variable. The first stores an exact decimal value, and the second and third store approximate decimal values.