2019-05-02笔记：MySQL存储引擎和数据类型

一、MySQL存储引擎

查看MySQL支持的引擎：

SHOW ENGINES;

 

1.FEDERATED

许多环境需要在一台服务器上运行Apache、MySQL和PHP。事实上，这有很多用处，但是如果需要从一些不同的MySQL服务器上聚合数据，其中有些服务器可能位于网络之外甚至归另外的公司所有，这时该怎么办？因为一直都可以连接远程MySQL数据库服务器，这实际上不是问题。但是，管理每个单独服务器连接的过程很快会变得很麻烦。为缓解此问题，可以使用FEDERATED存储引擎，创建远程表的本地指针。这样执行查询时就好像表位于本地一样，可以减少与各个远程数据库进行连接的麻烦。

在配置文件中添加 federated，就可以使用了。

            

   

假设一个表 products 位于远程服务器（称为服务器 A）的 corporate 数据库。

CREATE TABLE products (
    id SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    SKU CHAR(8) NOT NULL,
    NAME VARCAHR(35) NOT NULL,
    price DECIMAL(6,2)
) ENGINE=MyISAM;

假设要从另外一个服务器（称为服务器 B）访问此表。为此，在服务器 B 上创建一个相同的表结构，唯一的区别是该表引擎类型应当为 FEDERATED，而不是MyISAM。此外，必须提供连接参数，以便服务器 B 与服务器 A 上的通信。

CREATE TABLE products (
    id SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    SKU CHAR(8) NOT NULL,
    NAME VARCAHR(35) NOT NULL,
    price DECIMAL(6,2)
) ENGINE=FEDERATED
CONNECTION='mysql:/username:password@192.168.1.2:3306/corporate/products';

创建之后，就可以在B服务器上访问 products 表来访问服务器 A 的 products 表。此外，假如连接字符串中指定的用户拥有必要的权限，可以添加、修改和删除远程表中的数据。

2.MRG_MYISAM（MEGRE）

MyISAM 还提供了另外一种类型，尽管其使用不如其他引擎突出，但在某些情况下非常有用。这种类型被称为 MERGE 表，实际上是相同 MyISAM 表的聚合器。 假设数据库通常用于存储时间特定的数据——因该能很快想到，销售信息、服务器日志和航班时间表就属于这一类数据。但可以想见，这些数据存储会很快变得很大且非常难于管理。因此，一种常用的存储策略是将数据分成很多表，每个名称与特殊的时间块相关。例如，可以用 12 个相同的表来存储服务器日志数据，每个表用对应各个月份的名字命名。 不过，有必要基于所有 12 个表的数据生成报表，这意味着需要编写并更新多表查询，以反映这些表中的是信息。与其编写这些可能出现错误的查询，不如将这些表合并起来使用一条查询。之后可以删除 MERGE 表，而不影响原来的数据。

3.MyISAM

MyISAM 表无法处理事务，这意味着应当对所有非事务需求使用此类型，从而避免事务型存储引擎（如 InnoDB）所需的额外开销。

适用情况：

选择密集的表。MyISAM存储引擎在筛选大量数据时非常迅速，甚至在高流量环境中也是如此。

插入密集的表。MyISAM的并发插入特性允许同时选择和插入数据。

3种MyISAM格式：

MyISAM静态、MyISAM动态、MyISAM压缩

MyISAM静态：

如果所有的表列的大小都是静态的(即不使用 xBLOB、XTEXT、或 VARCHAR 数据类型)，MySQL 就会自动使用静态 MyISAM 格式。这种类型的表性能非常高，因为在维护和访问以预定义格式存储的数据时开销很低而且最不可能出现因数据损坏而失败的情况。但是这项优点要以空间为代价，因为每列都需要分配给该列的最大空间，而无论该空间是否被真正使用。

MyISAM动态：

如果有表列被定义为动态的(使用 xBLOB、XTEXT、或 VARCHAR 数据类型)，MySQL 就会自动使用动态格式。虽然 MyISAM 动态表占用的空间比静态格式所占空间少，但空间的节省导致了性能的下降。如果某个字段的内容发生改变，则其位置很可能需要移动，这会导致碎片的产生。随着数据集中碎片的增加，数据访问性能就会相应降低。

