2019-12-24

# 浅析 MySQL 的隐式转换

[作者简介] 陈晓，信息部订单组研发工程师，目前主要负责小米订单中台业务。

• 场景一

• 场景二

## 什么是隐式类型转换

MySQL 中对隐式转换的定义：

When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly.

### 比较操作时 MySQL 隐式类型转换规则

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.

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

### 根据规则看下示例

The server includes dtoa, a conversion library that provides the basis for improved conversion > between string or DECIMAL values and approximate-value (FLOAT/DOUBLE) numbers:

Consistent conversion results across platforms, which eliminates, for example, Unix versus > Windows conversion differences.

Accurate representation of values in cases where results previously did not provide sufficient > precision, such as for values close to IEEE limits.

Conversion of numbers to string format with the best possible precision. The precision of dtoa > is always the same or better than that of the standard C library functions.

MySQL 里使用了 dtoa 这个转换类库来实现字符跟浮点数的相互转换，一是提高了浮点数的精度（相比 c 标准库）, 二是保持平台一致性。

1801537632024345856

## 其他情况

mysql server 处理调用栈

item 和 field 分别对应的数据

## 附录

### 关于浮点数的问题

Problems with Floating-Point Values

### 关于进制问题

Round-to-Even for Floating Point

Round-To-Even 在于 To-Up , To-Down, To-towards-Zero 对比中，在一定数据量基础上，更加精准。To-Up 的平均值比真实数值偏大，To-Down 偏小。