Updated June 2, 2023
Introduction to MySQL Numeric Data Types
MySQL supports many diverse data types categorized as Numeric types, String types, and Date & Time. The data type determines the type of data values that will be stored in the fields of a database table. Additionally, MySQL supports the implementation of all standard ANSI numeric SQL data types in queries. This numeric data type consists of exact types such as INTEGER, DECIMAL, NUMERIC, and SMALLINT, whereas approximate types include FLOAT, DOUBLE PRECISION, and REAL.
Date and Time types provide us with various data values related to date format and time or both. The string type supports character values with long-range to be specified, such as CHAR, VARCHAR, BLOB, or TEXT. We will learn to apply these data types to effectively design the tables in our databases.
Syntax
Below, we illustrate the syntaxes used for the standard numeric data types in MySQL:
Numeric types Description
- TINYINT – Very small-sized integer having signed range -128 to 127 and unsigned range 0-255 but default size width 255
- SMALLINT – Small-sized integer with less signed range than MEDIUMINT and unsigned to 0-65535 but default size 255.
- MEDIUMINT- Medium-sized integer having unsigned range 0-16777215 and signed range less than INT but default size width 255.
- INT – Standard-sized integer whose signed range is less than BIGINT but with default size width up to 255.
- BIGINT – Large-sized integer having maximum size width of up to 255.
- DECIMAL – Fixed or Static point integer whose default value size is 10 and defines as (p,q) where q denotes the number of digits to be provided to the right of the decimal point. But q ranges from 0-p and has 0 as the default value.
- FLOAT – Floating point number with single-precision, i.e. (p), if p is in the range 0-24, then it defines FLOAT(), and if it is in the range 25-53, then it becomes DOUBLE().
- DOUBLE – Floating point number with double precision like (size, d), where d denotes the number of digits to be specified after the decimal point
- BIT- Specifies BIT size as the number of bits per value in a field that can hold values in the range of 1 to 64
- BOOL – If 0, then FALSE, and if 1, i.e., non-zero values, then TRUE
- BOOLEAN – Identical to BOOL
MySQL uses the BIT data type to store the bit values and numeric types. But except for the BIT type, the Numeric types can be signed or unsigned. The unsigned option makes MySQL prohibit the negative values for the table column. Whereas for the signed or ZeroFill option, MySQL will instantly add the unsigned attribute to the table column.
How does Numeric Work in MySQL?
MySQL supports Numeric types of typical data types for bit values, integers, decimals, floating numbers, and fixed-point numbers. When defined for the columns in the table, these data types will then demonstrate the values of data to be inserted in the database table and with their respective length size.
The numeric type holds exact or approximate types of values:
- Inexact type, the integer and decimal data types are included. Integers are whole numbers without fractional parts like 45, -7, 0, etc., and can be used for applications such as weight, height, number of stars, number of persons, or even number of bacteria present in a rotten thing. Decimals help to store the exact numeric values where they may contain the fractional part like 2.34, -0.012and helps store information related to scientific calculations and monetary values in database records. Integers and decimals store values with exact calculations, without including rounding values.
- The Float and Double data types represent approximate values, similar to Decimals. These data types can store fractional parts but are not exact and instead hold approximate number values like 2.8E+3 or -1.2E-115. These values provided need to be specified for rounding as they can be large, so these types are suitable for tasks like getting the normal crop revenue, unemployment charges, or distances. The values are real numbers and are not accurate.
The BIT values hold the bit-field values of the related column value. Here, it supports integer values of 0, 1, or NULL.
Examples of MySQL Numeric Datatypes
Let us demonstrate some examples of MySQL Numeric Data Types as follows:
Example #1 – Using INT, FLOAT, and DECIMAL data types
Suppose we will create a table named Number:
CREATE TABLE Number(Num_ID INT, Num_Float FLOAT, Num_Decimal DECIMAL(4,2));We will then insert a few values into the Number table as follows:
INSERT INTO Number(Num_ID, Num_Float, Num_Decimal) VALUES (1,2.5,2.5),(2,2.5,2.5), (3,2.5,2.5);View the table:
SELECT * FROM Number;Output:
We will now code to show decimals calculate more precise values that differ from a float, which provides inaccurate values.
INSERT INTO Number(Num_ID, Num_Float, Num_Decimal) VALUES (4,2.50078,2.50078);
SELECT * FROM Number;Output:
As you can see, the float data type takes the long decimal precision approximate value, but the decimal type does not support it because it has a fixed range of (4,2) mentioned while creating a table.
Example #2 – Using other Numeric data types
Let us add a table with ages but using different data types such as SMALLINT, TINYINT, MEDIUMINT, and BIGINT:
CREATE TABLE Age (ID TINYINT, Age1 SMALLINT, Age2 MEDIUMINT, Age3 BIGINT);Inserting some values according to the size of each data type can hold:
INSERT INTO Age (ID , Age1, Age2, Age3) VALUES(1,128,8300607,9103372036854775807);
Select * from Age;Output:
The input values are accepted as there is no violation in the length of numbers out of the range of data types. But if we try to enter out of range value, suppose for the TINYINT column, then what will happen? Let us see:
INSERT INTO Age (ID , Age1, Age2, Age3) VALUES(128,3008,6300897,7703372036854775233);Output:
It shows the same error running out of range for the id column. Again, we will correct the previous query, and then,
INSERT INTO Age (ID , Age1, Age2, Age3) VALUES(10,3008, 6300897, 7703372036854775233);Output:
The table “Age” now accepts and inserts the values.
Conclusion
When we create a table with multiple columns and provide each a data type according to the data records we want to save there, which can be numeric, string, or datetime, these numeric data types or others both take space in the server and have variable or fixed lengths to restrict the size of the value inserted into it.
With the help of data types, we can determine whether we can index the values or not. We can perform the comparison and other functions on the values of any particular data type.
Recommended Articles
We hope that this EDUCBA information on “MySQL Numeric” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
