MySQL之数据类型

一、VARCHAR与CHAR

这两者都是用来表示字符串的数据类型。

VARCHAR

VARCHAR属于可变长的字符串，相对于定长的字符串会更加节省存储空间。在存储数据的时候VARCHAR变量需要在原有的数据大小的基础上额外使用1或2个字节记录字符串的长度（当列的最大长度小于等于255时，使用1个字节记录，否则使用2个字节记录）。由于长度可变的特性，使得VARHCHAR类型的数据在UPDATE的时候耗时会更久。且在存储时会在VARCHAR类型数据的最后添加一个空格（可以理解成为长度改变所做的准备）。
下面情况使用VARCHAR类型是合适的：字符串列的最大长度比平均长度大很多（即字符串长度都比较短）；列的更新比较少。

CHAR

CHAR是定长的字符串，MySQL会根据字符串的长度分配足够的空间，且会在存储数据的时候剔除数据最后的空格。
CHAR类型适合存储长度较短且列的数据长度基本一致的数据；对于经常变更的数据也适合采用CHAR值，因为不会像VARCHAR的可变特性一样产生存储碎片的问题。
创建一个存储char字符串的表格：

mysql> CREATE TABLE char_test(char_col CHAR(10));

插入几条数据：

mysql> INSERT INTO char_test(char_col) VALUES 
('string1'),(' string2'),('string3   ');

注意此时第二个数据的开头与第三个数据的结尾存在空格。
查看结果：

mysql> SELECT * FROM char_test;
+----------+
| char_col |
+----------+
| string1  |
|  string2 |
| string3  |
+----------+
3 rows in set (0.00 sec)

会发现第二个数据开头的空格得以保留，而第三个数据结尾的空格被移除了，这也就验证了我们上面所说的CHAR类型在存储数据的时候会自动剔除结尾的空格。
为了对比我们再做一个VARCHAR类型的数据，添加相同的数据得到的结果是：

mysql> SELECT * FROM varchar_test;
+-------------+
| varchar_col |
+-------------+
| string1     |
|    string2  |
| string3     |
+-------------+
3 rows in set (0.00 sec)

第三个数据结尾的空格得以保留。

二、枚举（ENUM）类型

枚举类型其实和字符串类型非常相似，不同的是枚举类型在创建的时候就规定了这个字段所能使用的字符串的一个集合，之后该字段出现的字符串只能是枚举集合中存在的。这种情况虽然看似使得数据的操作变得不灵活，但是却因为这种先规定的特性使得数据在存储的时候能够被压缩的非常紧凑，节省了存储空间。
在一个表中创建一个枚举类型的字段：

mysql> CREATE TABLE enum_test(e ENUM('fish','apple','dog'));

上面的语句为表格创建了一个名为e的枚举字段，该字段所能出现的值就只有'fish'、'apple'和'dog'，其他内容都不能出现在这个字段中。
但是MySQL在存储ENUM类型的数据的时候并不是按照'fish'，'apple'，'dog'这样的内容来存储的，而是存储他们的索引，即存储的内容是一个证整数。

mysql> INSERT INTO enum_test VALUES ('fish'),('apple'),('dog');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM enum_test;
+-------+
| e     |
+-------+
| fish  |
| apple |
| dog   |
+-------+
3 rows in set (0.00 sec)

mysql> SELECT e+0 FROM enum_test;
+------+
| e+0  |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

从第二个查询的结果可以看出在MySQL内部，'fish'存储的其实是0，'apple'存储的其实是1，'dog'存储的其实是2，即根据枚举定义的时候的定义顺序的一个编号。
让我们再看看另一个现象：

mysql> SELECT * FROM enum_test ORDER BY e;
+-------+
| e     |
+-------+
| fish  |
| apple |
| dog   |
+-------+
3 rows in set (0.00 sec)

如果此时的e数据类型是字符串类型，那么执行ORDER BY e之后e的内容会按照字母的顺序排列，但很显然，当前的情况并没有按照字母顺序排列，而是依然按照定义时候的顺序排列，其实也就是按照对应存储的那个整数进行排列的。

三、日期和时间类型

DATETIME

DATETIME类型表示的时间范围广（1001年-9999年），精度为秒，与时区无关，使用8个字节的空间存储。
DATETIM的显示格式：

mysql> SELECT * FROM time_test;
+---------------------+
| time                |
+---------------------+
| 2020-01-01 22:37:08 |
+---------------------+
1 row in set (0.00 sec)

TIMESTAMP

TIMESTAMP类型所表示的时间范围没有那么广（1970-2038），使用4个字节的空间存储。
TIMESTAMP显示的值依赖性时区，MySQL服务器，操作系统以及客户端连接都能够进行时区的设置。这就意味着在多个时区存储或者访问数据，TIMESTAMP类型保存的值和DATETIME类型将很不一样，前者提供的值与时区有关系，后者则保留文本表示的日期和时间。
除了特殊情况外，都推荐使用TIMESTAMP来存储，因为它的空间效率比DATETIME更高。
如果存储的时间的精度需要比秒更小的粒度怎么办？MySQL目前还没有提供合适的数据类型，可以使用MariaDB。

四、选择优化的数据类型

更小的可能更好

指的是在确保数据类型能够满足该数据要求范围的情况下，应该使用尽可能小的数据类型。因为使用范围过大的数据类型会对性能与空间产生一定的消耗，特别是当数据量多的时候查询的效率会受到一定的影响。

简单就好

简单的数据类型操作通常会更加快捷与高效。例如，整数会比字符串的操作代价更低。有两个比较明显的例子：一是应该使用MySQL的内建类型（DATETIME，DATE，TIME）来存储时间和日期，而不是使用字符串来存储；二是应该使用整型存储IP地址。

尽量避免NULL

通常情况下最好指定列为NOT NULL，除非真的需要存储NULL值。使用NULL值后MySQL的优化将变得更加艰难，因为使用NULL将使得索引，索引统计以及值的比较都变得更加复杂；可为NULL的列也会占据更大的空间。