修复方法：

尽可能的使用静态数据类型。

经常使用 OPTIMIZE TABLE 语句，它会整理表的碎片，恢复由于表更新和删除导致的空间丢失。

MyISAM压缩：

有时会创建在整个应用程序生命周期中都只读的表。如果是这种情况，就可以使用 myisampack 工具将其转换为 MyISAM 压缩表来减少所占空间。在给定的硬件配置下(如，快速的处理器和低俗的硬盘驱动器)，性能的提升将相当显著。

4.BLACKHOLE

BLACKHOLE 在操作上与 MyISAM 引擎类似，只是它不存储任何数据。可以使用此引擎测量日志导致的开销，因为即使不存储数据也有可能记录查询日志。

5.CSV

CSV 存储引擎以一种逗号分隔的格式存储表数据，这类似于很多应用程序所支持的逗号分隔格式，如 OpenOffice 和 Microsoft Office。

虽然访问和操作 CSV 表与访问其他任何表类型一样，但 CSV 表实际上是文本文件。可以通过 MySQL 指定数据文件夹中的相应数据文件（带有 .csv 扩展名）复制现有的 CSV文件。

6.MEMORY

创建 MySQL MEMORY 存储引擎的出发点是速度。为得到最快的响应时间，采用的逻辑存储介质是系统内存。虽然在内存中存储表数据确实会提供很高的性能，但当 mysqld 守护进程崩溃时，所有的 MEMORY 数据都会丢失。

获得速度的同时也带来了一些缺陷。例如，MEMORY 表不支持 VARCHAR、BLOB 或 TEXT 数据类型，因为这种表类型按固定长度的记录格式存储。

当有如下情况时，可以考虑使用 MEMORY 表：

可以忽略。目标数据相对较小，而且被非常频繁的访问。在内存中存储数据会造成内存不能用于其他目的。可以通过参数 max_heap_table_size 控制 MEMORY 表的大小。

暂时。目标数据只是临时需要，在其生命周期中必须立即使用。

相对无关。存储在 MEMORY 表中的数据如果突然丢失，不会对应用服务器产生实质的负面影响，而且不会对数据完整性有长期影响。

而且支持散列索引和B树索引。B树索引优于散列索引的是，可以使用部分查询和统配查询，也可以使用<、>和=等操作符方便数据挖掘。

7.ARCHIVE

尽管目前拥有低开销、高容量的存储系统，像银行、医院和零售商这样的组织还是必须特别小心的以最有效的方式存储大量数据。因为这些数据一般必须维护很长时间，即使可能很少被访问，所以进行压缩是有意义的，并只在必要时才压缩。

ARCHIVE 存储引擎大大压缩了此类型表中的数据，它使用 zlib，在记录被请求时会实时进行解压。除了选择记录，还可以插入记录，当把旧数据移植到 ARCHIVE 表中时，这很有必要。但是，不允许删除或更新存储在这些表中的数据。

注意，存储在 ARCHIVE 中的任何数据都不会有索引，这意味着 SELECT 操作可能效率很低。如果出于某种原因需要对 ARCHIVE 表完成扩展分析，可以将此表转换成 MyISAM 表并重新创建必要的索引。

8.InnoDB

InnoDB表是如下情况的理想引擎：

更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求。

事务。InnoDB存储引擎是唯一支持事务的标准MySQL存储引擎，这是管理敏感数据的必须特性。

自动灾难性恢复。与其他存储引擎不同，InnoDB表能够自动从灾难中恢复。虽然MyISAM表也能在灾难后修复，但其过程要长的多。

二、数据类型

1.日期和时间数据类型

DATE

DATE 数据类型负责存储日期信息。虽然 MySQL 以标准的 YYYY-MM-DD 格式显示 DATE 值，但这些值可以使用数字或字符串形式来插入。例如，20180808 和 2018-08-08 都可以被接受为有效的输入。日期范围从 1000-01-01 到 9999-12-31.

DATETIME

DATETIME 数据类型负责存储日期和时间信息的组合。与 DATE 一样，DATETIME 值以标准格式 YYYY-MM-DD HH:MM:SS 存储。这些值可以使用数值或字符串形式插入。例如，20100810153510 和 2010-08-10 15:35:10 都可以被接受为有效输入。DATETIME 的范围为 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59。

