数据类型转换(数据库引擎)Data type conversion (Database Engine)

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse

在以下情况下,可以转换数据类型:Data types can be converted in the following scenarios:

  • 当一个对象的数据移到另一个对象,或两个对象之间的数据进行比较或组合时,数据可能必须从一个对象的数据类型转换为另一个对象的数据类型。When data from one object is moved to, compared with, or combined with data from another object, the data may have to be converted from the data type of one object to the data type of the other.
  • Transact-SQLTransact-SQL 结果列、返回代码或输出参数中的数据移到某个程序变量中时,必须将这些数据从 SQL ServerSQL Server 系统数据类型转换成该变量的数据类型。When data from a Transact-SQLTransact-SQL result column, return code, or output parameter is moved into a program variable, the data must be converted from the SQL ServerSQL Server system data type to the data type of the variable.

在应用程序变量与 SQL ServerSQL Server 结果集列、返回代码、参数或参数标记之间进行转换时,支持的数据类型转换由数据库 API 定义。When you convert between an application variable and a SQL ServerSQL Server result set column, return code, parameter, or parameter marker, the supported data type conversions are defined by the database API.

隐式和显式转换Implicit and explicit conversion

可以隐式或显式转换数据类型。Data types can be converted either implicitly or explicitly.

隐式转换对用户不可见。Implicit conversions are not visible to the user. SQL ServerSQL Server 会自动将数据从一种数据类型转换为另一种数据类型。automatically converts the data from one data type to another. 例如,将 smallint 与 int 进行比较时,在比较之前,smallint 会被隐式转换为 int。For example, when a smallint is compared to an int, the smallint is implicitly converted to int before the comparison proceeds.

GETDATE() 隐式转换为日期样式 0。GETDATE() implicitly converts to date style 0. SYSDATETIME() 隐式转换为日期样式 21。SYSDATETIME() implicitly converts to date style 21.

显式转换使用 CAST 或 CONVERT 函数。Explicit conversions use the CAST or CONVERT functions.

CAST 和 CONVERT 函数可将值(局部变量、列或其他表达式)从一种数据类型转换为另一种数据类型。The CAST and CONVERT functions convert a value (a local variable, a column, or another expression) from one data type to another. 例如,以下 CAST 函数可将数值 $157.27 转换为字符串 '157.27'For example, the following CAST function converts the numeric value of $157.27 into a character string of '157.27':

CAST ( $157.27 AS VARCHAR(10) )  

如果希望 Transact-SQLTransact-SQL 程序代码符合 ISO 标准,请使用 CAST 而不要使用 CONVERT。Use CAST instead of CONVERT if you want Transact-SQLTransact-SQL program code to comply with ISO. 如果要利用 CONVERT 中的样式功能,请使用 CONVERT 而不要使用 CAST。Use CONVERT instead of CAST to take advantage of the style functionality in CONVERT.

以下图例显示了可对 SQL ServerSQL Server 系统提供的数据类型执行的所有显式和隐式数据类型转换。The following illustration shows all explicit and implicit data type conversions that are allowed for SQL ServerSQL Server system-supplied data types. 这些包括 xml、bigint 和sql_variant。These include xml, bigint, and sql_variant. 不存在对 sql_variant 数据类型的赋值进行的隐式转换,但是存在转换为 sql_variant 的隐式转换 。There is no implicit conversion on assignment from the sql_variant data type, but there is implicit conversion to sql_variant.

数据类型转换表Data type conversion table

虽然上面的图表说明了 SQL Server 中允许的所有显式和隐式转换,但并未指出转换的结果数据类型。While the above chart illustrates all the explicit and implicit conversions that are allowed in SQL Server, it does not indicate the resulting data type of the conversion. 当 SQL Server 执行显式转换时,语句本身会确定结果数据类型。When SQL Server performs an explicit conversion, the statement itself determines the resulting data type. 对于隐式转换,赋值语句(例如设置变量的值或在列中插入值)将产生由变量声明或列定义所定义的数据类型。For implicit conversions, assignment statements such as setting the value of a variable or inserting a value into a column result in the data type that was defined by the variable declaration or column definition. 对于比较运算符或其他表达式,结果数据类型取决于数据类型优先级的规则。For comparison operators or other expressions, the resulting data type depends on the rules of data type precedence.

例如,以下脚本定义一个类型为 varchar 的变量,将 int 类型值赋给该变量,然后选择该变量与字符串的串联。As an example, the following script defines a variable of type varchar, assigns an int type value to the variable, then selects a concatenation of the variable with a string.

DECLARE @string VARCHAR(10);
SET @string = 1;
SELECT @string + ' is a string.'

1int 值会转换为 varchar,因此 SELECT 语句返回值 1 is a string.The int value of 1 is converted to a varchar, so the SELECT statement returns the value 1 is a string..

下面的示例演示改为使用 int 变量的类似脚本:The following example, shows a similar script with an int variable instead:

DECLARE @notastring INT;
SET @notastring = '1';
SELECT @notastring + ' is not a string.'

在此例中,SELECT 语句会引发以下错误:In this case, the SELECT statement throws the following error:

Msg 245, Level 16, State 1, Line 3 Conversion failed when converting the varchar value ' is not a string.' to data type int.

