mysql 隐式转换
类型系统
type and program language 这本书介绍了类型系统. 什么是类型系统呢?
为什么会有隐式转换
我写了很久弱类型语言,一直遇到各种隐式转换
但是最近(2020/04/26)我大概知道隐式转换的本质了 . 说到底,弱类型语言也是有类型的语言,变量是有类型的.变量就是一个类型集合里面的一个元素
举个例子
比如一个集合颜色{red,yellow,white,blue}
不同类型的变量说到底也不是一个集合的内容,是没法直接比较.那么编译器就帮你做了一个映射,映射成一个类型,然后可以比较了,就那么简单.
那么隐式转换的问题是什么呢?其实是开发人员可能没有注意到发生了隐式转换,执行路径和预期不一致
那么隐式转换的好处是什么呢?可以少写很多代码, 可以更快
这本书讲了表达式和求值
sql也是一种弱类型语言,所以也有弱类型的大坑隐式转换
mysql的类型系统有人详细描述过吗?或者有相关的文档来说明吗?就像jls一样,可能是我没有看完完整的mysql文档吧
mysql类型
mysql类型分为以下几种:
- numeric
- date and time
- string
- json
例子
select count(case when number_col='' OR number_col IS NULL THEN 1 END) FROM test;
假如number_col列是数字类型(比如int),则会发生隐式转换 number_col = ''里面,空字符串''会转换成 0
隐式转换在什么时候发生?
相关sql
select 1='222';
Thread 28 "mysqld" hit Breakpoint 1, my_strtod (str=0x7f3a500061d0 "222", end=0x7f3ad4d46998, error=0x7f3ad4d469bc) at /home/dinosaur/Downloads/mysql-5.7.21/strings/dtoa.c:465
465 {
(gdb) bt
#0 my_strtod (str=0x7f3a500061d0 "222", end=0x7f3ad4d46998, error=0x7f3ad4d469bc) at /home/dinosaur/Downloads/mysql-5.7.21/strings/dtoa.c:465
#1 0x0000000001f7279d in my_strntod_8bit (cs=0x2e8ea60 <my_charset_utf8_general_ci>, str=0x7f3a500061d0 "222", length=3, end=0x7f3ad4d46998, err=0x7f3ad4d469bc)
at /home/dinosaur/Downloads/mysql-5.7.21/strings/ctype-simple.c:741
#2 0x0000000000fdaaf2 in double_from_string_with_check (cs=0x2e8ea60 <my_charset_utf8_general_ci>, cptr=0x7f3a500061d0 "222", end=0x7f3a500061d3 "")
at /home/dinosaur/Downloads/mysql-5.7.21/sql/item.cc:3577
#3 0x0000000000fdacc5 in Item_string::val_real (this=0x7f3a500061d8) at /home/dinosaur/Downloads/mysql-5.7.21/sql/item.cc:3594
#4 0x0000000000f9e9b9 in Item::val_result (this=0x7f3a500061d8) at /home/dinosaur/Downloads/mysql-5.7.21/sql/item.h:1592
#5 0x0000000000fedf4b in Item_cache_real::cache_value (this=0x7f3a50006928) at /home/dinosaur/Downloads/mysql-5.7.21/sql/item.cc:10089
#6 0x0000000000fec91a in Item_cache::has_value (this=0x7f3a50006928) at /home/dinosaur/Downloads/mysql-5.7.21/sql/item.cc:9650
#7 0x0000000000fedfbb in Item_cache_real::val_real (this=0x7f3a50006928) at /home/dinosaur/Downloads/mysql-5.7.21/sql/item.cc:10098
#8 0x0000000000fff539 in Arg_comparator::compare_real (this=0x7f3a500065f8) at /home/dinosaur/Downloads/mysql-5.7.21/sql/item_cmpfunc.cc:1748
#9 0x0000000001014cc8 in Arg_comparator::compare (this=0x7f3a500065f8) at /home/dinosaur/Downloads/mysql-5.7.21/sql/item_cmpfunc.h:92
#10 0x00000000010017e7 in Item_func_eq::val_int (this=0x7f3a50006520) at /home/dinosaur/Downloads/mysql-5.7.21/sql/item_cmpfunc.cc:2507
#11 0x0000000000fe6144 in Item::send (this=0x7f3a50006520, protocol=0x7f3a50001d10, buffer=0x7f3ad4d46e10) at /home/dinosaur/Downloads/mysql-5.7.21/sql/item.cc:7563
#12 0x00000000015d4c48 in THD::send_result_set_row (this=0x7f3a50000b70, row_items=0x7f3a500058c8) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_class.cc:4677
#13 0x00000000015ceed3 in Query_result_send::send_data (this=0x7f3a50006770, items=...) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_class.cc:2717
#14 0x00000000015e697a in JOIN::exec (this=0x7f3a500069f0) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_executor.cc:158
#15 0x00000000016892ba in handle_query (thd=0x7f3a50000b70, lex=0x7f3a50002e78, result=0x7f3a50006770, added_options=0, removed_options=0)
at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_select.cc:184
#16 0x000000000163939e in execute_sqlcom_select (thd=0x7f3a50000b70, all_tables=0x0) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_parse.cc:5156
#17 0x0000000001632405 in mysql_execute_command (thd=0x7f3a50000b70, first_level=true) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_parse.cc:2792
#18 0x000000000163a31c in mysql_parse (thd=0x7f3a50000b70, parser_state=0x7f3ad4d48550) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_parse.cc:5582
#19 0x000000000162f0a3 in dispatch_command (thd=0x7f3a50000b70, com_data=0x7f3ad4d48e00, command=COM_QUERY) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_parse.cc:1458
#20 0x000000000162df32 in do_command (thd=0x7f3a50000b70) at /home/dinosaur/Downloads/mysql-5.7.21/sql/sql_parse.cc:999
#21 0x0000000001770f97 in handle_connection (arg=0x5271810) at /home/dinosaur/Downloads/mysql-5.7.21/sql/conn_handler/connection_handler_per_thread.cc:300
#22 0x0000000001de0b41 in pfs_spawn_thread (arg=0x526e200) at /home/dinosaur/Downloads/mysql-5.7.21/storage/perfschema/pfs.cc:2190
#23 0x00007f3ade33b6ba in start_thread (arg=0x7f3ad4d49700) at pthread_create.c:333
#24 0x00007f3add76d41d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109
隐式转换规则
If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe <=> equality comparison operator. For NULL <=> NULL, the result is true. No conversion is needed.
If both arguments in a comparison operation are strings, they are compared as strings.
If both arguments are integers, they are compared as integers.
Hexadecimal values are treated as binary strings if not compared to a number.
If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. This is not done for the arguments to IN(). To be safe, always use complete datetime, date, or time strings when doing comparisons. For example, to achieve best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type.
A single-row subquery from a table or tables is not considered a constant. For example, if a subquery returns an integer to be compared to a DATETIME value, the comparison is done as two integers. The integer is not converted to a temporal value. To compare the operands as DATETIME values, use CAST() to explicitly convert the subquery value to DATETIME.
If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value.
In all other cases, the arguments are compared as floating-point (real) numbers. For example, a comparison of string and numeric operands takes places as a comparison of floating-point numbers.
mysql 隐式转换可能不走索引
文档只描述了字符串转数字的情况
举例
下面是表的例子先看表的样子,表里面underlying_code 是varchar类型
show create table `base_underlying_information`
CREATE TABLE `base_underlying_information` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`sec_id` varchar(10) NOT NULL COMMENT '标的ID',
`uni_code` varchar(30) NOT NULL COMMENT '标识，规则code-last-type',
`underlying_code` varchar(50) NOT NULL COMMENT '标的代码',
... 省略一堆其他字段
PRIMARY KEY (`id`),
KEY `idx_underlying_code` (`underlying_code`),
) ENGINE=InnoDB CHARSET=utf8
```
- 隐式转换的时候
当sql中 条件是数字而 `603023`的时候
```
EXPLAIN SELECT * FROM `base_underlying_information` WHERE underlying_code = 603023
```
这时候的explain 是发现没有走索引
因为满足以下条件
> In all other cases, the arguments are compared as floating-point (real) numbers. For example, **a comparison of string and numeric operands takes places as a comparison of floating-point numbers.**
```
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE base_underlying_information \N ALL idx_underlying_code \N \N \N 506079 10.00 Using where
```
发生了隐式转换
下面是文档的描述
> For comparisons of a string column with a number, MySQL cannot use an index on the column to look up the value quickly. If str_col is an indexed string column, the index cannot be used when performing the lookup in the following statement:
```
SELECT * FROM tbl_name WHERE str_col=1;
```
> The reason for this is that there are many different strings that may convert to the value 1, such as '1', ' 1', or '1a'.
- 没有隐式转换的时候
因为表里面是varchar 条件里面也是varchar 所以是没有隐式转换
```
EXPLAIN SELECT * FROM `base_underlying_information` WHERE underlying_code = '603023'
```
```
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE base_underlying_information \N ref idx_underlying_code idx_underlying_code 152 const 1 100.00 \N
```
看了一下词法分析好像没有做转换的,看了一下词法分析也没有做,那应该是运行时的时候做的,那是哪个函数呢?
- http://postgres.cn/docs/9.6/extend-type-system.html
- https://dev.mysql.com/doc/refman/8.0/en/date-and-time-literals.html
- https://blog.csdn.net/n88Lpo/article/details/101013055
- https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html