【开发篇】MySQL存储引擎（MyISAM、 InnoDB）、数据类型、字符集的选择

一、存储类型的选择

  和大多数数据库不同，MySQL 中有一个存储引擎的概念，针对不同的存储需求可以选择最优的存储引擎。那什么又是存储引擎呢？简而言之，存储引擎就是指表的类型。在具体开发时，为了提高MySQL数据库管理系统的使用效率和灵活性，可以根据实际需要来选择存储引擎。因为存储引擎指定了表的类型，即如何存储和索引数据、是否支持事务等，同时存储引擎也决定了表在计算机中的存储方式。这里将实例化的讲解在开发中如何选择存储引擎。

1、MySQL 存储引擎概述

  插件式存储引擎是 MySQL 数据库最重要的特性之一，用户可以根据应用的需要选择如何存储和索引数据、是否使用事务等。MySQL 支持的存储引擎包括 MyISAM、InnoDB、BDB、MEMORY、MERGE、ARCHIVE、CSV、BLACKHOLE、FEDERATED 等。
  默认情况下，创建新表不指定表的存储引擎，则新表是默认存储引擎的，如果需要修改默认的存储引擎，则可以在参数文件中设置 default-table-type。

查看当前的默认存储引擎，可以使用下面的语句：

show variables like 'default_storage_engine';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| default_storage_engine| InnoDB |
+---------------+--------+
1 row in set (0.00 sec)

查看数据库支持的存储引擎方法：

SHOW ENGINES;

  在创建新表的时候，可以通过增加 ENGINE 关键字设置新建表的存储引擎，例如，在下面的例子中，表 ai 就是 MyISAM 存储引擎的，而 country 表就是 InnoDB 存储引擎的：

CREATE TABLE ai (
 i bigint(20) NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (i)
) ENGINE=MyISAM DEFAULT CHARSET=gbk;


CREATE TABLE country (
 country_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
 country VARCHAR(50) NOT NULL,
 last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (country_id)
)ENGINE=InnoDB DEFAULT CHARSET=gbk;

  也可以使用 ALTER TABLE 语句，将一个已经存在的表修改成其他的存储引擎。下面的例子介绍了如何将表 ai 从 MyISAM 存储引擎修改成 InnoDB 存储引擎：

alter table ai engine = innodb;

2、各种存储引擎的特性

  下面重点介绍几种常用的存储引擎,并对比各个存储引擎之间的区别，以便在使用时，忘记的时候查看选择。重点介绍最常使用的 4 种存储引擎：MyISAM、InnoDB、MEMORY 和 MERGE。

  

1. MyISAM

  MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物和外键。所以对事务完整性没有要求或者以 SELECT、INSERT 为主的应用基本上都可以使用这个引擎来创建表。
  每个 MyISAM 在磁盘上存储成 3 个文件，其文件名都和表名相同，但扩展名分别是：

• .frm（存储表定义）；
• .MYD（MYData，存储数据）；
• .MYI （MYIndex，存储索引）。

  数据文件和索引文件可以放置在不同的目录，平均分布 IO，获得更快的速度。要指定索引文件和数据文件的路径，需要在创建表的时候通过 DATA DIRECTORY 和 INDEX DIRECTORY 语句指定，也就是说不同 MyISAM 表的索引文件和数据文件可以放置到不同的路径下。文件路径需要是绝对路径，并且具有访问权限。

  讲到这里就不得不讲下MyISAM引擎的索引结构----B+Tree。其中B+Tree的数据域存储的内容为实际数据的地址，也就是上面说的索引文件和数据文件是分开的，只不过是用索引指向了实际的数据，这种索引就是所谓的非聚集索引。如下图所示：

  这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。也就是说MyISAM中，主键索引和辅助索引是同级别的，没有主次之分。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

  MyISAM 的表又支持 3 种不同的存储格式，分别是：

