关于Mysql的隐式转换的笔记

之前遇到关于round()函数计算四舍五入的内容，涉及到Mysql隐式转换的规则，以及隐式转换可能产生的影响。

隐式转换发生的情景：

查询参数类型与数据库字段类型不一致时存在隐式转换

隐式转换的一些规则：

首先从官网获得的隐式转换发生的描述：

The following rules describe how conversion occurs for comparison operations:

1. 有NULL的情况一般不需要做转换，因为大多数的运算结果都是NULL，在进行大于等于，小于等于的情况下，null和null做比较可以得到true的返回。

• 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.

1. String和String比较按照string的比较规则处理，整数和整数比较，按照整数的规则处理。

• If both arguments in a comparison operation are strings, they are compared as strings.
• If both arguments are integers, they are compared as integers.

1. 十六进制的值会转作二进制的string类型

• Hexadecimal values are treated as binary strings if not compared to a number.

1. 时间字段和常数进行比较的时候，比较执行前常数转化为时间戳

• 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.

1. 但是如果遇到了时间戳和子查询返回的常数结果进行比较得时候，则是时间戳方进行隐式转换，形成常数和常数比较。

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.

1. 如果其中一个参数是十进制有精度得decimal类型，则将decimal转换成另一个参数得数据类型。但是，如果比较的是decimal和其他数值类型例如int类型、float类型，则都是将另一个参数转换成decimal(变成decimal和decimal比较)

• 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.

1. 其他就都按照双精度得float类型进行比较，例如，比较string和numeric类型得值，实际是两个值都转化成float，再做运算

• In all other cases, the arguments are compared as floating-point (double-precision) numbers. For example, a comparison of string and numeric operands takes place as a comparison of floating-point numbers.

综上，根据上面对文档的理解，隐式转换可以归纳出如下规则：

1. 两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回 1，这两种情况都不需要做类型转换
2. 两个参数都是字符串，会按照字符串来比较，不做类型转换
3. 两个参数都是整数，按照整数来比较，不做类型转换
4. 十六进制的值和非数字做比较时，会被当做二进制串
5. 有一个参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp
6. 有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较
7. 所有其他情况下，两个参数都会被转换为浮点数再进行比较。

varchar类型隐式转换的问题

遗留待处理的问题：“数据类型的转换从varchar类型变成了float或者double（猜想应该是float，隐式转换的细节需要另外再去求证）“

回顾一下当时的场景：Round（）函数内部是一个varchar类型字段与decimal类型做运算，导致结果变成了一个float

这种情况适用于“所有其他情况下，两个参数都会被转换为浮点数再进行比较“这类情况。官方文档中确实也是这么描述的“a comparison of string and numeric operands takes place as a comparison of floating-point numbers“

隐式转换可能存在的隐患

1. 导致索引失效：

如下面这个案例，string类型的str_col列上如有和整型常数的比较的where条件就会导致本来str_col上的索引失效。

因为索引是按照string本身来排列的，但是比较中会先进行转换且多种string可能转换成同一个结果，转换完后再进行比较，转换结果不适用原有的基于str_col的索引排序。

• 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;

1. 由于转换可能导致筛选出不正确的结果，导致误删除或者误修改

发生这个问题的原因是，多种原始值肯会通过隐式转换变成同一个结果，例如字符串的隐式转换中，如果字符串以数字开头且全部都是数字，则转换为数字结果是整个字符串；字符串部分是数字，则转换为数字结果是截止到第一个不是数字的字符为止。