MySQL - Coalesce() 函数

MySQL - COALESCE() 函数

在数据库管理中，表记录中出现缺失或未知数据是很常见的。SQL 不会将这些字段留空或使用零等占位符值，而是提供一个特殊标记：NULL。NULL 值表示数据的缺失。

然而，在运行查询时，NULL 值可能会带来不便。COALESCE() 函数是一个强大的工具，通过在查询结果中提供默认值或备用值来处理 NULL 值。

理解 MySQL COALESCE() 函数

MySQL COALESCE() 函数按顺序评估一系列参数，并返回它遇到的第一个非 NULL 值。如果列表中所有参数都是 NULL，则该函数本身返回 NULL。

语法

COALESCE() 函数的基本语法如下：

COALESCE(expression1, expression2, ..., expression_n)

该函数可以在任何 SELECT 语句中使用。

示例：基本用法

在此查询中，COALESCE() 将从左到右检查每个值。它将跳过前两个 NULL 值，并返回 ‘Hello’，因为这是第一个非 NULL 值。

SELECT COALESCE(NULL, NULL, 'Hello', 'Tutorialspoint') AS Result;

输出

Result
Hello

在表数据中的实际应用

让我们通过一个实际示例来演示 COALESCE()。假设我们有一个 products 表，其中一些产品有 sale_price（销售价格），而另一些只有 regular_price（常规价格）。

示例：创建和填充表

首先，我们来创建一个 products 表。

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    regular_price DECIMAL(10, 2) NOT NULL,
    sale_price DECIMAL(10, 2)
);

现在，我们插入一些数据。请注意，某些产品的 sale_price 为 NULL。

INSERT INTO products (product_name, regular_price, sale_price)
VALUES
    ('Laptop', 1200.00, 999.00),
    ('Mouse', 25.00, NULL),
    ('Keyboard', 75.00, 60.00),
    ('Monitor', 300.00, NULL);

products 表现在看起来像这样：

id	product_name	regular_price	sale_price
1	Laptop	1200.00	999.00
2	Mouse	25.00	NULL
3	Keyboard	75.00	60.00
4	Monitor	300.00	NULL

示例：使用 COALESCE() 确定显示价格

如果存在 sale_price，我们希望显示它；否则，我们将显示 regular_price。COALESCE() 非常适合此用途。我们可以在结果集中创建一个名为 display_price 的新列。

SELECT
    product_name,
    regular_price,
    sale_price,
    COALESCE(sale_price, regular_price) AS display_price
FROM products;

输出

结果集优雅地显示了每个产品应显示的正确价格。

product_name	regular_price	sale_price	display_price
Laptop	1200.00	999.00	999.00
Mouse	25.00	NULL	25.00
Keyboard	75.00	60.00	60.00
Monitor	300.00	NULL	300.00

专业提示：数据类型优先级

COALESCE() 的一个关键方面是数据类型优先级。如果参数具有不同的数据类型，结果将采用优先级更高的数据类型。例如，DECIMAL 类型的优先级高于 INT。如果您使用 COALESCE(int_column, decimal_column)，结果将被转换为 DECIMAL 类型。

与 COALESCE 不同，IFNULL(expr1, expr2) 函数是 MySQL 特有的，并且只接受两个参数。COALESCE 是标准 SQL 函数，更具灵活性，因为它接受多个参数。对于简单的双参数备用情况，IFNULL 可能会稍微快一些，但由于 COALESCE 的标准化和灵活性，通常更受青睐。

在现代应用程序代码中使用 COALESCE()

从应用程序执行 SQL 查询是一项标准任务。以下是使用现代编程语言运行 COALESCE 查询的示例。这些示例强调安全性、可维护性和现代最佳实践。

安全最佳实践：环境变量

永远不要在源代码中硬编码数据库凭证。使用环境变量来存储敏感信息，例如数据库主机、用户名和密码。这可以防止凭证在 Git 等版本控制系统中暴露。

# .env file (should not be committed to Git)
DB_HOST=localhost
DB_USER=root
DB_PASSWORD=your_secure_password
DB_NAME=your_database_name

Node.js
Python
Java
PHP



此示例使用 `mysql2/promise` 库提供 `async/await` 支持和连接池，这对于 Web 应用程序强烈推荐使用。

**1. 设置：**
`npm install mysql2 dotenv`

