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

ci 是 case insensitive 的缩写， cs 是 case 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 set 是 latin1 时，若字段类型为 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 中， 若一张表里面不存在 varchar、text 以及其变形、blob 以及其变形的字段的话，那么张这个表其实也叫 静态表 ，即该表的 row_format=fixed，就是说每条记录所占用的字节一样。其优点读取快，缺点浪费额外一部分空间。

若一张表里面存在 varchar、text以及其变形、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 的效率稍高。
  • 目前 VARCHAR 是 VARCHAR2 的同义词。工业标准的 VARCHAR 类型可以存储空字符串，但是 ORACLE 不这样做，尽管它保留以后这样做的权利。Oracle 自己开发了一个数据类型 VARCHAR2，这个类型不是一个标准的VARCHAR ，它将在数据库中 varchar 列可以存储空字符串的特性改为存储 NULL 值。如果你想有向后兼容的能力，Oracle建议使用 VARCHAR2 而不是VARCHAR。

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

洋葱_SilovPHP & Go

一个爱看闲书偶尔钻研代码的厨子。