char 和 varchar (Transact-SQL)char and varchar (Transact-SQL)

适用对象: 是SQL Server 是Azure SQL 数据库 是Azure Synapse Analytics (SQL DW) 是并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

字符数据类型 char(大小固定)或 varchar(大小可变) 。Character data types that are either fixed-size, char, or variable-size, varchar. SQL Server 2019 (15.x)SQL Server 2019 (15.x) 起,使用启用了 UTF-8 的排序规则时,这些数据类型会存储 Unicode 字符数据的整个范围,并使用 UTF-8 字符编码。Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), when a UTF-8 enabled collation is used, these data types store the full range of Unicode character data and use the UTF-8 character encoding. 若指定了非 UTF-8 排序规则,则这些数据类型仅会存储该排序规则的相应代码页支持的字符子集。If a non-UTF-8 collation is specified, then these data types store only a subset of characters supported by the corresponding code page of that collation.

参数Arguments

char [ ( n ) ] 固定大小字符串数据 。char [ ( n ) ] Fixed-size string data. n 用于定义字符串大小(以字节为单位),并且它必须为 1 到 8,000 之间的值 。n defines the string size in bytes and must be a value from 1 through 8,000. 对于单字节编码字符集(如拉丁文),存储大小为 n 个字节,并且可存储的字符数也为 n。 For single-byte encoding character sets such as Latin, the storage size is n bytes and the number of characters that can be stored is also n. 对于多字节编码字符集,存储大小仍为 n 个字节,但可存储的字符数可能小于 n。 For multibyte encoding character sets, the storage size is still n bytes but the number of characters that can be stored may be smaller than n. char 的 ISO 同义词是 character 。The ISO synonym for char is character. 有关字符集的详细信息,请参阅单字节和多字节字符集For more information on character sets, see Single-Byte and Multibyte Character Sets.

varchar [ ( n | max ) ] 可变大小字符串数据 。varchar [ ( n | max ) ] Variable-size string data. 使用 n 定义字符串大小(以字节为单位),可以是介于 1 和 8,000 之间的值;或使用 max 指明列约束大小上限为最大存储 2^31-1 个字节 (2GB)。Use n to define the string size in bytes and can be a value from 1 through 8,000 or use max to indicate a column constraint size up to a maximum storage of 2^31-1 bytes (2 GB). 对于单字节编码字符集(如拉丁文),存储大小为 n + 2 个字节,并且可存储的字符数也为 n。 For single-byte encoding character sets such as Latin, the storage size is n bytes + 2 bytes and the number of characters that can be stored is also n. 对于多字节编码字符集,存储大小仍为 n + 2 个字节,但可存储的字符数可能小于 n 。For multi-byte encoding character sets, the storage size is still n bytes + 2 bytes but the number of characters that can be stored may be smaller than n. varchar 的 ISO 同义词是 charvarying 或 charactervarying 。The ISO synonyms for varchar are charvarying or charactervarying. 有关字符集的详细信息,请参阅单字节和多字节字符集For more information on character sets, see Single-Byte and Multibyte Character Sets.

RemarksRemarks

一个常见误解是,认为在 CHAR(n) 和 VARCHAR(n) 中,n 定义字符数 。A common misconception is to think that CHAR(n) and VARCHAR(n), the n defines the number of characters. 但在 CHAR(n) 和 VARCHAR(n) 中,n 定义字符串的长度(以字节为单位)(0-8,000) 。But in CHAR(n) and VARCHAR(n) the n defines the string length in bytes (0-8,000). n 不会定义可存储的字符数 。n never defines numbers of characters that can be stored. 这类似于 NCHAR(n) 和 NVARCHAR(n) 的定义 。This is similar to the definition of NCHAR(n) and NVARCHAR(n).
出现此误解的原因是,在使用单字节编码时,CHAR 和 VARCHAR 的存储大小为 n 个字节,并且字符数也为 n 。The misconception happens because when using single-byte encoding, the storage size of CHAR and VARCHAR is n bytes and the number of characters is also n. 但是,对于多字节编码(如 UTF-8),更高的 Unicode 范围 (128-1,114,111) 会导致一个字符使用两个或更多字节。However, for multi-byte encoding such as UTF-8, higher Unicode ranges (128-1,114,111) result in one character using two or more bytes. 例如,在定义为 CHAR (10) 的列中,数据库引擎Database Engine 可以存储使用单字节编码(Unicode 范围 0-127)的 10 个字符,但在使用多字节编码(Unicode 范围 128-1,114,111)时,将不超过 10 个字符。For example, in a column defined as CHAR(10), the 数据库引擎Database Engine can store 10 characters that use single-byte encoding (Unicode range 0-127), but less than 10 characters when using multi-byte encoding (Unicode range 128-1,114,111). 有关 Unicode 存储和字符范围的详细信息,请参阅 UTF-8 与 UTF-16 之间的存储差异For more information about Unicode storage and character ranges, see Storage differences between UTF-8 and UTF-16.

