Skip to content

Commit 288f2df

Browse files
author
yangjingjing
committed
init blog
1 parent 436c5b7 commit 288f2df

22 files changed

+5220
-0
lines changed

‎_posts/2017-05-30-MySQL默认值选型.md‎

Lines changed: 179 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,179 @@
1+
---
2+
layout: post
3+
categories: [SQL]
4+
description: none
5+
keywords: SQL
6+
---
7+
# MySQL默认值选型
8+
如果对一个字段没有过多要求，是使用“”还是使用 NULL，一直是个让人困惑的问题。即使有前人留下的开发规范，但是能说清原因的也没有几个。NULL 是“”吗？在辨别 NULL 是不是空的这个问题上，感觉就像是在证明 1 + 1 是不是等于 2。
9+
10+
在 MySQL 中的 NULL 是一种特殊的数据。一个字段是否允许为 NULL，字段默认值是否为 NULL。
11+
12+
主要有如下几种情况：
13+
14+
字段类型 表定义中设置方式 字段值
15+
数值类型 (INT/BIGINT) Default NULL / Default 0 NULL / NUM
16+
字符类型 (CHAR/VARCHAR) Default NULL / Default '' / Default 'ab' NULL / '' / String
17+
18+
## NULL 与空字符存储上的区别
19+
表中如果允许字段为 NULL，会为每行记录分配 NULL 标志位。NULL 除了在每行的行首存有 NULL 标志位，实际存储不占有任何空间。如果表中所有字段都是非 NULL，就不存在这个标示位了。网上有一些验证 MySQL 中 NULL 存储方式的文章，可以参考下。
20+
21+
## NULL使用上的一些问题。
22+
数值类型，对一个允许为NULL的字段进行min、max、sum、加减、order by、group by、distinct 等操作的时候。字段值为非 NULL 值时，操作很明确。如果使用 NULL， 需要清楚的知道如下规则：
23+
24+
数值类型，以 INT 列为例
25+
1) 在 min / max / sum / avg 中 NULL 值会被直接忽略掉，如下是测试结果，可能 min / max / sum 还比较可以理解，但 avg 真的是你想要的结果吗？
26+
```
27+
CREATE TABLE `t1` (
28+
`id` int(16) NOT NULL AUTO_INCREMENT,
29+
`name` varchar(20) DEFAULT NULL,
30+
`number` int(11) DEFAULT NULL,
31+
PRIMARY KEY (`id`)
32+
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
33+
34+
select * from t1;
35+
+------+----------+--------+
36+
| id | name | number |
37+
+------+----------+--------+
38+
| 1 | zhangsan | NULL |
39+
| 2 | lisi | NULL |
40+
| 3 | wangwu | 0 |
41+
| 4 | zhangliu | 4 |
42+
+------+----------+--------+
43+
44+
select max(number) from t1;
45+
+-------------+
46+
| max(number) |
47+
+-------------+
48+
| 4 |
49+
+-------------+
50+
select min(number) from t1;
51+
+-------------+
52+
| min(number) |
53+
+-------------+
54+
| 0 |
55+
+-------------+
56+
select sum(number) from t1;
57+
+-------------+
58+
| sum(number) |
59+
+-------------+
60+
| 4 |
61+
+-------------+
62+
select avg(number) from t1;
63+
+-------------+
64+
| avg(number) |
65+
+-------------+
66+
| 2.0000 |
67+
+-------------+
68+
```
69+
2) 对 NULL 做加减操作,如 1 + NULL，结果仍是 NULL
70+
```
71+
select 1+NULL;
72+
+--------+
73+
| 1+NULL |
74+
+--------+
75+
| NULL |
76+
+--------+
77+
```
78+
3) order by 以升序检索字段的时候 NULL 会排在最前面（倒序相反）
79+
```
80+
select * from t1 order by number;
81+
+----+----------+--------+
82+
| id | name | number |
83+
+----+----------+--------+
84+
| 1 | zhangsan | NULL |
85+
| 2 | lisi | NULL |
86+
| 3 | wangwu | 0 |
87+
| 4 | zhangliu | 4 |
88+
+----+----------+--------+
89+
select * from t1 order by number desc;
90+
+----+----------+--------+
91+
| id | name | number |
92+
+----+----------+--------+
93+
| 4 | zhangliu | 4 |
94+
| 3 | wangwu | 0 |
95+
| 1 | zhangsan | NULL |
96+
| 2 | lisi | NULL |
97+
+----+----------+--------+
98+
```
99+
4) group by / distinct 时，NULL 值被视为相同的值
100+
```
101+
select distinct(number) from t1;
102+
+--------+
103+
| number |
104+
+--------+
105+
| NULL |
106+
| 0 |
107+
| 4 |
108+
+--------+
109+
select number,count(*) from t1 group by number;
110+
+--------+----------+
111+
| number | count(*) |
112+
+--------+----------+
113+
| NULL | 2 |
114+
| 0 | 1 |
115+
| 4 | 1 |
116+
+--------+----------+
117+
```
118+
字符类型，在使用 NULL 值的时候，也需要格外注意
119+
1) 字段是字符时，你无法一目了然的区分这个值到底是 NULL ，还是字符串 'NULL'
120+
```
121+
insert into t1 (name,number) values ('NULL',5);
122+
insert into t1 (number) values (6);
123+
124+
select * from t1 where number in (5,6);
125+
+----+------+--------+
126+
| id | name | number |
127+
+----+------+--------+
128+
| 5 | NULL | 5 |
129+
| 6 | NULL | 6 |
130+
+----+------+--------+
131+
select name is NULL from t1 where number=5;
132+
+--------------+
133+
| name is NULL |
134+
+--------------+
135+
| 0 |
136+
+--------------+
137+
select name is NULL from t1 where number=6;
138+
+--------------+
139+
| name is NULL |
140+
+--------------+
141+
| 1 |
142+
+--------------+
143+
```
144+
2) 统计包含 NULL 字段的值，NULL 值不包括在里面
145+
```
146+
select count(*) from t1;
147+
+----------+
148+
| count(*) |
149+
+----------+
150+
| 6 |
151+
+----------+
152+
select count(name)from t1;
153+
+-------------+
154+
| count(name) |
155+
+-------------+
156+
| 5 |
157+
+-------------+
158+
select * from t1 where name is null;
159+
+----+------+--------+
160+
| id | name | number |
161+
+----+------+--------+
162+
| 6 | NULL | 6 |
163+
+----+------+--------+
164+
```
165+
3) 如果你用 length 去统计一个 VARCHAR 的长度时，NULL 返回的将不是数字
166+
```
167+
select length(name) from t1 where name is null;
168+
+--------------+
169+
| length(name) |
170+
+--------------+
171+
| NULL |
172+
+--------------+
173+
```
174+
总结：
175+
NULL 本身是一个特殊值，MySQL 采用特殊的方法来处理 NULL 值。从理解肉眼判断，操作符运算等操作上，可能和我们预期的效果不一致。可能会给我们项目上的操作不符合预期。
176+
177+
你必须要使用 IS NULL / IS NOT NULL 这种与普通 SQL 大相径庭的方式去处理 NULL。
178+
179+
尽管在存储空间上，在索引性能上可能并不比空值差，但是为了避免其身上特殊性，给项目带来不确定因素，因此建议默认值不要使用 NULL。

