在MySQL中,NULL字段的处理,需要注意,当在处理查询条件中有NULL,很有可能你得到的值不是想要的,因为,在MySQL中,判断NULL值相等(=)或者不等(!=)都会返回false。主要出现在常见的SELECT以及WHERE字句中。
为了处理这种特殊的情况,MySQL提供了如下的关键字进行特殊处理:
IS NULL: 当列的值是NULL,此运算符返回true。
IS NOT NULL: 当列的值不为NULL, 运算符返回true。
<=>: 比较操作符(不同于=运算符),当比较的的两个值为NULL时返回true。
关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。
在MySQL中,NULL值与任何其它值的比较(即使是NULL)永远返回false,即 NULL = NULL 返回false 。
下面看看例子,就很清楚的理解是什么意思了。
先在test数据库中创建一个表checknull。
1 mysql> use test
2 Database changed
3 mysql> show tables;
4 Empty set (0.00 sec)
5
6 mysql> create table checknull(
7 -> name varchar(30) not null,
8 -> age int);
9 Query OK, 0 rows affected (0.11 sec)
我们看看这个表的创建基本信息,用show和desc分别查看:
1 mysql> show create table checknull;
2 +-----------+-------------------------------------------------------------------------------------------------------------------------------+
3 | Table | Create Table |
4 +-----------+-------------------------------------------------------------------------------------------------------------------------------+
5 | checknull | CREATE TABLE `checknull` (
6 `name` varchar(30) NOT NULL,
7 `age` int(11) DEFAULT NULL
8 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
9 +-----------+-------------------------------------------------------------------------------------------------------------------------------+
10 1 row in set (0.01 sec)
11
12 mysql> desc checknull;
13 +-------+-------------+------+-----+---------+-------+
14 | Field | Type | Null | Key | Default | Extra |
15 +-------+-------------+------+-----+---------+-------+
16 | name | varchar(30) | NO | | NULL | |
17 | age | int(11) | YES | | NULL | |
18 +-------+-------------+------+-----+---------+-------+
19 2 rows in set (0.00 sec)
我们开始测试一下,第一步,向这个表中插入数据:
1 mysql> insert checknull (name, age) values("water", 30);
2 Query OK, 1 row affected (0.00 sec)
3
4 mysql>
5 mysql> insert checknull (name, age) values("shihuc", NULL);
6 Query OK, 1 row affected (0.00 sec)
7
8 mysql>
9 mysql> select * from checknull;
10 +--------+------+
11 | name | age |
12 +--------+------+
13 | water | 30 |
14 | shihuc | NULL |
15 +--------+------+
16 2 rows in set (0.00 sec)
接下来,再查询看看,先查询所有的age字段是NULL的用户信息:
1 mysql> select * from checknull where age =NULL;
2 Empty set (0.00 sec)
3
4 mysql> select * from checknull where age IS NULL;
5 +--------+------+
6 | name | age |
7 +--------+------+
8 | shihuc | NULL |
9 +--------+------+
10 1 row in set (0.00 sec)
1 mysql> select * from checknull where age !=NULL;
2 Empty set (0.00 sec)
3
4 mysql> select * from checknull where age IS NOT NULL;
5 +-------+------+
6 | name | age |
7 +-------+------+
8 | water | 30 |
9 +-------+------+
10 1 row in set (0.00 sec)
是不是发现,结果不同?那么我现在,通过修改大小写来查看查询结果:
1 mysql> select * from checknull where age IS null;
2 +--------+------+
3 | name | age |
4 +--------+------+
5 | shihuc | NULL |
6 +--------+------+
7 1 row in set (0.00 sec)
8
9 mysql> select * from checknull where age = null;
10 Empty set (0.00 sec)
1 mysql> select * from checknull where age != null;
2 Empty set (0.00 sec)
3
4 mysql> select * from checknull where age is not null;
5 +-------+------+
6 | name | age |
7 +-------+------+
8 | water | 30 |
9 +-------+------+
10 1 row in set (0.00 sec)
发现结果和上面的日志反映的内容一样。
这里补充一个小tips,那就是在Linux下,mysql默认情况下,数据库的名字,表的名字和字段的名字是区分大小写的,但是字段的值是不区分大小写的。
表的名字和字段的名字是否区分大小写,可以查看数据库变量lower_case_table_names的值,0表示区分大小写;1表示不区分,统一按照小写对待。
1 mysql> show variables like "%case%";
2 +------------------------+-------+
3 | Variable_name | Value |
4 +------------------------+-------+
5 | lower_case_file_system | OFF |
6 | lower_case_table_names | 0 |
7 +------------------------+-------+
8 2 rows in set (0.00 sec)
而对于字段的值,想要区分大小写,可以使用BINARY加以限制。不管是在创建表的时候,还是在查询的条件字句中都可以使用。
1 mysql> create table lowupper(
2 -> name varchar(30) not null,
3 -> age int,
4 -> home varchar(40) binary);
5 Query OK, 0 rows affected (0.10 sec)
6
7 mysql> show create table lowupper;
8 +----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
9 | Table | Create Table |
10 +----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
11 | lowupper | CREATE TABLE `lowupper` (
12 `name` varchar(30) NOT NULL,
13 `age` int(11) DEFAULT NULL,
14 `home` varchar(40) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL
15 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
16 +----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
17 1 row in set (0.00 sec)
上面日志中,可以看到,创建表的时候,
在home字段后面加了约束binary,通过show可以看到,home字段有一个校对规则 latin1_bin,
说明,这个会在插入/查询数据的时候是区分大小写的。
下面插入数据做些验证:
1 mysql> insert lowupper (name, age, home) values ("shihuc", 30, "Beijing, china");
2 Query OK, 1 row affected (0.00 sec)
3
4 mysql>
5 mysql> insert lowupper (name, age, home) values ("water", 33, "BEIJING, china");
6 Query OK, 1 row affected (0.00 sec)
7
8 mysql> insert lowupper (name, age, home) values ("xiaocheng", 33, "hubei");
9 Query OK, 1 row affected (0.00 sec)
10
11 mysql> insert lowupper (name, age, home) values ("zhangsan", null, "china");
12 Query OK, 1 row affected (0.00 sec)
13
14 mysql> insert lowupper (name, age, home) values ("lisi", null, "CHINA");
15 Query OK, 1 row affected (0.00 sec)
16
17 mysql> insert lowupper (name, age, home) values ("wangwu", null, "China");
18 Query OK, 1 row affected (0.00 sec)
19
20 mysql> select * from lowupper;
21 +-----------+------+----------------+
22 | name | age | home |
23 +-----------+------+----------------+
24 | shihuc | 30 | Beijing, china |
25 | water | 33 | BEIJING, china |
26 | xiaocheng | 33 | hubei |
27 | zhangsan | NULL | china |
28 | lisi | NULL | CHINA |
29 | wangwu | NULL | China |
30 +-----------+------+----------------+
31 6 rows in set (0.00 sec)
下面,再查询一下看看,是否有区分:
1 mysql> select * from lowupper where home = "china";
2 +----------+------+-------+
3 | name | age | home |
4 +----------+------+-------+
5 | zhangsan | NULL | china |
6 +----------+------+-------+
7 1 row in set (0.00 sec)
再操作上面checknull表,在其中插入一条新的数据,进行查询,看是否区分大小写:
1 mysql> insert checknull (name, age) values ("SHIHUC", null);
2 Query OK, 1 row affected (0.00 sec)
3
4 mysql> select * from checknull;
5 +--------+------+
6 | name | age |
7 +--------+------+
8 | water | 30 |
9 | shihuc | NULL |
10 | SHIHUC | NULL |
11 +--------+------+
12 3 rows in set (0.00 sec)
13
14 mysql> select * from checknull where name = "shihuc";
15 +--------+------+
16 | name | age |
17 +--------+------+
18 | shihuc | NULL |
19 | SHIHUC | NULL |
20 +--------+------+
21 2 rows in set (0.00 sec)
22
23 mysql> select * from checknull where binaryname = "shihuc";
24 +--------+------+
25 | name | age |
26 +--------+------+
27 | shihuc | NULL |
28 +--------+------+
29 1 row in set (0.00 sec)
是不是很显然的,说明MySQL的大小写问题,还是很有意思的,需要注意,在linux环境下。windows环境下,没有测试,不是很确定。有经验的可以分享一下!
http://www.cnblogs.com/shihuc/p/5165169.html