• 静态（固定长度）表；
• 动态表；
• 压缩表。
    其中，静态表是默认的存储格式。静态表中的字段都是非变长字段，这样每个记录都是固定长度的，这种存储方式的优点是存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多。静态表的数据在存储的时候会按照列的宽度定义补足空格，但是在应用访问的时候并不会得到这些空格，这些空格在返回给应用之前已经去掉。但是也有些需要特别注意的问题，如果需要保存的内容后面本来就带有空格，那么在返回结果的时候也会被去掉，开发人员在编写程序的时候需要特别注意，因为静态表是默认的存储格式，开发人员可能并没有意识到这一点，从而丢失了尾部的空格。
    动态表中包含变长字段，记录不是固定长度的，这样存储的优点是占用的空间相对较少，但是频繁地更新删除记录会产生碎片，需要定期执行 OPTIMIZE TABLE 语句或 myisamchk -r 命令来改善性能，并且出现故障的时候恢复相对比较困难。
    压缩表由 myisampack 工具创建，占据非常小的磁盘空间。因为每个记录是被单独压缩的，所以只有非常小的访问开支。

2. InnoDB

  InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB 写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。但是由于其其他方面的优势，在5.5版本之后，MySQL的默认引擎变成了InnoDB。

  Innodb存储引擎存储数据库数据，一共有两个文件(没有专门保存数据的文件)：

• Frm文件： 表的定义文件。
• Ibd文件：数据和索引存储文件。数据以主键进行聚集存储，把真正的数据保存在叶子节点中。

  Innodb主键索引为聚集索引，这点和MyISAM不一样。首先简单理解一下聚集索引的概念：数据库表行中数据的物理顺序和键值的逻辑顺序相同（一个数据库只能有一个聚集索引）。
  Innodb也采用B+Tree存储结构，但是与MyISAM有些许不同，先看其图示：

  和MyISAM的不同：

• 它的叶子节点存储的不是MyISAM那样的物理地址，而是真实的数据。
• InnoDB中，辅助索引的叶子节点存放的是住索引的关键字的值，也就是说，InnoDB中必须要先有主索引，然后其他索引依赖于主索引。之所以这样设计，一个很重要的原因就是：如果和MyISAM一样在主键索引和辅助索引的叶子节点中都存放数据行指针，一旦数据发生迁移，则需要去重新组织维护所有的索引，而现在，只需要出现组织主索引即可。
• MySQL 支持外键的存储引擎只有 InnoDB，在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。

3. MEMORY

  MEMORY 存储引擎使用存在内存中的内容来创建表。每个 MEMORY 表只实际对应一个磁盘文件，格式是.frm。MEMORY 类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用 HASH 索引，但是一旦服务关闭，表中的数据就会丢失掉，但是表结构会保存下来。
  在启动 MySQL 服务的时候使用–init-file 选项，把 INSERT INTO … SELECT 或 LOAD DATA INFILE 这样的语句放入这个文件中，就可以在服务启动时从持久稳固的数据源装载表。
  服务器需要足够内存来维持所有在同一时间使用的 MEMORY 表，当不再需要 MEMORY表的内容之时，要释放被MEMORY 表使用的内存，应该执行 DELETE FROM 或 TRUNCATE TABLE，或者整个地删除表（使用 DROP TABLE 操作）。每个 MEMORY 表中可以放置的数据量的大小，受到 max_heap_table_size 系统变量的约束，这个系统变量的初始值是 16MB，可以按照需要加大。此外，在定义 MEMORY 表的时候，可以通过 MAX_ROWS 子句指定表的最大行数。
  MEMORY 类型的存储引擎主要用在那些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果。对 MEMORY 存储引擎的表进行更新操作要谨慎，因为数据并没有实际写入到磁盘中，所以一定要对下次重新启动服务后如何获得这些修改后的数据有所考虑。

4. MERGE

  MERGE 存储引擎是一组 MyISAM 表的组合，这些 MyISAM 表必须结构完全相同（即每张表的字段顺序、字段名称、字段类型、索引定义的顺序及其定义的方式必须相同），MERGE表本身并没有数据，对 MERGE 类型的表可以进行查询、更新、删除的操作，这些操作实际上是对内部的实际的 MyISAM 表进行的。对于 MERGE 类型表的插入操作，是通过INSERT_METHOD 子句定义插入的表，可以有 3 个不同的值，使用 FIRST 或 LAST 值使得插入操作被相应地作用在第一或最后一个表上，不定义这个子句或者定义为 NO，表示不能对这
