MySQL - DECIMAL

MySQL - DECIMAL 数据类型

MySQL DECIMAL 数据类型

MySQL DECIMAL 数据类型用于存储精确数值数据。这意味着数值会按照您定义的方式精确存储，而不会像浮点类型那样出现小的舍入误差。因此，DECIMAL 是财务和货币数据的理想选择，例如薪水、账户余额和交易金额，在这些场景中，绝对精度至关重要。

每当您需要执行精确计算并避免像 FLOAT 和 DOUBLE 这样的近似值数据类型可能导致的不准确性时，都应该使用 DECIMAL。

在内部，MySQL 以二进制格式存储 DECIMAL 值，将九个十进制数字打包成 4 字节，确保了数字的整数部分和小数部分都能高效且精确地存储。

语法和属性

语法

定义 DECIMAL 数据类型列的语法如下：

column_name DECIMAL(P, D);

其中：

• P (Precision，精度)：可存储的总位数，包括小数点左侧和右侧的位数。P 的最大值为 65。
• D (Scale，标度)：小数点右侧可存储的位数。D 的范围是 0 到 30。MySQL 要求 D 必须小于或等于 P (D <= P)。

例如，定义为 price DECIMAL(10, 2) 的列可以存储最大值为 99999999.99 的价格。如果您省略 P 和 D，它将默认使用 DECIMAL(10, 0)。

在 MySQL 中，DEC、NUMERIC 和 FIXED 都是 DECIMAL 的同义词。您可以互换使用它们。

属性：UNSIGNED 和 ZEROFILL

DECIMAL 类型支持两个常用属性：

• UNSIGNED：如果指定，该列将只接受非负值。这实际上将该列可以容纳的最大正值加倍。
• ZEROFILL：如果指定，MySQL 会自动用前导零填充显示值，直到达到精度指定的宽度。例如，DECIMAL(10, 2) ZEROFILL 列中的值 123.45 将显示为 00000123.45。

关于 ZEROFILL 弃用的注意事项

警告：ZEROFILL 属性已在 MySQL 8.0.17 中弃用，并计划在未来版本中移除。不鼓励使用它。使用 ZEROFILL 还会隐式地为列添加 UNSIGNED 属性，这可能是一个意想不到的副作用。现代最佳实践是在应用程序的表示层处理显示格式，如零填充，而不是在数据库中处理。

存储和性能

MySQL 为 DECIMAL 数字的整数部分和小数部分分别分配存储空间。它将 9 位数字的组打包成 4 字节。剩余数字所需的存储空间如下所示：

剩余位数	所需字节
0	0
1-2	1
3-4	2
5-6	3
7-9	4

对于 DECIMAL(20, 6) 列，有 6 位小数和 20 - 6 = 14 位整数。小数部分需要 3 字节（用于 6 位数字）。整数部分有一个 9 位数字的组（4 字节）和 5 个剩余数字（3 字节）。总存储空间：3 + 4 + 3 = 10 字节。

实际示例

让我们创建一个表来存储员工数据，包括他们的薪水。

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

现在，让我们向表中插入一些记录。

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

该表现在包含：

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

如果我们尝试插入一个超出定义精度范围的值，MySQL 将会引发错误。

-- This will fail because the value is too large for DECIMAL(10, 2)
-- 这将失败，因为该值对于 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

最佳实践与常见陷阱

DECIMAL、FLOAT 和 DOUBLE 的选择

• 对于财务数据、货币或任何需要精确度量的值，请使用 DECIMAL。
• 对于科学计算或当您需要表示非常大的数值范围并且可以容忍轻微的舍入误差时，请使用 FLOAT（4 字节）或 DOUBLE（8 字节）。它们是近似值类型。

常见错误：低估精度需求

一个常见的错误是定义 DECIMAL 列时精度不足。请始终考虑该列可能需要容纳的最大值，包括聚合函数（如 SUM()）的结果，并据此定义您的精度和标度。稍微高估总比因超出范围错误而导致应用程序失败要好。

在应用程序代码中使用 DECIMAL

安全至上：预处理语句

当从应用程序与数据库交互时，请始终使用预处理语句（或参数化查询）。这种做法对于防止 SQL 注入攻击至关重要，因为恶意用户可能会修改您的 SQL 查询。所有现代数据库库都支持此功能。

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
    // 创建带有 DECIMAL 列的表
    $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
        // 创建带有 DECIMAL 列的表
        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
            // 创建带有 DECIMAL 列的表
            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.")