数据类型转换(数据库引擎)

可以按以下方案转换数据类型:

  • 当一个对象的数据移到另一个对象,或两个对象之间的数据进行比较或组合时,数据可能需要从一个对象的数据类型转换为另一个对象的数据类型。

  • 将 Transact-SQL 结果列、返回代码或输出参数中的数据移到某个程序变量中时,必须将这些数据从 SQL Server 系统数据类型转换成该变量的数据类型。

可以隐式或显式转换数据类型:

  • 隐式转换对用户不可见。

    SQL Server 会自动将数据从一种数据类型转换为另一种数据类型。例如,将 smallint 与 int 进行比较时,在比较之前 smallint 会被隐式转换为 int。请注意,查询优化器可能生成一个查询计划来在任意时间执行此转换。这可能由于转换失败(如丢失精度和尝试将非数值字符串转换为数值)而导致运行时错误。有关详细信息,请参阅故障排除查询表达式中的错误和警告

  • 显式转换使用 CAST 或 CONVERT 函数。

    CAST 和 CONVERT 函数可将值(局部变量、列或其他表达式)从一种数据类型转换为另一种数据类型。例如,以下 CAST 函数可将数值 $157.27 转换为字符串 '157.27':

    CAST ( $157.27 AS VARCHAR(10) )
    

    如果希望 Transact-SQL 程序代码符合 ISO 标准,请使用 CAST 而不要使用 CONVERT。如果要利用 CONVERT 中的样式功能,请使用 CONVERT 而不要使用 CAST。

从一个 SQL Server 对象的数据类型转换为另一种数据类型时,不支持某些隐式和显式数据类型转换。例如,nchar 值无法被转换为 image 值。nchar 只能显式转换为 binary,而不支持隐式转换为 binary。但是,nchar 既可以显式也可以隐式转换为 nvarchar。

当处理 sql_variant 数据类型时,SQL Server 支持将其他数据类型的对象隐式转换为 sql_variant 类型。但是,SQL Server 不支持从 sql_variant 数据隐式转换为其他数据类型的对象。

有关 SQL Server 对象之间支持的转换的详细信息,请参阅 CAST 和 CONVERT (Transact-SQL)

在应用程序变量与 SQL Server 结果集列、返回代码、参数或参数标记之间进行转换时,支持的数据类型转换由数据库 API 定义。有关详细信息,请参阅将数据移至程序变量

数据类型转换行为

本主题中的下列几部分说明了由下面的数据类型演示的转换行为:

binary 和 varbinary 数据

money 数据

bit 数据

decimal 和 numeric 数据

字符数据

使用 OLE 自动化存储过程转换数据类型

日期和时间数据

整数数据

float 和 real 数据

转换 binary 和 varbinary 数据

当数据从字符串数据类型(char、varchar、nchar、nvarchar、binary、varbinary、text、ntext 或 image)转换为不同长度的 binary 或 varbinary 数据类型时,SQL Server 将在数据的右侧填充或截断数据。从其他数据类型转换为 binary 或 varbinary 时,将在数据的左侧填充或截断数据。填充将通过使用十六进制的零来完成。

如果 binary 数据是最容易来回移动的数据,则将数据转换为 binary 和 varbinary 数据类型很有用。将任一类型的任一值转换为足够大的二进制值,然后转换回原类型时,如果两次转换都是在相同的 SQL Server 版本上进行的,将始终生成相同的值。值的二进制表示形式在不同 SQL Server 版本之间可能会有所不同。

可以将 int、smallint 和 tinyint 转换为 binary 或 varbinary,但是如果将 binary 转换回整数值,则在发生了截断的情况下此值将不同于原始整数值。例如,以下 SELECT 语句显示整数值 123456 通常被存储为二进制值 0x0001e240:

SELECT CAST( 123456 AS BINARY(4) )

但是,以下 SELECT 语句显示如果 binary 目标太小而不能保存整个值,则前导数字会被自动截断,以使该数值存储为 0xe240:

SELECT CAST( 123456 AS BINARY(2) )

以下批处理显示,这种自动截断会影响算术运算而不产生错误:

