在 SQL 中选择 CHAR 而不是 VARCHAR 的用例是什么? [英] What are the use cases for selecting CHAR over VARCHAR in SQL?

问题描述

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

I realize that CHAR is recommended if all my values are fixed-width. But, so what? Why not just pick VARCHAR for all text fields just to be safe.

推荐答案

一般规则是如果所有行的长度都接近相同，则选择 CHAR.当长度显着变化时，选择VARCHAR(或NVARCHAR).CHAR 也可能快一点，因为所有行的长度都相同.

The general rule is to pick CHAR if all rows will have close to the same length. Pick VARCHAR (or NVARCHAR) when the length varies significantly. CHAR may also be a bit faster because all the rows are of the same length.

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

It varies by DB implementation, but generally, VARCHAR (or NVARCHAR) uses one or two more bytes of storage (for length or termination) in addition to the actual data. So (assuming you are using a one-byte character set) storing the word "FooBar"

• CHAR(6) = 6 字节(无开销)
• VARCHAR(100) = 8 个字节(2 个字节的开销)
• CHAR(10) = 10 个字节(4 个字节的浪费)

• CHAR(6) = 6 bytes (no overhead)
• VARCHAR(100) = 8 bytes (2 bytes of overhead)
• CHAR(10) = 10 bytes (4 bytes of waste)

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

The bottom line is CHAR can be faster and more space-efficient for data of relatively the same length (within two characters length difference).

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

Note: Microsoft SQL has 2 bytes of overhead for a VARCHAR. This may vary from DB to DB, but generally, there is at least 1 byte of overhead needed to indicate length or EOL on a VARCHAR.

正如Gaven 在评论中指出的那样:事情发生变化时来到多字节字符集，这是 VARCHAR 成为更好选择的情况.

As was pointed out by Gaven in the comments: Things change when it comes to multi-byte characters sets, and is a is case where VARCHAR becomes a much better choice.

关于VARCHAR声明长度的说明:因为它存储了实际内容的长度，所以你不会浪费未使用的长度.因此，在 VARCHAR(6)、VARCHAR(100)、或 VARCHAR(MAX) 中存储 6 个字符使用相同的存储量.详细了解使用 VARCHAR(MAX) 时的差异.您可以在 VARCHAR 中声明最大大小以限制存储的数量.

A note about the declared length of the VARCHAR: Because it stores the length of the actual content, then you don't waste unused length. So storing 6 characters in VARCHAR(6), VARCHAR(100), or VARCHAR(MAX) uses the same amount of storage. Read more about the differences when using VARCHAR(MAX). You declare a maximum size in VARCHAR to limit how much is stored.

在评论中 AlwaysLearning 指出 Microsoft Transact-SQL 文档 似乎说反了.我认为这是一个错误，或者至少文档不清楚.

In the comments AlwaysLearning pointed out that the Microsoft Transact-SQL docs seem to say the opposite. I would suggest that is an error or at least the docs are unclear.

这篇关于在 SQL 中选择 CHAR 而不是 VARCHAR 的用例是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！