如果没有在数据定义或变量声明语句中指定 n,则默认长度为 1。When n isn't specified in a data definition or variable declaration statement, the default length is 1. 如果在使用 CAST 和 CONVERT 函数时未指定 n,则默认长度为 30。If n isn't specified when using the CAST and CONVERT functions, the default length is 30.

为使用 char 或 varchar 的对象分配的是默认的数据库排序规则,但可使用 COLLATE 子句分配特定的排序规则 。Objects that use char or varchar are assigned the default collation of the database, unless a specific collation is assigned using the COLLATE clause. 该排序规则控制用于存储字符数据的代码页。The collation controls the code page that is used to store the character data.

SQL ServerSQL Server 中的多字节编码包括:Multibyte encodings in SQL ServerSQL Server include:

  • 使用代码页 936 及 950(中文)、932(日文)或 949(韩文)的某些东亚语言的双字节字符集 (DBCS)。Double-byte character sets (DBCS) for some East Asian languages using code pages 936 and 950 (Chinese), 932 (Japanese), or 949 (Korean).
  • 使用代码页 65001 的 UTF-8。UTF-8 with code page 65001. 适用范围: SQL ServerSQL Server (从 SQL Server 2019 (15.x)SQL Server 2019 (15.x) 开始)Applies to: SQL ServerSQL Server (Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x)))

若你拥有支持多种语言的站点:If you have sites that support multiple languages:

  • SQL Server 2019 (15.x)SQL Server 2019 (15.x) 开始,考虑使用已启用 UTF-8 的排序规则,以支持 Unicode 并最大程度地减少字符转换问题。Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), consider using a UTF-8 enabled collation to support Unicode and minimize character conversion issues.
  • 若使用较低版本的 SQL Server 数据库引擎SQL Server Database Engine,请考虑使用 Unicode nchar 或 nvarchar 数据类型,以最大程度地减少字符转换问题 。If using a lower version of the SQL Server 数据库引擎SQL Server Database Engine, consider using the Unicode nchar or nvarchar data types to minimize character conversion issues.

若使用 char 或 varchar ,则建议:If you use char or varchar, we recommend to:

  • 如果列数据项的大小一致,则使用 char 。Use char when the sizes of the column data entries are consistent.
  • 如果列数据项的大小差异相当大,则使用 varchar 。Use varchar when the sizes of the column data entries vary considerably.
  • 如果列数据项大小相差很大,而且字符串长度可能超过 8,000 字节,请使用 varchar(max) 。Use varchar(max) when the sizes of the column data entries vary considerably, and the string length might exceed 8,000 bytes.

当执行 CREATE TABLE 或 ALTER TABLE 时,如果 SET ANSI_PADDING 为 OFF,则定义为 NULL 的 char 列将作为 varchar 处理 。If SET ANSI_PADDING is OFF when either CREATE TABLE or ALTER TABLE is executed, a char column that is defined as NULL is handled as varchar.

警告

