在SQL中选择CHAR over VARCHAR的用例有哪些？

我意识到如果我的所有值都是固定宽度的话,建议使用CHAR.但是,那又怎样？为什么不为所有文本字段选择VARCHAR只是为了安全.

1> Jim McKeeth..：

---

如果所有行都具有接近相同的长度,通常选择CHAR.当长度变化很大时选择VARCHAR.CHAR也可能更快一些,因为所有行的长度都相同.

它因DB实现而异,但通常除了实际数据外,VARCHAR还使用一个或两个以上的存储字节(用于长度或终止).因此(假设您使用的是单字节字符集)存储单词"FooBar"

CHAR(6)= 6个字节(无开销)

VARCHAR(10)= 8个字节(2个字节的开销)

CHAR(10)= 10个字节(4个字节的开销)

底线是CHAR对于相对相同长度的数据(在两个字符长度差异内)可以更快且更节省空间.

注意:Microsoft SQL对VARCHAR有2个字节的开销.这可能因DB而异,但通常需要至少1个字节的开销来指示VARCHAR上的长度或EOL.

正如Gaven在评论中指出的那样,如果你使用像UTF8这样的多字节可变长度字符集,那么CHAR存储了存储字符数所需的最大字节数.因此,如果UTF8最多需要3个字节来存储一个字符,那么即使只存储latin1字符,CHAR(6)也将固定为18个字节.所以在这种情况下,VARCHAR成为一个更好的选择.

---

另一个原因是页面拆分和碎片.由于varchar列上的页面拆分,我有一个带有IDEN PK的表,其中99%碎片化.一个非常活跃的表,根据应用程序的性质,将创建一个新的行空行,然后填充.Char修复了碎片问题.

---

@Jim McKeeth - 这些计算只有在你使用latin1字符集时才有效.由于现在大多数人都应该使用utf8,因此你的CHAR列将平均使用3倍的空间作为VARCHAR,它主要存储基本多语言平面中的字符.

---

@JimMcKeeth是的,这是完全正确的.由于CHAR是固定长度,因此必须将其固定在可以使用的最大可能空间.在UTF8中,每个字符3个字节.对于varchar,可以根据需要为每个字符使用1-3个字节.这是在MySQL手册中:http://dev.mysql.com/doc/refman/5.0/en/charset-unicode-utf8.html

---

@GavinTowey SQLSERVER将UCS-2用于其NCHAR和NVARCHAR数据类型.每个字符总是两个字节.

---

字符串FooBar和varchar(100)与char(100)有什么区别？我认为这表明差异更大,是吗？没有？

---

@Nenotlep对于字符串"FooBar",VARCHAR(100)将是8个字节(2个字节的开销),而CHAR(100)将是100个字节(94个字节的开销).使用CHAR时,您将分配在设计时指定的固定字节数.当您使用VARCHAR时,只要数据发生更改,空间分配就会发生变化,并且总是有2个字节的开销(可能是为了存储可变长度数据的当前长度).

2> Ethan Post..：

---

如果您正在与我合作并且您正在与Oracle合作,我可能会让您varchar在几乎所有情况下使用.char使用较少的处理能力的假设varchar可能是真的...现在......但数据库引擎随着时间的推移变得更好,这种一般规则构成了未来的"神话".

另一件事:我从未见过性能问题,因为有人决定一起去varchar.您将更好地利用编写良好代码的时间(减少对数据库的调用)和高效的SQL(索引如何工作,优化器如何做出决策,为什么exists比in通常更快......).

最后的想法:我已经看到了各种各样的使用问题CHAR,人们在寻找"他们应该寻找的时候",或者人们在寻找'FOO'时他们应该寻找'FOO(这里有一堆空间)'或者没有修剪尾随空白的人,或者Powerbuilder的错误,它将从Oracle过程返回的值加起来最多2000个空白.

---

我对你的第一段不同意,因为char可能提供一个对优化器有用的提示,甚至是未来的提示,它可能有助于传达该列的意图.但是你的第三段+1.我讨厌所有额外的空间.一个字段应该只存储我放入其中的任何内容而不需要所有[解释]填充.基本上,我只是使用char,如果所有数据的长度完全相同,不多也不少,现在和永远.当然,这是非常罕见的,通常是char(1).

3> Hank Gay..：

---

