SQL 数据库 数据类型 固定长度的字符串怎么定义

在

Microsoft®

SQL

Server™

中，每个列、局部变量、表达式和参数都有一个相关的数据类型，这是指定对象可持有的数据类型（整型、字符、money

等等）的特性。SQL

Server

提供系统数据类型集，定义了可与

SQL

Server

一起使用的所有数据类型。下面列出系统提供的数据类型集。

可以定义用户定义的数据类型，其是系统提供的数据类型的别名。有关用户定义的数据类型的更多信息，请参见

sp_addtype

和创建用户定义的数据类型。

当两个具有不同数据类型、排序规则、精度、小数位数或长度的表达式通过运算符进行组合时：

通过将数据类型的优先顺序规则应用到输入表达式的数据类型来确定所得值的数据类型。有关更多信息，请参见数据类型的优先顺序。

如果结果数据类型为

char、varchar、text、nchar、nvarchar

或

ntext，则结果值的排序规则由排序规则的优先顺序规则决定。有关更多信息，请参见排序规则的优先顺序。

结果的精度、小数位数及长度取决于输入表达式的精度、小数位数及长度。有关更多信息，请参见精度、小数位数和长度。

SQL

Server

为

SQL-92

兼容性提供了数据类型同义词。有关更多信息，请参见数据类型同义词。

精确数字

整数

bigint

从

-2^63

(-9223372036854775808)

到

2^63-1

(9223372036854775807)

的整型数据（所有数字）。

int

从

-2^31

(-2,147,483,648)

到

2^31

-

1

(2,147,483,647)

的整型数据（所有数字）。

smallint

从

-2^15

(-32,768)

到

2^15

-

1

(32,767)

的整数数据。

tinyint

从

0

到

255

的整数数据。

bit

bit

1

或

0

的整数数据。

decimal

和

numeric

decimal

从

-10^38

+1

到

10^38

–1

的固定精度和小数位的数字数据。

numeric

功能上等同于

decimal。

money

和

smallmoney

money

货币数据值介于

-2^63

(-922,337,203,685,477.5808)

与

2^63

-

1

(+922,337,203,685,477.5807)

之间，精确到货币单位的千分之十。

smallmoney

货币数据值介于

-214,748.3648

与

+214,748.3647

之间，精确到货币单位的千分之十。

近似数字

float

从

-1.79E

+

308

到

1.79E

+

308

的浮点精度数字。

real

从

-3.40E

+

38

到

3.40E

+

38

的浮点精度数字。

datetime

和

smalldatetime

datetime

从

1753

年

1

月

1

日到

9999

年

12

月

31

日的日期和时间数据，精确到百分之三秒（或

3.33

毫秒）。

smalldatetime

从

1900

年

1

月

1

日到

2079

年

6

月

6

日的日期和时间数据，精确到分钟。

字符串

char

固定长度的非

Unicode

字符数据，最大长度为

8,000

个字符。

varchar

可变长度的非

Unicode

数据，最长为

8,000

个字符。

text

可变长度的非

Unicode

数据，最大长度为

2^31

-

1

(2,147,483,647)

个字符。

Unicode

字符串

nchar

固定长度的

Unicode

数据，最大长度为

4,000

个字符。

nvarchar

可变长度

Unicode

数据，其最大长度为

4,000

字符。sysname

是系统提供用户定义的数据类型，在功能上等同于

nvarchar(128)，用于引用数据库对象名。

ntext

可变长度

Unicode

数据，其最大长度为

2^30

-

1

(1,073,741,823)

个字符。

二进制字符串

binary

固定长度的二进制数据，其最大长度为

8,000

个字节。

varbinary

可变长度的二进制数据，其最大长度为

8,000

个字节。

image

可变长度的二进制数据，其最大长度为

2^31

-

1

(2,147,483,647)

个字节。

其它数据类型

cursor

游标的引用。

sql_variant

一种存储

SQL

