MySQL NULL 值处理

在数据库操作中，NULL值是一个特殊的值，表示数据未知、不存在或者不可用。正确理解和处理NULL值对于确保数据的完整性和查询结果的准确性至关重要。

NULL值的特性

• 唯一性：NULL不同于空字符串或数值0，它是一个独立的特殊值。
• 比较：在进行比较时，任何与NULL的比较（除了IS NULL或IS NOT NULL）都会返回NULL，而不是TRUE或FALSE。
• 函数处理：许多SQL函数会忽略NULL值，除非该函数特别指明如何处理NULL。

检查NULL值

使用 IS NULL 和 IS NOT NULL

检查列是否包含NULL值最直接的方式是使用IS NULL和IS NOT NULL：

-- 查询所有姓名为空的学生
SELECT * FROM students WHERE name IS NULL;

-- 查询所有姓名不为空的学生
SELECT * FROM students WHERE name IS NOT NULL;

使用 COALESCE 函数

COALESCE()函数可以用于提供一个默认值，当遇到NULL时返回这个默认值：

-- 返回姓名，如果姓名为NULL则显示“无名氏”
SELECT COALESCE(name, '无名氏') AS student_name FROM students;

处理NULL值

使用 IFNULL 函数

IFNULL()函数允许指定当遇到NULL时应该返回的值：

-- 如果学生的年龄是NULL，则显示18岁
SELECT IFNULL(age, 18) AS student_age FROM students;

使用 NVL 函数

NVL()函数在Oracle数据库中常用于替代IFNULL，但在MySQL中并不存在。然而，可以通过自定义函数来模拟其行为：

// www.javascriptcn.com code example
DELIMITER //
CREATE FUNCTION NVL(expr1 VARCHAR(255), expr2 VARCHAR(255))
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
    RETURN IFNULL(expr1, expr2);
END//
DELIMITER ;

-- 使用自定义的NVL函数
SELECT NVL(address, '地址未知') AS student_address FROM students;

使用 IF 函数

IF()函数可用于更复杂的条件判断，例如根据NULL值决定返回什么内容：

-- 如果学生的成绩是NULL，显示“未记录”，否则显示成绩
SELECT IF(score IS NULL, '未记录', score) AS student_score FROM students;

避免NULL值

尽管NULL值有其用途，但避免不必要的NULL值通常会使数据管理和查询设计更加简单和高效。

设置默认值

在创建表时为字段设置默认值可以防止NULL值的出现：

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT DEFAULT 18,
    address VARCHAR(255) DEFAULT '地址未知'
);

使用 CHECK 约束

虽然MySQL对CHECK约束的支持有限，但某些版本仍然允许使用它来强制执行特定的验证逻辑，从而减少NULL值的产生：

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT CHECK (age > 0),
    address VARCHAR(255)
);

结论

正确地处理NULL值是数据库管理中的重要技能之一。通过理解NULL值的特性和学习各种检查及处理方法，你可以更好地维护和查询你的数据。此外，通过合理的设计表结构和添加适当的约束，还可以有效减少NULL值的出现，从而提高数据的一致性和查询效率。