MySQL – DECIMAL
Table of content
”;
The MySQL Decimal Data Type
The MySQL DECIMAL data type is used to store numeric values with decimal points. It allows for precise calculations and can be configured to store a specified number of digits before and after the decimal point.
We often use this datatype for the columns that require exact precision such as salaries of employees, employee PF balances, etc.
Internally, MySQL stores DECIMAL values using a binary format that allocates storage for the integer and fractional parts of the number separately. This binary format efficiently packs 9 digits into 4 bytes of storage.
Syntax
Following is the syntax to define a column whose data type is DECIMAL −
column_name DECIMAL(P,D);
Where,
-
P is called precision which specifies the total number of significant digits that can be stored in the column, both to the left and right of the decimal point. The range of P is 1 to 65.
-
D is a scale that specifies the maximum number of digits that can be stored after the decimal point. The range of D should be between 0 and 30 and D is less than or equal to (<=) P.
For instance, if we define a column as DECIMAL(10,2), it can store numbers with up to 10 digits, and up to 2 digits to the right of the decimal point.
In MySQL, instead of the DECIMAL keyword, we can also use the “DEC”, “FIXED” and “NUMERIC” keywords because they are synonyms for DECIMAL.
Attributes
The DECIMAL keyword has two attributes: UNSIGNED and ZEROFILL.
-
UNSIGNED − When used, it indicates that the column does not accept negative values.
-
ZEROFILL − If used, it pads the number with zeros to the specified width.
Precision and Scale
In the following query, we define a SALARY column with the DECIMAL data type, specifying a precision of 5 and a scale of 3 −
SALARY decimal(5,3)
This definition means that the SALARY column can store values with up to 5 digits in total, including 3 digits to the right of the decimal point. The range for this column would be from 99.999 to -99.999.
No Decimal Places
In here, the SALARY column contains no fractional part or decimal point. The following two queries are same −
SALARY DECIMAL(5); SALARY DECIMAL(5,0);
Both declarations indicate that the SALARY column can store values as whole numbers without decimal places.
Default Precision
If you omit the precision value, the default precision P will be 10 −
SALARY DECIMAL;
MySQL DECIMAL Storage
MySQL stores values of the “DECIMAL” data type using a binary format that optimizes storage. Specifically, MySQL packs 9 digits into 4 bytes. Storage is allocated separately for the integer and fractional parts, with 4 bytes used for each set of 9 digits. Any remaining digits require additional storage.
The storage required for remaining (leftover) digits is demonstrated in the following table:
Leftover Digits | Bytes |
---|---|
0 | 0 |
1-2 | 1 |
3-4 | 2 |
5-6 | 3 |
7-9 | 4 |
Consider a DECIMAL(30,9) column, which has 9 digits for the fractional part and 30 – 9 = 21 digits for the integer part. In this case, the fractional part takes 4 bytes. The integer part takes 8 bytes for the first 18 digits, and for the leftover 3 digits, it requires an additional 2 bytes. Therefore, the DECIMAL(30,9) column requires a total of 14 bytes.
Example
To further understand this, let us create a table named EMPLOYEES using the following query −
CREATE TABLE EMPLOYEES ( ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, SALARY decimal(14,4) NOT NULL, PRIMARY KEY (ID) );
Using the following query, we are inserting some records into the above created table −
INSERT INTO EMPLOYEES (NAME, SALARY) VALUES ("Krishna", 150050.34), ("Kalyan", 100000.65);
The EMPLOYEES table obtained is as follows −
ID | NAME | SALARY |
---|---|---|
1 | Krishna | 150050.3400 |
2 | Kalyan | 100000.6500 |
Using the following query, we are including the ZEROFILL attribute in the “SALARY” column −
ALTER TABLE EMPLOYEES MODIFY SALARY decimal(14, 4) zerofill;
Following is the output of the above query −
Query OK, 2 rows affected, 1 warning (0.03 sec) Records: 2 Duplicates: 0 Warnings: 1
Here, we are trying to fetch all the records from the EMPLOYEES tables after including the ZEROFILL attribute on SALARY column −
SELECT * FROM EMPLOYEES;
The records will display zeros padded based on the range specified in the “SALARY” column −
ID | NAME | SALARY |
---|---|---|
1 | Krishna | 0000150050.3400 |
2 | Kalyan | 0000100000.6500 |
Decimal Datatype Using a Client Program
We can also create column of the decimal datatype using the client program.
Syntax
To create a column of decimal datatype through a PHP program, we need to execute the “CREATE TABLE” statement using the mysqli function query() as follows −
$sql = ''CREATE TABLE EMPLOYEES ( ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, SALARY decimal(14,4) NOT NULL, PRIMARY KEY (ID) )''; $mysqli->query($sql);
To create a column of decimal datatype through a JavaScript program, we need to execute the “CREATE TABLE” statement using the query() function of mysql2 library as follows −
sql = "CREATE TABLE EMPLOYEES ( ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, SALARY decimal(14,4) NOT NULL, PRIMARY KEY (ID) )"; con.query(sql);
To create a column of decimal datatype through a Java program, we need to execute the “CREATE TABLE” statement using the JDBC function execute() as follows −
String sql = "CREATE TABLE STUDENTS (ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, BRANCH ENUM ("CSE", "ECE", "MECH"), FEES int NOT NULL, PRIMARY KEY (ID))"; statement.execute(sql);
To create a column of decimal datatype through a python program, we need to execute the “CREATE TABLE” statement using the execute() function of the MySQL Connector/Python as follows −
sql = ''CREATE TABLE EMPLOYEES (ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, SALARY decimal(14,4) NOT NULL, PRIMARY KEY (ID))'' cursorObj.execute(sql)
Example
Following are the programs −
$dbhost = ''localhost''; $dbuser = ''root''; $dbpass = ''password''; $dbname = ''TUTORIALS''; $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname); if ($mysqli->connect_errno) { printf("Connect failed: %s
", $mysqli->connect_error); exit(); } //printf(''Connected successfully.
''); //create table with boolean column $sql = ''CREATE TABLE EMPLOYEES ( ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, SALARY decimal(14,4) NOT NULL, PRIMARY KEY (ID) )''; $result = $mysqli->query($sql); if ($result) { printf("Table created successfully...!n"); } //insert data into created table $q = "INSERT INTO EMPLOYEES (NAME, SALARY) VALUES (''Krishna'', 150050.34), (''Kalyan'', 100000.65)"; if ($res = $mysqli->query($q)) { printf("Data inserted successfully...!n"); } //now display the table records $s = "SELECT ID, Salary FROM EMPLOYEES"; if ($r = $mysqli->query($s)) { printf("Table Records: Where Salary is decimal type! n"); while ($row = $r->fetch_assoc()) { printf(" ID: %d, Salary: %s", $row["ID"], $row["Salary"]); printf("n"); } } else { printf(''Failed''); } $mysqli->close();
Output
The output obtained is as follows −
Table created successfully...! Data inserted successfully...! Table Records: Where Salary is decimal type! ID: 1, Salary: 150050.3400 ID: 2, Salary: 100000.6500
var mysql = require("mysql2"); var con = mysql.createConnection({ host: "localhost", user: "root", password: "password", }); //Connecting to MySQL con.connect(function (err) { if (err) throw err; // console.log("Connected successfully...!"); // console.log("--------------------------"); sql = "USE TUTORIALS"; con.query(sql); //create a employees table, that accepts one column of decimal type. sql = "CREATE TABLE EMPLOYEES ( ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, SALARY decimal(14,4) NOT NULL, PRIMARY KEY (ID) )"; con.query(sql); //insert data into created table sql = "INSERT INTO EMPLOYEES (NAME, SALARY) VALUES (''Krishna'', 150050.34), (''Kalyan'', 100000.65)"; con.query(sql); //select datatypes of salary sql = `SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ''EMPLOYEES'' AND COLUMN_NAME = ''SALARY''`; con.query(sql, function (err, result) { if (err) throw err; console.log(result); }); });
Output
The output produced is as follows −
[ { DATA_TYPE: ''decimal'' } ]
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class Decimal { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/TUTORIALS"; String username = "root"; String password = "password"; try { Class.forName("com.mysql.cj.jdbc.Driver"); Connection connection = DriverManager.getConnection(url, username, password); Statement statement = connection.createStatement(); System.out.println("Connected successfully...!"); //Decimal data types...!; String sql = "CREATE TABLE EMPLOYEES ( ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, SALARY decimal(14,4) NOT NULL, PRIMARY KEY (ID))"; statement.execute(sql); System.out.println("column of a Decimal type created successfully...!"); ResultSet resultSet = statement.executeQuery("DESCRIBE EMPLOYEES"); while (resultSet.next()){ System.out.println(resultSet.getString(1)+" "+resultSet.getString(2)); } connection.close(); } catch (Exception e) { System.out.println(e); } } }
Output
The output obtained is as shown below −
Connected successfully...! column of a Decimal type created successfully...! ID int NAME varchar(30) SALARY decimal(14,4)
import mysql.connector # Establishing the connection connection = mysql.connector.connect( host=''localhost'', user=''root'', password=''password'', database=''tut'' ) # Creating a cursor object cursorObj = connection.cursor() # Create table with decimal column sql = '''''' CREATE TABLE EMPLOYEES ( ID int NOT NULL AUTO_INCREMENT, NAME varchar(30) NOT NULL, SALARY decimal(14,4) NOT NULL, PRIMARY KEY (ID) )'''''' cursorObj.execute(sql) print("The table is created successfully!") # Data to be inserted data_to_insert = [ (''Krishna'', 150050.34), (''Kalyan'', 100000.65) ] # Insert data into the created table insert_query = "INSERT INTO EMPLOYEES (NAME, SALARY) VALUES (%s, %s)" cursorObj.executemany(insert_query, data_to_insert) # Commit the changes after the insert operation connection.commit() print("Rows inserted successfully.") # Now display the table records select_query = "SELECT * FROM EMPLOYEES" cursorObj.execute(select_query) result = cursorObj.fetchall() print("Table Data:") for row in result: print(row) cursorObj.close() connection.close()
Output
Following is the output of the above code −
The table is created successfully! Rows inserted successfully. Table Data: (1, ''Krishna'', Decimal(''150050.3400'')) (2, ''Kalyan'', Decimal(''100000.6500''))
”;