sql-conversion-functions

SQL - 数据类型转换函数

数据类型转换（或称“类型转换”，casting）是 SQL 中的一项基本操作。它用于将一个值从一种数据类型显式转换为另一种。这通常在比较或连接不同数据类型的列、执行计算或格式化数据以供显示时是必要的。

标准函数与特定数据库函数

ANSI SQL 标准提供了 CAST 函数，这是数据类型转换中最具可移植性且支持最广泛的方法。然而，许多数据库系统提供了自己的专有函数，例如 SQL Server 中的 CONVERT，它们可能提供额外的格式化功能。

主要转换函数

下面是最常见的转换函数及其典型用例的比较。

函数	主要系统	描述和用例
CAST(expression AS datatype)	ANSI 标准	转换类型最便携的方式。如果无法转换，则抛出错误。用于将字符串转换为数字、数字转换为字符串或字符串转换为日期。
CONVERT(datatype, expression, [style])	SQL Server	类似于 CAST，但包含一个可选的 style 参数，用于将日期和数字格式化为特定的字符串表示（例如，‘dd/mm/yyyy’）。
TRY_CAST(expression AS datatype)	SQL Server, Azure SQL	CAST 的安全版本。它尝试转换并在失败时返回 NULL，而不是抛出错误。适用于清理混乱或不可靠的数据。
TRY_CONVERT(datatype, expression, [style])	SQL Server, Azure SQL	CONVERT 的安全版本。转换失败时返回 NULL。在格式化可能无效的数据时很有用。
expression::datatype	PostgreSQL	PostgreSQL 特有的简洁、非标准类型转换语法。功能上等同于 CAST(expression AS datatype)。

示例：使用 CAST

想象一下，一个表中价格被错误地存储为文本。要执行计算，你必须将其转换为数值类型。

-- 将数值数据存储为 VARCHAR 是一种常见的反模式
CREATE TABLE product_listings (product_name VARCHAR(100), price_text VARCHAR(20));
INSERT INTO product_listings VALUES ('Laptop', '1299.99'), ('Mouse', '25');

-- 我们必须将 price_text 转换为数值类型才能计算 10% 的税
SELECT
    product_name,
    price_text,
    CAST(price_text AS DECIMAL(10, 2)) * 1.10 AS price_with_tax
FROM product_listings;

示例：使用 TRY_CAST 进行安全转换

现在，考虑一下如果数据混乱会发生什么。使用 CAST 会导致查询失败。

-- 添加一行无效数据
INSERT INTO product_listings VALUES ('Keyboard', 'Not Available');

-- 使用 CAST 时，此查询将因错误而失败
-- SELECT CAST(price_text AS DECIMAL(10, 2)) FROM product_listings;

-- 使用 TRY_CAST（在 SQL Server 中）可以优雅地处理错误
SELECT
    product_name,
    TRY_CAST(price_text AS DECIMAL(10, 2)) AS price_numeric
FROM product_listings;

-- 输出将是：
-- Laptop, 1299.99
-- Mouse, 25.00
-- Keyboard, NULL

最佳实践

• 使用正确的数据类型设计：最好的解决方案是从一开始就在表模式中使用正确的数据类型。避免将数字或日期存储为字符串。
• 优先使用标准 CAST：为了在不同数据库系统之间实现最大程度的兼容性，请使用标准的 CAST 函数，除非你特别需要 CONVERT 等专有函数的样式代码功能。
• 对不可靠数据使用安全转换：在处理来自外部源或用户输入的数据时，使用 TRY_CAST 等安全转换函数来防止查询失败并优雅地处理无效数据。