浅谈 MySQL 的隐式类型转换

前段时间进行生产数据处理时，遇到了一个令人迷惑的问题！

我在做两个表的关联更新时，源表中某字段的值为 10 ，更新到新表相应字段中，值变成了 10.00 。

update a, b set a.amount = b.amount where a.b_id = b.b_id;

在经过一番排查后，我发现 b 中该字段的类型是 decimal(18,2)，而 a 表却是 VARCHAR(4)。

当然，两个表中相同含义的字段应该使用相同的类型，这里不讨论最佳实践

我使用的数据库连接工具，在 decimal 的小数位是 0 的时候，不显示小数位。比如 10.00，会显示 10。

这个场景中，amount 字段表示某种配置，根据我们的数据库规范，应该使用 VARHCAR 类型。

所以，我想当然的以为 b 表中的 amount 字段就是 VARCHAR 类型的某种配置。

然后，在执行了上面的语句后，a 表中的 amount 字段插入的是 10.00。

这也就是说，decimal 类型在转换成 VARCHAR 类型时，会保留整数和小数部分。

这引起了我对 MySQL 隐式类型转换的好奇，所以翻了下官方文档。

对于比较运算符，当两侧的数据类型不一致时，会发生类型转换，主要规则如下：

• 如果两个参数都是 NULL，除了 <=>，结果都是 NULL。

  -- NULL
  select null = null;
  select null > null;
  ​
  -- 1
  select null <=> null;

• 如果两个参数都是字符串，那么他们会按照字符串的比较规则进行比较。

• 如果两个参数都是整数，那么他们会按照整数的比较规则进行比较。

• 16 进制的值如果不是跟数字进行比较，则会被当做二进制串处理。

-- 1
select 0x1 = 1;

​

• 如果其中一个参数类型是 decimal，另一个是 decimal 或整数，则按照 decimal 进行运算；如果另一个是浮点型，则按照浮点型进行运算。 

• 其他情况，都按照浮点类型的比较处理。如字符串和数据的比较。

  -- 0
  select '1' > 2;
  select '1' > 2.0

日期的比较比较复杂，本次先不讨论。

 

特别注意，对于字符串和数字进行比较运算时，MySQL 无法使用索引，而且可能出现不可预料的结果，如

CREATE TABLE `str_test` (
  `str` varchar(45) DEFAULT NULL,
  KEY `idx_str` (`str`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
​
insert into test.str_test values(' 1'),('1'),('1a');
​
-- 查询结果为 "1"
select * from test.str_test where str = '1';
-- 查询结果为 " 1"、"1"、"1a"
select * from test.str_test where str = 1;