MySQL - IS NOT NULL 运算符

MySQL：掌握 IS NOT NULL 运算符

在数据库管理中，NULL 值表示数据缺失或未知。重要的是要理解，NULL 不同于数字零 (0) 或空字符串 (“)。它表示没有任何值。

为了在查询中有效地处理 NULL 值，MySQL 提供了两个特定的运算符：

IS NULL：检查值是否为 NULL。
IS NOT NULL：检查值是否不为 NULL。

本教程将重点介绍 `IS NOT NULL` 运算符，它是过滤记录和确保数据完整性的基本工具。

`IS NOT NULL` 运算符

IS NOT NULL 运算符是一个条件运算符，用于 SELECT、UPDATE 和 DELETE 语句的 WHERE 子句。其目的是过滤记录，确保特定列包含实际值而不是 NULL。

语法

SELECT column1, column2, ...
FROM table_name
WHERE column_name IS NOT NULL;

设置：创建示例表

让我们设置一个 CUSTOMERS 表来演示示例。有些记录会故意在 AGE 和 SALARY 列中包含 NULL 值。

-- 创建 CUSTOMERS 表
CREATE TABLE CUSTOMERS(
    ID INT AUTO_INCREMENT,
    NAME VARCHAR(20) NOT NULL,
    AGE INT, -- 允许 NULL 值
    ADDRESS VARCHAR(255),
    SALARY DECIMAL(18, 2), -- 允许 NULL 值
    PRIMARY KEY(ID)
);

-- 插入示例数据
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);

示例：选择非 NULL 记录

以下查询检索所有年龄已知（即 AGE 列不为 NULL）的客户。

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

输出

ID	姓名	年龄	薪水
1	Ramesh	32	NULL
2	Khilan	25	1500.00
4	Chaitali	25	NULL
5	Hardik	27	8500.00
7	Muffy	24	10000.00

与其他语句的高级用法

与 `COUNT()` 结合使用

你可以将 IS NOT NULL 与 COUNT() 等聚合函数结合使用。请注意，COUNT(*) 计算符合 WHERE 子句的所有行，而 COUNT(column_name) 仅计算该特定列中非 NULL 的值。

此查询计算有记录薪水的客户数：

-- 这会计算 SALARY 不为 NULL 的行。
SELECT COUNT(*) AS NumberOfSalariesRecorded
FROM CUSTOMERS
WHERE SALARY IS NOT NULL;

-- 达到相同目的更直接的方法：
SELECT COUNT(SALARY) AS NumberOfSalariesRecorded
FROM CUSTOMERS;

与 `UPDATE` 结合使用

你可以使用 IS NOT NULL 有条件地更新行。例如，我们为所有有薪水记录的客户发放奖金。

UPDATE CUSTOMERS
SET SALARY = SALARY * 1.10 -- 10% 奖金
WHERE SALARY IS NOT NULL;

与 `DELETE` 结合使用

类似地，你可以用它来删除记录。例如，删除所有有记录年龄的客户：

-- 使用 DELETE 语句时要小心！
DELETE FROM CUSTOMERS
WHERE AGE IS NOT NULL;

最佳实践和常见陷阱

陷阱：`col != value` 与 `IS NOT NULL`

一个常见错误是尝试使用 <> 或 != 等比较运算符过滤 NULL 值。NULL 代表一种未知状态，因此它不能与任何其他值进行比较。任何与 NULL 的比较（例如，AGE != 30 或 AGE = NULL）都会产生 UNKNOWN 结果，并且该行将从结果集中排除。

-- 错误：这不会返回年龄为 NULL 的客户。
SELECT * FROM CUSTOMERS WHERE AGE != 32;

-- 正确：要检查非 NULL 值，请始终使用 IS NOT NULL。
SELECT * FROM CUSTOMERS WHERE AGE IS NOT NULL;

最佳实践：模式设计

在可能的情况下，在表创建时将列定义为 NOT NULL 并提供 DEFAULT 值。这可以在数据库层面强制实施数据完整性，并减少应用程序逻辑中对 NULL 检查的需求。

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

在应用程序代码中使用 `IS NOT NULL`

以下是如何从各种编程语言执行 IS NOT NULL 查询的现代、安全示例。关键的最佳实践是使用预处理语句（或参数化查询）来防止 SQL 注入。

Node.js (使用 mysql2)
Python (使用 mysql-connector-python)
Java (使用 JDBC)
PHP (使用 mysqli)



### 设置
安装 `mysql2` 包：`npm install mysql2`

### 代码
此示例使用 `async/await` 以实现更简洁的异步代码，并使用连接池以获得更好的性能。