‎_posts/2017-06-03-MySQL数据SQL函数执行.md‎

Lines changed: 105 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,105 @@
1+
---
2+
layout: post
3+
categories: [MySQL]
4+
description: none
5+
keywords: MySQL
6+
---
7+
# MySQL数据SQL函数执行
8+
9+
## MySQL中执行一条SQL的总体流程
10+
一条包含函数的SQL语句，在mysql中会经过: 客户端发送，服务器连接，语法解析，语句执行的过程。
11+
12+
调试源码，分析函数的具体执行过程，在客户端，执行select to_char(‘test’) from dual。
13+
14+
跟踪堆栈：pthread_start→handle_one_connection→do_handle_one_connect→do_command→dispatch_command，确定SQL函数的执行入口为dispatch_command
15+
16+
调试跟踪SQL内部执行过程为：
17+
18+
## SQL函数执行过程
19+
析堆栈信息，确定SQL函数主要执行过程为：
20+
21+
SQL_PARSE 语法解析
22+
SQL_RESOLVER prepare准备执行
23+
SQL_EXCUTOR 具体执行函数
24+
SQL_PARSE堆栈：
25+
```
26+
1 To_char_instantiator::instantiate(To_char_instantiator * const this, THD * thd, PT_item_list * args) (/home/bob/work/percona-server/sql/item_create.cc:785)
27+
2 (anonymous namespace)::Function_factory<To_char_instantiator>::create_func((anonymous namespace)::Function_factory<To_char_instantiator> * const this, THD * thd, LEX_STRING function_name, PT_item_list * item_list) (/home/bob/work/percona-server/sql/item_create.cc:1203)
28+
3 PTI_function_call_generic_ident_sys::itemize(PTI_function_call_generic_ident_sys * const this, Parse_context * pc, Item ** res) (/home/bob/work/percona-server/sql/parse_tree_items.cc:259)
29+
4 PTI_expr_with_alias::itemize(PTI_expr_with_alias * const this, Parse_context * pc, Item ** res) (/home/bob/work/percona-server/sql/parse_tree_items.cc:337)
30+
5 PT_item_list::contextualize(PT_item_list * const this, Parse_context * pc) (/home/bob/work/percona-server/sql/parse_tree_helpers.h:112)
31+
6 PT_select_item_list::contextualize(PT_select_item_list * const this, Parse_context * pc) (/home/bob/work/percona-server/sql/parse_tree_nodes.cc:3813)
32+
7 PT_query_specification::contextualize(PT_query_specification * const this, Parse_context * pc) (/home/bob/work/percona-server/sql/parse_tree_nodes.cc:1551)
33+
8 PT_query_expression::contextualize(PT_query_expression * const this, Parse_context * pc) (/home/bob/work/percona-server/sql/parse_tree_nodes.cc:4178)
34+
9 PT_select_stmt::make_cmd(PT_select_stmt * const this, THD * thd) (/home/bob/work/percona-server/sql/parse_tree_nodes.cc:648)
35+
10 LEX::make_sql_cmd(LEX * const this, Parse_tree_root * parse_tree) (/home/bob/work/percona-server/sql/sql_lex.cc:5237)
36+
11 THD::sql_parser(THD * const this) (/home/bob/work/percona-server/sql/sql_class.cc:2978)
37+
12 parse_sql(THD * thd, Parser_state * parser_state, Object_creation_ctx * creation_ctx) (/home/bob/work/percona-server/sql/sql_parse.cc:7333)
38+
13 dispatch_sql_command(THD * thd, Parser_state * parser_state, bool update_userstat) (/home/bob/work/percona-server/sql/sql_parse.cc:5237)
39+
14 dispatch_command(THD * thd, const COM_DATA * com_data, enum_server_command command) (/home/bob/work/percona-server/sql/sql_parse.cc:1978)
40+
15 do_command(THD * thd) (/home/bob/work/percona-server/sql/sql_parse.cc:1426)
41+
16 handle_connection(void * arg) (/home/bob/work/percona-server/sql/conn_handler/connection_handler_per_thread.cc:307)
42+
17 pfs_spawn_thread(void * arg) (/home/bob/work/percona-server/storage/perfschema/pfs.cc:2899)
43+
18 libpthread.so.0!start_thread(void * arg) (/build/glibc-eX1tMB/glibc-2.31/nptl/pthread_create.c:477)
44+
19 libc.so.6!clone() (/build/glibc-eX1tMB/glibc-2.31/sysdeps/unix/sysv/linux/x86_64/clone.S:95)
45+
```
46+
47+
SQL_RESOLVER堆栈：
48+
```
49+
1 Item_func_to_char::resolve_type(Item_func_to_char * const this, THD * thd) (/home/bob/work/percona-server/sql/item_timefunc.cc:3821)
50+
2 Item_func::fix_fields(Item_func * const this, THD * thd) (/home/bob/work/percona-server/sql/item_func.cc:309)
51+
3 Item_str_func::fix_fields(Item_str_func * const this, THD * thd, Item ** ref) (/home/bob/work/percona-server/sql/item_strfunc.cc:161)
52+
4 setup_fields(THD * thd, ulong want_privilege, bool allow_sum_func, bool split_sum_funcs, bool column_update, const mem_root_deque<Item*> * typed_items, mem_root_deque<Item*> * fields, Ref_item_array ref_item_array) (/home/bob/work/percona-server/sql/sql_base.cc:9216)
53+
5 Query_block::prepare(Query_block * const this, THD * thd, mem_root_deque<Item*> * insert_field_list) (/home/bo6 b/work/percona-server/sql/sql_resolver.cc:275)
54+
7 Sql_cmd_select::prepare_inner(Sql_cmd_select * const this, THD * thd) (/home/bob/work/percona-server/sql/sql_select.cc:467)
55+
8 Sql_cmd_dml::prepare(Sql_cmd_dml * const this, THD * thd) (/home/bob/work/percona-server/sql/sql_select.cc:389)
56+
9 Sql_cmd_dml::execute(Sql_cmd_dml * const this, THD * thd) (/home/bob/work/percona-server/sql/sql_select.cc:522)
57+
10 mysql_execute_command(THD * thd, bool first_level) (/home/bob/work/percona-server/sql/sql_parse.cc:4740)
58+
11 dispatch_sql_command(THD * thd, Parser_state * parser_state, bool update_userstat) (/home/bob/work/percona-server/sql/sql_parse.cc:5337)
59+
12 dispatch_command(THD * thd, const COM_DATA * com_data, enum_server_command command) (/home/bob/work/percona-server/sql/sql_parse.cc:1978)
60+
13 do_command(THD * thd) (/home/bob/work/percona-server/sql/sql_parse.cc:1426)
61+
14 handle_connection(void * arg) (/home/bob/work/percona-server/sql/conn_handler/connection_handler_per_thread.cc:307)
62+
15 pfs_spawn_thread(void * arg) (/home/bob/work/percona-server/storage/perfschema/pfs.cc:2899)
63+
16 libpthread.so.0!start_thread(void * arg) (/build/glibc-eX1tMB/glibc-2.31/nptl/pthread_create.c:477)
64+
17 libc.so.6!clone() (/build/glibc-eX1tMB/glibc-2.31/sysdeps/unix/sysv/linux/x86_64/clone.S:95)
65+
```
66+
SQL_EXCUTOR 堆栈：
67+
```
68+
1 Item_func_to_char::val_str(Item_func_to_char * const this, String * str) (/home/bob/work/percona-server/sql/item_timefunc.cc:3915)
69+
2 Item::send(Item * const this, Protocol * protocol, String * buffer) (/home/bob/work/percona-server/sql/item.cc:7025)
70+
3 THD::send_result_set_row(THD * const this, const mem_root_deque<Item*> & row_items) (/home/bob/work/percona-server/sql/sql_class.cc:2793)
71+
4 Query_result_send::send_data(Query_result_send * const this, THD * thd, const mem_root_deque<Item*> & items) (/home/bob/work/percona-server/sql/query_result.cc:100)
72+
5 Query_expression::ExecuteIteratorQuery(Query_expression * const this, THD * thd) (/home/bob/work/percona-server/sql/sql_union.cc:1249)
73+
6 Query_expression::execute(Query_expression * const this, THD * thd) (/home/bob/work/percona-server/sql/sql_union.cc:1287)
74+
7 Sql_cmd_dml::execute_inner(Sql_cmd_dml * const this, THD * thd) (/home/bob/work/percona-server/sql/sql_select.cc:791)
75+
8 Sql_cmd_dml::execute(Sql_cmd_dml * const this, THD * thd) (/home/bob/work/percona-server/sql/sql_select.cc:575)
76+
9 mysql_execute_command(THD * thd, bool first_level) (/home/bob/work/percona-server/sql/sql_parse.cc:4740)
77+
10 dispatch_sql_command(THD * thd, Parser_state * parser_state, bool update_userstat) (/home/bob/work/percona-server/sql/sql_parse.cc:5337)
78+
11 dispatch_command(THD * thd, const COM_DATA * com_data, enum_server_command command) (/home/bob/work/percona-server/sql/sql_parse.cc:1978)
79+
12 do_command(THD * thd) (/home/bob/work/percona-server/sql/sql_parse.cc:1426)
80+
13 handle_connection(void * arg) (/home/bob/work/percona-server/sql/conn_handler/connection_handler_per_thread.cc:307)
81+
14 pfs_spawn_thread(void * arg) (/home/bob/work/percona-server/storage/perfschema/pfs.cc:2899)
82+
15 libpthread.so.0!start_thread(void * arg) (/build/glibc-eX1tMB/glibc-2.31/nptl/pthread_create.c:477)
83+
16 libc.so.6!clone() (/build/glibc-eX1tMB/glibc-2.31/sysdeps/unix/sysv/linux/x86_64/clone.S:95)
84+
```
85+
SQL_PARSE： SQL解析生成AST语法的语法树，to_char函数，实际中已解析为Item_func_to_char的语法树节点。
86+
87+
SQL_RESOLVER： 准备阶段prepare，初始化赋值，如Item_func_to_char::resolve_type设定函数转换后的数据类型。
88+
89+
SQL_EXCUTOR： 执行阶段，执行to_char函数功能，通过Item_func_to_char::val_str，实际功能处理过程。
90+
91+
92+
93+
94+
95+
96+
97+
98+
99+
100+
101+
102+
103+
104+
105+

0 commit comments

Comments
 (0)