～ MySQL 数据类型和存储引擎

数据类型（data_type）

数据类型是指系统中所允许的数据的类型。MySQL 数据类型定义了列中可以存储什么数据以及该数据怎样存储的规则。

数据库中的每个列都应该有适当的数据类型，用于限制或允许该列中存储的数据。例如，列中存储的为数字，则相应的数据类型应该是数值类型。

如果使用错误的数据类型可能会导致数据丢失。因此，在创建表时必须为每个列设置正确的数据类型和长度。

MySQL 的数据类型大概可以分为 5 种，分别是整数类型、浮点类型（以及定点数类型）、日期类型和时间类型、字符串类型、二进制类型等。 // 整数类型和浮点类型可以统称为数值类型

[ 分类 ] 为方便查阅，以下为 MySQL 中的常用的 < 数据类型？！>：

（其中，常用的是：int、decimal、varchar(M)、data），定义字段的数据类型对数据库的优化是十分重要的。

[ 选择 ] MySQL 提供了大量的数据类型，为了优化存储和提高数据库性能，在任何情况下都应该使用最精确的数据类型。

So，了解了 MySQL 的基本数据类型及其特性（包括它们能够存放的值的类型和占用空间）后，在创建数据表的时候改如何选择数据类型呢？ — 基本原则是：在选择数据类型时，首先要考虑这个列存放的值是什么类型的。

一般来说，用数值类型列存储数字、用字符类型列存储字符串、用时态类型列存储日期和时间。

# 数值类型

对于数值类型，如果要存储的数字是整数（没有小数部分），则使用整数类型；如果要存储的数字是小数（带有小数部分），则可以选用 DECIMAL 或浮点类型，但是一般 float 类型。

例如，如果列的取值范围是 1~99999 之间的整数，则 MEDIUMINT UNSIGNED 类型是最好的选择。

MEDIUMINT 是整数类型，UNSIGNED 用来将数字类型无符号化。
比如 INT 类型的取值范围是 -2 147 483 648 ～ 2 147 483 647，
那么 INT UNSIGNED 类型的取值范围就是 0 ～ 4 294 967 295。

如果需要存储某些整数值，则值的范围决定了可选用的数据类型。如果取值范围是 0~1000，那么可以选择 SMALLINT~BIGINT 之间的任何一种类型。如果取值范围超过了 200 万，则不能使用 SMALLINT，可以选择的类型变为从 MEDIUMINT 到 BIGINT 之间的某一种。

当然，完全可以为要存储的值选择一种最“大”的数据类型。但是，如果正确选择数据类型，不仅可以使表的存储空间变小，也会提高性能。因为与较长的列相比，较短的列的处理速度更快。当读取较短的值时，所需的磁盘读写操作会更少，并且可以把更多的键值放入内存索引缓冲区里。

如果无法获知各种可能值的范围，则只能靠猜测，或者使用 BIGINT 以满足最坏情况的需要。如果猜测的类型偏小，那么也不是就无药可救。将来，还可以使用 ALTER TABLE 让该列变得更大些。

如果数值类型需要存储的数据为货币，如人民币。在计算时，使用到的值常带有元和分两个部分。它们看起来像是浮点值，但 FLOAT 和 DOUBLE 类型都存在四舍五入的误差问题，因此不太适合。因为人们对自己的金钱都很敏感，所以需要一个可以提供完美精度的数据类型。

可以把货币表示成 DECIMAL(M,2) 类型，其中 M 为所需取值范围的最大宽度。这种类型的数值可以精确到小数点后 2 位。DECIMAL 的优点在于不存在舍入误差，计算是精确的。

对于电话号码、信用卡号和社会保险号都会使用非数字字符。因为空格和短划线不能直接存储到数字类型列里，除非去掉其中的非数字字符。但即使去掉了其中的非数字字符，也不能把它们存储成数值类型，以避免丢失开头的“零”。

# 日期和时间类型

MySQL 对于不同种类的日期和时间都提供了数据类型，比如 YEAR 和 TIME。如果只需要记录年份，则使用 YEAR 类型即可；如果只记录时间，可以使用 TIME 类型。

如果同时需要记录日期和时间，则可以使用 TIMESTAMP 或者 DATETIME 类型。由于TIMESTAMP 列的取值范围小于 DATETIME 的取值范围，因此存储较大的日期最好使用 DATETIME。

TIMESTAMP 也有一个 DATETIME 不具备的属性。默认情况下，当插入一条记录但并没有指定 TIMESTAMP 这个列值时，MySQL 会把 TIMESTAMP 列设为当前的时间。因此当需要插入记录和当前时间时，使用 TIMESTAMP 是方便的，另外 TIMESTAMP 在空间上比 DATETIME 更有效。

