MySQL - DECIMAL

MySQL - The DECIMAL Data Type

The MySQL DECIMAL Data Type

The MySQL DECIMAL data type is used for storing exact-value numeric data. This means that values are stored precisely as you define them, without the small rounding errors that can occur with floating-point types. This makes DECIMAL the ideal choice for financial and monetary data, such as salaries, account balances, and transaction amounts, where absolute precision is critical.

You should use DECIMAL whenever you need to perform precise calculations and avoid the potential inaccuracies of approximate-value data types like FLOAT and DOUBLE.

Internally, MySQL stores DECIMAL values in a binary format that packs nine base-10 digits into 4 bytes, ensuring efficient and precise storage for both the integer and fractional parts of a number.

Syntax and Attributes

Syntax

The syntax to define a column with the DECIMAL data type is as follows:

column_name DECIMAL(P, D);

Where:

• P (Precision): The total number of digits that can be stored, both to the left and right of the decimal point. The maximum value for P is 65.
• D (Scale): The number of digits that can be stored to the right of the decimal point. The range for D is 0 to 30. MySQL requires that D is less than or equal to P (D <= P).

For example, a column defined as price DECIMAL(10, 2) can store a price up to 99999999.99. If you omit P and D, it defaults to DECIMAL(10, 0).

In MySQL, DEC, NUMERIC, and FIXED are synonyms for DECIMAL. You can use them interchangeably.

Attributes: UNSIGNED and ZEROFILL

The DECIMAL type supports two common attributes:

• UNSIGNED: If specified, the column will only accept non-negative values. This effectively doubles the maximum positive value the column can hold.
• ZEROFILL: If specified, MySQL automatically pads the displayed value with leading zeros up to the width specified by the precision. For example, a value of 123.45 in a DECIMAL(10, 2) ZEROFILL column would display as 00000123.45.

A Note on ZEROFILL Deprecation

Warning: The ZEROFILL attribute has been deprecated as of MySQL 8.0.17 and is planned for removal in a future version. Its use is discouraged. Using ZEROFILL also implicitly adds the UNSIGNED attribute to the column, which can be an unexpected side effect. Modern best practice is to handle display formatting, like zero-padding, in your application’s presentation layer, not in the database.

Storage and Performance

MySQL allocates storage for the integer and fractional parts of a DECIMAL number separately. It packs groups of 9 digits into 4 bytes. The storage required for any remaining digits is shown below:

Leftover Digits	Bytes Required
0	0
1-2	1
3-4	2
5-6	3
7-9	4

For a DECIMAL(20, 6) column, there are 6 fractional digits and 20 - 6 = 14 integer digits. The fractional part requires 3 bytes (for 6 digits). The integer part has one group of 9 digits (4 bytes) and 5 leftover digits (3 bytes). Total storage: 3 + 4 + 3 = 10 bytes.

Practical Examples

Let’s create a table to store employee data, including their salary.

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    salary DECIMAL(10, 2) NOT NULL
);

Now, let’s insert some records into the table.

INSERT INTO employees (name, salary) VALUES
('Alice', 75000.50),
('Bob', 82000.00),
('Charlie', 125000.75);

The table now contains:

id	name	salary
1	Alice	75000.50
2	Bob	82000.00
3	Charlie	125000.75

If we try to insert a value that exceeds the defined precision, MySQL will raise an error.

-- This will fail because the value is too large for DECIMAL(10, 2)
INSERT INTO employees (name, salary) VALUES ('David', 100000000.00);
-- ERROR 1264 (22003): Out of range value for column 'salary' at row 1

Best Practices and Common Pitfalls

Choosing Between DECIMAL, FLOAT, and DOUBLE

• Use DECIMAL for financial data, currency, or any value that requires exact precision.
• Use FLOAT (4 bytes) or DOUBLE (8 bytes) for scientific calculations or when you need to represent a very large range of values and can tolerate minor rounding inaccuracies. They are approximate-value types.

Common Mistake: Underestimating Precision Needs

A common mistake is defining a DECIMAL column with insufficient precision. Always consider the maximum possible value the column might need to hold, including results from aggregations (like SUM()), and define your precision and scale accordingly. It’s better to slightly overestimate than to have your application fail due to out-of-range errors.

Using DECIMAL in Application Code

Security First: Prepared Statements

When interacting with a database from an application, always use prepared statements (or parameterized queries). This practice is crucial for preventing SQL injection attacks, where a malicious user could otherwise alter your SQL queries. All modern database libraries support this feature.

PHP (PDO)
Node.js (mysql2)
Java (JDBC)
Python (mysql-connector)



To create a table and insert `DECIMAL` data using PHP with PDO, you should use prepared statements to ensure security and type correctness.