每个非 null varchar(max) 或 nvarchar(max) 列都需要 24 个字节的附加固定分配,这将在执行排序操作期间根据 8,060 字节行限制进行计数。Each non-null varchar(max) or nvarchar(max) column requires 24 bytes of additional fixed allocation which counts against the 8,060 byte row limit during a sort operation. 这样一来,可能会为非 null varchar(max) 或 nvarchar(max)(可在表格中进行创建)列数创建隐式限制。This can create an implicit limit to the number of non-null varchar(max) or nvarchar(max) columns that can be created in a table.
在以下情况下不提供特殊错误:创建表格(最大行大小超过允许的最大 8,060 字节时出现的一般警告除外)时,或插入数据时。No special error is provided when the table is created (beyond the usual warning that the maximum row size exceeds the allowed maximum of 8,060 bytes) or at the time of data insertion. 这一较大的行大小可能会导致在执行某些正常操作(例如聚集索引键更新或完整列集排序)期间出现错误(例如错误 512),用户在执行操作前无法预料到此类错误。This large row size can cause errors (such as error 512) during some normal operations, such as a clustered index key update, or sorts of the full column set, which users can anticipate until performing an operation.

转换字符数据Converting Character Data

如果将字符表达式转换为不同大小的字符数据类型,则对于新数据类型而言过长的值将被截断。When character expressions are converted to a character data type of a different size, values that are too long for the new data type are truncated. 出于从字符表达式转换的目的将 uniqueidentifier 类型视为字符类型,因此,在转换到字符类型时要遵循截断规则。The uniqueidentifier type is considered a character type for the purposes of conversion from a character expression, and so is subject to the truncation rules for converting to a character type. 请参阅后面的“示例”一节。See the Examples section that follows.

如果将某个字符表达式转换为不同数据类型或大小的字符表达式(例如从 char(5) 转换为 varchar(5) 或从 char(20) 转换为 char(15)),则输入值的排序规则会被分配给经过转换的值 。When a character expression is converted to a character expression of a different data type or size, such as from char(5) to varchar(5), or char(20) to char(15), the collation of the input value is assigned to the converted value. 如果将非字符表达式转换为字符数据类型,则当前数据库的默认排序规则会被分配给经过转换的值。If a noncharacter expression is converted to a character data type, the default collation of the current database is assigned to the converted value. 在任意一种情况下,都可以使用 COLLATE 子句分配特定的排序规则。In either case, you can assign a specific collation by using the COLLATE clause.

备注

char 和 varchar 数据类型支持代码页转换,但是 text 数据类型不支持 。Code page translations are supported for char and varchar data types, but not for text data type. SQL ServerSQL Server 的早期版本一样,将不报告代码页转换期间的数据丢失。As with earlier versions of SQL ServerSQL Server, data loss during code page translations isn't reported.

要转换为近似 numeric 数据类型的字符表达式可以包括可选的指数表示法。Character expressions that are being converted to an approximate numeric data type can include optional exponential notation. 此表示法为一个小写的 e 或一个大写的 E 后跟可选的加号 (+) 或减号 (-),再后跟一个数字。This notation is a lowercase e or uppercase E followed by an optional plus (+) or minus (-) sign and then a number.

要转换为精确 numeric 数据类型的字符表达式必须包含数字、小数点和可选的加号 (+) 或减号 (-) 。Character expressions that are being converted to an exact numeric data type must consist of digits, a decimal point, and an optional plus (+) or minus (-). 将忽略前导空格。Leading blanks are ignored. 不允许在字符串中使用逗号分隔符,例如 123,456.00 中的千位分隔符。Comma separators, such as the thousands separator in 123,456.00, aren't allowed in the string.

要转换为 money 或 smallmoney 数据类型的字符表达式还可以包含可选的小数点和美元符号 ($) 。Character expressions being converted to money or smallmoney data types can also include an optional decimal point and dollar sign ($). 可以使用逗号分隔符(如在 $123,456.00 中)。Comma separators, as in $123,456.00, are allowed.

示例Examples

A.A. 在变量声明中显示 n 的默认值。Showing the default value of n when used in variable declaration.

以下示例显示在变量声明中使用 charvarchar 数据类型时,n 的默认值为 1 。The following example shows the default value of n is 1 for the char and varchar data types when they are used in variable declaration.

DECLARE @myVariable AS varchar = 'abc';  
DECLARE @myNextVariable AS char = 'abc';  
--The following returns 1  
SELECT DATALENGTH(@myVariable), DATALENGTH(@myNextVariable);  
GO  

B.B. 在 CAST 和 CONVERT 中使用 varchar 时,显示 n 的默认值。Showing the default value of n when varchar is used with CAST and CONVERT.

