How to specify Decimal Precision and scale number in MySQL database using PHPMyAdmin?

When working with DECIMAL data types in MySQL through PHPMyAdmin, you need to specify both precision and scale to properly store monetary values or other exact numeric data. This tutorial shows you how to configure decimal precision and scale using PHPMyAdmin interface.

Understanding DECIMAL Precision and Scale

The DECIMAL data type requires two parameters:

DECIMAL(precision, scale)

Where:

• Precision (X) − Total number of digits that can be stored
• Scale (Y) − Number of digits after the decimal point

Example of DECIMAL Usage

For DECIMAL(6,4):

• Total digits: 6
• Digits after decimal: 4
• Digits before decimal: 2 (6-4=2)
• Valid values: 12.3456, 99.9999, 1.0000

Creating Table with DECIMAL Column in PHPMyAdmin

Step 1: Select Database

First, select your target database from the PHPMyAdmin interface ?

Step 2: Configure Table Structure

When creating the table structure, specify the DECIMAL column with precision and scale ?

Step 3: Insert Sample Data

Here's how to insert data into your table with DECIMAL values ?

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "sample_db";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Insert data with DECIMAL values
$sql = "INSERT INTO DecimalPrecisionDemo (amount) VALUES 
        (12.3456), 
        (99.9999), 
        (1.0000)";

if ($conn->query($sql) === TRUE) {
    echo "Records inserted successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

The output of the above code is ?

Records inserted successfully

Step 4: Verify Data Storage

Check how MySQL stores your decimal values ?

<?php
$conn = new mysqli("localhost", "root", "", "sample_db");

$result = $conn->query("SELECT * FROM DecimalPrecisionDemo");

while($row = $result->fetch_assoc()) {
    echo "ID: " . $row["id"] . " - Amount: " . $row["amount"] . "<br>";
}

$conn->close();
?>

The output of the above code is ?

ID: 1 - Amount: 12.3456
ID: 2 - Amount: 99.9999
ID: 3 - Amount: 1.0000

Important Notes

• MySQL automatically rounds values that exceed the specified scale
• Values exceeding precision limits will cause an error
• DECIMAL provides exact precision, unlike FLOAT or DOUBLE
• Use DECIMAL for financial calculations to avoid rounding errors

By properly configuring DECIMAL precision and scale in PHPMyAdmin, you ensure accurate storage of numeric data with exact decimal places, which is crucial for financial and scientific applications.