Introduction to MySQL Numeric Data Types
MySQL supports many diverse data types categorized as Numeric types, String types, and Date & Time. This data type defines the kind of data values that will be stored in the fields of a database table. In addition, MySQL allows all standard ANSI numeric SQL data types to be implemented in the 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 various data values related to date format and time or both together. 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 design the tables in our databases effectively.
The Syntaxes used for the Standard Numeric data types in MySQL are illustrated below:
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 default size width up to 255.
- BIGINT – Large-sized integer having maximum size width up to 255.
- DECIMAL – Fixed or Static point integer where its 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 0-p and has 0 as the default value.
- FLOAT – Floating point number with single-precision, i.e. (p), if p is in range 0-24 then, it defines FLOAT(), and if it is in 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 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
In MySQL, we use the BIT data type to store the bit values in addition to numeric types. But except for the BIT type, the Numeric types can be either 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 add instantly the unsigned attribute to the table column.
How Numeric works in MySQL?
MySQL supports Numeric types of typical data types for bit values, integer, decimals, floating numbers, and fixed-point numbers. When defined for the columns present 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, count 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 it may contain the fractional part like 2.34, -0.012and is useful for storing information related to scientific calculations and monetary values in database records. Integers and Decimals are stored with exact calculations and not including the rounding values.
- The Float and Double data types are used for approximate type, which is similar to Decimal that can hold fractional parts but includes not exact but approximate number values like 2. 8E+3, -1.2E-115. These values provided need to be specified for rounding as they can be large in size, so these types are suitable for tasks like getting the normal crop revenue, unemployment charges, or distances. The values are real numbers and not accurate.
The BIT values hold the bit-field values of the related column value. Here, it supports integer values that will be 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, 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;
We will now code to show decimal calculates 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;
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 – of 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;
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);
It shows the same error running out of range for the id column. Again, we will just correct the previous query then,
INSERT INTO Age (ID , Age1, Age2, Age3) VALUES(10,3008, 6300897, 7703372036854775233);
The values are now accepted and inserted into the table Age.
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.
We can also determine if the values can be indexed or not with the help of data types. We can perform the comparison and other functions on the values of any particular data type.
This is a guide to MySQL Numeric. Here we discuss How Numeric works in MySQL along with the Examples and outputs. You may also have a look at the following articles to learn more –