为什么 MySQL 不推荐默认值为 null ？
发布时间：2022-08-09 11:22:56 所属栏目：MySql教程 来源：互联网
导读：通常能听到的答案是使用了NULL值的列将会使索引失效,但是如果实际测试过一下,你就知道IS NULL会使用索引.所以上述说法有漏洞。 着急的人拉到最下边看结论 Preface Null is a special constraint of columns. The columns in table will be added null constr
|
通常能听到的答案是使用了NULL值的列将会使索引失效,但是如果实际测试过一下,你就知道IS NULL会使用索引.所以上述说法有漏洞。 着急的人拉到最下边看结论 Preface Null is a special constraint of columns. The columns in table will be added null constrain if you do not define the column with “not null” key words explicitly when creating the table.Many programmers like to define columns by default because of the conveniences(reducing the judgement code of nullibility) what consequently cause some uncertainty of query and poor performance of database. NULL值是一种对列的特殊约束,我们创建一个新列时,如果没有明确的使用关键字not null声明该数据列,Mysql会默认的为我们添加上NULL约束。 有些开发人员在创建数据表时,由于懒惰直接使用Mysql的默认推荐设置.(即允许字段使用NULL值).而这一陋习很容易在使用NULL的场景中得出不确定的查询结果以及引起数据库性能的下降。 Introduce Null is null means it is not anything at all,we cannot think of null is equal to ‘’ and they are totally different. MySQL provides three operators to handle null value:“IS NULL”,“IS NOT NULL”,"<=>" and a function ifnull(). IS NULL: It returns true,if the column value is null. IS NOT NULL: It returns true,if the columns value is not null. <=>: It’s a compare operator similar with “=” but not the same.It returns true even for the two null values. (eg. null <=> null is legal) IFNULL(): Specify two input parameters,if the first is null value then returns the second one. It’s similar with Oracle’s NVL() function. NULL并不意味着什么都没有,我们要注意 NULL 跟 ''(空值)是两个完全不一样的值.MySQL中可以操作NULL值操作符主要有三个。 IS NULL IS NOT NULL <=> 太空船操作符,这个操作符很像=,select NULL<=>NULL可以返回true,但是select NULL=NULL返回false. IFNULL 一个函数.怎么使用自己查吧…反正我会了 Example Null never returns true when comparing with any other values except null with “<=>”. NULL通过任一操作符与其它值比较都会得到NULL,除了<=>. 1 (root@localhost mysql3306.sock)[zlm]>create table test_null( 2 -> id int not null, 3 -> name varchar(10) 4 -> ); 5 Query OK, 0 rows affected (0.02 sec) 6 7 (root@localhost mysql3306.sock)[zlm]>insert into test_null values(1,'zlm'); 8 Query OK, 1 row affected (0.00 sec) 9 10 (root@localhost mysql3306.sock)[zlm]>insert into test_null values(2,null); 11 Query OK, 1 row affected (0.00 sec) 12 13 (root@localhost mysql3306.sock)[zlm]>select * from test_null; 14 +----+------+ 15 | id | name | 16 +----+------+ 17 | 1 | zlm | 18 | 2 | NULL | 19 +----+------+ 20 2 rows in set (0.00 sec) 21 // -------------------------------------->这个很有代表性<---------------------- 22 (root@localhost mysql3306.sock)[zlm]>select * from test_null where name=null; 23 Empty set (0.00 sec) 24 25 (root@localhost mysql3306.sock)[zlm]>select * from test_null where name is null; 26 +----+------+ 27 | id | name | 28 +----+------+ 29 | 2 | NULL | 30 +----+------+ 31 1 row in set (0.00 sec) 32 33 (root@localhost mysql3306.sock)[zlm]>select * from test_null where name is not null; 34 +----+------+ 35 | id | name | 36 +----+------+ 37 | 1 | zlm | 38 +----+------+ 39 1 row in set (0.00 sec) 40 41 (root@localhost mysql3306.sock)[zlm]>select * from test_null where null=null; 42 Empty set (0.00 sec) 43 44 (root@localhost mysql3306.sock)[zlm]>select * from test_null where null<>null; 45 Empty set (0.00 sec) 46 47 (root@localhost mysql3306.sock)[zlm]>select * from test_null where null<=>null; 48 +----+------+ 49 | id | name | 50 +----+------+ 51 | 1 | zlm | 52 | 2 | NULL | 53 +----+------+ 54 2 rows in set (0.00 sec) 55 //null<=>null always return true,it's equal to "where 1=1". 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455 Null means “a missing and unknown value”.Let’s see details below. NULL代表一个不确定的值,就算是两个NULL,它俩也不一定相等.(像不像C中未初始化的局部变量) 1 (root@localhost mysql3306.sock)[zlm]>SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL; 2 +-----------+---------------+------------+----------------+ 3 | 0 IS NULL | 0 IS NOT NULL | '' IS NULL | '' IS NOT NULL | 4 +-----------+---------------+------------+----------------+ 5 | 0 | 1 | 0 | 1 | 6 +-----------+---------------+------------+----------------+ 7 1 row in set (0.00 sec) 8 9 //It's not equal to zero number or vacant string. 10 //In MySQL,0 means fasle,1 means true. 11 12 (root@localhost mysql3306.sock)[zlm]>SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL; 13 +----------+-----------+----------+----------+ 14 | 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL | 15 +----------+-----------+----------+----------+ 16 | NULL | NULL | NULL | NULL | 17 +----------+-----------+----------+----------+ 18 1 row in set (0.00 sec) 19 20 //It cannot be compared with number. 21 //In MySQL,null means false,too. 123456789101112131415161718192021 It truns null as a result if any expression contains null value. 任何有返回值的表达式中有NULL参与时,都会得到另外一个NULL值. 1 (root@localhost mysql3306.sock)[zlm]>select ifnull(null,'First is null'),ifnull(null+10,'First is null'),ifnull(concat('abc',null),'First is null'); 2 +------------------------------+---------------------------------+--------------------------------------------+ 3 | ifnull(null,'First is null') | ifnull(null+10,'First is null') | ifnull(concat('abc',null),'First is null') | 4 +------------------------------+---------------------------------+--------------------------------------------+ 5 | First is null | First is null | First is null | 6 +------------------------------+---------------------------------+--------------------------------------------+ 7 1 row in set (0.00 sec) 8 9 //null value needs to be disposed with ifnull() function,what usually causes sql statement more complex. 10 //As we all know,MySQL does not support funcion index.Therefore,indexes on the column may not be used.That's really worse. 12345678910 It’s diffrent when using count() & count(null column). 使用count(*) 或者 count(null column)结果不同,count(null column)<=count(*). （编辑：伊春站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