MySQL 没有提供时间部分为可选的日期类型。DATE 没有时间部分，DATETIME 必须有时间部分。如果时间部分是可选的，那么可以使用 DATE 列来记录日期，再用一个单独的 TIME 列来记录时间。然后，设置 TIME 列可以为 NULL。SQL 语句如下：

CREATE TABLE mytb1 (
    date DATE NOT NULL,  #日期是必需的
    time TIME NULL  #时间可选(可能为NULL)
);

# 字符串类型

字符串类型没有像数字类型列那样的“取值范围"，但它们都有长度的概念。如果需要存储的字符串短于 256 个字符，那么可以使用 CHAR、VARCHAR 或 TINYTEXT。如果需要存储更长一点的字符串，则可以选用 VARCHAR 或某种更长的 TEXT 类型。

如果某个字符串列用于表示某种固定集合的值，那么可以考虑使用数据类型 ENUM 或 SET。

CHAR 和 VARCHAR 之间的特点和选择

CHAR 和 VARCHAR 的区别如下:

CHAR 是固定长度字符，VARCHAR 是可变长度字符。

CHAR 会自动删除插入数据的尾部空格，VARCHAR 不会删除尾部空格。

CHAR 是固定长度，所以它的处理速度比 VARCHAR 的速度要快，但是它的缺点就是浪费存储空间。所以对存储不大，但在速度上有要求的可以使用 CHAR 类型，反之可以使用 VARCHAR类型来实现。

存储引擎对于选择 CHAR 和 VARCHAR 的影响:

对于 MyISAM 存储引擎，最好使用固定长度的数据列代替可变长度的数据列。这样可以使整个表静态化，从而使数据检索更快，用空间换时间。

对于InnoDB存储引擎，最好使用可变长度的数据列，因为 InnoDB 数据表的存储格式不分固定长度和可变长度，因此使用 CHAR 不一定比使用 VARCHAR 更好，但由于 VARCHAR 是按照实际的长度存储，比较节省空间，所以对磁盘 I/O 和数据存储总量比较好。

ENUM 和 SET

ENUM 只能取单值，它的数据列表是一个枚举集合。它的合法取值列表最多允许有 65 535个成员。因此，在需要从多个值中选取一个时，可以使用 ENUM。比如，性别字段适合定义，为 ENUM 类型，每次只能从‘男’或‘女’中取一个值。

SET 可取多值。它的合法取值列表最多允许有 64 个成员。空字符串也是一个合法的 SET值。在需要取多个值的时候，适合使用 SET 类型，比如，要存储一个人兴趣爱好，最好使用SET类型。

ENUM 和 SET 的值是以字符串形式出现的，但在内部，MySQL 以数值的形式存储它们。

# 二进制类型

BLOB 是二进制字符串，TEXT 是非二进制字符串，两者均可存放大容量的信息。BLOB 主要存储图片、音频信息等，而 TEXT 只能存储纯文本文件。

[ 转义字符的使用 ] 在 MySQL 中，除了常见的字符之外，我们还会遇到一些特殊的字符，如换行符、回车符等。这些符号无法用字符来表示，因此需要使用某些特殊的字符来表示特殊的含义，这些字符就是转义字符。

转义字符一般以反斜杠符号\开头，用来说明后面的字符不是字符本身的含义，而是表示其它的含义。

以下是 MySQL 中常见的转义字符：

转义字符区分大小写，例如：'\b' 解释为退格，但 '\B' 解释为 'B'。

有以下几点需要注意：

字符串的内容包含单引号'时，可以用单引号'或反斜杠\来转义。

字符串的内容包含双引号"时，可以用双引号"或反斜杠\来转义。

一个字符串用双引号" 引用时，该字符串中的单引号 '不需要特殊对待，且不必被重复转义。同理，一个字符串用单引号' 引用时，该字符串中的双引号" 不需要特殊对待，且不必被重复转义。

如果你想要把二进制数据插入到一个 BLOB 列，下列字符必须使用反斜杠\转义：

NUL：ASCII 0。可以使用“\0“表示。

\：ASCII 92，反斜线。用“\\”表示。

' ：ASCII 39，单引号。用“\'”表示。

" ：ASCII 34，双引号。用“\"”表示。

存储引擎

数据库存储引擎是数据库底层软件组件，数据库管理系统使用数据引擎进行创建、查询、更新和删除数据操作。简而言之，存储引擎就是指表的类型。数据库的存储引擎决定了表在计算机中的存储方式。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎还可以获得特定的功能。

现在许多数据库管理系统都支持多种不同的存储引擎。MySQL 的核心就是存储引擎。

