MySQL编程进阶之：数据类型转换与格式化：`CAST()`和`CONVERT()`的用法。

大家好，我是数据老司机。今天咱们聊聊MySQL里的“变形金刚”——数据类型转换和格式化。 这俩兄弟，一个是CAST()，一个是CONVERT()，都是能把MySQL里的数据变个模样的魔法师。

开场白：为啥需要变形金刚？

想象一下，你从数据库里取出来的数据，就像一块未经雕琢的石头。它可能是个整数，但你想把它变成日期，或者是个日期，你想把它变成字符串，方便你展示。这时候，CAST()和CONVERT()就派上用场了。

第一部分：CAST()——强制类型转换

CAST()就像一个霸道的教练，它会强制把一个数据类型转换成你指定的类型。它的语法很简单：

CAST(expression AS datatype)

• expression: 你要转换的表达式，可以是列名、常量、或者其他表达式。
• datatype: 你要转换的目标数据类型。

1. 把字符串变成整数

假设我们有个表 products，里面有个 price 列，存的是字符串类型的价格。但我们想对价格进行数学运算，那就得先把它变成数字。

SELECT CAST(price AS SIGNED) AS price_number FROM products;

这里，SIGNED 代表有符号整数。你也可以用 UNSIGNED 代表无符号整数。

2. 把字符串变成日期

假如有个 orders 表，里面有个 order_date 列，存的是字符串类型的日期，比如 ‘2023-10-26’。要把它变成日期类型，方便我们按日期范围查询。

SELECT CAST(order_date AS DATE) AS order_date_date FROM orders;

3. 把日期变成字符串

反过来，如果你想把日期变成字符串，方便展示，也可以用 CAST()。

SELECT CAST(NOW() AS CHAR) AS now_string;

这条语句会把当前时间变成一个字符串。

4. 支持的数据类型

CAST() 支持的数据类型包括：

• BINARY[(N)]: 二进制字符串。
• CHAR[(N)]: 字符型字符串。
• DATE: 日期类型。
• DATETIME: 日期时间类型。
• DECIMAL[(M[,D])]: 精确数值类型，M是总位数，D是小数位数。
• SIGNED [INTEGER]: 有符号整数。
• UNSIGNED [INTEGER]: 无符号整数。
• TIME: 时间类型。

第二部分：CONVERT()——更灵活的转换

CONVERT() 比 CAST() 稍微灵活一点，它不仅可以转换数据类型，还可以指定字符集。它的语法是：

CONVERT(expression, datatype)
CONVERT(expression USING transcoding_name)

第一种形式和 CAST() 类似，也是转换数据类型。第二种形式是转换字符集。

1. 转换数据类型（和CAST()类似）

和 CAST() 一样， CONVERT() 也可以把字符串变成整数、日期等等。

SELECT CONVERT('123', SIGNED) AS number;
SELECT CONVERT('2023-10-26', DATE) AS date;

2. 转换字符集

这是 CONVERT() 的一个杀手锏。 假设你的数据库用的是 utf8 字符集，但你想把数据转换成 latin1 字符集。

SELECT CONVERT('你好' USING latin1);

这条语句会把 ‘你好’ 转换成 latin1 字符集。 请注意，如果你的数据包含 latin1 字符集无法表示的字符，转换可能会失败，或者出现乱码。

3. 指定字符集的例子

假设数据库默认字符集是 utf8mb4, 某个字段的数据是 latin1 编码的，查询时可能会出现乱码。 我们可以使用 CONVERT 函数转换字符集。

SELECT CONVERT(column_name USING utf8mb4) FROM table_name;

第三部分：CAST() vs CONVERT()：选哪个？

这两个函数都能实现数据类型转换，那我们该选哪个呢？

• 兼容性： CAST() 是 SQL 标准，因此在不同的数据库系统中，它的语法基本一致。 CONVERT() 是 MySQL 特有的函数，其他数据库系统可能不支持。 如果你的代码需要在不同的数据库系统之间移植，建议使用 CAST()。
• 功能： CONVERT() 比 CAST() 多了一个字符集转换的功能。 如果你需要转换字符集，只能选择 CONVERT()。
• 可读性： 个人感觉 CAST() 的语法更清晰一点， AS 关键字让代码更易读。

