sql char和varchar数据类型两者区别对比-mysql教程-PHP中文网

sql char和varchar数据类型两者区别对比

php中文网

发布： 2016-06-07 17:48:56

原创

2250人浏览过

本文章介绍mssql server中char和varchar数据性能对比分析，详细的利用实例来说明两者的关系，有需要了解的朋友可以参考一下。

1. char
固定长度，最长n个字符。

2. varchar
最大长度为n的可变字符串。
（n为某一整数，不同，最大长度n不同）

如果我们将长度设为10：char(10), varchar(10), 然后我们将值设为： ‘apple’。
Char(10) 存储的值会是：’apple ’.
Varchar(10) 存储的值会是：’apple’.

char 和 varchar 不能存储 unicode 字符。

数据类型	可以存储 unicode	固定长度
char	不	是
nchar	是	是
varchar	不	不
nvarchar	是	不

char和varchar区别：
     varchar必char节省空间，但在效率上比char稍微差一些。
     说varchar比char节省空间，是因为varchar是可变字符串，比如：用varchar(5)存储字符串“abc”，只占用3个字节的存储空间，而用char(5)存储，则占用5个字节（“abc ”）。
     说varchar比char效率稍差，是因为，当修改varchar数据时，可能因为数据长度不同，导致数据迁移（即：多余I/O）。其中，oracle对此多余I/O描述的表达是：“行迁移”（Row Migration）。

看个实例

本次测试的SQL SERVER 2000 的char 与 varchar。

测试分三组,每次增加插入的行数,脚本如下:

代码如下

复制代码

CREATE TABLE [dbo].[testchar] (
[a] [int] IDENTITY (1, 1) NOT NULL ,
[b] [char] (200) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[testvarchar] (
[a] [int] IDENTITY (1, 1) NOT NULL ,
[b] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
--truncate table testvarchar
--truncate table testchar
declare @i as int
set @i=0
while @i begin
insert into testvarchar values(cast(@i as varchar(200)))
set @i=@i+1
if @i=50000
break
end
declare @i as int
set @i=0
while @i begin
insert into testchar values(cast(@i as char(200)))
set @i=@i+1
if @i=50000
break
end

实验结果:

插入行数	数据类型	Cpu	duration
50000	varchar	2359	21203
50000	char	2344	22250
100000	varchar	4156	44500
100000	char	4172 Operator OpenAI推出的AI智能体工具 231 查看详情	44186
2000000	varchar	8907	89093
2000000	char	9188	96530

两表存储空间比较

代码如下	复制代码
Testchar:73.94M Testvarchar:7.94M

从国外网站找到一则实例

Fixed-length (char) or variable-length (varchar) character data types.

char[(n)]

Fixed-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is n bytes. The SQL-92 synonym for char is character.

varchar[(n)]

Variable-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is the actual length in bytes of the data entered, not n bytes. The data entered can be 0 characters in length. The SQL-92 synonyms for varchar are char varying or character varying.

Remarks
When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30.

Objects using 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 used to store the character data.

Sites supporting multiple languages should consider using the Unicode nchar or nvarchar data types to minimize character conversion issues. If you use char or varchar:

Use char when the data values in a column are expected to be consistently close to the same size.

Use varchar when the data values in a column are expected to vary considerably in size.
If SET ANSI_PADDING is OFF when CREATE TABLE or ALTER TABLE is executed, a char column defined as NULL is handled as varchar.

When the collation code page uses double-byte characters, the storage size is still n bytes. Depending on the character string, the storage size of n bytes may be less than n characters.