Server

支持的各种数据类型（text、ntext、timestamp

和

sql_variant

除外）值的数据类型。

table

一种特殊的数据类型，存储供以后处理的结果集。

timestamp

数据库范围的唯一数字，每次更新行时也进行更新。

uniqueidentifier

全局唯一标识符

(GUID)。

请参见

CREATE

PROCEDURE

CREATE

TABLE

DECLARE

@local_variable

EXECUTE

表达式

函数

LIKE

SET

sp_bindefault

sp_bindrule

sp_droptype

sp_help

sp_rename

sp_unbindefault

sp_unbindrule

使用

Unicode

数据

数据库表中字段类型有二进制数据类型、字符数据类型、数字数据类型 等其中类型。具体类型如下所示：

1、二进制数据类型 

Binary、Varbinary、Image 

2、字符数据类型 

Char，Varchar和 Text

3、Unicode数据类型

包括Nchar,Nvarchar和Ntext

4、日期和时间数据类型

包括Datetime, Smalldatetime, Date, TimeStamp

5、数字数据类型

数字数据类型包括正数和负数、小数和整数

6、货币数据类型

表示正的或者负的货币数量。

7、特殊数据类型

特殊的数据类型有3种，即Timestamp、Bit 和 Uniqueidentifier。

现代数据库一般都支持CHAR与VARCHAR字符型字段类型，CHAR是用来保存定长字符，存储空间的大小为字段定义的长度，与实际字符长度无关，当输入的字符小于定义长度时最后会补上空格。VARCHAR是用来保留变长字符，在数据库中存储空间的大小是实际的字符长度，不会像CHAR一样补上空格，这样占用的空间更少。

从以上特点来看，VARCHAR比CHAR有明显的优势，因此大部份数据库设计时都应该采用VARCHAR类型。那为什么还需要CHAR类型呢，个人认为有以下几个原因：

1、为了跟以前版本的数据库进行一个兼容，因为很久以前数据库只支持CHAR类型，有些应用的业务逻辑也只是针对CHAR类型设计的，所以数据库软件也就一直保留CHAR类型。

2、CHAR类型是定长的，一些数据库可以在每条记录中不存储字段长度信息，这样可以节省部份空间，也可以方便做一些内存对齐提高性能，但个人认为这带来的性能提升非常微小，至少ORACLE数据库是没有意义的。

3、还有说法是有些数据经常修改，长度可能变化，会引起碎片，采用CHAR就不会产生碎片，这个说法比较多，但我认为既然长度会变化，那用VARCHAR更能节省内存与存储空间来提升性能，只要数据块预留的空间没有问题，采用VARCHAR性能更好。

对于ORACLE数据库，我找不到充足的理由来使用CHAR类型，而且CHAR还会带来讨厌的空格，有些文章说MYSQL的MYISAM存储引擎在和长度固定的情况下CHAR比VARCHAR好，这个没有测试过，不太了解。

由于VARCHAR是变长存储，那么很多人会有疑问，比如STATUS字段定义VARCHAR(10)与VARCHAR(1000)有什么区别，反正是变长的，存储空间都一样，省得以后要加长又要改变字段定义。 下面说一下我的理解：

1、字段长度是数据库一种约束，可以保证进入数据库的数据符合长度要求，定义合理的字段长度可以减少一部份非法数据进入，比如：我们业务中STATUS只有‘NEW’,‘DELETE’,‘CLOSE’3种状态，使用VARCHAR(5)保存，这样可以有效的减少非法数据进入，定义合理的长度也可以让人容易理解字段的用途，试想一下，如果你所有的字符字段长度都是VARCHAR(4000)会是什么样的情况。

2、VARCHAR的字段长度虽然对数据存储没有太大影响，但对特定的数据库还是有一些细微差别，比如MYSQL中定义的长度如果小于255，字段长度用1个字节表示，如果超过255，字段的长度将固定用2个字节表示。如果你的业务数据最大长度只有10，但定义长度为256则每条记录会多浪费了一个字节来存储长度。ORACLE没有这样的问题，它会根据每条记录字段的实际长度动态选择长度标识。