个 MERGE 表执行插入操作。可以对 MERGE 表进行 DROP 操作，这个操作只是删除 MERGE 的定义，对内部的表没有任何的影响。
  MERGE 表在磁盘上保留两个文件，文件名以表的名字开始，一个.frm 文件存储表定义，另一个.MRG 文件包含组合表的信息，包括 MERGE 表由哪些表组成、插入新的数据时的依据。可以通过修改.MRG 文件来修改 MERGE 表，但是修改后要通过 FLUSH TABLES 刷新。

3、选择合适的存储引擎

  在选择存储引擎时，应根据应用特点选择合适的存储引擎，对于复杂的应用系统可以根据实际情况选择多种存储引擎进行组合。

1. 常用引擎的特点及适用环境

• MyISAM：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常适合的。MyISAM 是在 Web、数据仓储和其他应用环境下最常使用的存储引擎
  之一。
• InnoDB：默认的 MySQL 插件式存储引擎。用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包括很多的更新、删除操作，那么 InnoDB 存储引擎应该是比较合适的选择。InnoDB 存储引擎除了有效地降低由于删除和更新导致的锁定，还可以确保事务的完整提交（Commit）和回滚（Rollback），对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB 都是合适的选择。
• MEMORY：将所有数据保存在 RAM 中，在需要快速定位记录和其他类似数据的环境下，可提供极快的访问。MEMORY 的缺陷是对表的大小有限制，太大的表无法 CACHE 在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据是可以恢复的。MEMORY 表通常用于更新不太频繁的小表，用以快速得到访问结果。
• MERGE：用于将一系列等同的 MyISAM 表以逻辑方式组合在一起，并作为一个对象引用它们。MERGE 表的优点在于可以突破对单个 MyISAM 表大小的限制，并且通过将不同的表分布在多个磁盘上，可以有效地改善MERGE表的访问效率。这对于诸如数据仓储等VLDB环境十分适合

  注意：
  以上只是我们按照实施经验提出的关于存储引擎选择的一些建议，但是不同应用的特点是千差万别的，选择使用哪种存储引擎才是最佳方案也不是绝对的，这需要根据用户各自的需求进行测试，从而得到最适合自己的结果。

2. 创建索引原则

列的离散型：
  离散型的计算公式：count(distinct column_name):count(*)，就是用去重后的列值个数比个数。值在 (0,1] 范围内。离散型越高，越适合做索引。
  为什么这么说呢，举个极端的例子，数据如下：

  可以算出，性别的离散度为0.5，而国籍的是0.25。当用性别做索引时，数据分两部分，有提高查询效率，而用国籍做索引时，查询效率和全文检索没差别，当然这是个例子，实际中，数百万行的数据用性别其实效率提升不大哈。

最左匹配原则：
  对于索引中的关键字进行对比的时候，一定是从左往右以此对比（数值型直接比较，字符型转换成ascll码进行比较），且不可跳过。所以在sql查询中使用like %a 时候索引会失效，因为%表示全匹配，如果已经全匹配就不需要索引，还不如直接全表扫描。

最少空间原则：
  前面已经说过，当关键字占用的空间越小，则每个节点保存的关键字个数就越多，每次加载进内存的关键字个数就越多，检索效率就越高。创建索引的关键字要尽可能占用空间小。

3. 构建索引经验总结