MySQL 提供了多个不同的存储引擎，包括处理事务安全表的引擎和处理非事务安全表的引擎。在 MySQL 中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。

MySQL 5.7 支持的存储引擎有 InnoDB、MyISAM、Memory、Merge、Archive、CSV、BLACKHOLE 等。

可以使用SHOW ENGINES;语句查看系统所支持的引擎类型，如图所示：

Support 列的值表示某种引擎是否能使用，YES表示可以使用，NO表示不能使用，DEFAULT表示该引擎为当前默认的存储引擎。

[ 如何选择 MySQL 存储引擎？ ] 在使用 MySQL 数据库管理系统时，选择一个合适的存储引擎是一个非常复杂的问题。不同的存储引擎都有各自的特性、优势和使用的场合，正确的选择存储引擎可以提高应用的使用效率。

为了能够正确地选择存储引擎，必须掌握各种存储引擎的特性。下面重点介绍几种常用的存储引擎，它们对各种特性的支持如下所示：

表中主要介绍了 MyISAM、InnoDB 和 MEMORY 三种存储引擎特性的对比。

下面详细介绍这 3 个存储引擎的应用场合并给出相应的建议：

1) MyISAM

在 MySQL 5.1 版本及之前的版本，MyISAM 是默认的存储引擎。

MyISAM 存储引擎不支持事务和外键，所以访问速度比较快。如果应用主要以读取和写入为主，只有少量的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择 MyISAM 存储引擎是非常适合的。

MyISAM 是在 Web 数据仓储和其他应用环境下最常使用的存储引擎之一。

2) InnoDB

MySQL 5.5 版本之后默认的事务型引擎修改为 InnoDB。

InnoDB 存储引擎在事务上具有优势，即支持具有提交、回滚和崩溃恢复能力的事务安装，所以比 MyISAM 存储引擎占用更多的磁盘空间。

如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包括很多的更新、删除操作，那么 InnoDB 存储引擎是比较合适的选择。

InnoDB 存储引擎除了可以有效地降低由于删除和更新导致的锁定，还可以确保事务的完整提交（Commit）和回滚（Rollback），对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB 都是合适的选择。

3) MEMORY

MEMORY 存储引擎将所有数据保存在 RAM 中，所以该存储引擎的数据访问速度快，但是安全上没有保障。

MEMORY 对表的大小有限制，太大的表无法缓存在内存中。由于使用 MEMORY 存储引擎没有安全保障，所以要确保数据库异常终止后表中的数据可以恢复。

如果应用中涉及数据比较少，且需要进行快速访问，则适合使用 MEMORY 存储引擎。

总之，不同应用的特点是千差万别的，选择适应存储引擎才是最佳方案也不是绝对的，这需要根据实际应用进行测试，从而得到最适合的结果。

# 操作默认存储引擎

如果需要操作默认存储引擎，首先需要查看默认存储引擎。可以通过执行下面的语句来查看默认的存储引擎：

SHOW VARIABLES LIKE 'default_storage_engine%'  # 查询默认存储引擎

下图，执行结果显示，InnoDB 存储引擎为默认存储引擎。

如需修改默认存储引擎，就需要修改配置文件 my.cnf，在 my.cnf 配置文件的 [mysqld] 后面加上

default-storage-engine=存储引擎名称

然后保存，重启MySQL服务就可以了或者使用下面的语句也可以修改数据库临时的默认存储引擎，但是当再次重启客户端时，默认存储引擎仍然是 InnoDB。

SET default_storage_engine=< 存储引擎名 >

如下图所示，将 MySQL 数据库的临时默认存储引擎修改为 MyISAM；此时，可以发现 MySQL 的默认存储引擎已经变成了 MyISAM。

# 修改单个表的存储引擎

数据表默认使用当前 MySQL 默认的存储引擎，有时为了达到数据表的特殊功能要求，也可重新设置表的存储类型。

MySQL 存储引擎主要有 InnoDB、MyISAM、Memory、BDB、Merge、Archive、Federated、CSV、BLACKHOLE 等。

在修改存储引擎之前，先使用 SHOW CREATE TABLE 语句查看 student 表当前的存储引擎。

mysql> SHOW CREATE TABLE student \G
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `stuId` int(4) DEFAULT NULL,
  `id` int(4) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `stuno` int(11) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `age` int(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

可以看到，student 表当前的存储引擎为 InnoDB。然后将 student 表的存储引擎修改为 MyISAM 类型，SQL 语句为：

ALTER TABLE student ENGINE=MyISAM;

使用 SHOW CREATE TABLE 语句再次查看 student 表的存储引擎，会发现student 表的存储引擎变成了“MyISAM”。