3、字段定义的长度对索引也有较大影响。ORACLE对索引长度还是有一定限制，8i官方文档说明单条记录索引信息的长度不能超过数据块大小的40%，9i中是75%，实际上也差不多，具体可以见jametong的http://www.dbthink.com/?p=20这篇文档，里面有详细的测试结果。如果你的数据块大小是8K，那么索引字段的定义长度不能超过6398，比如，你要给表上2个VARCHAR(4000)字段建组合索引，创建时会直接报错。另外索引组织表及在线重建索引(因为中间会临时创建一个索引组织表)允许的索引信息长度更小，只能是数据块大小的40%，实际中8K的数据块大小，要使用在线重建索引，那定义的长度不能超过3215。从以上可以看出，数据块大小为8K时，设计字段时如果要定义为VARCHAR(4000)，那这个字段就不能考虑建立索引，因为即使能建上，也不能做在线重定义 *** 作，DBA要进行索引维护时只能停止应用，这将对系统的可用性产生较大影响。关于ORACLE索引长度限制测试的脚本如下：

关于ORACLE的索引长度还有一些特别的规则，比如自定义函数返回的字符定义长度固定是4000，所以要用自定义函数做函数索引需要特别注意一下，这可能会影响在线重建索引不能 *** 作。

内置函数的索引长度根据函数决定，比如UPPER这种不改变长度的就是索引字段定义的长度，SUBSTR这种会改变长度要根据函数截取长度决定。

NUMBER类型字段的长度固定是22。

DATA类型字段的长度固定是7。

索引默认是升序，如果要降序建的索引长度是字段定义长度*1.5+1。

MYSQL对索引长度限制比较复杂，每种版本及存储引擎都不一样，如下是MYSQL5.1.58测试的结果：

INNODB的最大总长度是3072字节，单个字符字段是767字节，如果字段长度大于767则自动截取前767个字符。

MYISAM最大总长度是1000字节，单个字符字段是1000字节。

MEMORY的最大总长度是3072字节，单个字符字段是3072字节。

4、变长字段定义的长度虽然不会影响服务器数据空间大小，但是对于客户端的内存有影响，因为客户端在用SQL从数据库读取数据时，首先会取到字段定义的长度，然后分配足够的内存，也就是说如果你定义的字段长度是1K，实际长度是10字节，要取1K记录，那客户端会分配1MB的内存， 但只保存了10K有效数据。这将会比较严重的浪费客户端内存。特别是一些高并发或者是取大量数据的场景，容易产生内存溢出。

5、关于字段长度对齐的问题，有些设计人员喜欢定义字段的长度为4或者8的倍数，如16,32,64,128之类的，理由是可以做到内存对齐，对于这个问题我没有深入分析过，个人认为必要性不大，也没看到过这种优化能提升性能的案例。如果一个VARCHAR(1)定义为VARCHAR(4)反而浪费内存与存储，实际上我看到在ORACLE jdbc驱动中会将所有的字符类型数据保存在一个大的char[]中，把所有NUMBER与DATE类型放在另一个char[]中，这样整合后都不清楚如何内存对齐了。

综上所述：VARCHAR类型字段长度不能随便定义，并不是越大越好，还是需要根据实际业务数据定义一个合适的长度。我个人对于一些可以完全预估的长度就按实际长度定义，比如年月、状态、标记之类的信息。对于不确定长度的业务数据如NAME、STYLE之类的信息定义一个合理值，如VARCHAR(20)，VARCHAR(30) 之类 。对于描述性或备注性的信息，这些字段也确定不会有索引，长度也不可预知，所以留更大的长度，避免以后经常进行长度调整，如VARCHAR(1024)，或者直接VARCHAR2(4000) 。