TIME

TIME 数据类型负责存储时间信息，支持范围相当大，不仅足以表示标准和军用时间格式，还可以表示扩展时间间隔。其范围为 - 838:59:59 ~ 838:59:59。

TIMESTAMP

TIMESTAMP 数据类型不同于 DATETIME，执行了影响 TIMESTAMP 数据的 INSERT 或 UPDATE 操作之后，MySQL 会自动将其更新为当前的日期和时间。TIMESTAMP 值显示为 HH:MM:SS 格式，与 DATE 和 DATETIME 数据类型一样，也可以使用数值或字符串形式进行赋值。TIMESTAMP 的范围为 1970-01-01 00:00:00 ~ 2037-12-31 23:59:59，其存储需要4个字节。

YEAR

YEAR 数据类型负责存储年份特定的信息，根据上下文支持多种范围。

两位数据 - 1 ~ 99. 范围为 1 ~ 69 的值转换为 2001 ~ 2069 的值，而范围为 70 ~ 99 的值转换为范围为 1970 ~ 1999 的值。

四位数值 - 1901 ~ 2155。

两位数字字符串 - "00" ~ "99"。范围为 "00" ~ "69" 的值转换为 "2000" ~ "2069" 的值，而范围为 "70" ~ "99" 的值转换为范围为 "1970" ~ "1999" 的值。

四位数字字符串 -  "1901" ~ "2155"。

2.数值数据类型

BOOL 和 BOOLEAN

BOOL 和 BOOLEAN 只是 TINYINT(1) 的别名，用于赋值 0 或 1。

BIGINT [ (M) ]

BIGINT 数据类型提供了 MySQL 最大的整数范围，支持的有符号数范围是 -9 223 372 036 854 775 808 ~ 9 223 372 036 854 775 807，无符号范围是 0 ~ 18 446 744 073 709 551 615。

INT [ (M) ] [UNSIGNED] [ZEROFILL]

INT 数据类型提供了 MySQL 的第二大整数范围，支持的有符号数范围是 -2 147 483 648 ~ 2 147 483 647，无符号数范围是 0 ~ 4 294 967 295。

MEDIUMINT [ (M) ] [UNSIGNED] [ZEROFILL]

MEDIUMINT 数据类型提供了 MySQL 的第三大整数范围，支持的有符号数范围是 -8 388 608 ~ 8 388 607，无符号数范围是 0 ~ 16 777 215。

SMALLINT [ (M) ] [UNSIGNED] [ZEROFILL]

SMALLINT 数据类型提供了 MySQL 的第四大整数范围，支持的有符号数范围是 -32 768 ~ 32 767，无符号数范围是 0 ~ 65535。

TINYINT [ (M) ] [UNSIGNED] [ZEROFILL]

TINYINT 数据类型是 MySQL 最小的整数范围，支持的有符号数范围是 -128 ~ 127，无符号数范围是 0 ~ 255。

DECIMAL ([M,D]) [UNSIGNED] [ZEROFILL]

DECIMAL 数据类型是存储为字符串的浮点数，支持的有符号数范围是 -1.7976931348623157E+308 ~ -2.2250738585072014E-308，无符号数范围是 2.2250738585072014E-308 ~ 1.7976931348623157E+308。在确定数值的总大小时，忽略小数点和负号。

DOUBLE ([M,D]) [UNSIGNED] [ZEROFILL]

DOUBLE 数据类型时双精度浮点数，支持的有符号数范围是 -1.7976931348623157E+308 ~ -2.2250738585072014E-308，无符号数范围是 2.2250738585072014E-308 ~ 1.7976931348623157E+308。

FLOAT ([M,D]) [UNSIGNED] [ZEROFILL]

这种 FLOAT 数据类型变体是 MySQL 的单精度浮点数表示形式，支持的有符号数范围是 -3.402823466E+38 ~ -1.175494351E-38，无符号数范围是 1.175494351E ~ 3.402823466E+38。

3.字符串数据类型

CHAR(Length)

