MySQL数据类型和字符集的选择

前言

在使用MySQL的时候我们会遇到一个问题就是如何去选择合适的数据类型和字符集,选择合适的数据类型可以提供效率和节省空间,选择合适的字符集可以解决一些编码问题。

数据类型

char和varchar

都是用来存储字符串的,但是它们的检索方式不同,char属于固定长度的字符类型,而varchar属于可变长度的字符类型。

char(4)存储长度varchar(4)存储长度
‘    ‘4个字节1个字节
‘ab’‘ab  ‘4个字节‘ab’2个字节
‘abc’‘abc ‘4个字节‘abc’3个字节
‘abcd’‘abcd’4个字节‘abcd’4个字节

由于char是固定长度的,所以它的处理速度比varchar快的多,但是浪费存储空间,所以对于那些长度变化不大并对查询速度有要求的数据可以考虑使用char类型来存储。

另外,随着MySQL版本的不断升级,varchar数据类型的性能也在不断改进并提高,所以实际使用中varchar被使用的更多。

text和blob

一般存储大量文本会选择text或者blob,二者区别就是blob存储的是二进制数据,而text存储的是文本数据。

blob和text在大量删除操作时会引起一些性能问题,删除操作会在数据表中留下很大的”空洞”,以后填入这些”空洞”的记录在插入的性能上会有影响,为了提高性能,建议定期使用OPTIMIZE TABLE功能对这类表进行碎片化整理。

另外可以使用合成的(Synthetic)索引来提高大文本字段的查询性能,也就是给创建一个散列值,用来对比查询。

浮点数和定点数

当一个字段被定义为浮点类型后,如果插入的数据的精度超过该列定义的实际精度,则插入值会被四舍五入到实际定义的精度值,定点数不同于浮点数的是以字符串形式表示小数,不存在四舍五入,所以定点数可以更精确的存储数据。

日期类型的选择

MySQL中的日期类型有date、time、datetime、timestamp。根据实际情况选择能够满足应用的最小存储的日期类型,如果只需要存储年份就使用year类型,而不要使用4个字节的date存储,这样可以节约存储提高操作效率。如果要记录的年月日时分秒,并且记录的年份比较久远,最好使用datetime而不要使用timestamp,因为timestamp表示的日期范围比datetime要短得多。如果记录的日期需要让不同的时区的用户使用,最好使用timestamp存储,因为日期类型中只有它能够和实际时区相对应。

字符集

查看字符集

MySQL服务可以支持多种字符集,在同一台服务器、同一个数据库甚至同一个表的不同字段都可以指定使用不同的字符集,相比oracle等其他数据库,在同一个数据库只能使用相同字符集,MySQL明显存在着更大的灵活性。

查看所有可用的字符集:

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 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)

每个字符集至少对应一个校对规则,校对规则是用来定义比较字符串的方式,字符集和校对规则是一对多的关系,MySQL支持30多种字符集和70多种校对规则。

可以使用show collation like '***'命令或者通过系统表information_schema.COLLATIONS来查看相关字符集的校对规则:

mysql> show collation like 'gbk%';
+----------------+---------+----+---------+----------+---------+
| Collation      | Charset | Id | Default | Compiled | Sortlen |
+----------------+---------+----+---------+----------+---------+
| gbk_chinese_ci | gbk     | 28 | Yes     | Yes      |       1 |
| gbk_bin        | gbk     | 87 |         | Yes      |       1 |
+----------------+---------+----+---------+----------+---------+
2 rows in set (0.00 sec)

校对规则命名约定:_ci指大小写不敏感。_bin指二元,比较是基于字符编码的值而与language无关。

例如上面例子中的gbk的校对规则,其中gbk_chinese_ci是默认的校对规则,对大小写不明感,而gbk_bin按照编码值进行比较,对大小写敏感。

设置字符集

MySQL的字符集校对规则有4个级别的默认设置:服务器级别、数据库级别、表级别、字段级别。

服务器级别在服务启动的时候确定,可以在my.cnf中设置:

[mysqld]
character-set-server=gbk

或者在启动选项中指定:

mysqld --character-set-server=gbk

或者在编译时指定

shell> cmake . -DDEFAULT_CHARSET=gbk

如果没有特别的指定服务器字符集,默认使用的是latin1作为字符集,上面只是指定了字符集,而没有指定校对规则,可以使用show variables like 'character_set_server'命令查询当前服务器字符集和校对规则。

mysql> show variables like 'character_set_server';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| character_set_server | latin1 |
+----------------------+--------+
1 row in set (0.00 sec)

mysql> show variables like 'collation_server';
+------------------+-------------------+
| Variable_name    | Value             |
+------------------+-------------------+
| collation_server | latin1_swedish_ci |
+------------------+-------------------+
1 row in set (0.00 sec)

数据库级别可以在创建数据库的时候指定,也可以在创建完数据库后通过alter database来修改。

mysql> show variables like 'character_set_database';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| character_set_database | latin1 |
+------------------------+--------+
1 row in set (0.01 sec)

mysql> show variables like 'collation_database';
+--------------------+-------------------+
| Variable_name      | Value             |
+--------------------+-------------------+
| collation_database | latin1_swedish_ci |
+--------------------+-------------------+
1 row in set (0.00 sec)

表级别可以在创建表的时候指定也可以通过alter table命令来进行修改,可以使用下面命令来查看字符集:

mysql> show create table ai \G;
*************************** 1. row ***************************
       Table: ai
Create Table: CREATE TABLE `ai` (
  `i` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARS

字符集修改步骤

如果在应用运行了一段时间后发现需要修改字符集,不能直接通过alter databasealter table来修改字符集,因为表中已经有了数据,这样会造成数据编码错误,所以通常需要先导出数据,调整字符集后再导入数据。