除了性能优势之外,CHAR还可以用于指示所有值应该是相同的长度,例如,美国州缩写的列.

4> Jarrett Meye..：

---

Char有点快,所以如果你有一个你知道的列将是一定的长度,使用char.例如,对于性别存储(M)ale /(F)emale /(U)n,对于美国州存储2个字符.

---

@Andrew,并非所有db都支持ENUM数据类型.例如,MSSQLServer没有.另外,存储为int的ENUM占用4个字节.CHAR(1)占用1个字节,NCHAR(1)占用2个字节.

---

不确定这是一个很好的答案,因为ENUM通常会更有意义,虽然我不确定该类型有多广泛支持(在MySQL之外).

---

不好的例子,ENUM最适合这种情况.更好的例子是3个字母的IATA机场代码

5> 小智..：

---

NChar或Char的表现是否更好？

好问题.在某些情况下,简单的答案是肯定的.让我们看看是否可以解释这一点.

显然我们都知道,如果我创建一个包含varchar(255)列的表(让我们调用此列myColumn)并插入一百万行,但每行只插入几个字符到myColumn中,表格将会小得多(整体而言)存储引擎所需的数据页数比我创建myColumn为char(255).无论何时我在该表上执行操作(DML)并请求很多行,当myColumn是varchar时它会更快,因为我不必在末尾移动所有那些"额外"空格.移动,就像在SQL Server执行内部排序时一样,例如在不同或联合操作期间,或者在它的查询计划期间选择合并等等.移动也可能意味着将数据从服务器获取到本地的时间个人电脑或另一台电脑或任何消费者.

但是使用varchar会有一些开销.SQL Server必须在每行上使用两个字节的指示符(开销)来知道特定行的myColumn在其中有多少字节.这不是提出问题的额外2个字节,而是必须在每行上"解码"myColumn中数据的长度.

根据我的经验,最有意义的是在将要在查询中连接的列上使用char而不是varchar.例如,表的主键或将被索引的其他列.人口统计表上的CustomerNumber,或解码表上的CodeID,或订单表上的OrderNumber.通过使用char,查询引擎可以更快地执行连接,因为它可以执行直接指针运算(确定性),而不必在读取页面时将其指针移动为可变数量的字节.我知道我可能在最后一句话中失去了你.SQL Server中的联接基于"谓词"的概念.谓词是一个条件.例如myColumn = 1,或OrderNumber <500.

因此,如果SQL Server正在执行DML语句,并且连接的谓词或"键"是固定长度(char),则查询引擎不必执行足够的工作来匹配从一个表到行的行另一张桌子.它不必知道数据在行中的时间长度,然后沿着字符串向下走以找到结束.所有这些都需要时间.

现在请记住,这很容易实现.我见过用于在线系统中主键字段的字符.宽度必须保持很小,即char(15)或合理的东西.并且它在在线系统中效果最好,因为您通常只检索或插入少量行,因此必须"rtrim"您将在结果集中获得的尾随空格是一项微不足道的任务,而不是必须加入数百万从一个表到另一个表上的数百万行的行.

CHAR对在线系统上的varchar有意义的另一个原因是它减少了页面拆分.通过使用char,您实际上是"保留"(并浪费)该空间,因此如果用户稍后出现并将更多数据放入该列,则SQL已经为其分配了空间并且在其中.

使用CHAR的另一个原因与第二个原因类似.如果程序员或用户对数百万行进行"批量"更新,例如在注释字段中添加一些句子,则不会在半夜收到DBA的电话,想知道为什么他们的驱动器已满.换句话说,它可以使数据库的大小更可预测地增长.

所以这些是在线(OLTP)系统可以从char over varchar中受益的3种方式.我几乎没有在仓库/分析/ OLAP场景中使用char,因为通常你有很多数据,所有这些char列可以累积大量的浪费空间.

请记住,char可以使您的数据库更大,但大多数备份工具都有数据压缩,因此您的备份大小与使用varchar时的大小相同.例如LiteSpeed或RedGate SQL备份.

另一个用途是在为将数据导出到固定宽度文件而创建的视图中.假设我必须将一些数据导出到平面文件以供大型机读取.它是固定宽度(不分隔).我喜欢将数据存储在我的"staging"表中作为varchar(因此在我的数据库上占用更少的空间),然后使用视图将所有内容CAST到它的等效char,其长度对应于该列的固定宽度的宽度.例如:

create table tblStagingTable (
pkID BIGINT (IDENTITY,1,1),
CustomerFirstName varchar(30),
CustomerLastName varchar(30),
CustomerCityStateZip varchar(100),
CustomerCurrentBalance money )

insert into tblStagingTable
(CustomerFirstName,CustomerLastName, CustomerCityStateZip) ('Joe','Blow','123 Main St Washington, MD 12345', 123.45)

create view vwStagingTable AS
SELECT CustomerFirstName = CAST(CustomerFirstName as CHAR(30)),
CustomerLastName = CAST(CustomerLastName as CHAR(30)),
CustomerCityStateZip = CAST(CustomerCityStateZip as CHAR(100)),
CustomerCurrentBalance = CAST(CAST(CustomerCurrentBalance as NUMERIC(9,2)) AS CHAR(10))

SELECT * from vwStagingTable

这很酷,因为我内部的数据占用的空间较少,因为它使用的是varchar.但是当我使用DTS或SSIS或者甚至只是从SSMS剪切和粘贴到记事本时,我可以使用视图并获得正确数量的尾随空格.在DTS我们曾经有一个叫做的功能,该死的我忘了我认为它被称为"建议列"或其他东西.在SSIS中,您不能再这样做了,您必须繁琐地定义平面文件连接管理器.但是,由于您具有视图设置,因此SSIS可以知道每列的宽度,并且在构建数据流任务时可以节省大量时间.

所以底线...使用varchar.使用char的原因很少,而且仅出于性能原因.如果您的系统具有数百万行的数据,那么如果谓词是确定性的(char),您会看到明显的差异,但对于大多数使用char的系统而言,只是浪费空间.

希望有所帮助.杰夫

6> Tony BenBrah..：

---

有性能优势,但这里没有提到:行迁移.使用char,你可以预先保留整个空间.所以我们说你有一个char(1000),你存储了10个字符,你将耗尽所有1000个空格字符.在varchar2(1000)中,您将只使用10个字符.修改数据时出现问题.假设您将列更新为包含900个字符.扩展varchar的空间可能在当前块中不可用.在这种情况下,数据库引擎必须将行迁移到另一个块,并将原始块中的指针指向新块中的新行.要读取此数据,数据库引擎现在必须读取2个块.
没有人可以暧昧地说varchar或char更好.有一个时间权衡的空间,并考虑数据是否会更新,特别是如果它有可能增长.

7> Bryan Rehbei..：

---

早期性能优化与使用最佳实践类型的规则之间存在差异.如果要创建新表,其中总是有一个固定长度字段,那么使用CHAR是有意义的,在这种情况下你应该使用它.这不是早期优化,而是实施经验法则(或最佳实践).

ie - 如果您有2个字母的州字段,请使用CHAR(2).如果您有一个包含实际状态名称的字段,请使用VARCHAR.

8> Grzegorz Gie..：

---

我会选择varchar,除非该列存储固定值,如美国州代码 - 总是2个字符长,并且有效的美国州代码列表不会经常更改:).

在其他所有情况下,即使存储哈希密码(固定长度),我也会选择varchar.

为什么 - char类型列始终使用空格来实现,这使得列my_column被定义为char(5),并且在比较中具有值'ABC':

my_column = 'ABC' -- my_column stores 'ABC  ' value which is different then 'ABC'

假.

此功能可能会在开发过程中导致许多恼人的错误,并使测试更加困难.

9> Scott Duffy..：

---

如果该字段中的所有数据值都相同,则CHAR占用的存储空间比VARCHAR少.现在也许在2009年,如果你将VARCHAR转换为CHAR,800GB数据库对于所有意图和目的都是相同的,但是对于短字符串(1或2个字符),CHAR仍然是行业"最佳实践".

现在,如果你看一下大多数数据库提供的各种数据类型,即使只有整数(bit,tiny,int,bigint),也有理由选择其中一种.每次只选择bigint实际上对该领域的目的和用途有点无知.如果一个字段只代表一个年龄的人,那么bigint就是矫枉过正.现在它不一定是"错误的",但效率不高.

但它是一个有趣的论点,随着数据库随着时间的推移而改进,可以认为CHAR与VARCHAR相关性较低.