MySQL - IS NULL 运算符

MySQL：处理 NULL 值

理解 MySQL 中的 NULL

在 SQL 中，NULL 值表示数据的缺失。它与零（0）、空字符串（”）或字符串字面量 ‘NULL’ 有本质区别。NULL 值表示数据未知、不适用或尚未提供。

由于 NULL 代表一个未知值，您不能使用标准比较运算符（如 =、<>、< 或 >）来测试它。使用这些运算符将任何值与 NULL 进行比较都会导致结果为 NULL，这在 WHERE 子句中被视为 ‘false’。相反，MySQL 提供了特定的运算符：IS NULL 和 IS NOT NULL。

IS NULL 运算符

IS NULL 运算符用于过滤特定列值为 NULL 的记录。

语法

SELECT column_list
FROM table_name
WHERE column_name IS NULL;

示例：设置

首先，让我们为示例创建并填充一个 CUSTOMERS 表。注意，AGE 和 SALARY 列可以接受 NULL 值。

CREATE TABLE CUSTOMERS (
    ID INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(100) NOT NULL,
    AGE INT, -- 允许 NULL 值
    ADDRESS VARCHAR(255),
    SALARY DECIMAL(10, 2) -- 允许 NULL 值
);

INSERT INTO CUSTOMERS (NAME, AGE, ADDRESS, SALARY) VALUES
('Ramesh', 32, 'Ahmedabad', NULL),
('Khilan', 25, 'Delhi', 1500.00),
('Kaushik', NULL, 'Kota', 2000.00),
('Chaitali', 25, 'Mumbai', NULL),
('Hardik', 27, 'Bhopal', 8500.00),
('Komal', NULL, 'Hyderabad', 4500.00),
('Muffy', 24, 'Indore', 10000.00);

我们的初始表如下所示：

ID	姓名	年龄	地址	薪资
1	Ramesh	32	Ahmedabad	NULL
2	Khilan	25	Delhi	1500.00
3	Kaushik	NULL	Kota	2000.00
4	Chaitali	25	Mumbai	NULL
5	Hardik	27	Bhopal	8500.00
6	Komal	NULL	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

示例：使用 IS NULL 进行 SELECT

要查找所有年龄未记录的客户，我们使用 IS NULL：

SELECT ID, NAME, AGE FROM CUSTOMERS
WHERE AGE IS NULL;

输出

ID	姓名	年龄
3	Kaushik	NULL
6	Komal	NULL

IS NOT NULL 运算符

相反地，IS NOT NULL 用于过滤列中具有非 NULL 值的记录。

示例：计算非 NULL 薪资的数量

让我们计算有多少客户有记录的薪资：

SELECT COUNT(*) AS NumberOfEmployeesWithSalary
FROM CUSTOMERS
WHERE SALARY IS NOT NULL;

输出

有薪资的员工数量
5

注意：COUNT(SALARY) 也会起作用并给出相同的结果，因为像 COUNT(column) 这样的聚合函数会忽略 NULL 值。

使用函数处理 NULL 值（COALESCE, IFNULL）

有时，您需要将 NULL 替换为默认值，以便显示或计算。COALESCE 和 IFNULL 是实现此目的的完美选择。

COALESCE(value1, value2, ...)：返回列表中第一个非 NULL 的值。 IFNULL(expr1, expr2)：如果 expr1 不是 NULL，则返回 expr1；否则，返回 expr2。

示例：使用 COALESCE

SELECT NAME, COALESCE(SALARY, 0.00) AS AdjustedSalary
FROM CUSTOMERS;

此查询显示每位客户的薪资，对于薪资为 NULL 的客户显示 0.00。

空安全相等运算符 (<=>)

MySQL 提供了一个特殊的运算符，<=>（空安全相等），它与标准 = 运算符类似，但如果两个操作数都为 NULL，则返回 1（真）；如果其中一个为 NULL，则返回 0（假）。这在特定情况下可能有用，但对于检查 NULL 值，IS NULL 更常用且可读性更好。

