MySQL数据类型的选择
精华
小牛编辑
201浏览
2023-03-14
MySQL 提供了大量的数据类型，为了优化存储和提高数据库性能，在任何情况下都应该使用最精确的数据类型。
前面主要对 MySQL 中的数据类型及其基本特性进行了描述，包括它们能够存放的值的类型和占用空间等。本节主要讨论创建数据库表时如何选择数据类型。
可以说字符串类型是通用的数据类型，任何内容都可以保存在字符串中，数字和日期都可以表示成字符串形式。
但是也不能把所有的列都定义为字符串类型。对于数值类型，如果把它们设置为字符串类型的，会使用很多的空间。并且在这种情况下使用数值类型列来存储数字，比使用字符串类型更有效率。
另外需要注意的是，由于对数字和字符串的处理方式不同，查询结果也会存在差异。例如，对数字的排序与对字符串的排序是不一样的。
例如，数字 2 小于数字 11，但字符串 '2' 却比字符串 '11' 大。此问题可以通过把列放到数字上下文中来解决，如下面 SQL 语句：
如果让 MySQL 把一个字符串列当作一个数字列来对待，会引发很严重的问题。这样做会迫使让列里的每一个值都执行从字符串到数字的转换，操作效率低。而且在计算过程中使用这样的列，会导致 MySQL 不会使用这些列上的任何索引，从而进一步降低查询的速度。
所以我们在选择数据类型时要考虑存储、查询和整体性能等方面的问题。
在选择数据类型时，首先要考虑这个列存放的值是什么类型的。一般来说，用数值类型列存储数字、用字符类型列存储字符串、用时态类型列存储日期和时间。
例如，如果列的取值范围是 1~99999 之间的整数，则 MEDIUMINT UNSIGNED 类型是最好的选择。
当然，完全可以为要存储的值选择一种最“大”的数据类型。但是，如果正确选择数据类型，不仅可以使表的存储空间变小，也会提高性能。因为与较长的列相比，较短的列的处理速度更快。当读取较短的值时，所需的磁盘读写操作会更少，并且可以把更多的键值放入内存索引缓冲区里。
如果无法获知各种可能值的范围，则只能靠猜测，或者使用 BIGINT 以满足最坏情况的需要。如果猜测的类型偏小，那么也不是就无药可救。将来，还可以使用 ALTER TABLE 让该列变得更大些。
如果数值类型需要存储的数据为货币，如人民币。在计算时，使用到的值常带有元和分两个部分。它们看起来像是浮点值，但 FLOAT 和 DOUBLE 类型都存在四舍五入的误差问题，因此不太适合。因为人们对自己的金钱都很敏感，所以需要一个可以提供完美精度的数据类型。
可以把货币表示成 DECIMAL(M,2) 类型，其中 M 为所需取值范围的最大宽度。这种类型的数值可以精确到小数点后 2 位。DECIMAL 的优点在于不存在舍入误差，计算是精确的。
对于电话号码、信用卡号和社会保险号都会使用非数字字符。因为空格和短划线不能直接存储到数字类型列里，除非去掉其中的非数字字符。但即使去掉了其中的非数字字符，也不能把它们存储成数值类型，以避免丢失开头的“零”。
如果同时需要记录日期和时间，则可以使用 TIMESTAMP 或者 DATETIME 类型。由于TIMESTAMP 列的取值范围小于 DATETIME 的取值范围，因此存储较大的日期最好使用 DATETIME。
TIMESTAMP 也有一个 DATETIME 不具备的属性。默认情况下，当插入一条记录但并没有指定 TIMESTAMP 这个列值时，MySQL 会把 TIMESTAMP 列设为当前的时间。因此当需要插入记录和当前时间时，使用 TIMESTAMP 是方便的，另外 TIMESTAMP 在空间上比 DATETIME 更有效。
MySQL 没有提供时间部分为可选的日期类型。DATE 没有时间部分，DATETIME 必须有时间部分。如果时间部分是可选的，那么可以使用 DATE 列来记录日期，再用一个单独的 TIME 列来记录时间。然后，设置 TIME 列可以为 NULL。SQL 语句如下：
如果某个字符串列用于表示某种固定集合的值，那么可以考虑使用数据类型 ENUM 或 SET。
CHAR 是固定长度，所以它的处理速度比 VARCHAR 的速度要快，但是它的缺点就是浪费存储空间。所以对存储不大，但在速度上有要求的可以使用 CHAR 类型，反之可以使用 VARCHAR类型来实现。
存储引擎对于选择 CHAR 和 VARCHAR 的影响:
SET 可取多值。它的合法取值列表最多允许有 64 个成员。空字符串也是一个合法的 SET值。在需要取多个值的时候，适合使用 SET 类型，比如，要存储一个人兴趣爱好，最好使用SET类型。
ENUM 和 SET 的值是以字符串形式出现的，但在内部，MySQL 以数值的形式存储它们。
前面主要对 MySQL 中的数据类型及其基本特性进行了描述，包括它们能够存放的值的类型和占用空间等。本节主要讨论创建数据库表时如何选择数据类型。
可以说字符串类型是通用的数据类型，任何内容都可以保存在字符串中，数字和日期都可以表示成字符串形式。
但是也不能把所有的列都定义为字符串类型。对于数值类型，如果把它们设置为字符串类型的，会使用很多的空间。并且在这种情况下使用数值类型列来存储数字，比使用字符串类型更有效率。
另外需要注意的是，由于对数字和字符串的处理方式不同，查询结果也会存在差异。例如，对数字的排序与对字符串的排序是不一样的。
例如，数字 2 小于数字 11，但字符串 '2' 却比字符串 '11' 大。此问题可以通过把列放到数字上下文中来解决，如下面 SQL 语句：
SELECT course+ 0 as num ... ORDER BY num;
让 course 列加上 0，可以强制列按数字的方式来排序，但这么做很明显是不合理的。如果让 MySQL 把一个字符串列当作一个数字列来对待，会引发很严重的问题。这样做会迫使让列里的每一个值都执行从字符串到数字的转换，操作效率低。而且在计算过程中使用这样的列，会导致 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 以数值的形式存储它们。