Mariadb 简明教程
MariaDB - Null Values
在处理 NULL 值时，记住它们是未知值。它们不是有效值，即空字符串或零。在表创建中，列规范允许设置它们以接受空值或拒绝空值。只需使用 NULL 或 NOT NULL 子句。这在缺少记录信息（如 ID 编号）的情况下有应用。
When working with NULL values, remember they are unknown values. They are not empty strings or zero, which are valid values. In table creation, column specifications allow for setting them to accept null values, or reject them. Simply utilize a NULL or NOT NULL clause. This has applications in cases of missing record information like an ID number.
用户定义变量在明确赋值之前具有 NULL 值。存储例程参数和局部变量允许设置 NULL 值。当局部变量没有默认值时，它具有 NULL 值。
User-defined variables have a value of NULL until explicit assignment. Stored routine parameters and local variables allow setting a value of NULL. When a local variable has no default value, it has a value of NULL.
NULL 不区分大小写，并且具有以下别名 -
NULL is case-insensitive, and has the following aliases −
-
UNKNOWN (a boolean value)
-
\N
NULL Operators
标准比较运算符不能与 NULL 一起使用（例如 =、>、>=、⇐、< 或 !=），因为对 NULL 值的所有比较都返回 NULL，而不是 true 或 false。与 NULL 的比较或可能包含 NULL 的比较必须使用“<⇒”（NULL 安全）运算符。
Standard comparison operators cannot be used with NULL (e.g., =, >, >=, ⇐, <, or !=) because all comparisons with a NULL value return NULL, not true or false. Comparisons with NULL or possibly containing it must use the “<⇒” (NULL-SAFE) operator.
其他可用的运算符有 -
Other available operators are −
-
IS NULL − It tests for a NULL value.
-
IS NOT NULL − It confirms the absence of a NULL value.
-
ISNULL − It returns a value of 1 on discovery of a NULL value, and 0 in its absence.
-
COALESCE − It returns the first non-NULL value of a list, or it returns a NULL value in the absence of one.
Sorting NULL Values
在排序操作中，NULL 值具有最低的值，因此 DESC 顺序将 NULL 值放在底部。MariaDB 允许为 NULL 值设置更高的值。
In sorting operations, NULL values have the lowest value, so DESC order results in NULL values at the bottom. MariaDB allows for setting a higher value for NULL values.
有两种方法可以做到这一点，如下所示 -
There are two ways to do this as shown below −
SELECT column1 FROM product_tbl ORDER BY ISNULL(column1), column1;另一种方法 -
The other way −
SELECT column1 FROM product_tbl ORDER BY IF(column1 IS NULL, 0, 1), column1 DESC;NULL Functions
当任何参数为 NULL 时，函数通常会输出 NULL。但是，有一些函数专门设计用于管理 NULL 值。它们是 -
Functions generally output NULL when any parameters are NULL. However, there are functions specifically designed for managing NULL values. They are −
-
IFNULL() − If the first expression is not NULL it returns it. When it evaluates to NULL, it returns the second expression.
-
NULLIF() − It returns NULL when the compared expressions are equal, if not, it returns the first expression.
SUM 和 AVG 等函数会忽略 NULL 值。
Functions like SUM and AVG ignore NULL values.
Inserting NULL Values
在声明为 NOT NULL 的列中插入 NULL 值时，会发生错误。在默认 SQL 模式下，NOT NULL 列将改为基于数据类型插入默认值。
On insertion of a NULL value in a column declared NOT NULL, an error occurs. In default SQL mode, a NOT NULL column will instead insert a default value based on data type.
当字段为 TIMESTAMP、AUTO_INCREMENT 或虚拟列时，MariaDB 会以不同的方式管理 NULL 值。在 AUTO_INCREMENT 列中插入会导致序列中的下一个数字插入到它所在的位置。在 TIMESTAMP 字段中，MariaDB 会为此分配当前时间戳。在本教程的后面部分讨论的虚拟列中，会分配默认值。
When a field is a TIMESTAMP, AUTO_INCREMENT, or virtual column, MariaDB manages NULL values differently. Insertion in an AUTO_INCREMENT column causes the next number in the sequence to insert in its place. In a TIMESTAMP field, MariaDB assigns the current timestamp instead. In virtual columns, a topic discussed later in this tutorial, the default value is assigned.
唯一索引可以保存许多 NULL 值，但主键不能为 NULL。
UNIQUE indices can hold many NULL values, however, primary keys cannot be NULL.