• 索引列的数据长度满足业务的情况下能少则少。
• 表中的索引并不是越多越好，冗余或者无用索引会占用磁盘空间并且会影响增删改的效率。
• Where 条件中，like 9%， like %9%， like%9，三种方式都用不到索引。后两种方式对于索引是无效的。第一种9%是不确定的，决定于列的离散型，结论上讲可以用到，如果发现离散情况特别差的情况下，查询优化器觉得走索引查询性能更差，还不如全表扫描。
• Where条件中IN可以使用索引， NOT IN 无法使用索引。
• 多用指定查询，只返回自己想要的列，少用select *。
• 查询条件中使用函数，索引将会失效，这和列的离散性有关，一旦使用到函数，函数具有不确定性。
• 联合索引中，如果不是按照索引最左列开始查找，无法使用索引。
• 对联合索引精确匹配最左前列并范围匹配另一列，可以使用到索引。
• 联合索引中，如果查询有某个列的范围查询，其右边所有的列都无法使用索引。

二、数据类型的选择

  在使用 MySQL 创建数据表时都会遇到一个问题，如何为字段选择合适的数据类型。例 如，创建一张员表用来记录员工的信息，这时对员工的各种属性如何来进行定义？也许大家会想，这个问题很简单，每个字段可以使用很多种数据类型来定义，比如 int、float、double、decimal 等。其实正因为可选择的数据类型太多，才需要依据一些原则来“挑选”最适合的数据类型。
  虽然我们之前已经学习了各种数据类型，但是那时候只是讲解了各种的类型的特点，这里讲解开发时，需要注意的地方。

1、CHAR 与 VARCHAR

  CHAR 和 VARCHAR 类型类似，都用来存储字符串，但它们保存和检索的方式不同。CHAR 属于固定长度的字符类型，而 VARCHAR 属于可变长度的字符类型。 下表通过将各种字符串值保存到 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字节

  上面需要注意的是最后一行的值只适用非“严格模式”时，如果 MySQL 运行在严格模式，超过列长度的值将不会保存，并且会出现错误提示，关于“严格模式””将在后面讲解。另外检索时从 CHAR 列删除了尾部的空格。
  由于 CHAR 是固定长度的，所以它的处理速度比 VARCHAR 快得多，但是其缺点是浪费存储空间，程序需要对行尾空格进行处理，所以对于那些长度变化不大并且对查询速度有较高要求的数据可以考虑使用 CHAR 类型来存储。另外，随着 MySQL 版本的不断升级，VARCHAR 数据类型的性能也在不断改进并提高，所以在许多的应用中，VARCHAR 类型被更多地使用。
  在 MySQL 中，不同的存储引擎对 CHAR 和 VARCHAR 的使用原则有所不同，这里简单概括如下。

• MyISAM 存储引擎：建议使用固定长度的数据列代替可变长度的数据列。
• MEMORY 存储引擎：目前都使用固定长度的数据行存储，因此无论使用 CHAR 或VARCHAR 列都没有关系。两者都是作为 CHAR 类型处理。
• InnoDB 存储引擎：建议使用 VARCHAR 类型。对于 InnoDB 数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的 CHAR 列不一定比使用可变长度 VARCHAR 列性能要好。因而，主要的性能因素是数据行使用的存储总量。由于 CHAR 平均占用的空间多于 VARCHAR，因此使用 VARCHAR 来最小化需要处理的数据行的存储总量和磁盘 I/O 是比较好的。

2、TEXT 与 BLOB

  一般在保存少量字符串的时候，我们会选择 CHAR 或者 VARCHAR；而在保存较大文本时，通常会选择使用 TEXT 或者 BLOB，二者之间的主要差别是 BLOB 能用来保存二进制数据，比如照片；而 TEXT 只能保存字符数据，比如一篇文章或者日记。TEXT 和 BLOB 中又分别包括TEXT、MEDIUMTEXT、LONGTEXT 和 BLOB、MEDIUMBLOB、LONGBLOB3 种不同的类型，它们之间的主要区别是存储文本长度不同和存储字节不同，用户应该根据实际情况选择能够满足需求的最小存储类型。

 注意点：