**2. 代码 (`app.js`)：**
```javascript
const mysql = require('mysql2/promise');
require('dotenv').config();

// 创建一个连接池
const pool = mysql.createPool({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

async function getProductPrices() {
    let connection;
    try {
        // 从连接池获取一个连接
        connection = await pool.getConnection();
        console.log("成功连接到数据库。");

        const sql = `
            SELECT
                product_name,
                regular_price,
                sale_price,
                COALESCE(sale_price, regular_price) AS display_price
            FROM products;`;

        const [rows] = await connection.execute(sql);

        console.log("\n产品价格：");
        console.table(rows);

    } catch (error) {
        console.error("发生错误：", error);
    } finally {
        if (connection) {
            connection.release(); // 将连接释放回连接池
        }
    }
}

async function main() {
    await getProductPrices();
    await pool.end(); // 应用程序关闭时关闭连接池
}

main();

3. 运行： node app.js

此示例使用 mysql-connector-python 库和上下文管理器（with 语句）来进行干净的资源管理。

1. 设置： pip install mysql-connector-python python-dotenv

2. 代码 (app.py)：

import mysql.connector
import os
from dotenv import load_dotenv
from decimal import Decimal

load_dotenv()

def get_product_prices():
    try:
        # `with` 语句确保连接自动关闭
        with mysql.connector.connect(
            host=os.getenv("DB_HOST"),
            user=os.getenv("DB_USER"),
            password=os.getenv("DB_PASSWORD"),
            database=os.getenv("DB_NAME")
        ) as connection:
            print("成功连接到数据库。")

            sql = """
                SELECT
                    product_name,
                    regular_price,
                    sale_price,
                    COALESCE(sale_price, regular_price) AS display_price
                FROM products;
            """

            # `with` 语句确保游标自动关闭
            with connection.cursor(dictionary=True) as cursor:
                cursor.execute(sql)
                results = cursor.fetchall()

                print("\n产品价格：")
                for row in results:
                    print(f"- {row['product_name']}：${row['display_price']}")

    except mysql.connector.Error as e:
        print(f"连接 MySQL 或执行查询时出错：{e}")

if __name__ == "__main__":
    get_product_prices()

3. 运行： python app.py

此现代 Java 示例使用 JDBC 和 try-with-resources 进行自动资源管理，并使用属性文件进行配置。假设您正在使用 Maven 或 Gradle 等构建工具。

1. Maven 依赖 (pom.xml)：

<dependencies>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.33</version>
    </dependency>
</dependencies>

2. 代码 (Main.java)：

import java.sql.*;

public class Main {
    public static void main(String[] args) {
        // 从环境变量加载凭证以提高安全性
        String url = "jdbc:mysql://" + System.getenv("DB_HOST") + "/" + System.getenv("DB_NAME");
        String user = System.getenv("DB_USER");
        String password = System.getenv("DB_PASSWORD");

        String sql = "SELECT product_name, regular_price, sale_price, " +
                     "COALESCE(sale_price, regular_price) AS display_price " +
                     "FROM products";

        // `try-with-resources` 确保所有资源自动关闭
        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            System.out.println("成功连接到数据库。");
            System.out.println("\n产品价格：");
            System.out.printf("%-20s %-15s\n", "Product Name", "Display Price");
            System.out.println("-------------------------------------");

            while (rs.next()) {
                String productName = rs.getString("product_name");
                double displayPrice = rs.getDouble("display_price");
                System.out.printf("%-20s $%-14.2f\n", productName, displayPrice);
            }

        } catch (SQLException e) {
            System.err.println("发生数据库错误：");
            e.printStackTrace();
        }
    }
}

此示例使用现代 mysqli 扩展，采用面向对象风格和预处理语句，以防止 SQL 注入。

1. 设置： composer require vlucas/phpdotenv

2. 代码 (app.php)：

<?php
require_once __DIR__ . '/vendor/autoload.php';

$dotenv = Dotenv\Dotenv::createImmutable(__DIR__);
$dotenv->load();

// 来自 .env 文件的数据库凭证
$dbhost = $_ENV['DB_HOST'];
$dbuser = $_ENV['DB_USER'];
$dbpass = $_ENV['DB_PASSWORD'];
$dbname = $_ENV['DB_NAME'];

// 使用 mysqli 建立连接（面向对象风格）
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

$query = "SELECT product_name, regular_price, sale_price, COALESCE(sale_price, regular_price) AS display_price FROM products";

try {
    $stmt = $mysqli->prepare($query);
    $stmt->execute();
    $result = $stmt->get_result();

    echo "成功连接并查询数据库。\n\n";
    echo "产品价格：\n";
    printf("%-20s %s\n", "Product", "Display Price");
    echo str_repeat('-', 35) . "\n";

    while ($row = $result->fetch_assoc()) {
        printf("%-20s $%.2f\n", $row['product_name'], $row['display_price']);
    }

    $stmt->close();
} catch (mysqli_sql_exception $e) {
    echo "发生错误：" . $e->getMessage() . "\n";
} finally {
    $mysqli->close();
}

?>

3. 运行： php app.php