CHAR 数据类型为 MySQL 提供了固定长度的字符串表示形式，支持最大 255 个字符。如果插入的字符串不足占用 Length 指定的空间，剩余部分将被填充为空白。在获取时，将忽略这些空白。

VARCHAR(Length)

VARCHAR 数据类型时 MySQL 的可变长度字符串表示形式，支持的长度为 0 ~ 65536 个字符。

LONGBLOB

LONGBLOB 数据类型是 MySQL 最大的二进制字符串表示形式，支持最大长度 4 294 967 295 个字符。

LONGTEXT

LONGTEXT 数据类型是 MySQL 最大的非二进制文本字符串，支持最大长度 4 294 967 295 个字符。

MEDIUMBLOB

MEDIUMBLOB 数据类型是 MySQL 的第二大二进制字符串表示形式，支持最大长度 16 777 215 个字符。

MEDIUMTEXT

MEDIUMTEXT 数据类型是 MySQL 的第二大非二进制文本字符串，支持最大长度 16 777 215 个字符。

BLOB

BLOB 数据类型是 MySQL 的第三大二进制字符串表示形式，支持最大长度 65 535 个字符。

TEXT

TEXT 数据类型是 MySQL 的第三大非二进制文本字符串，支持最大长度 65 535 个字符。

TINYBLOB

TINYBLOB 数据类型是 MySQL 最小的二进制字符串表示形式，支持最大长度 255 个字符。

TINYTEXT

TINYTEXT 数据类型是 MySQL 最小的非二进制文本字符串，支持最大长度 255 个字符。

ENUM("member1","member2",..."member65535")

ENUM 数据类型为最多存储一组预定义值中的零个或多个值提供了一种方法，这组值最多包括 65535 个不同成员。成员的选择限制为列定义中声明的值。如果列声明包括 NULL 属性，则 NULL 将被认为是一个有效值，并且是默认值。如果声明了 NOT NULL ，则列表的第一个成员是默认值。

SET("member1","member2",..."member64")

SET 数据类为指定一组预定义值中的零个或多个值提供了一种方法，这组值最多包括 64 个不同成员。值的选择限制为列定义中声明的值。存储需求是1、2、3、4或8个值，这取决与成员的数目。可以使用公式(N+7)/8确定确切的需求，这里N是集合大小。

三、数据类型属性

1.AUTO_INCREMENT

为列赋此属性将为每个新插入的行赋值为上一次的 ID+1。

MySQL要求将 AUTO_INCREMENT 属性用与作为主键的列。此外，每个表只允许有一个 AUTO_INCREMENT列。

2.BINARY

BINARY 属性只能用于 CHAR 和 VARCHAR 值。当为列指定了此属性时，将以区分大小写的方式排序（根据 ASCII）。与之相反，忽略 BINARY 属性时将使用不区分大小写的方式排序。

3.DEFAULT

DEFAULT 属性确保在没有任何值可用的情况下，赋予某个常量值。这个值必须是常量，因为 MySQL 不允许插入函数或表达式。此外，此属性无法用于 BLOB 或 TEXT 列。如果已经为此列指定了 NULL 属性，没有指定默认值时默认值为NULL，否则（如果指定了 NOT NULL）默认值将依赖于字段的数据类型。

4.INDEX

普通索引。

5.NOT NULL

如果将一个列定义为 NOT NULL，将不允许向该列插入 NULL 值。建议在重要情况下始终使用NOT NULL 属性，因为它提供了一个基本验证，确保已经向查询传递了所有必要的值。

6.NULL

为列指定 NULL 属性时，该列可以保持为空。

7.PRIMARY KEY

PRIMARY KEY 属性用于确保指定行的唯一性。指定为主键的列中，值不能重复，也不能为空。为指定为主键的列赋予 AUTO_INCREMENT 属性时很常见的，因为此列不必与行数据有任何关系，而只是作为一个唯一表示符。

8.UNIQUE

被赋予 UNIQUE 属性的列将确保所有值都有不同的值，只是 NULL 值可以重复。

9.ZEROFILL

ZEROFILL 属性可用于任何数值类型，用 0 填充所有剩余字段空间。例如，无符号 INT  的默认宽度为10；因此，当 "零填充" 的 INT 值为 4 时，将表示它为 0000000004。