一文带你搞懂MySQL中的隐式类型转换和显式类型转换_Mysql

隐式转换

机制与规则

在mysql中，当操作涉及不同类型的数据时，会根据一定的规则自动进行类型转换。这种转换被称为隐式转换。

数字与字符串：如果一个数值与一个字符串进行比较或运算，mysql会尝试将字符串转换为数值。
日期与字符串：在日期相关的操作中，mysql会尝试将字符串转换为日期格式。
字符集转换：如果两个字符串属于不同的字符集，mysql会在必要时进行字符集转换。

示例代码及行为

-- 字符串到数字的转换
select '123abc' + 0; -- 结果是123，因为mysql只考虑了开头的数字部分
 
-- 比较操作中的隐式转换
select '123' = 123; -- true，因为mysql将字符串'123'转换为整数
 
-- 尝试将非数值字符串转换为数字
select 'abc' + 0; -- 结果是0，因为mysql无法识别任何有效的数字部分
 
-- 日期与字符串的比较
select '2025-04-13' > now(); -- mysql将字符串转换为日期进行比较
 
-- 字符集之间的隐式转换（如果字符集兼容）
select concat('你好', 'hello'); -- 如果数据库支持，不同字符集的字符串可以直接连接

注意事项

索引失效：隐式转换可能导致索引失效，特别是在where子句中对列进行隐式转换时，这会影响查询性能。

逻辑错误：如上述例子所示，非预期的转换可能会导致程序逻辑错误，例如将非数值字符串转换为数字通常会导致结果为0。

数据丢失：从一种类型转换为另一种类型时，可能会发生数据丢失或精度丢失。

显式转换

使用cast()和convert()

显式转换允许用户明确指定如何进行数据类型转换，这通常比隐式转换更安全且可预测。mysql提供了cast()和convert()函数用于此目的。

cast()函数

cast()是sql标准的一部分，因此它不仅限于mysql，也可以在其他数据库系统中使用。这使得cast()非常适合编写跨数据库兼容的sql代码。

语法：

select cast(expression as type);

示例：

-- 将字符串转换为日期类型
select cast('2025-04-13' as date);
 
-- 将浮点数转换为整数
select cast(123.456 as signed);
 
-- 将字符串转换为时间类型
select cast('10:15:30' as time);

convert()函数

convert()是mysql特有的函数，提供了比cast()更多的灵活性，特别是在处理字符集转换方面。convert()有两种不同的用法：一种用于类型转换，另一种用于字符集转换。

语法

-- 类型转换
select convert(expression, type);
 
-- 字符集转换
select convert(expression using charset_name);

示例：

-- 使用 convert() 进行类型转换
select convert('2025-04-13', date);
 
-- 使用 convert() 进行字符集转换
select convert('测试' using utf8mb4);
 
-- 将二进制数据转换为字符
select convert(x'4d7953514c' using utf8mb4);

主要区别与详细解析

标准化：

cast()遵循sql标准，这意味着它可以在多种数据库管理系统（dbms）中使用。
convert()是mysql特有的，尽管某些其他dbms也有类似的函数，但其具体实现可能不同。

字符集转换：

convert()支持通过using关键字进行字符集之间的转换，这是cast()不具备的功能。
示例：select convert('测试' using utf8mb4);

语法差异：

cast()使用as关键字指定目标类型，如cast('2025-04-13' as date)。
convert()直接跟目标类型或者使用using来指定字符集，如convert('测试' using utf8mb4)。

灵活性：

在一些复杂的场景下，比如需要同时改变数据类型和字符集时，convert()可能会更加灵活和强大。

应用场景

跨数据库兼容性：如果希望编写的sql代码能够在多个数据库平台之间轻松移植，优先选择cast()。

字符集转换需求：当涉及到多语言文本的数据处理，特别是需要进行字符集转换时，必须使用convert()。

类型转换：对于大多数简单的类型转换任务，两者都可以胜任，可以根据个人或团队的习惯选择使用哪一个。

选择建议

如果你需要编写跨数据库兼容的sql代码，优先考虑使用cast()。

当需要执行字符集转换时，必须使用convert()函数，并且需要指定using子句。

在其他情况下，根据个人或团队的习惯选择使用哪一个函数即可，因为它们在大多数情况下是可以互换使用的。

示例代码及行为

-- 使用 cast() 进行类型转换
select cast('2025-04-13' as date); -- 将字符串转换为日期类型
 
-- 使用 convert() 进行字符集转换
select convert('测试', char character set utf8mb4);
 
-- 将浮点数转换为整数
select cast(123.456 as signed);
 
-- 更复杂的日期时间转换
select convert('2025-04-13 10:07:00', datetime);
 
-- 使用 convert() 进行二进制转换
select convert('test', binary);