DECLARE @BinaryVariable2 BINARY(2)

SET @BinaryVariable2 = 123456
SET @BinaryVariable2 = @BinaryVariable2 + 1

SELECT CAST( @BinaryVariable2 AS INT)
GO

最终结果为 57921,而不是 123457。

注意注意

不能保证在 SQL Server 各个版本之间对任一数据类型与 binary 数据类型进行转换的结果是一致的。

转换为 bit 数据

转换为 bit 会将任何非零值升为 1。

转换字符数据

如果将字符表达式转换为不同大小的字符数据类型,则对于新数据类型而言过长的值将被截断。出于从字符表达式转换的目的将 uniqueidentifier 类型视为字符类型,因此在转换到字符类型时要遵循截断规则。有关详细信息,请参阅 uniqueidentifier (Transact-SQL)

如果将某个字符表达式转换为不同数据类型或大小的字符表达式(例如从 char(5) 转换为 varchar(5) 或从 char(20) 转换为 char(15)),则输入值的排序规则会被分配给经过转换的值。如果将非字符表达式转换为字符数据类型,则当前数据库的默认排序规则会被分配给经过转换的值。在任意一种情况下,都可以使用 COLLATE 子句分配特定的排序规则。

注意注意

char 和 varchar 数据类型支持代码页转换,但是 text 数据类型不支持。与 SQL Server 的早期版本一样,将不报告代码页转换期间的数据丢失。

要转换为近似 numeric 数据类型的字符表达式包含可选的指数符号 [一个大写或小写的字母 E 后跟可选的加号 (+) 或减号 (-),然后再跟一个数字]。

要转换为精确 numeric 数据类型的字符表达式必须包含数字、小数点和可选的加号 (+) 或减号 (-)。将忽略前导空格。逗号分隔符(例如 123,456.00 中的千位分隔符)在字符串中禁用。

要转换为 money 或 smallmoney 数据类型的字符表达式还可以包含可选的小数点和美元符号 ($)。可以使用逗号分隔符(如在 $123,456.00 中)。

以下示例显示了如何转换数据以便于显示。此示例在执行字符串比较之前将销售数据转换为字符数据,并将当前日期转换为样式 3,dd/mm/yy。

USE AdventureWorks2008R2;
GO
SELECT BusinessEntityID,
   CAST(SalesYTD AS varchar(12)),
   CONVERT(VARCHAR(12), GETDATE(), 3)
FROM Sales.SalesPerson
WHERE CAST(SalesYTD AS varchar(20) ) LIKE '1%';
GO

此示例将 uniqueidentifier 值转换为 char 数据类型。

DECLARE @myid uniqueidentifier
SET @myid = NEWID()
SELECT CONVERT(char(255), @myid) AS 'char';
GO

此示例将当前日期转换为样式 3,dd/mm/yy。

SELECT CONVERT(char(12), GETDATE(), 3);
GO

转换日期和时间数据

当转换为 date 和 time 数据类型时,SQL Server 将会拒绝它无法识别为日期或时间的所有值。有关所有 Transact-SQL 日期和时间数据类型及函数的概述,请参阅日期和时间函数 (Transact-SQL)

下面的示例将 date 和 datetime2 值分别转换为 varchar 和 binary 数据类型。

DECLARE @mydate date;
SET @mydate = '4/05/98';

SELECT CAST(@mydate AS varchar) AS DATE_VARCHAR;
GO

DECLARE @mydate  datetime2;
SET @mydate     = '4/05/98';

SELECT  CAST(@mydate AS binary) AS DATE_BINARY;
GO

下面是结果集:

(1 row(s) affected)

DATE_VARCHAR

------------------------------

Apr 5 1998

(1 row(s) affected)

DATE_BINARY

--------------------------------------------------------------

0x0700000000008B210B

(1 row(s) affected)

转换 float 和 real 数据

如果将 float 值转换为任一整数类型,这些值将被截断。

