欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL中对于NULL值的理解和使用教程

程序员文章站 2024-02-23 20:31:40
null值的概念是造成sql的新手的混淆的普遍原因,他们经常认为null是和一个空字符串''的一样的东西。不是这样的!例如,下列语句是完全不同的: mysq...

null值的概念是造成sql的新手的混淆的普遍原因,他们经常认为null是和一个空字符串''的一样的东西。不是这样的!例如,下列语句是完全不同的:

mysql> insert into my_table (phone) values (null); 
mysql> insert into my_table (phone) values ("");

两个语句把值插入到phone列,但是第一个插入一个null值而第二个插入一个空字符串。第一个的含义可以认为是“电话号码不知道”,而第二个则可意味着“她没有电话”。

在sql中,null值在于任何其他值甚至null值比较时总是假的(false)。包含null的一个表达式总是产生一个null值,除非在包含在表达式中的运算符和函数的文档中指出。在下列例子,所有的列返回null:

mysql> select null,1+null,concat('invisible',null);

如果你想要寻找值是null的列,你不能使用=null测试。下列语句不返回任何行,因为对任何表达式,expr = null是假的:

mysql> select * from my_table where phone = null;

要想寻找null值,你必须使用is null测试。下例显示如何找出null电话号码和空的电话号码:

mysql> select * from my_table where phone is null; 
mysql> select * from my_table where phone = "";

在mysql中,就像很多其他的sql服务器一样,你不能索引可以有null值的列。你必须声明这样的列为not null,而且,你不能插入null到索引的列中。

当用load data infile读取数据时,空列用''更新。如果你想要在一个列中有null值,你应该在文本文件中使用\n。字面上的词'null'也可以在某些情形下使用。

当使用order by时,首先呈现null值。如果你用desc以降序排序,null值最后显示。当使用group by时,所有的null值被认为是相等的。

为了有助于null的处理,你能使用is null和is not null运算符和ifnull()函数。

mysql的 ifnull() 函数和oracle的 nvl() 函数功能类似。以下简单举例:
ifnull(expr1, expr2)
如果expr1不是null,ifnull()返回expr1,否则它返回expr2。ifnull()返回一个数字或字符串值,取决于它被使用的上下文环境。

mysql> select ifnull(1,0); 
    -> 1 
mysql> select ifnull(0,10); 
    -> 0 
mysql> select ifnull(1/0,10); 
    -> 10 
mysql> select ifnull(1/0,'yes'); 
    -> 'yes' 
nvl( string1, replace_with)

功能:如果string1为null,则nvl函数返回replace_with的值,否则返回string1的值。
引申一下,此nvl的作用与sqlserver 中的 isnull( string1, replace_with) 一样。
注意事项:string1和replace_with必须为同一数据类型,除非显式的使用to_char函数。
例:

nvl(to_char(numeric_column), 'some string')

其中numeric_column代指某个数字类型的值。
例:

nvl(yanlei777,0) > 0

nvl(yanlei777, 0) 的意思是 如果 yanlei777 是null,则取 0值

null与空值的辨析
来看这样一段代码:

create table `test` (
`col1` varchar( 10 ) character set utf8 collate utf8_general_ci not null ,
`col2` varchar( 10 ) character set utf8 collate utf8_general_ci null
) engine = myisam ;

错误(不可以插入null值):

insert into `test` values (null,1);

正确(插入''没问题):

insert into `test` values ('',1);
insert into `test` values ('', null);
insert into `test` values ('1', '2');

检索(''不是null,所以not null会把''都算进去):

select * from `test` where col1 is not null

结果:所有三条数据

检索(正常检索):

select * from `test` where col1 <> ''

结果:最后一条数据

检索(<>'',会同时排除''和null的数据,只检索有内容的数据):

select * from `test` where col2<>''

总结:
1. null的存储不是'',是其它特殊的字符表示。
2. null在检索<>''的时候,也会被排除,因为没有真正有意义的内容
3. not null严格按照字面意思,''不会被排除。
4. 定义not null的字段,可以插入''