```php
<?php
$host = 'localhost';
$dbname = 'TUTORIALS';
$user = 'root';
$pass = 'password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$dbname;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    echo "Connected successfully.\n";

    // Create table with a DECIMAL column
    $pdo->exec('CREATE TABLE IF NOT EXISTS products (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        price DECIMAL(10, 2) NOT NULL
    )');
    echo "Table 'products' is ready.\n";

    // Insert data using a prepared statement
    $stmt = $pdo->prepare("INSERT INTO products (name, price) VALUES (:name, :price)");

    $products = [
        ['name' => 'Laptop', 'price' => '1299.99'],
        ['name' => 'Mouse', 'price' => '25.50']
    ];

    foreach ($products as $product) {
        $stmt->execute($product);
    }
    echo "Data inserted successfully.\n";

} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}
?>

Using the modern mysql2/promise library with async/await syntax provides a clean and robust way to handle database operations in Node.js.

const mysql = require('mysql2/promise');

async function main() {
    let connection;
    try {
        connection = await mysql.createConnection({
            host: 'localhost',
            user: 'root',
            password: 'password',
            database: 'TUTORIALS'
        });

        console.log('Connected successfully.');

        // Create table with a DECIMAL column
        await connection.execute(`
            CREATE TABLE IF NOT EXISTS products (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(100) NOT NULL,
                price DECIMAL(10, 2) NOT NULL
            );
        `);
        console.log("Table 'products' is ready.");

        // Insert data using a prepared statement
        const insertSql = "INSERT INTO products (name, price) VALUES (?, ?)";
        const products = [
            ['Laptop', '1299.99'],
            ['Mouse', '25.50']
        ];

        for (const product of products) {
            await connection.execute(insertSql, product);
        }
        console.log('Data inserted successfully.');

    } catch (error) {
        console.error('Database operation failed:', error);
    } finally {
        if (connection) {
            await connection.end();
            console.log('Connection closed.');
        }
    }
}

main();

In Java, use a PreparedStatement within a try-with-resources block to ensure that database resources are closed automatically and to prevent SQL injection.

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class DecimalExample {
    private static final String URL = "jdbc:mysql://localhost:3306/TUTORIALS";
    private static final String USER = "root";
    private static final String PASSWORD = "password";

    public static void main(String[] args) {
        String createTableSql = "CREATE TABLE IF NOT EXISTS products (" +
                              "id INT AUTO_INCREMENT PRIMARY KEY, " +
                              "name VARCHAR(100) NOT NULL, " +
                              "price DECIMAL(10, 2) NOT NULL)";

        String insertSql = "INSERT INTO products (name, price) VALUES (?, ?)";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            System.out.println("Connected successfully.");

            try (PreparedStatement stmt = conn.prepareStatement(createTableSql)) {
                stmt.execute();
                System.out.println("Table 'products' is ready.");
            }

            try (PreparedStatement stmt = conn.prepareStatement(insertSql)) {
                // Insert first product
                stmt.setString(1, "Laptop");
                stmt.setBigDecimal(2, new BigDecimal("1299.99"));
                stmt.executeUpdate();

                // Insert second product
                stmt.setString(1, "Mouse");
                stmt.setBigDecimal(2, new BigDecimal("25.50"));
                stmt.executeUpdate();

                System.out.println("Data inserted successfully.");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Python’s mysql-connector library integrates well with context managers (with statements) for handling connections and cursors. Pass parameters as a tuple to the execute method.

import mysql.connector
from mysql.connector import errorcode
from decimal import Decimal

config = {
    'user': 'root',
    'password': 'password',
    'host': 'localhost',
    'database': 'TUTORIALS'
}

try:
    with mysql.connector.connect(**config) as cnx:
        print("Connected successfully.")
        with cnx.cursor() as cursor:
            # Create table with a DECIMAL column
            create_table_query = (
                "CREATE TABLE IF NOT EXISTS products ("
                "  id INT AUTO_INCREMENT PRIMARY KEY,"
                "  name VARCHAR(100) NOT NULL,"
                "  price DECIMAL(10, 2) NOT NULL"
                ")"
            )
            cursor.execute(create_table_query)
            print("Table 'products' is ready.")

            # Insert data using prepared statements
            insert_query = "INSERT INTO products (name, price) VALUES (%s, %s)"
            products = [
                ('Laptop', Decimal('1299.99')),
                ('Mouse', Decimal('25.50'))
            ]

            for product in products:
                cursor.execute(insert_query, product)

            cnx.commit() # Commit the transaction
            print(f"{cursor.rowcount} records inserted successfully.")

except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)
finally:
    if 'cnx' in locals() and cnx.is_connected():
        cnx.close()
        print("Connection closed.")