文章存档
相关博文
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
分类： oracle
2024-07-02 11:35:26
oracle数据库的varchar2(n){banned}最佳多可以储存多少个中文字符呢？
varchar2(n char)可以储存n个中文字符（一个中文字符数据，gbk正常占用2个字节，utf-8占用3个字节数据）
varchar2(n byte)可以储存n个字节数据
varchar2(n)对应应该是char还是byte由数据库参数: nls_length_semantics 控制。
类似：
sql> select name,value from v$parameter where name='nls_length_semantics';
name
----------------------------------------------------------------------------
value
----------------------------------------------------------------------------
nls_length_semantics
byte
这个数据库的varchar2(n)对应就是varchar2(n byte)
varchar2(4000 char)就可以储存4000个中文字符吗？
通过查询oracle文档和测试发现：
varchar2(n char)/varchar2(n byte)在：12c以前{banned}最佳多只能储存4000个字节！注意是字节。
对应gbk字符集{banned}最佳多可以储存2000个中文字符。
utf-8字符集应该就是4000/3=1333个中文字符。
12c以后（含12c）
select count(1) from v$parameter where upper(name)='max_string_size' and upper(value)='extended'
如果返回为1：varchar2(n)n{banned}最佳大为32767个字节。
返回为0：{banned}最佳大储存4000个字节。（和12c以前就一样）
也就是说varchar2(4000 char)，max_string_size为extended时候，{banned}最佳大可以储存4000个中文字符串，因为4000个中文字符对应字节数为：4000*2=8000 or 4000*3=12000都没有超过32767个字节，所以是可以储存4000个中文字符的。
varchar2(32767 char){banned}最佳大可以储存多少个中文字符呢？gbk就是16383，utf-8就是10922
类似的nvarchar2和这个varchar2类似。
char(n char) {banned}最佳大储存2000字节，n个中文字符。这两个条件是同时起作用的，任何一个超过上限就会报错。
oracle 10.2的文档：
varchar2(size [byte | char])
variable-length character string having maximum length size bytes or characters. maximum size is 4000 bytes or characters, and minimum is 1 byte or 1 character. you must specify size for varchar2.
byte indicates that the column will have byte length semantics; char indicates that the column will have character semantics.
nvarchar2(size)
variable-length unicode character string having maximum length size characters. the number of bytes can be up to two times size for al16utf16 encoding and three times size for utf8 encoding. maximum size is determined by the national character set definition, with an upper limit of 4000 bytes. you must specify size for nvarchar2.
char [(size [byte | char])]
fixed-length character data of length size bytes. maximum size is 2000 bytes or characters. default and minimum size is 1 byte.
byte and char have the same semantics as for varchar2.
nchar[(size)]
fixed-length character data of length size characters. the number of bytes can be up to two times size for al16utf16 encoding and three times size for utf8 encoding. maximum size is determined by the national character set definition, with an upper limit of 2000 bytes. default and minimum size is 1 character.
19c文档：
varchar2(size [byte | char])
variable-length character string having maximum length size bytes or characters. you must specify size for varchar2. minimum size is 1 byte or 1 character. maximum size is:
32767 bytes or characters if max_string_size = extended
4000 bytes or characters if max_string_size = standard
refer to extended data types for more information on the max_string_size initialization parameter.
byte indicates that the column will have byte length semantics. char indicates that the column will have character semantics.
varchar2(n char)可以储存n个中文字符（一个中文字符数据，gbk正常占用2个字节，utf-8占用3个字节数据）
varchar2(n byte)可以储存n个字节数据
varchar2(n)对应应该是char还是byte由数据库参数: nls_length_semantics 控制。
类似：
sql> select name,value from v$parameter where name='nls_length_semantics';
name
----------------------------------------------------------------------------
value
----------------------------------------------------------------------------
nls_length_semantics
byte
这个数据库的varchar2(n)对应就是varchar2(n byte)
varchar2(4000 char)就可以储存4000个中文字符吗？
通过查询oracle文档和测试发现：
varchar2(n char)/varchar2(n byte)在：12c以前{banned}最佳多只能储存4000个字节！注意是字节。
对应gbk字符集{banned}最佳多可以储存2000个中文字符。
utf-8字符集应该就是4000/3=1333个中文字符。
12c以后（含12c）
select count(1) from v$parameter where upper(name)='max_string_size' and upper(value)='extended'
如果返回为1：varchar2(n)n{banned}最佳大为32767个字节。
返回为0：{banned}最佳大储存4000个字节。（和12c以前就一样）
也就是说varchar2(4000 char)，max_string_size为extended时候，{banned}最佳大可以储存4000个中文字符串，因为4000个中文字符对应字节数为：4000*2=8000 or 4000*3=12000都没有超过32767个字节，所以是可以储存4000个中文字符的。
varchar2(32767 char){banned}最佳大可以储存多少个中文字符呢？gbk就是16383，utf-8就是10922
类似的nvarchar2和这个varchar2类似。
char(n char) {banned}最佳大储存2000字节，n个中文字符。这两个条件是同时起作用的，任何一个超过上限就会报错。
oracle 10.2的文档：
varchar2(size [byte | char])
variable-length character string having maximum length size bytes or characters. maximum size is 4000 bytes or characters, and minimum is 1 byte or 1 character. you must specify size for varchar2.
byte indicates that the column will have byte length semantics; char indicates that the column will have character semantics.
nvarchar2(size)
variable-length unicode character string having maximum length size characters. the number of bytes can be up to two times size for al16utf16 encoding and three times size for utf8 encoding. maximum size is determined by the national character set definition, with an upper limit of 4000 bytes. you must specify size for nvarchar2.
char [(size [byte | char])]
fixed-length character data of length size bytes. maximum size is 2000 bytes or characters. default and minimum size is 1 byte.
byte and char have the same semantics as for varchar2.
nchar[(size)]
fixed-length character data of length size characters. the number of bytes can be up to two times size for al16utf16 encoding and three times size for utf8 encoding. maximum size is determined by the national character set definition, with an upper limit of 2000 bytes. default and minimum size is 1 character.
19c文档：
varchar2(size [byte | char])
variable-length character string having maximum length size bytes or characters. you must specify size for varchar2. minimum size is 1 byte or 1 character. maximum size is:
32767 bytes or characters if max_string_size = extended
4000 bytes or characters if max_string_size = standard
refer to extended data types for more information on the max_string_size initialization parameter.
byte indicates that the column will have byte length semantics. char indicates that the column will have character semantics.
给主人留下些什么吧！~~