本博客所有原创文章采用知识共享署名-非商业性使用-相同方式共享,转载请保留链接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 | BIGINT AUTO_INCREMENT NOT NULL PRIMARY KEY的缩写 |
于是再次更改表的设计:
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而是一个溢出的超大整数,注意安全。