数据库中将数字类型存储成字符型有哪些坏处？

    工作中经常见到一些设计粗糙的数据库，其中将数字类型的字段定义和存储成字符型是一种比较常见的情况。部分开发同学或者非数据库岗位很多为了图方便，往往又不在意这些细节，除非等到出现可见的或者一些不可见的问题时才会去研究隐藏其后的真相。所以，这里简单总结一下，在数据库系统中，将数字类型存储成字符类型会有哪些不好的地方。以关系型数据库MySQL示例。

    ①字符类型往往比数字类型占用更多的存储

    首先，字符类型往往占用更多的存储，但非总是占用更多的存储。MySQL以固定长度存储数字类型：TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT分别占用1、2、3、4和8个Byte存储。所以，就“1024”这个数值，如果以BIGINT、CHAR(4)和VARCHAR存储，不考虑其他，分别需要8、4和5个Byte存储（VARCHAR类型以是否存储了超过255个字符为界限分别需要额外1个或者2个Byte来记录存储长度）。但实际使用中，我们遇到的数字类型，其值域或者说取值空间是很宽大的，或者刚好可以放入一个较小的数字类型定义中。绝大多数情况下，总是可以通过选用数字类型从而节省存储空间。文件型数据库，数据最终是存储在磁盘上的文件，当需要对其查询时再读取到内存中。所以，更少的存储空间消耗与更高的查询性能是直接相关的。

    ②数字类型存储成字符类型可能使索引失效

    字段间进行比较时，如果字段类型不一致，在查询之前需要类型转换，否则无法直接进行比较。因为额外的类型转换操作，使得字段上的索引不可用。因此，将数字类型存储成字符类型，并且为其定义了索引，当其与其他数字类型字段关联查询时，该索引就失效了。

    ③数字类型存储成字符类型可能使部分SQL函数和命令失效

    数字的计算比较是作为一个整体在CPU中进行的。当数字类型存储成字符型时，会受到编码和排序规则影响；对字符型的查询是从左往右一个字符一个字符依次进行的，即最左前缀匹配。因此，将字符型数字直接传入部分函数或命令会使结果失真。

    首先创建两张示例表，插入相同的数据，但数据类型定义不一致。

CREATE TABLE `i` (
	`i` INT(11) NULL DEFAULT NULL
)
;
INSERT INTO `i` (`i`) VALUES (4);
INSERT INTO `i` (`i`) VALUES (8);
INSERT INTO `i` (`i`) VALUES (11);
INSERT INTO `i` (`i`) VALUES (23);
INSERT INTO `i` (`i`) VALUES (1024);
INSERT INTO `i` (`i`) VALUES (2147483647);

CREATE TABLE `s` (
	`s` VARCHAR(50) NULL DEFAULT NULL
)
;
INSERT INTO `s` (`s`) VALUES ('4');
INSERT INTO `s` (`s`) VALUES ('8');
INSERT INTO `s` (`s`) VALUES ('11');
INSERT INTO `s` (`s`) VALUES ('23');
INSERT INTO `s` (`s`) VALUES ('1024');
INSERT INTO `s` (`s`) VALUES ('2147483647');

    数字类型存储成字符型使得SQL查询结果失真。

mysql> SELECT MAX(i.i) FROM i;
+------------+
| MAX(i.i)   |
+------------+
| 2147483647 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT MAX(s.s) FROM s;
+----------+
| MAX(s.s) |
+----------+
| 8        |
+----------+
1 row in set (0.00 sec)

mysql> SELECT i.i FROM i ORDER BY i.i;
+------------+
| i          |
+------------+
|          4 |
|          8 |
|         11 |
|         23 |
|       1024 |
| 2147483647 |
+------------+
6 rows in set (0.00 sec)

mysql> SELECT s.s FROM s ORDER BY s.s;
+------------+
| s          |
+------------+
| 1024       |
| 11         |
| 2147483647 |
| 23         |
| 4          |
| 8          |
+------------+
6 rows in set (0.00 sec)

    为了得到预想的数值结果，必须额外进行一次数据类型转换操作，包括隐式转换和显式转换两种方式。隐式转换是进行一个简单的不改变数值大小的数学计算；显式转换是借助CAST或者CONVERT函数。隐式操作在开发维护工作中往往就是出问题的根源，所以如果真的到了需要进行类型转换的时候，建议使用标准SQL规范的CAST。

mysql> SELECT MAX(s.s + 0) FROM s;
+--------------+
| MAX(s.s + 0) |
+--------------+
|   2147483647 |
+--------------+
1 row in set (0.01 sec)

mysql> SELECT MAX(CAST(s.s AS DECIMAL)) FROM s;
+---------------------------+
| MAX(CAST(s.s AS DECIMAL)) |
+---------------------------+
|                2147483647 |
+---------------------------+
1 row in set (0.00 sec)

 

 

    参考：

https://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html

https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_max

https://dev.mysql.com/doc/refman/5.7/en/cast-functions.html#function_cast