MySQL 建表建库的一些设置项及其意义浅析

一、 Charact-Set & Collation

charset 表示字符集,常用 utf8 格式,需要支持 emoji 等特殊情况的时候,用到其扩展格式 utf8mb4

1
2
3
utf8 	为3-Byte 宽度
utf8mb4 为4-Byte宽度,mb4就是most bytes 4的意思,专门用来兼容四字节的unicode.
utf8mb4 为 utf8 的扩展集.意味着包含了utf8的所有字符。
1、关于 GBK/UTF8/GB2312

UTF- 8:Unicode Transformation Format-8bit,允许含BOM,但通常不含BOM。是用以解决国际上字符的一种多字节编码,它对英文使用8位(即一个字节),中文使用24为(三个字节)来编码。UTF-8包含全世界所有国家需要用到的字符,是国际编码,通用性强。UTF-8编码的文字可以在各国支持UTF8字符集的浏览器上显示。如,如果是UTF8编码,则在外国人的英文IE上也能显示中文,他们无需下载IE的中文语言支持包。

GBK是国家标准GB2312基础上扩容后兼容GB2312的标准。GBK的文字编码是用双字节来表示的,即不论中、英文字符均使用双字节来表示,为了区分中文,将其最高位都设定成1。GBK包含全部中文字符,是国家编码,通用性比UTF8差,不过UTF8占用的数据库比GBD大。

GBK、GB2312等与UTF8之间都必须通过Unicode编码才能相互转换:

1
2
GBK、GB2312 	--- 	Unicode 	--- 	UTF8
UTF8 --- Unicode --- GBK、GB2312

GB2312是GBK的子集,GBK是GB18030的子集。
GBK是包括中日韩字符的大字符集合。如果是中文的网站 推荐 GB2312 优先级大于GBK,因为后者有时还是有点问题。

为了避免所有乱码问题,应该采用 UTF-8 ,将来要支持国际化也非常方便

UTF-8可以看作是大字符集,它包含了大部分文字的编码。使用UTF-8的一个好处是其他地区的用户(如香港台湾)无需安装简体中文支持就能正常观看你的文字而不会出现乱码。

  • gb2312是简体中文的码
  • gbk支持简体中文及繁体中文
  • big5支持繁体中文
  • utf-8支持几乎所有字符
2、关于 Collation

该部分参考文章:mysql 中 character set 与 collation 的点滴理解

Collation:即比对方法。用于指定数据集如何排序,以及字符串的比对规则。

每个 character set 会对应一定数量的 collation. 查看方法是在 mysql 的 console 下输入:

1
mysql> show collation;  

collation 名字的规则可以归纳为这两类:

1
2
<character set>_<language/other>_<ci/cs>
<character set>_bin

例如: utf8_danish_ci

cicase insensitive 的缩写, cscase sensitive 的缩写。即,指定大小写是否敏感。

PS: 奇怪的是 utf8 字符集对应的 collation 居然没有一个是 cs 的。

那么 utf8_general_ci, utf8_unicode_ci, utf8_danish_ci 有什么区别? 他们各自存在的意义又是什么?

同一个 character set 的不同 collation 的区别在于 排序、字符串对比的准确度(相同两个字符在不同国家的语言中的排序规则可能是不同的)以及性能

例如:utf8_general_ci 在排序的准确度上要逊于 utf8_unicode_ci, 当然,对于英语用户应该没有什么区别。但性能上(排序以及比对速度)要略优于 utf8_unicode_ci。 例如前者没有对德语中 ß = ss的支持。而 utf8_danish_ci 相比 utf8_unicode_ci 增加了对丹麦语的特殊排序支持。

补充:

1、当表的 character setlatin1 时,若字段类型为 nvarchar , 则字段的字符集自动变为 utf8

可见 database character set, table character set, field character set 可逐级覆盖。

2、在 ci 的 collation 下,如何在比对时区分大小写:

1
2
3
4
/** sql_1 **/
mysql> select * from pet where name = binary 'whistler';
/** sql_2 **/
mysql> select * from pet where binary name = 'whistler';

如上两条sql,查询结果应该是相同的,但是推荐使用前者,这样可以保证当前字段的索引依然有效, 而后者会使索引失效。

二、行格式 ROW_FORMAT

1、静态表和动态表

以下,参考:Mysql的row_format

1
2
3
4
5
CREATE TABLE `table_a` (
`a_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
.....
PRIMARY KEY (`a_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC COMMENT='demo表';

如上demo中,建表语句中有一项:ROW_FORMAT=DYNAMIC, 表示该表为动态表。

在 MySQL 中, 若一张表里面不存在 varchartext 以及其变形、blob 以及其变形的字段的话,那么张这个表其实也叫 静态表 ,即该表的 row_format=fixed,就是说每条记录所占用的字节一样。其优点读取快,缺点浪费额外一部分空间。

若一张表里面存在 varchartext以及其变形、blob以及其变形的字段的话,那么张这个表其实也叫 动态表 ,即该表的 row_format=dynamic ,就是说每条记录所占用的字节是动态的。其优点节省空间,缺点增加读取的时间开销。

所以,做搜索查询量大的表一般都以空间来换取时间,设计成静态表。

关于 静态表和动态表,占用字节长度是动态还是静态的区别,可以查看后面提到的 varchar 和 char 的区别来理解。

row_format 还有其他一些值:

1
2
3
4
5
6
DEFAULT
FIXED
DYNAMIC
COMPRESSED
REDUNDANT
COMPACT

修改行格式

1
ALTER TABLE table_name ROW_FORMAT = DEFAULT;

修改格式导致的结果:

1
2
fixed--->dynamic: 这会导致CHAR变成VARCHAR
dynamic--->fixed: 这会导致VARCHAR变成CHAR
2、 varchar 和 char 的区别
  • CHAR 的长度是固定的,而 VARCHAR 的长度是可以变化的, 比如,存储字符串 “abc”,对于 CHAR (10),表示你存储的字符将占 10 个字节(包括 7 个空字符),而同样的 VARCHAR (10) 则只占用 3 个字节的长度,10只是最大值,当你存储的字符小于 10 时,按实际长度存储。
  • CHAR 的效率比 VARCHAR 的效率稍高。
  • 目前 VARCHARVARCHAR2 的同义词。工业标准的 VARCHAR 类型可以存储空字符串,但是 ORACLE 不这样做,尽管它保留以后这样做的权利。Oracle 自己开发了一个数据类型 VARCHAR2,这个类型不是一个标准的VARCHAR ,它将在数据库中 varchar 列可以存储空字符串的特性改为存储 NULL 值。如果你想有向后兼容的能力,Oracle建议使用 VARCHAR2 而不是VARCHAR

何时该用 CHAR ,何时该用 VARCHAR? **
CHARVARCHAR 是一对矛盾的统一体,两者是互补的关系.
VARCHARCHAR 节省空间,在效率上比 CHAR 会稍微差一些,即要想获得效率,就必须牺牲一定的空间,这也就是我们在数据库设计上常说的 _以空间换效率_。
VARCHAR 虽然比 CHAR 节省空间,但是如果一个 VARCHAR 列经常被修改,而且每次被修改的数据的长度不同,这会引起 **行迁移(Row Migration)
现象,而这造成多余的 I/O ,是数据库设计和调整中要尽力避免的,在这种情况下用 CHAR 代替 VARCHAR 会更好一些。