- ubuntu12.04环境下使用kvm ioctl接口实现最简单的虚拟机
- Ubuntu 通过无线网络安装Ubuntu Server启动系统后连接无线网络的方法
- 在Ubuntu上搭建网桥的方法
- ubuntu 虚拟机上网方式及相关配置详解
CFSDN坚持开源创造价值,我们致力于搭建一个资源共享平台,让每一个IT人在这里找到属于你的精彩世界.
这篇CFSDN的博客文章MySQL为何不建议使用默认值为null列由作者收集整理,如果你对这篇文章有兴趣,记得点赞哟.
通常能听到的答案是使用了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值操作符主要有三个. 。
Example 。
Null never returns true when comparing with any other values except null with “<=>”. NULL通过任一操作符与其它值比较都会得到NULL,除了<=>. 。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
|
(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"
.
|
Null means “a missing and unknown value”.Let's see details below. NULL代表一个不确定的值,就算是两个NULL,它俩也不一定相等.(像不像C中未初始化的局部变量) 。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
(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.
|
It truns null as a result if any expression contains null value. 任何有返回值的表达式中有NULL参与时,都会得到另外一个NULL值. 。
1
2
3
4
5
6
7
8
9
10
|
(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.
|
It's diffrent when using count(*) & count(null column). 使用count(*) 或者 count(null column)结果不同,count(null column)<=count(*). 。
1
2
3
4
5
6
7
8
9
10
|
(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.
|
如果使用者对NULL属性不熟悉,很容易统计出错误的结果. 。
When using distinct,group by,order by,all null values are considered as the same value. 虽然select NULL=NULL的结果为false,但是在我们使用distinct,group by,order by时,NULL又被认为是相同值. 。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
(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
).
|
MySQL supports to use index on column which contains null value(what's different from oracle). MySQL中支持在含有NULL值的列上使用索引,但是Oracle不支持.这就是我们平时所说的如果列上含有NULL那么将会使索引失效. 严格来说,这句话对与MySQL来说是不准确的. 。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
|
(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
query,the newly added row
is
retrieved
by
primary
key
.
//
In
the
second
query,the newly added row
is
retrieved
by
secondary
key
"k_1"
//It has been proved that indexes can be used
on
the columns which contain
null
value.
//
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
.
|
这个是我自己测试的例子. 。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
|
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)
|
null value always leads to many uncertainties when disposing sql statement.It may cause bad performance accidentally. 。
列中使用NULL值容易引发不受控制的事情发生,有时候还会严重托慢系统的性能. 。
例如
null value will not be estimated in aggregate function() which may cause inaccurate results. 对含有NULL值的列进行统计计算,eg. count(),max(),min(),结果并不符合我们的期望值. 。
null value will influence the behavior of the operations such as “distinct”,“group by”,“order by” which causes wrong sort. 干扰排序,分组,去重结果. 。
null value needs ifnull() function to do judgement which makes the program code more complex. 有的时候为了消除NULL带来的技术债务,我们需要在SQL中使用IFNULL()来确保结果可控,但是这使程序变得复杂. null value needs a extra 1 byte to store the null information in the rows. 。
NULL值并是占用原有的字段空间存储,而是额外申请一个字节去标注,这个字段添加了NULL约束.(就像额外的标志位一样) As these above drawbacks,it's not recommended to define columns with default null. We recommand to define “not null” on all columns and use zero number & vacant string to substitute relevant data type of null. 。
根据以上缺点,我们并不推荐在列中设置NULL作为列的默认值,你可以使用NOT NULL消除默认设置,使用0或者''空字符串来代替NULL. 。
https://www.cnblogs.com/aaron8219/p/9259379.html 。
到此这篇关于MySQL为何不建议使用默认值为null列的文章就介绍到这了,更多相关MySQL默认值为null内容请搜索我以前的文章或继续浏览下面的相关文章希望大家以后多多支持我! 。
原文链接:https://blog.csdn.net/qq_30549099/article/details/107395521 。
最后此篇关于MySQL为何不建议使用默认值为null列的文章就讲到这里了,如果你想了解更多关于MySQL为何不建议使用默认值为null列的内容请搜索CFSDN的文章或继续浏览相关文章,希望大家以后支持我的博客! 。
最近开始学习oracle和sql。 在学习的过程中,我遇到了几个问题,我的 friend 在接受采访时被问到这些问题。 SELECT * FROM Employees WHERE NULL IS N
这个问题在这里已经有了答案: Can we subtract NULL pointers? (4 个回答) 关闭 2 个月前。 是否定义了NULL - NULL? (char *)NULL - (ch
是否有推荐的方法(根据 .net Framework 指南)检查 null,例如: if (value == null) {//code1} else {//code2} 或 if (value !=
我正在尝试将值插入数据库,但出现这样的错误任何人都可以告诉我为什么该值为空,如下所示: An exception occurred while executing 'INSERT INTO perso
这个问题在这里已经有了答案: String concatenation with a null seems to nullify the entire string - is that desire
您好,我正在 Android 联系人搜索模块中工作。我正在查询下方运行。 cur = context.getContentResolver().query(ContactsContract.Data.
下面的 SQL 表定义说明了从我的 MYSQL 数据库创建表的语句之一,该数据库是由我公司的前开发人员开发的。 DROP TABLE IF EXISTS `classifieds`.`category
我主要有应用程序开发背景。在编程语言中 variable == null或 variable != null有效。 当涉及到 SQL 时,以下查询不会给出任何语法错误,但也不会返回正确的结果。 sel
我在尝试检查某些元素是否为 NULL 时遇到段错误或不。任何人都可以帮忙吗? void addEdge(int i, int j) { if (i >= 0 && j > 0)
在 SQL 服务器中考虑到以下事实:Col1 和 Col2 包含数值和 NULL 值 SELECT COALESCE(Col1,Col2) 返回一个错误:“COALESCE 的至少一个参数必须是一个不
在 SQL 服务器中考虑到以下事实:Col1 和 Col2 包含数值和 NULL 值 SELECT COALESCE(Col1,Col2) 返回一个错误:“COALESCE 的至少一个参数必须是一个不
下面查询的关系代数表达式是什么?我找不到“Is Null”的表达式。 SELECT reader.name FROM reader LEFT JOIN book_borrow ON reader.ca
我正在尝试使用三元运算符来检查值是否为 null 并返回一个表达式或另一个。将此合并到 LINQ 表达式时,我遇到的是 LINQ 表达式的 Transact-SQL 转换试图执行“column = n
我在给定的代码中看到了以下行: select(0, (fd_set *) NULL, (fd_set *) NULL, (fd_set *) NULL, &timeout); http://linux
var re = /null/g; re.test('null null'); //> true re.test('null null'); //> true re.test('null null')
这个问题在这里已经有了答案: 关闭 13 年前。 我今天避开了一场关于数据库中空值的激烈辩论。 我的观点是 null 是未指定值的极好指示符。团队中有意见的其他每个人都认为零和空字符串是可行的方法。
由于此错误,我无法在模拟器中运行我的应用: Error:null value in entry: streamOutputFolder=null 或 gradle - Error:null value
我正在尝试在 Android 应用程序中创建电影数据库,但它返回错误。知道这意味着什么吗? public Cursor returnData() { return db.query(TABLE
我一直在检查浏览器中的日期函数以及运行时间 new Date (null, null, null); 在开发工具控制台中,它给出了有效的日期 Chrome v 61 回归 Sun Dec 31 189
为什么 NA==NULL 会导致 logical (0) 而不是 FALSE? 为什么 NULL==NULL 会导致 logical(0) 而不是 TRUE? 最佳答案 NULL 是一个“零长度”对象
我是一名优秀的程序员,十分优秀!