• BLOB 和 TEXT 值会引起一些性能问题，特别是在执行了大量的删除操作时。删除操作会在数据表中留下很大的“空洞”，以后填入这些“空洞”的记录在插入的性能上会有影响。为了提高性能，建议定期使用 OPTIMIZE TABLE 功能对这类表进行碎片整理，避免因为“空洞”导致性能问题。OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用，而且在OPTIMIZE TABLE运行过程中，MySQL会锁定表。
• 可以使用合成的（Synthetic）索引来提高大文本字段（BLOB 或 TEXT）的查询性能。
  简单来说，合成索引就是根据大文本字段的内容建立一个散列值，并把这个值存储在单独的数据列中，接下来就可以通过检索散列值找到数据行了。但是，要注意这种技术只能用于精确匹配的查询（散列值对于类似<或>=等范围搜索操作符是没有用处的）。可以使用 MD5()函数生成散列值，也可以使用 SHA1()或 CRC32()，或者使用自己的应用程序逻辑来计算散列值。请记住数值型散列值可以很高效率地存储。同样，如果散列算法生成的字符串带有尾部空格，就不要把它们存储在 CHAR 或 VARCHAR 列中，它们会受到尾部空格去除的影响。合成的散列索引对于那些 BLOB 或 TEXT 数据列特别有用。用散列标识符值查找的速度比搜索BLOB 列本身的速度快很多。
• 在不必要的时候避免检索大型的 BLOB 或 TEXT 值。
• 把 BLOB 或 TEXT 列分离到单独的表中。

3、浮点数与定点数

  浮点数一般用于表示含有小数部分的数值。当一个字段被定义为浮点类型后，如果插入数据的精度超过该列定义的实际精度，则插入值会被四舍五入到实际定义的精度值（也就是显示和实际值不一样），然后插入，四舍五入的过程不会报错。在 MySQL 中 float、double（或 real）用来表示浮点数。定点数实际上是以字符串形式存放的，所以定点数可以更加精确的保存数据。如果实际插入的数值精度大于实际定义的精度，则 MySQL 会进行警告（默认的 SQLMode 下），但是数据按照实际精度四舍五入后插入；如果 SQLMode 是在 TRADITIONAL（传统模式）下，则系统会直接报错，导致数据无法插入。在 MySQL 中，decimal（或 numberic）用来表示定点数。

-- 比较浮点和定点的例子，演示浮点数的误差问题
CREATE TABLE test (c1 float(10,2),c2 decimal(10,2));

insert into test values(131072.32,131072.32);

select * from test;
+-----------+-----------+
| c1 | c2 |
+-----------+-----------+
| 131072.31 | 131072.32 |
+-----------+-----------+
1 row in set (0.00 sec)

注意：在今后关于浮点数和定点数的应用中，要考虑到以下几个原则：

• 浮点数存在误差问题；
• 对货币等对精度敏感的数据，应该用定点数表示或存储；
• 在编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较；
• 要注意浮点数中一些特殊值的处理。
  

4、日期类型选择

  在前文已经介绍过各种时间类型，这里不再累赘。主要说下选择时间类型的原则。

• 根据实际需要选择能够满足应用的最小存储的日期类型。如果应用只需要记录“年 份”，那么用 1 个字节来存储的YEAR 类型完全可以满足，而不需要用 4 个字节来存储的 DATE 类型。这样不仅仅能节约存储，更能够提高表的操作效率。
• 如果要记录年月日时分秒，并且记录的年份比较久远，那么最好使用 DATETIME，而不要使用 TIMESTAMP。因为 TIMESTAMP 表示的日期范围比 DATETIME 要短得多。
• 如果记录的日期需要让不同时区的用户使用，那么最好使用 TIMESTAMP，因为日期类型中只有它能够和实际时区相对应。

三、字符集的选择

  从本质上来说，计算机只能识别二进制代码，因此，不论是计算机程序还是其处理的数据，最终都必须转换成二进制码，计算机才能认识。为了使计算机不仅能做科学计算，也能处理文字信息，人们想出了给每个文字符号编码以便于计算机识别处理的办法，这就是计算机字符集的由来。

