MySQL 数据类型选择原则

目录

• 小而美

• 简单至上

• 避免空值

• 选择数据类型的步骤

• 数据类型介绍

• 一、串数据类型

• 二、数值数据类型

• 三、日期和时间数据类型

• 四、二进制数据类型

• 结语：

小而美

通常来说，尽可能使用占用存储空间小的数据类型来存储数据。这类数据类型通常也会更快，并且占用的磁盘空间、内存乃至缓存都更小，而且占用的 CPU 处理周期也少。

但是，务必准确估计要存储的数据值的范围。因为在数据表结构的多个地方扩充数据范围会是一个痛苦且耗时的过程。如果在犹豫哪种数据类型合适，那就选择你认为不会超出范围的最小空间的类型（在系统早期或者数据表 数据不多的情况下也可以进行调整）。

简单至上

数据类型越简单意味着处理数据的 CPU 周期越少。例如，整型相比字符型而言，处理起来更容易，这是因为字符集和比对使得字符的比较更复杂。举两个例子：应该使用 MySQL 内置的类型来存储时间和日期，而不是字符串。IP 地址也应该使用整型存储。

避免空值

很多数据表都是要可为空的列，虽然在应用中并不需要存储缺省值NULL。通常来说，指定列为 NOT NULL 会比存储 NULL 要更优。

MySQL 对于涉及到可为空的列优化起来更为困难，这是因为空值列使得索引、索引统计和值比较都变得复杂。而且，可为空的列占据的存储空间更大，且需要特殊的处理。如果在可为空的列上指定了索引，这会需要每个索引入口多一个额外的字节，甚至会导致 MyISAM 引擎固定大小的索引转换为可变大小的索引（例如对整数型字段做单列索引）。 不过，将 NULL 列转换为 NOT NULL列的性能改进通常并不大。因此，除非已经发现了 NULL 列对性能有很大的影响，否则不要优先去对已有的数据表结构进行改动。但是，如果需要对列构建索引，那应该尽量避免该列值可以为空，通常好的习惯是直接设置该列为 NOT NULL。

当然，也有例外，例如在 InnoDB 中仅仅使用了一个 bit 来存储 NULL 值，因此对大量数据存储来说可以有效节省空间，但是如果是 MyISAM 引擎就不是这样了。

选择数据类型的步骤

选择数据类型的第一步是决定数据列使用哪种常用的数据类型来表示，是数值型、字符串型还是时间类型。通常直接选择就挺不错的，但是在某些情况下会有特殊（比如金额、时间戳）。

第二步就是选择具体的类型。MySQL对于同一种数据类型会有多种存储方式，基于数据值范围、精度以及存储的物理空间，而还有些数据类型有一些特殊的属性。

例如，DATETIME 和 TIMESTAMP 都可以存储时间和日期，都可以精确到秒。然而，TIMESTAMP 类型只需要一半的存储空间，并且包括了时区信息，还支持自动更新。但另一方面，它存储的时间范围更小，它的这些特殊特性可能变成障碍。

再来看看基本数据类型。MySQL 支持数据类型的别名，例如 INTEGER，BOOL 和 NUMERIC。这些仅仅是别名，虽然看起来会让人困惑，但是实际上对性能没有影响。如果使用了别名数据类型创建数据表，可回忆使用 SHOW CREATE TABLE，可以看到实际上 MySQL 会转换为基础数据类型，而不是别名。

数据类型：定义列中可以存储什么数据以及该数据实际怎样存储的基本规则。

数据类型用于以下目的：

1、允许限制可存储在列中的数据。如：数值数据类型列只能接受数值。

2、允许在内部更有效地存储数据。如：用比文本串更简洁的格式存储数值和日期时间值。

3、允许变换排序顺序。如：数据都作为串处理，则1位于10前，10位于2前（串以字典顺序排序，从左边开始比较，一次一个字符）；作为数值数据类型，数值才能正确排序。

数据类型介绍

一、串数据类型

最常用的数据类型，存储串，如名字、地址、电话号码等。

两种基本的串类型：定长串和变长串。

定长串：接受长度固定的字符串，其长度是在创建表时指定的。定长列不允许多于指定的字符数目，它们分配的存储空间与指定的一样多。如：CHAR。