-- 这将返回所有 AGE 为 NULL 的行
SELECT * FROM CUSTOMERS WHERE AGE <=> NULL;

实际示例（UPDATE, DELETE）

使用 IS NULL 进行 UPDATE

让我们将所有没有薪资的客户的薪资更新为默认值 2500.00。

UPDATE CUSTOMERS
SET SALARY = 2500.00
WHERE SALARY IS NULL;

执行此查询后，将更新两条记录。您可以通过 SELECT * FROM CUSTOMERS; 进行验证。

使用 IS NULL 进行 DELETE

现在，让我们删除所有年龄未知的记录。警告：这是一个破坏性操作。

DELETE FROM CUSTOMERS
WHERE AGE IS NULL;

这将删除 ‘Kaushik’ 和 ‘Komal’ 的记录。

常见陷阱和最佳实践

• 切勿使用 = NULL 或 != NULL： 这是最常见的错误。它不会按预期工作。请始终使用 IS NULL 或 IS NOT NULL。
• 注意聚合函数： 像 SUM、AVG、COUNT(column) 这样的函数会忽略 NULL 值。COUNT(*) 会计算所有行，无论是否包含 NULL。
• 使用 NOT NULL 约束： 如果某个列应始终具有值，请在表创建时使用 NOT NULL 约束定义它，以强制执行数据完整性。
• 考虑默认值： 除了允许 NULL 值，您可以在 CREATE TABLE 语句中为列定义一个 DEFAULT 值（例如，status VARCHAR(20) NOT NULL DEFAULT 'pending'）。

在客户端应用程序中使用 IS NULL

当从应用程序与数据库交互时，使用预处理语句对于防止 SQL 注入至关重要。以下是如何安全地执行查询以查找包含 NULL 值的记录。

Node.js（使用 mysql2/promise）

本示例使用现代的 async/await 语法和流行的 mysql2 库。

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

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

    const sql = 'SELECT ID, NAME, AGE FROM CUSTOMERS WHERE AGE IS NULL';
    const [rows, fields] = await connection.execute(sql);

    console.log('Customers with unknown age:');
    console.log(rows);
    // 输出将是一个对象数组，例如 [ { ID: 3, NAME: 'Kaushik', AGE: null }, ... ]

  } catch (error) {
    console.error('Database query failed:', error);
  } finally {
    if (connection) {
      await connection.end();
    }
  }
}

findCustomersWithNullAge();

Python（使用 mysql-connector-python）

本示例使用 with 语句来确保资源得到正确管理。

import mysql.connector
from mysql.connector import Error

def find_customers_with_null_age():
    try:
        with mysql.connector.connect(
            host='localhost',
            user='root',
            password='password',
            database='TUTORIALS'
        ) as connection:
            with connection.cursor(dictionary=True) as cursor:
                query = "SELECT ID, NAME, AGE FROM CUSTOMERS WHERE AGE IS NULL"
                cursor.execute(query)
                results = cursor.fetchall()

                print('Customers with unknown age:')
                for row in results:
                    print(row)
                # 输出: {'ID': 3, 'NAME': 'Kaushik', 'AGE': None}

    except Error as e:
        print(f"Error connecting to MySQL: {e}")

find_customers_with_null_age();

Java（使用 JDBC）

本示例使用 try-with-resources 语句，这是现代 Java 自动关闭连接的最佳实践。

import java.sql.*;

public class FindNullExample {
    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 sql = "SELECT ID, NAME, AGE FROM CUSTOMERS WHERE AGE IS NULL";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            System.out.println("Customers with unknown age:");
            while (rs.next()) {
                int id = rs.getInt("ID");
                String name = rs.getString("NAME");
                // 使用 wasNull() 检查最后读取的值是否为 SQL NULL
                int age = rs.getInt("AGE");
                String ageDisplay = rs.wasNull() ? "NULL" : String.valueOf(age);

                System.out.printf("ID: %d, Name: %s, Age: %s\n", id, name, ageDisplay);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}