1、MySQL的字符集

  对数据库来说，字符集更加重要，因为数据库存储的数据大部分都是各种文字，字符集对数据库的存储、处理性能，以及日后系统的移植、推广都会有影响。MySQL目前支持几十种字符集，在同一台服务器、同一个数据库、甚至同一个表的不同字段都可以指定使用不同的字符集，相比 Oracle 等其他数据库管理系统，在同一个数据库只能使用相同的字符集，MySQL 明显存在更大的灵活性。

  查看所有可用的字符集的命令是 show character set；。

  MySQL 的字符集包括字符集（CHARACTER）和校对规则（COLLATION）两个概念。字符集是用来定义 MySQL 存储字符串的方式，校对规则则是定义了比较字符串的方式。字符集和校对规则是一对多的关系，MySQL 支持 30 多种字符集的 70 多种校对规则。每个字符集至少对应一个校对规则。

  校对规则命名约定：它们以其相关的字符集名开始，通常包括一个语言名，并且以_ci（大小写不敏感）、_cs（大小写敏感）或_bin（二元，即比较是基于字符编码的值而与language无关）结束。

2、MySQL字符集的选择

  虽然没有一定之规，但在选择数据库字符集时，可以根据应用的需求，结合上面介绍的一些字符集的特点来权衡，主要考虑因素包括：

• 满足应用支持语言的需求，如果应用要处理各种各样的文字，或者将发布到使用不同语言的国家或地区，就应该选择 Unicode 字符集。对 MySQL 来说，也就是 UTF-8。
• 如果应用中涉及已有数据的导入，就要充分考虑数据库字符集对已有数据的兼容性。假如已有数据是 GBK 文字，如果选择 GB2312-80 为数据库字符集，就很可能出现某些文字无法正确导入的问题。
• 如果数据库只需要支持一般中文，数据量很大，性能要求也很高，那就应该选择双字节定长编码的中文字符集，比如 GBK。因为，相对于 UTF-8 而言，GBK 比较“小”，每个汉字只占 2 个字节，而 UTF-8 汉字编码需要 3 个字节，这样可以减少磁盘 I/O、数据库cache，以及网络传输的时间，从而提高性能。相反，如果应用主要处理英文字符，仅有少量汉字数据，那么选择 UTF-8 更好，因为 GBK、UCS-2、UTF-16 的西文字符编码都是 2 个字节,会造成很大不必要的开销。
• 如果数据库需要做大量的字符运算，如比较、排序等，选择定长字符集可能更好，因为定长字符集的处理速度要比变长字符集的处理速度快。
• 如果所有客户端程序都支持相同的字符集，应该优先选择该字符集作为数据库字符集。这样可以避免因字符集转换带来的性能开销和数据损失。

3、 MySQL字符集的设置

  MySQL 的字符集和校对规则有 4 个级别的默认设置：服务器级、数据库级、表级和字段级。它们分别在不同的地方设置，作用也不相同。

1. 服务器字符集和校对规则

  服务器字符集和校对，在 MySQL 服务启动的时候确定。

可以在 my.cnf 中设置：
[mysqld]
default-character-set=gbk

在启动选项中指定：
mysqld --default-character-set=gbk

在编译的时候指定：
./configure --with-charset=gbk

  如果没有特别的指定服务器字符集，默认使用 latin1 作为服务器字符集。上面 3 种设置的方式都只指定了字符集，没有指定校对规则，这样是使用该字符集默认的校对规则，如果要使用该字符集的非默认校对规则，则需要在指定字符集的同时指定校对规则。

show variables like ‘character_set_server’; 命令查询当前服务器的字符集。
show variables like ‘collation_server’; 命令查询当前服务器的校对规则

2. 数据库字符集和校对规则

  数据库的字符集和校对规则在创建数据库的时候指定，也可以在创建完数据库后通过“alter database”命令进行修改。需要注意的是，如果数据库里已经存在数据，因为修改字符集并不能将已有的数据按照新的字符集进行存放，所以不能通过修改数据库的字符集直接修改数据的内容。只影响之后插入数据的字符集。

设置数据库字符集的规则是：