为了计算表达式 @notastring + ' is not a string.',SQL Server 先遵循数据类型优先级的规则来完成隐式转换,然后才能计算表达式的结果。In order to evaluate the expression @notastring + ' is not a string.', SQL Server follows the rules of data type precedence to complete the implicit conversion before the result of the expression can be calculated. 由于 int 的优先级高于 varchar,因此 SQL Server 会尝试将字符串转换为整数,但是会失败,因为此字符串无法转换为整数。Because int has a higher precedence than varchar, SQL Server attempts to convert the string to an integer and fails because this string cannot be converted to an integer. 如果表达式提供可以转换的字符串,则该语句会成功,如以下示例所示:If the expression provides a string that can be converted, the statement succeeds, as in the following example:

DECLARE @notastring INT;
SET @notastring = '1';
SELECT @notastring + '1'

在此例中,字符串 1 可以转换为整数值 1,因而此 SELECT 语句会返回值 2In this case, the string 1 can be converted to the integer value 1, so this SELECT statement returns the value 2. 请注意,当提供的数据类型为整数时,+ 运算符会成为加法而不是串联。Note that the + operator becomes addition rather than concatenation when the data types provided are integers.

数据类型转换行为Data type conversion behaviors

将一个 SQL ServerSQL Server 对象的数据类型转换为另一种数据类型时,不支持某些隐式和显式数据类型转换。Some implicit and explicit data type conversions are not supported when you are converting the data type of one SQL ServerSQL Server object to another. 例如,nchar 值无法被转换为 image 值。For example, an nchar value cannot be converted to an image value. nchar 只能显式转换为 binary,而不支持隐式转换为 binary。An nchar can only be converted to binary by using explicit conversion, an implicit conversion to binary is not supported. 但是,nchar 既可以显式也可以隐式转换为 nvarchar。However, an nchar can be explicitly or implicitly converted to nvarchar.

以下各主题说明各对应数据类型展示的转换行为:The following topics describe the conversion behaviors exhibited by their corresponding data types:

使用 OLE 自动化存储过程转换数据类型Converting Data Types by Using OLE Automation Stored Procedures

由于 SQL ServerSQL Server 使用 Transact-SQLTransact-SQL 数据类型,而 OLE 自动化使用 Visual BasicVisual Basic 数据类型,因此 OLE 自动化存储过程必须转换在两者之间传递的数据。Because SQL ServerSQL Server uses Transact-SQLTransact-SQL data types and OLE Automation uses Visual BasicVisual Basic data types, the OLE Automation stored procedures must convert the data that passes between them.

下表说明了从 SQL ServerSQL ServerVisual BasicVisual Basic 的数据类型转换。The following table describes SQL ServerSQL Server to Visual BasicVisual Basic data type conversions.

SQL Server 数据类型SQL Server data type Visual Basic 数据类型Visual Basic data type
charvarchartextnvarcharntextchar, varchar, text, nvarchar, ntext 字符串String
decimalnumericdecimal, numeric 字符串String
bitbit 布尔值Boolean
binaryvarbinaryimagebinary, varbinary, image 一维 Byte() 数组One-dimensional Byte() array
intint LongLong
smallintsmallint 整数Integer
tinyinttinyint ByteByte
floatfloat 双精度Double
realreal 单精度Single
moneysmallmoneymoney, smallmoney 货币Currency
datetimesmalldatetimedatetime, smalldatetime 日期Date
设置为 NULL 的任何类型Anything set to NULL Variant 设置为 NullVariant set to Null

除了 binary、varbinary 和 image 值以外,所有单个 SQL ServerSQL Server 值都被转换为单个 Visual BasicVisual Basic 值。All single SQL ServerSQL Server values are converted to a single Visual BasicVisual Basic value with the exception of binary, varbinary, and image values. 这些值将被转换为 Visual BasicVisual Basic 中的一维 Byte() 数组。These values are converted to a one-dimensional Byte() array in Visual BasicVisual Basic. 此数组的范围为 Byte( 0 to length 1),其中 length 是 binary、varbinary 或 image 值中的字节数SQL ServerSQL ServerThis array has a range of Byte( 0 to length 1 ) where length is the number of bytes in the SQL ServerSQL Server binary, varbinary, or image values.

以下是从 Visual BasicVisual Basic 数据类型到 SQL ServerSQL Server 数据类型的转换。These are the conversions from Visual BasicVisual Basic data types to SQL ServerSQL Server data types.

Visual Basic 数据类型Visual Basic data type SQL Server 数据类型SQL Server data type
LongIntegerByteBooleanObjectLong, Integer, Byte, Boolean, Object intint
DoubleSingleDouble, Single floatfloat
货币Currency moneymoney
日期Date datetimedatetime
小于或等于 4000 个字符的 StringString with 4000 characters or less varchar/nvarcharvarchar/nvarchar
大于 4000 个字符的 StringString with more than 4000 characters text/ntexttext/ntext
小于或等于 8000 字节的一维 Byte() 数组One-dimensional Byte() array with 8000 bytes or less varbinaryvarbinary
大于 8000 字节的一维 Byte() 数组One-dimensional Byte() array with more than 8000 bytes 图像image

另请参阅See also

OLE 自动存储过程 (Transact-SQL)OLE Automation Stored Procedures (Transact-SQL)
CAST 和 CONVERT (Transact-SQL)CAST and CONVERT (Transact-SQL)
数据类型 (Transact-SQL)Data Types (Transact-SQL)
COLLATE (Transact-SQL)COLLATE (Transact-SQL)