mysql不推荐列默认值设置为null到底是为什么?
Java资料站
共 25935字,需浏览 52分钟
· 2021-08-14
点击上方蓝色字体,选择“标星公众号”
优质文章,第一时间送达
Preface
Introduce
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".
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.
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.
1 (root@localhost mysql3306.sock)[zlm]>select count(*),count(name) from test_null;
2 +----------+-------------+
3 | count(*) | count(name) |
4 +----------+-------------+
5 | 2 | 1 |
6 +----------+-------------+
7 1 row in set (0.00 sec)
8
9 //count(*) returns all rows ignore the null while count(name) returns the non-null rows in column "name".
10 // This will also leads to uncertainty if someone is unaware of the details above.
如果使用者对NULL属性不熟悉,很容易统计出错误的结果.
1 (root@localhost mysql3306.sock)[zlm]>insert into test_null values(3,null);
2 Query OK, 1 row affected (0.00 sec)
3
4 (root@localhost mysql3306.sock)[zlm]>select distinct name from test_null;
5 +------+
6 | name |
7 +------+
8 | zlm |
9 | NULL |
10 +------+
11 2 rows in set (0.00 sec)
12
13 //Two rows of null value returned one and the result became two.
14
15 (root@localhost mysql3306.sock)[zlm]>select name from test_null group by name;
16 +------+
17 | name |
18 +------+
19 | NULL |
20 | zlm |
21 +------+
22 2 rows in set (0.00 sec)
23
24 //Two rows of null value were put into the same group.
25 //By default,group by will also sort the result(null row showed first).
26
27 (root@localhost mysql3306.sock)[zlm]>select id,name from test_null order by name;
28 +----+------+
29 | id | name |
30 +----+------+
31 | 2 | NULL |
32 | 3 | NULL |
33 | 1 | zlm |
34 +----+------+
35 3 rows in set (0.00 sec)
36
37 //Three rows were sorted(two null rows showed first).
1 (root@localhost mysql3306.sock)[sysbench]>show tables;
2 +--------------------+
3 | Tables_in_sysbench |
4 +--------------------+
5 | sbtest1 |
6 | sbtest10 |
7 | sbtest2 |
8 | sbtest3 |
9 | sbtest4 |
10 | sbtest5 |
11 | sbtest6 |
12 | sbtest7 |
13 | sbtest8 |
14 | sbtest9 |
15 +--------------------+
16 10 rows in set (0.00 sec)
17
18 (root@localhost mysql3306.sock)[sysbench]>show create table sbtest1\G
19 *************************** 1. row ***************************
20 Table: sbtest1
21 Create Table: CREATE TABLE `sbtest1` (
22 `id` int(11) NOT NULL AUTO_INCREMENT,
23 `k` int(11) NOT NULL DEFAULT '0',
24 `c` char(120) NOT NULL DEFAULT '',
25 `pad` char(60) NOT NULL DEFAULT '',
26 PRIMARY KEY (`id`),
27 KEY `k_1` (`k`)
28 ) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8
29 1 row in set (0.00 sec)
30
31 (root@localhost mysql3306.sock)[sysbench]>alter table sbtest1 modify k int null,modify c char(120) null,modify pad char(60) null;
32 Query OK, 0 rows affected (4.14 sec)
33 Records: 0 Duplicates: 0 Warnings: 0
34
35 (root@localhost mysql3306.sock)[sysbench]>insert into sbtest1 values(100001,null,null,null);
36 Query OK, 1 row affected (0.00 sec)
37
38 (root@localhost mysql3306.sock)[sysbench]>explain select id,k from sbtest1 where id=100001;
39 +----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
40 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
41 +----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
42 | 1 | SIMPLE | sbtest1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
43 +----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
44 1 row in set, 1 warning (0.00 sec)
45
46 (root@localhost mysql3306.sock)[sysbench]>explain select id,k from sbtest1 where k is null;
47 +----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+--------------------------+
48 | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
49 +----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+--------------------------+
50 | 1 | SIMPLE | sbtest1 | NULL | ref | k_1 | k_1 | 5 | const | 1 | 100.00 | Using where; Using index |
51 +----+-------------+---------+------------+------+---------------+------+---------+-------+------+----------+--------------------------+
52 1 row in set, 1 warning (0.00 sec)
53
54 //In the first query,the newly added row is retrieved(检索) by primary key.
55 //In the second query,the newly added row is retrieved by secondary key "k_1"
56 // It has been proved that indexes can be used on the columns which contain null value.
通过explain 可以看到 mysql支持含有NULL值的列上使用索引
57 //column "k" is int datatype which occupies 4 bytes,but the value of "key_len" turn out to be 5.
// what's happed?Because null value needs 1 byte to store the null flag in the rows.
mysql> select * from test_1;
+-----------+------+------+
| name | code | id |
+-----------+------+------+
| gaoyi | wo | 1 |
| gaoyi | w | 2 |
| chuzhong | wo | 3 |
| chuzhong | w | 4 |
| xiaoxue | dd | 5 |
| xiaoxue | dfdf | 6 |
| sujianhui | su | 99 |
| sujianhui | NULL | 99 |
+-----------+------+------+
8 rows in set (0.00 sec)
mysql> explain select * from test_1 where code is NULL;
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | test_1 | NULL | ref | index_code | index_code | 161 | const | 1 | 100.00 | Using index condition |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from test_1 where code is not NULL;
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | test_1 | NULL | range | index_code | index_code | 161 | NULL | 7 | 100.00 | Using index condition |
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from test_1 where code='dd';
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | test_1 | NULL | ref | index_code | index_code | 161 | const | 1 | 100.00 | Using index condition |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from test_1 where code like "dd%";
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | test_1 | NULL | range | index_code | index_code | 161 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+--------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
Summary 总结
作者 | aaron8219
来源 | cnblogs.com/aaron8219/p/9259379.html
评论
某大公司为逼迫员工离职,竟然把他的工位安排到厕所旁,没想到他直接开始记录领导的如厕时间,还发到公司大群...
上一篇:字节的跳动职级与薪资(2024年)我们与公司间的合作,宛如两艘船只在茫茫大海上相互依靠,共同抵御风浪,携手驶向成功的彼岸。然而,当航向开始产生分歧,或是波涛汹涌的风浪改变了我们的初衷,我们或许应当冷静地选择和平分手,而非在风雨中硬撑。最近,一位网友的遭遇引起了广大职场人的关注和热议。这位网友
开发者全社区
0
BigDecimal 为什么可以保证精度不丢失?
来源:juejin.cn/post/7348709938023940136👉 欢迎加入小哈的星球 ,你将获得: 专属的项目实战 / Java 学习路线 / 一对一提问 / 学习打卡 / 赠书福利全栈前后端分离博客项目 2.0 版本完结啦, 演示链接
小哈学Java
0
纪不懂法,云浩止耕:要种地,先交钱
“我是建华镇党委副书记,我姓纪叫纪云浩,听懂了吗?等会车我们一定要扣走,地不属于你们的…”、“别找我,我不懂法“。。。“纪不懂法,云浩止耕”所说的就是最近非常火的内蒙古自治区通辽市开鲁县建华镇党委副书记、政法委员:纪云浩,而他还是一位90后,07年入伍,09年转业。。。春耕,农民的希望与挑战。又到一
机器学习算法工程师
0
新规!不授予学位!博士毕业更难了?
来源:阿秒富友研究院编辑:学妹据4月22日科技日报消息,全国人大常委会法工委发言人杨合庆在近日举行的记者会上表示,即将提请十四届全国人大常委会第九次会议审议的学位法草案二次审议稿将进一步完善学位授予条件和程序,并对保障博士学位质量作出专门规定。杨合庆介绍,2023年8月,十四届全国人大常委会第五次会
机器学习初学者
0
推荐 | 文本分析库cntext2.x使用手册
Tips: 公众号推送后内容只能更改一次,且只能改20字符。 如果内容出问题,或者想更新内容, 只能重复推送。 为了更好的阅读体验,建议阅读本文博客版, 链接地址https://textdata.cn/blog/2024-04-27-cntext2x-usage-tutorial/cntext是大邓
大邓和他的Python
1
为什么我们公司还在用 Python 开发项目?
作者:哇哒嘻哇https://www.zhihu.com/question/278798145/answer/3416549119最近几年里,经常看到某些曾重度使用 Python 的大公司迁移成其它语言技术栈,但是,那些小公司/小团队的情况如何呢?一直很想了解那些仍在坚持使用 Python,且支撑业
机器学习算法与Python实战
0
21.3K star!推荐一款可视化自动化测试/爬虫/数据采集神器!功能免费且强大!
【温馨提示】由于公众号更改了推送规则,不再按照时间顺序排列,如果不想错过测试开发技术精心准备的的干货文章,请将测试开发技术设为“星标☆”,看完文章在文尾处点亮“在看”!大家好,我是狂师!在大数据时代,信息的获取与分析变得尤为重要。对于开发者、数据分析师乃至非技术人员来说,能够高效地采集网络数据并进行
测试开发技术
4
【第128期】提升编程效率VSCode变量命名插件推荐
概述 在编程的世界里,一个好的变量名不仅能够提升代码的可读性,还能反映出程序员的专业水平。Visual Studio Code(VSCode)作为一个广受欢迎的代码编辑器,拥有丰富的插件生态系统,其中不乏能够帮助我们高效命名变量的工具。今天,我们就来介绍几款VSCode上能够提升变量命名效率的插件
前端微服务
0