• 如果指定了字符集和校对规则，则使用指定的字符集和校对规则；
• 如果指定了字符集没有指定校对规则，则使用指定字符集的默认校对规则；
• 如果没有指定字符集和校对规则，则使用服务器字符集和校对规则作为数据库的字符集和校对规则。

使用命令行
show variables like 'character_set_database;
show variables like ’ collation_database;
查看数据库的字符集和校对规则

推荐在创建数据库的时候明确指定字符集和校对规则，避免受到默认值的影响。

3. 表字符集和校对规则

  表的字符集和校对规则在创建表的时候指定，可以通过 alter table 命令进行修改，同样，如果表中已有记录，修改字符集对原有的记录并没有影响，不会按照新的字符集进行存放。表的字段仍然使用原来的字符集。

设置表的字符集的规则和上面基本类似：

• 如果指定了字符集和校对规则，使用指定的字符集和校对规则；
• 如果指定了字符集没有指定校对规则，使用指定字符集的默认校对规则；
• 如果没有指定字符集和校对规则，使用数据库字符集和校对规则作为表的字符集和校对规则。

查看表级别的字符集和校对规则命令

show create table tablename;

推荐在创建数据表的时候明确指定字符集和校对规则，避免受到默认值的影响。

4. 列字符集和校对规则

  MySQL 可以定义列级别的字符集和校对规则，主要是针对相同的表不同字段需要使用不同的字符集的情况，应该说一般遇到这种情况的几率比较小，这只是 MySQL 提供给我们一个灵活设置的手段。
  列字符集和校对规则的定义可以在创建表时指定，或者在修改表时调整，如果在创建表的时候没有特别指定字符集和校对规则，则默认使用表的字符集和校对规则。

5. 字符集的修改步骤

  如果在应用开始阶段没有正确的设置字符集，在运行一段时间以后才发现存在不能满足要求需要调整，又不想丢弃这段时间的数据，那么就需要进行字符集的修改。字符集的修改不能直接通过alter database character set 或者alter table tablename character set命令进行，这两个命令都没有更新已有记录的字符集，而只是对新创建的表或者记录生效。已有记录的字符集调整，需要先将数据导出，经过适当的调整重新导入后才可完成。
  以下模拟的是将 latin1 字符集的数据库修改成 GBK 字符集的数据库的过程。

1). 导出表结构：

mysqldump -uroot -p --default-character-set=gbk -d databasename> createtab.sql
其中–default-character-set=gbk 表示设置以什么字符集连接，-d 表示只导出表结构，不导出数据。

2). 手工修改 createtab.sql 中表结构定义中的字符集为新的字符集。
3). 确保记录不再更新，导出所有记录。

mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=latin1 databasename> data.sql

–quick：该选项用于转储大的表。它强制 mysqldump 从服务器一次一行地检索表中的行而不是检索所有行，并在输出前将它缓存到内存中。
–extended-insert：使用包括几个 VALUES 列表的多行 INSERT 语法。这样使转储文件更小，重载文件时可以加速插入。
–no-create-info：不写重新创建每个转储表的 CREATE TABLE 语句。
–default-character-set=latin1：按照原有的字符集导出所有数据，这样导出的文件中，所有中文都是可见的，不会保存成乱码。

4). 打开 data.sql，将 SET NAMES latin1 修改成 SET NAMES gbk。
5). 使用新的字符集创建新的数据库。

create database databasename default charset gbk;

6). 创建表，执行 createtab.sql。

mysql -uroot -p databasename < createtab.sql

7). 导入数据，执行 data.sql。

mysql -uroot -p databasename < data.sql

注意：
  选择目标字符集的时候，要注意最好是源字符集的超级，或者确定比源字符集的字库更大，否则如果目标字符集的字库小于源字符集的字库，那么目标字符集中不支持的字符倒入后会变成乱码，丢失一部分数据。例如，GBK 字符集的字库大于 GB2312 字符集，那么 GBK 字符集的数据，如果导入 GB2312 数据库中，就会丢失 GB2312 中不支持的那部分汉字的数据。