```javascript
// 文件名：queryNotNull.js
const mysql = require('mysql2/promise');

async function getCustomersWithAge() {
  let connection;
  try {
    // 使用连接池高效管理连接
    const pool = mysql.createPool({
      host: 'localhost',
      user: 'root',
      password: 'password',
      database: 'TUTORIALS',
      waitForConnections: true,
      connectionLimit: 10,
      queueLimit: 0
    });

    console.log('已连接到数据库！');

    const sql = "SELECT ID, NAME, AGE FROM CUSTOMERS WHERE AGE IS NOT NULL";

    // 连接池处理连接的获取和释放
    const [rows, fields] = await pool.query(sql);

    console.log('有记录年龄的客户：');
    if (rows.length === 0) {
      console.log('未找到记录。');
    } else {
      rows.forEach(row => {
        console.log(`ID: ${row.ID}, Name: ${row.NAME}, Age: ${row.AGE}`);
      });
    }

    // 应用程序关闭时应关闭连接池
    await pool.end();

  } catch (error) {
    console.error('数据库查询失败：', error);
  }
}

getCustomersWithAge();

输出

Connected to database!
Customers with a recorded age:
ID: 1, Name: Ramesh, Age: 32
ID: 2, Name: Khilan, Age: 25
ID: 4, Name: Chaitali, Age: 25
ID: 5, Name: Hardik, Age: 27
ID: 7, Name: Muffy, Age: 24

设置

安装官方 MySQL 连接器：pip install mysql-connector-python

代码

此示例使用 with 语句来确保连接和游标自动关闭。

import mysql.connector
from mysql.connector import Error

def get_customers_with_age():
    """ 连接到 MySQL 并获取年龄不为空的客户。 """
    try:
        # 对连接使用上下文管理器
        with mysql.connector.connect(
            host='localhost',
            user='root',
            password='password',
            database='TUTORIALS'
        ) as connection:
            print('已连接到数据库！')
            sql_query = "SELECT ID, NAME, AGE FROM CUSTOMERS WHERE AGE IS NOT NULL"

            # 对游标使用上下文管理器
            with connection.cursor(dictionary=True) as cursor:
                cursor.execute(sql_query)
                results = cursor.fetchall()

                print('有记录年龄的客户：')
                if not results:
                    print('未找到记录。')
                else:
                    for row in results:
                        print(f"ID: {row['ID']}, Name: {row['NAME']}, Age: {row['AGE']}")

    except Error as e:
        print(f"连接到 MySQL 时出错: {e}")

if __name__ == "__main__":
    get_customers_with_age()

输出

Connected to database!
Customers with a recorded age:
ID: 1, Name: Ramesh, Age: 32
ID: 2, Name: Khilan, Age: 25
ID: 4, Name: Chaitali, Age: 25
ID: 5, Name: Hardik, Age: 27
ID: 7, Name: Muffy, Age: 24

设置

确保你的项目类路径中包含 MySQL JDBC 驱动程序（例如 mysql-connector-java-8.x.x.jar）。

代码

此示例使用 try-with-resources 自动管理 Connection、Statement 和 ResultSet 等资源，从而防止资源泄漏。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;

public class QueryNotNull {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/TUTORIALS";
    private static final String USER = "root";
    private static final String PASS = "password";

    public static void main(String[] args) {
        String sql = "SELECT ID, NAME, AGE FROM CUSTOMERS WHERE AGE IS NOT NULL";

        // 使用 try-with-resources 进行自动资源管理
        try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            System.out.println("已连接到数据库！");
            System.out.println("有记录年龄的客户：");

            boolean found = false;
            while (rs.next()) {
                found = true;
                int id = rs.getInt("ID");
                String name = rs.getString("NAME");
                int age = rs.getInt("AGE");
                System.out.printf("ID: %d, Name: %s, Age: %d%n", id, name, age);
            }
            if (!found) {
                System.out.println("未找到记录。");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

输出

Connected to database!
Customers with a recorded age:
ID: 1, Name: Ramesh, Age: 32
ID: 2, Name: Khilan, Age: 25
ID: 4, Name: Chaitali, Age: 25
ID: 5, Name: Hardik, Age: 27
ID: 7, Name: Muffy, Age: 24

设置

确保你的 php.ini 文件中启用了 mysqli 扩展。

代码

此示例使用带有错误检查的现代面向对象 mysqli。

<?php
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';

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

// 检查连接错误
if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

printf("已连接到数据库！\n");

$sql = "SELECT ID, NAME, AGE FROM CUSTOMERS WHERE AGE IS NOT NULL";

// 执行查询
if ($result = $mysqli->query($sql)) {
    echo "有记录年龄的客户:\n";
    if ($result->num_rows > 0) {
        // 将结果作为关联数组获取
        while($row = $result->fetch_assoc()) {
            printf("ID: %d, Name: %s, Age: %d\n",
                $row["ID"],
                $row["NAME"],
                $row["AGE"]
            );
        }
    } else {
        echo "未找到记录。\n";
    }
    // 释放结果集
    $result->free();
} else {
    printf("Query failed: %s\n", $mysqli->error);
}

// 关闭连接
$mysqli->close();

?>

输出

Connected to database!
Customers with a recorded age:
ID: 1, Name: Ramesh, Age: 32
ID: 2, Name: Khilan, Age: 25
ID: 4, Name: Chaitali, Age: 25
ID: 5, Name: Hardik, Age: 27
ID: 7, Name: Muffy, Age: 24

MySQL - IS NOT NULL 运算符

MySQL：掌握 IS NOT NULL 运算符

IS NOT NULL 运算符

语法

设置：创建示例表

示例：选择非 NULL 记录

输出

与其他语句的高级用法

与 COUNT() 结合使用

与 UPDATE 结合使用

与 DELETE 结合使用