变长串：存储可变长度的文本。有些变长数据类型具有最大的定长，有些则是完全变长的，不管是哪种，只有指定的数据会得到保存（额外的数据不保存），如：TEXT。

PS：MySQL处理定长列远比处理变长列快得多。且MySQL不允许对变长列（或一个列的可变部分）进行索引。

数据类型说明：

•  CHAR：1～255个字符的定长串。长度必须在创建时指定，否则MySQL假定为CHAR(1)。

•  ENUM：接受最多64K个串组成的一个预定义集合的某个串。

•  LONGTEXT：与TEXT相同，但最大长度为4GB。

•  MEDIUMTEXT：与TEXT相同，但最大长度为16 K。

•  SET：接受最多64个串组成的一个预定义集合的零个或多个串。

•  TEXT：最大长度为64 K的变长文本。

•  TINYTEXT：与TEXT相同，但最大长度为255字节。

•  VARCHAR：长度可变，最多不超过255字节。如创建时指定为VARCHAR(n)，则可存储0到n个字符的变长串（其中n≤255）。

PS：

1、引号：使用何种形式的串数据类型，串值都必须括在引号内（通常使用单引号）。

2、须遵守的基本规则：如果数值是计算（求和、平均等）中使用的数值，则存储在数值数据类型列中。如果数值作为字符串使用，则保存在串数据类型列中。如：在数值字段中存储邮政编码01234，保存的是数值1234，丢失了一位数字。

二、数值数据类型

存储数值。MySQL支持多种数值数据类型，每种存储的数值具有不同的取值范围。

支持的取值范围越大，所需存储空间越多。此外，有的数值数据类型支持使用十进制小数点（和小数），而有的则只支持整数。表D-2列出了常用的MySQL数值数据类型。

PS：

1、所有数值数据类型（除BIT和BOOLEAN外）都可以有符号或无符号。有符号数值列可以存储正或负的数值，无符号数值列只能存储正数。

2、默认情况为有符号，若不需要存储负值，可以使用UNSIGNED，这样做将允许你存储两倍大小的值。

3、与串不同，数值不应该在引号内。

4、MySQL中没有专门存储货币的数据类型，一般情况下使用DECIMAL(8, 2)。

数据类型说明：

•  BIT：位字段，1～64位。在MySQL 5之前，BIT在功能上等价于TINYINT。

•  BIGINT：整数值，支持-9223372036854775808～9223372036854775807。如果是UNSIGNED，为0～18446744073709551615的数。

•  BOOLEAN（或BOOL）：布尔标志，为0或者为1，主要用于开/关（on/off）标志。

•  DECIMAL（或DEC）：精度可变的浮点值。

•  DOUBLE：双精度浮点值

•  FLOAT：单精度浮点值

•  INT（或INTEGER）：整数值，支持-2147483648～2147483647，UNSIGNED同上。

•  MEDIUMINT：整数值，支持-8388608～8388607，UNSIGNED同上。

•  REAL：4字节的浮点值。

•  SMALLINT：整数值，支持-32768～32767，UNSIGNED同上。

•  TINYINT：整数值，支持-128～127，UNSIGNED同上。

三、日期和时间数据类型

数据类型说明：

•  DATE：表示1000-01-01～9999-12-31的日期，格式为YYYY-MM-DD。

•  DATETIME：DATE和TIME的组合。

•  TIMESTAMP：功能和DATETIME相同，但范围较小。

•  TIME：格式为HH:MM:SS。

•  YEAR：2位数字表示，范围是70～69（1970～2069）；4位数字表示，范围是1901～2155

四、二进制数据类型

可存储任何数据（甚至包括二进制信息），如图像、多媒体、字处理文档等。

数据类型说明：

•  BLOB：Blob最大长度为64KB。

•  MEDIUMBLOB：Blob最大长度为16 MB。

•  LONGBLOB：Blob最大长度为4GB。

•  TINYBLOB：Blob最大长度为255字节。

结语：

MySQL 的数据表示方式很多，建议了解常用的数据类型的存储范围，占据的字节数，尽可能地根据产品预估数据值范围或长度，选择合适的数据类型，从而在创建表一开始就注重性能。后期再来调整的代价往往超出设计之初付出的细致思考的时间成本。

来源：https://juejin.cn/post/6966603936270647332