MySQL - NULL 值

MySQL：使用 NULL 值

理解 NULL

在 MySQL 中，NULL 是一个特殊的标记，用于表示数据库中不存在数据值。它与数字类型的零 (0) 或字符串类型的空字符串 (”) 有本质区别。NULL 值代表缺失、未知或不适用的数据。

一个需要记住的关键概念是，NULL 不等于任何东西，甚至不等于它自己。标准比较运算符如 =、!=、< 或 > 不能用于测试 NULL。您必须使用 IS NULL 或 IS NOT NULL 运算符。

定义 NOT NULL 列

为了强制列必须始终有值，您在创建表时使用 NOT NULL 约束。尝试向 NOT NULL 列插入 NULL 值（或在 INSERT 语句中省略该列且没有 DEFAULT 值）将导致错误。

语法

CREATE TABLE table_name (
    column1_name datatype NOT NULL,
    column2_name datatype, -- 此列可以为 NULL
    ...
);

示例

让我们创建一个 products 表，其中 name 和 price 是必填项，但 description 是可选的。

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    description TEXT, -- 可以为 NULL
    launch_date DATE -- 可以为 NULL
);

现在，让我们插入一些记录。请注意，我们可以省略 description 和 launch_date，这将导致它们被设置为 NULL。

INSERT INTO products (name, price, description, launch_date) VALUES
    ('Laptop Pro', 1200.00, 'A powerful laptop for professionals.', '2023-01-15'),
    ('Wireless Mouse', 25.50, NULL, '2023-02-20'),
    ('Mechanical Keyboard', 150.00, 'RGB-lit mechanical keyboard.', NULL),
    ('USB-C Hub', 45.00, NULL, NULL);

查询 NULL 值

要查找特定列包含 NULL 的行，请使用 IS NULL 运算符。

-- 查找缺少描述的产品
SELECT id, name, price FROM products WHERE description IS NULL;

此查询返回 ‘Wireless Mouse’ 和 ‘USB-C Hub’。

要查找存在值的行，请使用 IS NOT NULL。

-- 查找有发布日期的产品
SELECT name, launch_date FROM products WHERE launch_date IS NOT NULL;

此查询返回 ‘Laptop Pro’ 和 ‘Wireless Mouse’。

在查询和函数中处理 NULL

通常，您不希望在应用程序的输出中显示 NULL。MySQL 提供了替换 NULL 值的函数。

使用 IFNULL() 和 COALESCE()

IFNULL(expr1, expr2) 如果 expr1 不为 NULL 则返回 expr1，否则返回 expr2。COALESCE(value1, value2, ...) 返回其参数列表中第一个非 NULL 值。

-- 对于描述为 NULL 的产品，显示 'No description'
SELECT
    name,
    IFNULL(description, 'No description available') AS product_description
FROM products;

COALESCE 在有多个潜在备用值时更灵活：

-- 如果有短描述则使用短描述，否则使用通用注释，否则使用 'N/A'
SELECT name, COALESCE(short_description, generic_note, 'N/A') AS display_note FROM products;

聚合函数与 NULL

大多数聚合函数，如 SUM()、AVG()、MIN() 和 MAX() 都会忽略 NULL 值。COUNT(column_name) 统计该列中的非 NULL 值，而 COUNT(*) 则统计所有行，无论是否为 NULL 值。

SELECT
    COUNT(*) AS total_products,
    COUNT(launch_date) AS products_with_launch_date,
    AVG(price) AS average_price -- 平均 4 个价格
FROM products;

修改和删除包含 NULL 值的行

您可以在 UPDATE 和 DELETE 语句中使用 IS NULL 条件来定位特定行。

更新 NULL 值

-- 为所有没有发布日期的产品设置一个发布日期
UPDATE products
SET launch_date = '2023-11-01'
WHERE launch_date IS NULL;

删除包含 NULL 值的行

-- 删除没有描述的产品
DELETE FROM products
WHERE description IS NULL;

常见陷阱：column = NULL

初学者常犯的一个错误是尝试使用等值运算符与 NULL 进行比较。表达式 column = NULL 总是评估为 NULL（在 WHERE 子句中被视为假），而不是真或假。它永远不会找到任何行。

-- 这是不正确的，不会按预期工作
SELECT * FROM products WHERE description = NULL; -- 返回空集

-- 这是正确的方法
SELECT * FROM products WHERE description IS NULL;

实际应用：在代码中处理 NULL

在检索数据时，您的应用程序代码需要准备好处理 NULL 值，这些值通常映射到特殊类型，例如 Python 中的 None、JavaScript/PHP 中的 null 或 Java 中的 null。

Node.js (使用 mysql2)

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

async function getProducts() {
  let connection;
  try {
    connection = await mysql.createConnection({ /* connection details */ });
    const [rows] = await connection.execute('SELECT name, description FROM products');

    rows.forEach(product => {
      // 如果数据库中的 description 为 NULL，则 product.description 将为 `null`
      const description = product.description || 'Not available';
      console.log(`${product.name}: ${description}`);
    });

  } catch (error) {
    console.error('Failed to fetch products:', error);
  } finally {
    if (connection) await connection.end();
  }
}

getProducts();

Python (使用 mysql-connector-python)

import mysql.connector

def get_products():
    try:
        with mysql.connector.connect(/* connection details */) as conn:
            with conn.cursor(dictionary=True) as cursor:
                cursor.execute('SELECT name, description FROM products')
                for product in cursor.fetchall():
                    # 如果 description 为 NULL，则 product['description'] 将为 `None`
                    description = product.get('description') or 'Not available'
                    print(f"{product['name']}: {description}")
    except mysql.connector.Error as err:
        print(f'Error: {err}')

get_products()

Java (使用 JDBC 和 try-with-resources)

import java.sql.*;

public class NullValueDemo {
    public void getProducts() {
        String url = "jdbc:mysql://localhost:3306/your_database_name";
        String user = "root";
        String password = "password";

        String sql = "SELECT name, description FROM products";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                String name = rs.getString("name");
                String description = rs.getString("description");

                // 如果数据库值为 NULL，rs.getString() 将返回 null
                if (rs.wasNull()) {
                    description = "Not available";
                }
                System.out.println(name + ": " + description);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}