以下示例显示在 CASTCONVERT 函数中使用 charvarchar 数据类型时,n 的默认值为 30 。The following example shows that the default value of n is 30 when the char or varchar data types are used with the CAST and CONVERT functions.

DECLARE @myVariable AS varchar(40);  
SET @myVariable = 'This string is longer than thirty characters';  
SELECT CAST(@myVariable AS varchar);  
SELECT DATALENGTH(CAST(@myVariable AS varchar)) AS 'VarcharDefaultLength';  
SELECT CONVERT(char, @myVariable);  
SELECT DATALENGTH(CONVERT(char, @myVariable)) AS 'VarcharDefaultLength';  

C.C. 转换数据以便于显示Converting Data for Display Purposes

以下示例将两列转换为字符类型并应用一种样式,该样式将特定格式应用于所显示的数据。The following example converts two columns to character types and applies a style that applies a specific format to the displayed data. money 类型转换为字符数据并应用样式 1,这会对小数点左侧的每三个数字和对小数点右侧的每两个数字加一个逗号来显示值 。A money type is converted to character data and style 1 is applied, which displays the values with commas every three digits to the left of the decimal point, and two digits to the right of the decimal point. datetime 类型转换为字符数据并应用样式 3,这会以 dd/mm/yy 格式来显示数据。A datetime type is converted to character data and style 3 is applied, which displays the data in the format dd/mm/yy. 在 WHERE 子句中,money 类型强制转换为字符类型,以执行字符串比较操作 。In the WHERE clause, a money type is cast to a character type to perform a string comparison operation.

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

下面是结果集:Here is the result set.

BusinessEntityID SalesYTD              DisplayFormat CurrentDate             DisplayDateFormat  
---------------- --------------------- ------------- ----------------------- -----------------  
278              1453719.4653          1,453,719.47  2011-05-07 14:29:01.193 07/05/11  
280              1352577.1325          1,352,577.13  2011-05-07 14:29:01.193 07/05/11  
283              1573012.9383          1,573,012.94  2011-05-07 14:29:01.193 07/05/11  
284              1576562.1966          1,576,562.20  2011-05-07 14:29:01.193 07/05/11  
285              172524.4512           172,524.45    2011-05-07 14:29:01.193 07/05/11  
286              1421810.9242          1,421,810.92  2011-05-07 14:29:01.193 07/05/11  
288              1827066.7118          1,827,066.71  2011-05-07 14:29:01.193 07/05/11  

D.D. 转换 Uniqueidentifer 数据Converting Uniqueidentifer Data

以下示例将 uniqueidentifier 值转换为 char 数据类型。The following example converts a uniqueidentifier value to a char data type.

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

以下示例演示在值过长而无法转换数据类型时如何截断数据。The following example demonstrates the truncation of data when the value is too long for the data type being converted to. 因为 uniqueidentifier 类型限制为 36 个字符,所以,将截断超过该长度的字符 。Because the uniqueidentifier type is limited to 36 characters, the characters that exceed that length are truncated.

DECLARE @ID nvarchar(max) = N'0E984725-C51C-4BF4-9960-E1C80E27ABA0wrong';  
SELECT @ID, CONVERT(uniqueidentifier, @ID) AS TruncatedValue;  

下面是结果集:Here is the result set.

String                                       TruncatedValue  
-------------------------------------------- ------------------------------------  
0E984725-C51C-4BF4-9960-E1C80E27ABA0wrong    0E984725-C51C-4BF4-9960-E1C80E27ABA0  
  
(1 row(s) affected)  

另请参阅See also

nchar 和 nvarchar (Transact-SQL)nchar and nvarchar (Transact-SQL)
CAST 和 CONVERT (Transact-SQL)CAST and CONVERT (Transact-SQL)
COLLATE (Transact-SQL)COLLATE (Transact-SQL)
数据类型转换(数据库引擎)Data Type Conversion (Database Engine)
数据类型 (Transact-SQL)Data Types (Transact-SQL)
估计数据库的大小 Estimate the Size of a Database
排序规则和 Unicode 支持 Collation and Unicode Support
单字节和多字节字符集Single-Byte and Multibyte Character Sets