若要将 float 或 real 转换为字符数据,使用 STR 字符串函数通常比使用 CAST( ) 更有用。这是因为 STR 能够对格式进行更严格的控制。有关详细信息,请参阅 STR (Transact-SQL)内置函数 (Transact-SQL)

转换 money 数据

如果将整型数据类型转换为 money,则假设采用货币单位。例如,整数值 4 被转换为相当于 4 个货币单位的 money 值。

下面的示例将 smallmoney 和 money 值分别转换为 varchar 和 decimal 数据类型。

USE AdventureWorks2008R2;
GO
DECLARE @mymoney_sm smallmoney;
SET  @mymoney_sm = 3148.29;
SELECT  CAST(@mymoney_sm AS varchar) AS 'SM_MONEY varchar';
GO
DECLARE @mymoney    money;
SET  @mymoney    = 3148.29;
SELECT  CAST(@mymoney AS decimal)    AS 'MONEY DECIMAL';

GO

下面是结果集:

SM_MONEY VARCHAR

------------------------------

3148.29

(1 row(s) affected)

MONEY DECIMAL

----------------------

3148

(1 row(s) affected)

转换 decimal 和 numeric 数据

对于 decimal 和 numeric 数据类型,SQL Server 会将精度和小数位数的每个特定组合视为不同的数据类型。例如,将 decimal(5,5) 和 decimal(5,0) 视为不同的数据类型。

在 Transact-SQL 语句中,带有小数点的常量将自动转换为 numeric 数据值,而且使用必需的最小精度和小数位数。例如,常量 12.345 将被转换为精度为 5,小数位数为 3 的 numeric 值。

从 decimal 或 numeric 转换为 float 或 real 会导致精度的降低。从 int、smallint、tinyint、float、real、money 或 smallmoney 转换为 decimal 或 numeric 会导致溢出。

默认情况下,将数字转换为较低精度和小数位数的 decimal 或 numeric 值时,SQL Server 会进行舍入。但如果 SET ARITHABORT 选项为 ON,则发生溢出时,SQL Server 会产生错误。若仅降低精度和小数位数,则不会产生错误。

转换整数数据

将整数隐式转换为字符数据类型时,如果整数太大而无法容纳到字符字段中,则 SQL Server 会输入 ASCII 字符 42,即星号 (*)。

大于 2,147,483,647 的整数常量将转换为 decimal 数据类型,而不是 bigint 数据类型。下例显示当超过此阈值时,结果的数据类型将从 int 变为 decimal。

SELECT 2147483647 / 2 AS Result1, 2147483649 / 2 AS Result2 ;

下面是结果集:

Result1 Result2

1073741823 1073741824.500000

使用 OLE 自动化存储过程转换数据类型

由于 SQL Server 使用 Transact-SQL 数据类型,而 OLE 自动化使用 Visual Basic 数据类型,因此 OLE 自动化存储过程必须转换在两者之间转换数据。

下表说明了从 SQL Server 到 Visual Basic 的数据类型转换。

SQL Server 数据类型

Visual Basic 数据类型

char, varchar, text, nvarchar, ntext

String

decimal, numeric

String

bit

Boolean

binary, varbinary, image

一维 Byte() 数组

int

Long

smallint

Integer

tinyint

Byte

float

Double

real

Single

money, smallmoney

Currency

datetime, smalldatetime

Date

全部设置为 NULL

Variant 设为 Null

除了 binary、varbinary 和 image 值以外,所有单个 SQL Server 值都被转换为单个 Visual Basic 值。这些值将被转换为 Visual Basic 中的一维 Byte() 数组。此数组的范围为 Byte(0 to length1),其中 length 是 SQL Server binary、varbinary 或 image 值中的字节数。

以下是从 Visual Basic 数据类型到 SQL Server 数据类型的转换。

Visual Basic 数据类型

SQL Server 数据类型

Long, Integer, Byte, Boolean, Object

int

Double,Single

float

Currency

money

Date

datetime

小于或等于 4000 个字符的 String

varchar/nvarchar

大于 4000 个字符的 String

text/ntext

小于或等于 8000 字节的一维 Byte() 数组

varbinary

大于 8000 字节的一维 Byte() 数组

image