MySQL数据类型迷惑之整型xxxINT

最新推荐文章于 2024-09-23 07:00:00 发布

最新推荐文章于 2024-09-23 07:00:00 发布 · 120 阅读

文章标签：

#数据库

通过调整MySQL表字段的数据类型，如将int改为更小的mediumint和smallint，并使用unsigned属性，可以显著减少每行记录的大小，从而节省大量磁盘空间。

本博客所有原创文章采用知识共享署名-非商业性使用-相同方式共享，转载请保留链接http://chaoqun.17348.com/2008/11/mysql-data-types-int/

最近在做一些利用MySQL进行数据挖掘方面的尝试，处理的大多是海量的数据（一般是5000W条以上），由于数据量巨大，数据库表字段数据类型的选择就显示出重要性来了。

比如有下面的一个表：

mysql> desc test;
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| uid | int(11) | NO | | NULL | |
| cid | int(11) | NO | | NULL | |
| rating | int(11) | NO | | NULL | |
| day | date | NO | | NULL | |
+--------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

其中uid是用户ID，cid是内容ID，rating是打分值，取值是1～5，day是打分日期，就是这样一个表，往里面导入数据，大概1亿条，然后执行：

mysql> show table status like ‘test’;

数据库大概1.5G大小，注意里面的Avg_row_length字段，这个字段的意思平均每行占用的字节数，test表每行占用字节数（行大小）是16（3个int是12个字节，一个date是3个字节，然后再加1就是16个字节）。好大的数据，我们的优化开始了。

查看最大的一个uid是多少

mysql> select max(uid) from test;

发现最大的uid是2 649 429，只有7位数

查看最大的一个aid是多少

mysql> select max(cid) from test;

发现最大的cid是17 770，区区5位数

rating字段只有1～5这5个值，一位就搞定了

于是更改了test表设计

mysql> desc test;
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| uid | int(7)  | NO | | NULL | |
| cid | int(5)  | NO | | NULL | |
| rating | int(1)  | NO | | NULL | |
| day | date | NO | | NULL | |
+--------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

重新测试一下，大状况了，还是1.5G，每行还是16个字节，怎么会这样？查了一下手册得知：int(m) m表示最大显示宽度，注意是显示宽度，不会影响它的取值范围，你大可以在int(1)的字段中插入9999的数字，m不会影响此列的取值范围，也就是说int(1)和int(11)占用的字节数是一样多的，你是不是和我一样想当然了？下面是手册上关于xxxINT类型的详细说明：

MySQL数据类型	含义
TINYINT(m)	8位整数（1字节，取值范围-128～+127）；可选参数m表示最大显示宽度，对取值范围无影响，如果使用了UNSIGNED，则取值范围为0~255
SMALLINT(m)	16位整数（2字节，取值范围-32 768～+32 767）
MEDIUMINT(m)	24位整数（3字节，取值范围-8 388 608～+8 388 607）
INT(m)、INTERGER(m)	32位整数（4字节，取值范围-2 147 483 648～+2 147 483 647）
BIGINT(m)	64位整数（8字节，取值范围±9.22*1018
SERIAL	BIGINTAUTO_INCREMENTNOTNULLPRIMARYKEY的缩写

于是再次更改表的设计：

mysql> desc test;
+--------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+-------+
| uid | mediumint(8) unsigned | NO | | NULL | |
| cid | smallint(5) unsigned | NO | | NULL | |
| rating | tinyint(1) | NO | | NULL | |
| day | date | NO | | NULL | |
+--------+-----------------------+------+-----+---------+-------+

再执行

mysql> show table status like ‘test’;

test表大小变成不到960MB了，行大小变成10字节（3＋2＋1＋3＋1），苗条了许多。

另外，手册上说两个UNSIGNED的字段相减，值还是UNSIGNED，这就意味着如果用3-5的话，最后得到的结果肯定不是-2而是一个溢出的超大整数，注意安全。