文档章节

对Mysql的字符/字节的深入学习总结

GavinTop
 GavinTop
发布于 2014/06/10 21:42
字数 2653
阅读 352
收藏 1
点赞 0
评论 4


一直以来对mysql字符/字节长度这个地方就有误区:

1.int,varchar列类型的数据字段的length设置为4,8,16,32 这种长度就是字节的存储长度,就可以内存对齐了;

2.utf8字符集下一个汉字占用三个字节,GBK下一个汉字占用两个字节,任何字符集下一个英文字符占用一个字节,这个结论是正确的但是没有找到依据。

一、谈一下列类型的存储需求(此处的存储单位是字节):

数值类型存储需求

列类型

存储需求

TINYINT

1个字节

SMALLINT

2个字节

MEDIUMINT

3个字节

INT, INTEGER

4个字节

BIGINT

8个字节

FLOAT(p)

如果0 <= p <= 24为4个字节, 如果25   <= p <= 53为8个字节

FLOAT

4个字节

DOUBLE [PRECISION], item REAL

8个字节

DECIMAL(M,D),   NUMERIC(M,D)

变长;参见下面的讨论

BIT(M)

大约(M+7)/8个字节

 

附上取值范围:

类型

字节

最小值

最大值



(带符号的/无符号的)

(带符号的/无符号的)

TINYINT

1

-128

127



0

255

SMALLINT

2

-32768

32767



0

65535

MEDIUMINT

3

-8388608

8388607



0

16777215

INT

4

-2147483648

2147483647



0

4294967295

BIGINT

8

-9223372036854775808

9223372036854775807



0

18446744073709551615

 

 

DECIMAL(和NUMERIC)的存储需求与具体版本有关:

使用二进制格式将9个十进制(基于10)数压缩为4个字节来表示DECIMAL列值。每个值的整数和分数部分的存储分别确定。每个9位数的倍数需要4个字节,并且剩余的位需要4个字节的一部分。下表给出了超出位数的存储需求:

剩余的

字节

位数

数目

0

0

1

1

2

1

3

2

4

2

5

3

6

3

7

4

8

4

9

4

 

日期和时间类型的存储需求

列类型

存储需求

DATE

3个字节

DATETIME

8个字节

TIMESTAMP

4个字节

TIME

3个字节

YEAR

1个字节

 

字符串类型的存储需求

列类型

存储需求

CHAR(M)

M个字节,0 <= M   <= 255

VARCHAR(M)

L+1个字节,其中L <= M 且0 <= M <=   65535(参见下面的注释)

BINARY(M)

M个字节,0 <= M   <= 255

VARBINARY(M)

L+1个字节,其中L <= M 且0 <= M <=   255

TINYBLOB, TINYTEXT

L+1个字节,其中L < 28

BLOB, TEXT

L+2个字节,其中L < 216

MEDIUMBLOB, MEDIUMTEXT

L+3个字节,其中L < 224

LONGBLOB, LONGTEXT

L+4个字节,其中L < 232

ENUM('value1','value2',...)

1或2个字节,取决于枚举值的个数(最多65,535个值)

SET('value1','value2',...)

1、2、3、4或者8个字节,取决于set成员的数目(最多64个成员)

 

VARCHAR、BLOB和TEXT类是变长类型。每个类型的存储需求取决于列值的实际长度(用前面的表中的L表示),而不是该类型的最大可能的大小。例如,VARCHAR(10)列可以容纳最大长度为10的字符串。实际存储需求是字符串(L)的长度,加上一个记录字符串长度的字节。对于字符串'abcd',L是4,存储需要5个字节。

备注:这个地方是英文字符,不包含中文字符(简单地将一个中文字理解为一个字符)

对于CHAR、VARCHAR和TEXT类型,前面的表中的值LM应解释为字符数目,并且列定义中的这些类型的长度表示字符数目。例如,要想保存一个TINYTEXT值需要L字符+ 1个字节。

要想计算用于保存具体CHAR、VARCHAR或者TEXT列值的字节数,需要考虑该列使用的字符集。在具体情况中,当使用Unicode时,必须记住所有Unicode字符使用相同的字节数。

注释:VARCHAR列的有效最大长度为65,532字符。

 

Char和varchar的对比:

CHAR和VARCHAR类型类似,但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

CHAR和VARCHAR类型声明的长度表示你想要保存的最大字符数。例如,CHAR(30)可以占用30个字符。

CHAR列的长度固定为创建表时声明的长度。长度可以为从0到255的任何值。当保存CHAR值时,在它们的右边填充空格以达到指定的长度。当检索到CHAR值时,尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。

VARCHAR列中的值为可变长字符串。长度可以指定为0到65,535之间的值。(VARCHAR的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是65,532字节)。

同CHAR对比,VARCHAR值保存时只保存需要的字符数,另加一个字节来记录长度(如果列声明的长度超过255,则使用两个字节)。

VARCHAR值保存时不进行填充。当值保存和检索时尾部的空格仍保留,符合标准SQL。

如果分配给CHAR或VARCHAR列的值超过列的最大长度,则对值进行裁剪以使其适合。如果被裁掉的字符不是空格,则会产生一条警告。如果裁剪非空格字符,则会造成错误(而不是警告)并通过使用严格SQL模式禁用值的插入。

 

下面的表显示了将各种字符串值保存到CHAR(4)和VARCHAR(4)列后的结果,说明了CHAR和VARCHAR之间的差别:

CHAR(4)

存储需求

VARCHAR(4)

存储需求

''

'    '

4个字节

''

1个字节

'ab'

'ab  '

4个字节

'ab '

3个字节

'abcd'

'abcd'

4个字节

'abcd'

5个字节

'abcdefgh'

'abcd'

4个字节

'abcd'

5个字节

 

所以这个地方就说明以下几个问题:

1.varchar型数据和char型并不是只是存储长度可变和不可变的区别,还在于二者的存储结构上,在小于等于255的长度内,同一数据保存为varchar比保存为char多占一个字节(我理解的是用于标识是char型还是varchar型),在大于255的长度时,varchar比char多占用两个字节的存储长度。

2.就int等固定存储长度的列类型而言,不管设计数据库的时候设置的length值为多少,仍然是占用4个字节。以前总是会误以为int(3)只能存储3个长度的数字,int(11)就会存储11个长度的数字,这是大错特错的。其实当我们在选择使用int的类型的时候,不论是int(3)还是int(11),它在数据库里面存储的都是4个字节的长度,在使用int(3)的时候如果你输入的是10,会默认给你存储位010,也就是说这个3代表的是默认的一个长度,当你不足3位时,会帮你不全,当你超过3位时,就没有任何的影响。 int(10)与int(11)有什么区别,他们之间除了在存储的时候稍微有点区别外,在我们使用的时候是没有任何区别的。int(10)也可以代表2147483647这个值int(11)也可以代表。

 

http://blog.sina.com.cn/s/blog_610997850100wjrm.html

 

二、mysql里varchar/char等设置的length是字符长度还是字节长度

 

首先谈下字符和字节的区别:

 

http://www.regexlab.com/zh/encoding.htm

 

然后来看下下面的例子:

新建表为:

CREATE TABLE `qc_questionnaire_list` (

`id` int(16) unsigned NOT NULL AUTO_INCREMENT COMMENT 'questionnaire',

`title` varchar(31) DEFAULT NULL COMMENT '问卷标题(10个字)',

`theme` varchar(127) DEFAULT NULL COMMENT '问卷主题(30个字)',

`test` varchar(2) DEFAULT NULL,

`test1` char(2) DEFAULT NULL,

`test2` int(11) DEFAULT NULL,

`test3` int(16) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

 

插入数据时test和test1都为:啊啊啊,最终都只存入:啊啊

 

 

test和test1一个为varchar,一个为char,

 

解释下length()和char_length()


再修改下数据:

 

 

length()函数返回的是字节长度,char_length()返回的是字符的长度,所以可以看出:创建表时设置的length是字符的长度,而不是字节的长度。

 

 

http://cau99.blog.51cto.com/1855224/383023/

 

 

三、utf8字符集下一个汉字占用三个字节,GBK下一个汉字占用两个字节,任何字符集下一个英文字符占用一个字节的理论依据

 

从二中也可以看出,实际上utf8下汉字是占用了三个字节,英文字符占用一个字节

 

 

mysql> SHOW CHARACTER SET;

+----------+-----------------------------+---------------------+--------+

| Charset  | Description                 | Default collation   | Maxlen |

+----------+-----------------------------+---------------------+--------+

| big5     | Big5 Traditional Chinese    | big5_chinese_ci     |      2 |

| dec8     | DEC West European           | dec8_swedish_ci     |      1 |

| cp850    | DOS West European           | cp850_general_ci    |      1 |

| hp8      | HP West European            | hp8_english_ci      |      1 |

| koi8r    | KOI8-R Relcom Russian       | koi8r_general_ci    |      1 |

| latin1   | cp1252 West European        | latin1_swedish_ci   |      1 |

| latin2   | ISO 8859-2 Central European | latin2_general_ci   |      1 |

| swe7     | 7bit Swedish                | swe7_swedish_ci     |      1 |

| ascii    | US ASCII                    | ascii_general_ci    |      1 |

| ujis     | EUC-JP Japanese             | ujis_japanese_ci    |      3 |

| sjis     | Shift-JIS Japanese          | sjis_japanese_ci    |      2 |

| hebrew   | ISO 8859-8 Hebrew           | hebrew_general_ci   |      1 |

| tis620   | TIS620 Thai                 | tis620_thai_ci      |      1 |

| euckr    | EUC-KR Korean               | euckr_korean_ci     |      2 |

| koi8u    | KOI8-U Ukrainian            | koi8u_general_ci    |      1 |

| gb2312   | GB2312 Simplified Chinese   | gb2312_chinese_ci   |      2 |

| greek    | ISO 8859-7 Greek            | greek_general_ci    |      1 |

| cp1250   | Windows Central European    | cp1250_general_ci   |      1 |

| gbk      | GBK Simplified Chinese      | gbk_chinese_ci      |      2 |

| latin5   | ISO 8859-9 Turkish          | latin5_turkish_ci   |      1 |

| armscii8 | ARMSCII-8 Armenian          | armscii8_general_ci |      1 |

| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |

| ucs2     | UCS-2 Unicode               | ucs2_general_ci     |      2 |

| cp866    | DOS Russian                 | cp866_general_ci    |      1 |

| keybcs2  | DOS Kamenicky Czech-Slovak  | keybcs2_general_ci  |      1 |

| macce    | Mac Central European        | macce_general_ci    |      1 |

| macroman | Mac West European           | macroman_general_ci |      1 |

| cp852    | DOS Central European        | cp852_general_ci    |      1 |

| latin7   | ISO 8859-13 Baltic          | latin7_general_ci   |      1 |

| utf8mb4  | UTF-8 Unicode               | utf8mb4_general_ci  |      4 |

| cp1251   | Windows Cyrillic            | cp1251_general_ci   |      1 |

| utf16    | UTF-16 Unicode              | utf16_general_ci    |      4 |

| utf16le  | UTF-16LE Unicode            | utf16le_general_ci  |      4 |

| cp1256   | Windows Arabic              | cp1256_general_ci   |      1 |

| cp1257   | Windows Baltic              | cp1257_general_ci   |      1 |

| utf32    | UTF-32 Unicode              | utf32_general_ci    |      4 |

| binary   | Binary pseudo charset       | binary              |      1 |

| geostd8  | GEOSTD8 Georgian            | geostd8_general_ci  |      1 |

| cp932    | SJIS for Windows Japanese   | cp932_japanese_ci   |      2 |

| eucjpms  | UJIS for Windows Japanese   | eucjpms_japanese_ci |      3 |

+----------+-----------------------------+---------------------+--------+

 

可以设置字符集为GBK然后再看下char_length,length就明白了。

 

总结

 

1.为了加快查询和更新速度,要考虑内存对齐原则,加上空间的考虑,优先SMALLINT,INT,BIGINT,FLOAT,DOUBLE ,最后才是考虑varchar,char型。

 

2.在设计数据表的时候对于int/float/double/date等数据类型不用纠结于设定多少长度,长度只是用于显示而已,对于存储没有任何意义,varchar/char长度只需设置到最大的字数(或者字符数)就可以了,除非能够准确地判断该字段只存汉字或者只存英文(这样的可以参照内存对齐原则)。

 

最后:大家一定要多看官方文档,这篇文章里的大多数东西都是来源于官文文档。

 


© 著作权归作者所有

共有 人打赏支持
GavinTop
粉丝 18
博文 63
码字总数 48967
作品 0
朝阳
高级程序员
加载中

评论(4)

GavinTop
GavinTop

引用来自“0x0bject”的评论

引用来自“Gavin-Top”的评论

引用来自“0x0bject”的评论

OSChina博客的排版真是蛋疼啊……感觉表格被切了一块的样子……1

我直接把word里面的复制过来的,感悟很深啊
……我觉得这些东西太细节了,用到查就好了。

嗯 是的 这是个理解的总结
0x0bject
0x0bject

引用来自“Gavin-Top”的评论

引用来自“0x0bject”的评论

OSChina博客的排版真是蛋疼啊……感觉表格被切了一块的样子……1

我直接把word里面的复制过来的,感悟很深啊
……我觉得这些东西太细节了,用到查就好了。
GavinTop
GavinTop

引用来自“0x0bject”的评论

OSChina博客的排版真是蛋疼啊……感觉表格被切了一块的样子……1

我直接把word里面的复制过来的,感悟很深啊
0x0bject
0x0bject
OSChina博客的排版真是蛋疼啊……感觉表格被切了一块的样子……1
Java开发

JVM 内存溢出实例 - 实战 JVM(二) 介绍 JVM 内存溢出产生情况分析 Java - 注解详解 详细介绍 Java 注解的使用,有利于学习编译时注解 Java 程序员快速上手 Kotlin 11 招 这篇文章主要是写给...

掘金官方 ⋅ 01/04 ⋅ 0

mysql的char,varchar,text,blob的几点个人理解

mysql的char,varchar,text,blob是几个有联系但是有有很大区别的字段类型,这算是mysql的基础吧,可是基础没有学好,恶补一下。 先简单的总结一下: char:定长,最大255个字符 varchar:变长...

haorizi ⋅ 2013/01/28 ⋅ 0

深入理解MySQL 5.7 GTID系列(一)

MySQL GTID特性是5.6加入的一个强大的特性,它的目的在于使用GTID的MySQL能够在整个复制环境中能够自动地切换,而不像以前需要指定文件和位置,这也一定是未来发展的方向,我们熟知的MGR也是...

技术小能手 ⋅ 01/08 ⋅ 0

从职业发展到技术建议,谈DBA“特种兵”的修炼之道

本文根据杨建荣老师在〖DAMS 2017中国数据资产管理峰会〗现场演讲内容整理而成。 src="https://mmbiz.qlogo.cn/mmbizjpg/tibrg3AoIJTt095HmvAY8CXyx8vj4dwjiaZ2D4K1NGiahPFt22IpFGdEIMFKN7C...

杨建荣 ⋅ 2017/07/18 ⋅ 0

深入Mysql字符集设置

基本概念 • 字符(Character)是指人类语言中最小的表义符号。例如’A'、’B'等; • 给定一系列字符,对每个字符赋予一个数值,用数值来代表对应的字符,这一数值就是字符的编码(Encoding)。...

文文1 ⋅ 2016/07/13 ⋅ 0

mysql的varchar与text对比

varchar和text是mysql字符存储争议比较多的领域,究竟大字段用那个比较好,我们来对比一下,然后自行选择. 大小对比 VARCHAR :varchar在mysql中满足最大行限制,也就是 65535(16k)字节,在mys...

arthur376 ⋅ 05/28 ⋅ 0

关于 MySQL UTF8 编码下生僻字符插入失败/假死问题的分析

1、问题:mysql 遇到某些中文插入异常 最近有同学反馈了这样一个问题: 上述语句在脚本中 load 入库的时候会 hang 住,web 前端、命令行操作则要么抛出 Incorrect string value: 'xF0xA1x8B...

xrzs ⋅ 2014/11/12 ⋅ 13

Java IO/NIO学习总结

下面是自己学习整理Java IO/NIO的总结,期间浏览了网上很多优秀的总结分析文章,一并贴在这里供大家学习参考。IO的知识点学习大概分为以下几个部分: 概念理解 熟悉Java IO API 熟悉Java NI...

isam ⋅ 2016/03/22 ⋅ 0

想要Freestyle?先要遵守 MySQL 这十大铁律!

在工作中作为DBA的我们,可能也会需要面对开发人员各种 Freestyle 的要求,但是他们是否知道在操作数据库的时候,需要注意哪些事项呢? 今儿带着大家简单聊聊 MySQL 数据库的开发行为规范,十...

superZS ⋅ 2017/09/14 ⋅ 0

sql注入总结

本实验测试是基于sqli-labs的实验环境 环境配置:php+mysql 环境搭建请参考 http://www.freebuf.com/articles/web/34619.html Sql注入定义: 就是通过把sql命令插入到web表单提交或输入域名或...

wt7315 ⋅ 2016/07/20 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

对于程序员的招聘问题,作为软件人的一些吐槽和建议

作为软件人,找工作有时候似乎挺苦逼的。 说真的,让我去掉前面这句中“似乎”二字吧。就是苦逼!很多人都曾抱怨处在招聘的一方很糟糕——我们没有任何可靠的方式来甄别会写代码并且写得好的...

老道士 ⋅ 30分钟前 ⋅ 0

HDFS原理学习

一、概述 1、 Hadoop整合了众多的文件系统,首先提供了一个高层的文件系统抽象org.apache.hadoop.fs.FileSystem。然后有各个文件系统的实现类。 2、Hadoop是JAVA编写的,不同文件系统之间的交...

cjxcloud ⋅ 34分钟前 ⋅ 0

Linux下MySQL表名不区分大小写的设置方法(抄袭别人的)

Linux下MySQL表名不区分大小写的设置方法 MySQL表名不区分大小写的设置方法 在用centox安装mysql后,把项目的数据库移植了过去,发现一些表的数据查不到,排查了一下问题,最后发现是表名的大...

随风而浮沉 ⋅ 39分钟前 ⋅ 0

ubuntu下安装宋体simsun

sudo cp simsun.ttc /usr/share/fonts cd /usr/share/fonts sudo chmod 644 simsun.ttc 更新字体缓存: 代码: sudo mkfontscale 代码: sudo mkfontdir 代码: sudo fc-cache -fsv 安装chrome扩......

wangxuwei ⋅ 40分钟前 ⋅ 0

利用 ssh 传输文件

Linux 下一般可以用 scp 命令通过 ssh 传送文件: #把服务器上的 /home/user/a.txt 发送到本机的 /var/www/local_dir 目录下scp username@servername:/home/user/a.txt /var/www/local_dir...

大灰狼时间 ⋅ 50分钟前 ⋅ 0

web3j教程:android和java程序员如何使用web3j开发区块链以太坊

如何使用web3j为Java应用或Android App增加以太坊区块链支持,本教程内容即涉及以太坊中的核心概念,例如账户管理包括账户的创建、钱包创建、交易转账,交易与状态、智能合约开发与交互、过滤...

智能合约 ⋅ 今天 ⋅ 0

web3j开发java或android以太坊智能合约快速入门

web3j简介 web3j是一个轻量级、高度模块化、响应式、类型安全的Java和Android类库提供丰富API,用于处理以太坊智能合约及与以太坊网络上的客户端(节点)进行集成。 可以通过它进行以太坊区块链...

笔阁 ⋅ 今天 ⋅ 0

一起读书《深入浅出nodejs》-异步I/O

异步I/O “异步”这个名词其实很早就诞生了,但它大规模流行却是在Web 2.0浪潮中,它伴随着AJAX的第一个A(Asynchronous)席卷了Web。 为什么要异步I/O 关于异步I/O为何在Node里如此重要,这与...

小草先森 ⋅ 今天 ⋅ 0

JVM各种问题

1、如果启动什么都不设,会怎样? 先来看一个命令 [root@localhost bin]# java -XX:+PrintCommandLineFlags -version -XX:InitialHeapSize=29899008 -XX:MaxHeapSize=478384128 -XX:+PrintCo......

算法之名 ⋅ 今天 ⋅ 0

SAS笔记-宏2

宏是一种文本,一般来说其编译是在程序执行之前。 宏变量的创建 %let语句 %let macro_variables = text; %let是常见的宏变量建立方式,其编译就在执行前。如下例中,想要宏变量test等于数据集...

tonorth123 ⋅ 今天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部