总的来说，如果只需要转换数据类型，并且希望代码具有更好的兼容性，建议使用 CAST()。 如果需要转换字符集，或者对 MySQL 比较熟悉，可以使用 CONVERT()。

第四部分：数据格式化

除了数据类型转换，MySQL 还提供了一些函数来格式化数据，让数据更易读。

1. DATE_FORMAT()：格式化日期

DATE_FORMAT() 函数可以将日期格式化成你想要的字符串形式。 它的语法是：

DATE_FORMAT(date, format)

• date: 要格式化的日期。
• format: 格式化字符串。

常用的格式化字符串：

格式化字符串	含义	例子
%Y	四位年份	2023
%y	两位年份	23
%m	两位月份 (01-12)	10
%c	月份 (1-12)	10
%d	两位日期 (01-31)	26
%e	日期 (1-31)	26
%H	24小时制 (00-23)	15
%h	12小时制 (01-12)	03
%i	分钟 (00-59)	30
%s	秒 (00-59)	45
%p	AM/PM	PM

例子：

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS now_formatted; -- 2023-10-26 15:30:45
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日') AS now_formatted_chinese; -- 2023年10月26日

2. FORMAT()：格式化数字

FORMAT() 函数可以将数字格式化成带千位分隔符的字符串。它的语法是：

FORMAT(number, decimals)

• number: 要格式化的数字。
• decimals: 小数位数。

例子：

SELECT FORMAT(1234567.89, 2) AS number_formatted; -- 1,234,567.89
SELECT FORMAT(1234567, 0) AS number_formatted; -- 1,234,568 (四舍五入)

3. TRUNCATE()：截断数字

TRUNCATE() 函数可以将数字截断到指定的小数位数，不会四舍五入。它的语法是：

TRUNCATE(number, decimals)

• number: 要截断的数字。
• decimals: 小数位数。

例子：

SELECT TRUNCATE(1234567.89, 2) AS number_truncated; -- 1234567.89
SELECT TRUNCATE(1234567.89, 0) AS number_truncated; -- 1234567

第五部分：实战演练

假设我们有个 employees 表，包含以下字段：

• employee_id: 员工ID (INT)
• employee_name: 员工姓名 (VARCHAR)
• hire_date: 入职日期 (DATE)
• salary: 工资 (DECIMAL)

1. 查询所有员工的姓名和入职年份

SELECT employee_name, CAST(DATE_FORMAT(hire_date, '%Y') AS SIGNED) AS hire_year FROM employees;

这里，我们先用 DATE_FORMAT() 函数提取入职日期的年份，然后用 CAST() 函数把年份转换成整数。

2. 查询所有工资大于 10000 的员工姓名和格式化后的工资

SELECT employee_name, FORMAT(salary, 2) AS formatted_salary FROM employees WHERE salary > 10000;

这里，我们用 FORMAT() 函数把工资格式化成带两位小数的字符串。

3. 统计每个月入职的员工数量

SELECT DATE_FORMAT(hire_date, '%Y-%m') AS hire_month, COUNT(*) AS employee_count FROM employees GROUP BY hire_month;

这里，我们用 DATE_FORMAT() 函数把入职日期格式化成年-月的形式，然后按月分组统计员工数量。

第六部分：注意事项

• 隐式转换： MySQL 会在某些情况下自动进行类型转换，比如字符串和数字比较时。 但最好还是显式地使用 CAST() 或 CONVERT()，避免出现意想不到的结果。
• 数据丢失： 在类型转换时，可能会发生数据丢失。 比如把浮点数转换成整数，小数部分会被截断。
• 性能： 大量的数据类型转换可能会影响查询性能。 尽量在设计数据库时选择合适的数据类型，减少类型转换的需求。
• 字符集： 在进行字符集转换时，要确保目标字符集能够表示所有的数据，否则可能会出现乱码。

总结：

CAST() 和 CONVERT() 是 MySQL 中非常重要的函数，可以帮助我们灵活地处理数据类型转换和格式化。 掌握它们，可以让我们更好地查询和处理数据。 记住，数据类型转换就像变形金刚，能让你的数据焕然一新！

最后，希望大家能灵活运用 CAST() 和 CONVERT() 这两个“变形金刚”，让你的数据在 MySQL 的世界里自由驰骋！ 今天的讲座就到这里，谢谢大家！