MySQL为何不建议使用默认值为null列_Mysql_

 (root@localhost mysql3306.sock)[zlm]>create table test_null( -> id int not null, -> name varchar(10) -> ); Query OK, 0 rows affected (0.02 sec) (root@localhost mysql3306.sock)[zlm]>insert into test_null values(1,'zlm'); Query OK, 1 row affected (0.00 sec) (root@localhost mysql3306.sock)[zlm]>insert into test_null values(2,null); Query OK, 1 row affected (0.00 sec) (root@localhost mysql3306.sock)[zlm]>select * from test_null; +----+------+ | id | name | +----+------+ | 1 | zlm | | 2 | NULL | +----+------+ 2 rows in set (0.00 sec) (root@localhost mysql3306.sock)[zlm]>select * from test_null where name=null; Empty set (0.00 sec) (root@localhost mysql3306.sock)[zlm]>select * from test_null where name is null; +----+------+ | id | name | +----+------+ | 2 | NULL | +----+------+ 1 row in set (0.00 sec) (root@localhost mysql3306.sock)[zlm]>select * from test_null where name is not null; +----+------+ | id | name | +----+------+ | 1 | zlm | +----+------+ 1 row in set (0.00 sec) (root@localhost mysql3306.sock)[zlm]>select * from test_null where null=null; Empty set (0.00 sec) (root@localhost mysql3306.sock)[zlm]>select * from test_null where null<>null; Empty set (0.00 sec) (root@localhost mysql3306.sock)[zlm]>select * from test_null where null<=>null; +----+------+ | id | name | +----+------+ | 1 | zlm | | 2 | NULL | +----+------+ 2 rows in set (0.00 sec) //null<=>null always return true,it's equal to "where 1=1". 

 (root@localhost mysql3306.sock)[zlm]>SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL; +-----------+---------------+------------+----------------+ | 0 IS NULL | 0 IS NOT NULL | '' IS NULL | '' IS NOT NULL | +-----------+---------------+------------+----------------+ | 0 | 1 | 0 | 1 | +-----------+---------------+------------+----------------+ 1 row in set (0.00 sec) //It's not equal to zero number or vacant string. //In MySQL,0 means fasle,1 means true. (root@localhost mysql3306.sock)[zlm]>SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL; +----------+-----------+----------+----------+ | 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL | +----------+-----------+----------+----------+ | NULL | NULL | NULL | NULL | +----------+-----------+----------+----------+ 1 row in set (0.00 sec) //It cannot be compared with number. //In MySQL,null means false,too. 

 (root@localhost mysql3306.sock)[zlm]>select ifnull(null,'First is null'),ifnull(null+10,'First is null'),ifnull(concat('abc',null),'First is null'); +------------------------------+---------------------------------+--------------------------------------------+ | ifnull(null,'First is null') | ifnull(null+10,'First is null') | ifnull(concat('abc',null),'First is null') | +------------------------------+---------------------------------+--------------------------------------------+ | First is null | First is null | First is null | +------------------------------+---------------------------------+--------------------------------------------+ 1 row in set (0.00 sec) //null value needs to be disposed with ifnull() function,what usually causes sql statement more complex. //As we all know,MySQL does not support funcion index.Therefore,indexes on the column may not be used.That's really worse.

 (root@localhost mysql3306.sock)[zlm]>select count(*),count(name) from test_null; +----------+-------------+ | count(*) | count(name) | +----------+-------------+ | 2 | 1 | +----------+-------------+ 1 row in set (0.00 sec) //count(*) returns all rows ignore the null while count(name) returns the non-null rows in column "name". //This will also leads to uncertainty if someone is unaware of the details above.

 (root@localhost mysql3306.sock)[zlm]>insert into test_null values(3,null); Query OK, 1 row affected (0.00 sec) (root@localhost mysql3306.sock)[zlm]>select distinct name from test_null; +------+ | name | +------+ | zlm | | NULL | +------+ 2 rows in set (0.00 sec) //Two rows of null value returned one and the result became two. (root@localhost mysql3306.sock)[zlm]>select name from test_null group by name; +------+ | name | +------+ | NULL | | zlm | +------+ 2 rows in set (0.00 sec) //Two rows of null value were put into the same group. //By default,group by will also sort the result(null row showed first). (root@localhost mysql3306.sock)[zlm]>select id,name from test_null order by name; +----+------+ | id | name | +----+------+ | 2 | NULL | | 3 | NULL | | 1 | zlm | +----+------+ 3 rows in set (0.00 sec) //Three rows were sorted(two null rows showed first). 

 (root@localhost mysql3306.sock)[sysbench]>show tables; +--------------------+ | Tables_in_sysbench | +--------------------+ | sbtest1 | | sbtest10 | | sbtest2 | | sbtest3 | | sbtest4 | | sbtest5 | | sbtest6 | | sbtest7 | | sbtest8 | | sbtest9 | +--------------------+ 10 rows in set (0.00 sec) (root@localhost mysql3306.sock)[sysbench]>show create table sbtest1\G *************************** 1. row *************************** Table: sbtest1 Create Table: CREATE TABLE `sbtest1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `k` int(11) NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) (root@localhost mysql3306.sock)[sysbench]>alter table sbtest1 modify k int null,modify c char(120) null,modify pad char(60) null; Query OK, 0 rows affected (4.14 sec) Records: 0 Duplicates: 0 Warnings: 0 (root@localhost mysql3306.sock)[sysbench]>insert into sbtest1 values(100001,null,null,null); Query OK, 1 row affected (0.00 sec) (root@localhost mysql3306.sock)[sysbench]>explain select id,k from sbtest1 where id=100001; +----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | sbtest1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) (root@localhost mysql3306.sock)[sysbench]>explain select id,k from sbtest1 where k is null; +----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+--------------------------+ | 1 | SIMPLE | sbtest1 | NULL | ref | k_1 | k_1 | 5 | const | 1 | 100.00 | Using where; Using index | +----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) //In the first qu
                
提示：
                    本文由神整理自网络，如有侵权请联系本站删除！
                    

                    本站声明： 

                    1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持； 

                    2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！
                
                

                

                                            
上一篇：Node-Red实现MySQL数据库连接的方法_Mysql_
                                                                
下一篇：MySQL之权限以及设计数据库案例讲解_Mysql_