SQL Server数据库数据类型概述

一时失言乱红尘 2022-11-09 12:50 203阅读 0赞

在SQL Server中，设计表时需要定义表的列（ column 的数据类型。  数据类型不合适可能导致各种问题，例如查询性能问题，数据截断（truncation）。本文简要介绍SQL Server支持数据类型。

SQL SERVER微软官方文档[https://docs.microsoft.com/zh-cn/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver15][https_docs.microsoft.com_zh-cn_sql_t-sql_data-types_data-types-transact-sql_view_sql-server-ver15] ，在此选择常用的简要总结介绍。

**Character 字符串：**

<table> 
 <tbody> 
 <tr> 
 <td style="border-color:#c0c0c0;"> 数据类型 </td> 
 <td style="border-color:#c0c0c0;"> 描述 </td> 
 <td style="border-color:#c0c0c0;"> 存储长度(字符数) </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;"> char(n) </td> 
 <td> 固定长度的字符串。最多 8,000 个字符。 </td> 
 <td> n &lt;= 8000字符 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;"> varchar(n) </td> 
 <td> 可变长度的字符串。最多 8,000 个字符。 </td> 
 <td> 0-n字符 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;"> varchar(max) </td> 
 <td> 可变长度的字符串。最多 1,073,741,824 个字符。 </td> 
 <td> 0-max字符 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;"> text </td> 
 <td> 可变长度的字符串。最多 2GB 字符数据。【不推荐，将弃用】 </td> 
 <td> 0-2GB字符 </td> 
 </tr> 
 </tbody> 
</table>

**Unicode 字符串：**

<table> 
 <tbody> 
 <tr> 
 <td style="border-color:#c0c0c0;"> 数据类型 </td> 
 <td style="border-color:#c0c0c0;"> 描述 </td> 
 <td style="border-color:#c0c0c0;"> 存储长度(字符数) </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;"> nchar(n) </td> 
 <td> 固定长度的 Unicode 数据。最多 4,000 个字符。 </td> 
 <td> &nbsp;n &lt;=4000字符 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;"> nvarchar(n) </td> 
 <td> 可变长度的 Unicode 数据。最多 4,000 个字符。 </td> 
 <td> &nbsp;0-n &lt;=4000&nbsp; </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;"> nvarchar(max) </td> 
 <td> 可变长度的 Unicode 数据。最多 536,870,912 个字符。 </td> 
 <td> &nbsp;0-536,870,912 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;"> ntext </td> 
 <td> 可变长度的 Unicode 数据。最多 2GB 字符数据。&nbsp;【不推荐，将弃用】 </td> 
 <td> &nbsp;0-2GB字符 </td> 
 </tr> 
 </tbody> 
</table>

特别说明varchar和nvarchar的区别：

varchar在SQL Server中是采用单字节来存储数据的，nvarchar是使用Unicode双字节来存储数据的。

英文字符占一个字节，在存储时，如果字段类型是varchar，则只会占用一个字节，而如果字段的类型为nvarchar，则会占用两个字节。

中文字符占两个字节，在存储时，不管字段类型是varchar，还是nvarchar，都占用两个字节（一般采用Unicode编码）。

【注：Unicode （统一码、 万国码、单一码 ）是一种在计算机上使用的字符编码 。它为每种语言中的每个字符设定了统一并且唯一的二进制编码 ，以满足跨语言、跨平台进行文本转换 、处理的要求。】

正常情况下varchar也可存储中文字符，但是如果遇到操作系统是英文操作系统，且对中文字体的支持不全面时, 在SQL Server存储中文字符为varchar就会出现乱码(显示为??)；

解决问题办法的是把数据库字段的类型更改为nvarchar（或 者nchar）。

使用nvarchar类型，即可解决字符集兼容性问题(不用担心中文乱码问题)，又可在判断字符串时不需要考虑中英文两种字符的差别．缺点是会增大一倍的存储空间。

所以一般来说，如果可能含有中文字符，用nchar/nvarchar存储，如果纯英文和数字(保证不含中文)，则用char/varchar存储。

另外需要补充下，使用varchar存储字段，很可能会导致隐式转换；

实例如下：创建AAA表，并插入1000条测试数据，并在name1上建索引，然后分别执行以下两行SQL语句，查看执行计划，发现第二个多了constant Scan的开销。

DECLARE @name VARCHAR(100)   SET @name = '张三500'  SELECT \* FROM AAA WHERE  name1 = @name --varchar存储，参数类型为varchar，不存在隐式转换

DECLARE @name  NVARCHAR(100)  SET @name = '张三500'   SELECT \* FROM  AAA   WHERE  name1 = @name  --varchar存储，参数类型为nvarchar，存在隐式转换

注：有时会出现，使用了nvarchar数据类型，但是系统仍出现中文乱码或者特殊符号乱码的问题；这时候在更新或者插入的字段值前面加上N即可；（如果是varchar类型的，加上N也不好使的）。

那么这个N是干什么用的呢？字符串参数值的前面有一个大写的N，它的含义是表明后面引号里的字符串是unicode。

总结一下：1、中文乱码问题，首先保证列的类型是nvarchar；2、然后插入数据的时候用“N”保证数据是unicode的。 这样就不会显示莫名其妙的问号、中文乱码或者特殊符号乱码了。

**Binary 类型：**

<table> 
 <tbody> 
 <tr> 
 <td style="border-color:#c0c0c0;"> 数据类型 </td> 
 <td style="border-color:#c0c0c0;"> 描述 </td> 
 <td style="border-color:#c0c0c0;"> 存储长度(字符数) </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;"> bit </td> 
 <td> 允许 0、1 或 NULL </td> 
 <td> &nbsp;1字节 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;"> binary(n) </td> 
 <td> 固定长度的二进制数据。最多 8,000 字节。 </td> 
 <td> &nbsp;n &lt;= 8000字节 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;"> varbinary(n) </td> 
 <td> 可变长度的二进制数据。最多 8,000 字节。 </td> 
 <td> &nbsp;0-n字节 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;"> varbinary(max) </td> 
 <td> 可变长度的二进制数据。最多 2GB 字节。 </td> 
 <td> &nbsp;0-2GB字节 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;"> image </td> 
 <td> 可变长度的二进制数据。最多 2GB。&nbsp;【不推荐，将弃用】 </td> 
 <td> &nbsp;0-2GB字节 </td> 
 </tr> 
 </tbody> 
</table>

Bit类型比较特殊，在SQL Server 2008中，使用SSMS可视工具给bit类型的列赋值时，需要输入True或False，而在查询数据时，则显示为1或0。而在使用SQL语句添加数据时,则需要使用1和0表示。

字节越大表示的整数范围越大,如果超过字节范围出会溢出。

**Number 类型：**

<table> 
 <tbody> 
 <tr> 
 <td style="border-color:#c0c0c0;width:188px;"> 数据类型 </td> 
 <td style="border-color:#c0c0c0;width:601px;"> 描述 </td> 
 <td style="border-color:#c0c0c0;width:81.75pt;"> 存储长度(字符数) </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;width:188px;"> tinyint </td> 
 <td style="width:601px;"> 允许从 0 到 255 的所有数字。 </td> 
 <td style="width:81.75pt;"> 1字节 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;width:188px;"> smallint </td> 
 <td style="width:601px;"> 允许从 -32,768 到 32,767 的所有数字。 </td> 
 <td style="width:81.75pt;"> 2字节 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;width:188px;"> int </td> 
 <td style="width:601px;"> 允许从 -2,147,483,648 到 2147483647 的所有数字。 </td> 
 <td style="width:81.75pt;"> 4字节 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;width:188px;"> bigint </td> 
 <td style="width:601px;"> 允许介于 -9,223,372,036,854,775,808 和 9,223,372,036,854,775,807 之间的所有数字。 </td> 
 <td style="width:81.75pt;"> 8字节 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;width:188px;"> decimal(p,s) </td> 
 <td style="width:601px;"> 固定精度和比例的数字。允许从 -10^38 +1 到 10^38 -1 之间的数字。 p 参数指示可以存储的最大位数（小数点左侧和右侧）。p 必须是 1 到 38 之间的值。默认是 18。 s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。 </td> 
 <td style="width:81.75pt;"> 5-17字节 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;width:188px;"> numeric(p,s) </td> 
 <td style="width:601px;"> 固定精度和比例的数字。允许从 -10^38 +1 到 10^38 -1 之间的数字。 p 参数指示可以存储的最大位数（小数点左侧和右侧）。p 必须是 1 到 38 之间的值。默认是 18。 s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。 </td> 
 <td style="width:81.75pt;"> 5-17字节 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;width:188px;"> smallmoney </td> 
 <td style="width:601px;"> 介于 -214,748.3648 和 214,748.3647 之间的货币数据。 </td> 
 <td style="width:81.75pt;"> 4字节 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;width:188px;"> money </td> 
 <td style="width:601px;"> 介于 -922,337,203,685,477.5808 和 922,337,203,685,477.5807 之间的货币数据。 </td> 
 <td style="width:81.75pt;"> 8字节 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;width:188px;"> float(n) </td> 
 <td style="width:601px;"> 从 -1.79E + 308 到 1.79E + 308 的浮动精度数字数据。 参数 n 指示该字段保存 4 字节还是 8 字节。float(24) 保存 4 字节，而 float(53) 保存 8 字节。n 的默认值是 53。 </td> 
 <td style="width:81.75pt;"> 4或8字节 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;width:188px;"> real </td> 
 <td style="width:601px;"> 从 -3.40E + 38 到 3.40E + 38 的浮动精度数字数据。 </td> 
 <td style="width:81.75pt;"> 4字节 </td> 
 </tr> 
 </tbody> 
</table>

float和real是SQL Server的浮点型，其中float表示双精度浮点数据类型，real表示单精度浮点数据类型。存储的是近似数值，不可用于等值比较。

SQL Server精确数据类型使用Decimal或Numeric表示，两者完全一样，建议使用Decimal类型。decimal(5,3)表示整数部分和小数部分最大一共是5位，其中小数最大为3位，则整数位最大就是2位，如果输入2位以上的整数部分就会出错，如果输入的小数位数超出指定的位数，则自动四舍五入。

SQL Server货币数据类型使用Smallmoney、money表示，数据值有整数部分和小数部分组成，小数部分精确到4位。小数位数多于4位，则会自动四舍五入。

**Date 类型：**

<table> 
 <tbody> 
 <tr> 
 <td style="border-color:#c0c0c0;width:312px;"> 数据类型 </td> 
 <td style="border-color:#c0c0c0;"> 描述 </td> 
 <td style="border-color:#c0c0c0;width:159px;"> 存储长度(字符数) </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;width:312px;"> datetime </td> 
 <td> 从 1753 年 1 月 1 日 到 9999 年 12 月 31 日，精度为 3.33 毫秒。 </td> 
 <td style="width:159px;"> 8 bytes </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;width:312px;"> datetime2 </td> 
 <td> 从 1753 年 1 月 1 日 到 9999 年 12 月 31 日，精度为 100 纳秒。 </td> 
 <td style="width:159px;"> 6-8 bytes </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;width:312px;"> smalldatetime </td> 
 <td> 从 1900 年 1 月 1 日 到 2079 年 6 月 6 日，精度为 1 分钟。 </td> 
 <td style="width:159px;"> 4 bytes </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;width:312px;"> date </td> 
 <td> 仅存储日期。从 0001 年 1 月 1 日 到 9999 年 12 月 31 日。 </td> 
 <td style="width:159px;"> 3 bytes </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;width:312px;"> time </td> 
 <td> 仅存储时间。精度为 100 纳秒。 </td> 
 <td style="width:159px;"> 3-5 bytes </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;width:312px;"> datetimeoffset </td> 
 <td> 与 datetime2 相同，外加时区偏移。 </td> 
 <td style="width:159px;"> 8-10 bytes </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;width:312px;"> timestamp </td> 
 <td> 存储唯一的数字，每当创建或修改某行时，该数字会更新。timestamp 基于内部时钟，不对应真实时间。每个表只能有一个 timestamp 变量。 </td> 
 <td style="width:159px;"> &nbsp;8 bytes </td> 
 </tr> 
 </tbody> 
</table>

**其他数据类型：**

<table> 
 <tbody> 
 <tr> 
 <td style="border-color:#c0c0c0;width:178px;"> 数据类型 </td> 
 <td style="border-color:#c0c0c0;"> 描述 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;width:178px;"> sql_variant </td> 
 <td> 存储最多 8,000 字节不同数据类型的数据，除了 text、ntext 以及 timestamp。 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;width:178px;"> uniqueidentifier </td> 
 <td> 存储全局标识符 (GUID)。GUID（Globally Unique Identifier）是一个通过特定算法产生的二进制长度为128位的数字标识符，用于指示产品的唯一性。 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;width:178px;"> xml </td> 
 <td> 存储 XML（eXtensible Markup Language）格式化数据，用来传输和存储数据。最多 2GB。 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;width:178px;"> cursor </td> 
 <td> Cursor（游标）提供了在结果集中一次以行或者多行前进或向后浏览数据的能力。我们可以把游标当作一个指针，它可以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理。 </td> 
 </tr> 
 <tr> 
 <td style="border-color:#c0c0c0;width:178px;"> table </td> 
 <td> 存储结果集，供稍后处理。 </td> 
 </tr> 
 </tbody> 
</table>

附录、

SQL在线练习网站 [https://www.liaoxuefeng.com/wiki/1177760294764384/1179611432985088][https_www.liaoxuefeng.com_wiki_1177760294764384_1179611432985088]

提示：没办法保存，退出去内容就没了。

☆创建数据表的语法：  
CREATE TABLE table\_name (column\_name column\_type);  
如

建立Persons表  
CREATE TABLE Persons  
(  
PersonID int,  
Name varchar(40),  
Address varchar(80),  
phone varchar(20)  
);

☆向表中插入新记录。格式  
INSERT INTO table\_name  
VALUES (value1,value2,value3,...);  
或  
INSERT INTO table\_name(column1,column2,column3,...)  
VALUES (value1,value2,value3,...);  
如：  
INSERT INTO Persons(PersonID,Name,Address,phone) VALUES(1,"张三","X1市a1区b1街c1号","11111111");  
INSERT INTO Persons(PersonID,Name,Address,phone) VALUES(2,"李四","X1市a2区b2街c1号","22222222");  
INSERT INTO Persons(PersonID,Name,Address,phone) VALUES(3,"王五","X2市m1区n1街M1号","33333333");

【注意，其中的逗号、引号是英文的】

☆查询——从表中读取数据  
SELECT column\_name,column\_name  
FROM table\_name;  
或  
SELECT \* FROM table\_name;  
如：  
SELECT \* FROM Persons;

☆条件查询，在查询语句后附上  
WHERE 条件;  
如  
SELECT \* FROM Persons  
WHERE PersonID=2;

☆删除表  
语法：  
drop table 表名称；  
如  
drop table Persons;

[https_docs.microsoft.com_zh-cn_sql_t-sql_data-types_data-types-transact-sql_view_sql-server-ver15]: https://docs.microsoft.com/zh-cn/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver15
[https_www.liaoxuefeng.com_wiki_1177760294764384_1179611432985088]: https://www.liaoxuefeng.com/wiki/1177760294764384/1179611432985088