MySQL数据类型迷惑之整型xxxINT

秒速五厘米 2022-08-10 12:50 223阅读 0赞

> 本博客所有原创文章采用[知识共享署名-非商业性使用-相同方式共享][-_-]，转载请保留链接[http://chaoqun.17348.com/2008/11/mysql-data-types-int/][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类型的详细说明：

<table style="width: 600px;"> 
 <tbody> 
  <tr> 
   <td style="width: 160px;"> <pre>&nbsp;MySQL数据类型</pre> </td> 
   <td> <pre>&nbsp;含义</pre> </td> 
  </tr> 
  <tr> 
   <td> <pre>&nbsp;TINYINT(m)</pre> </td> 
   <td> <pre>&nbsp;8位整数（1字节，取值范围-128～+127）；可选参数m表示最大显示宽度，对取值范围无影响，如果使用了UNSIGNED，则取值范围为0~255</pre> </td> 
  </tr> 
  <tr> 
   <td> <pre>&nbsp;SMALLINT(m)</pre> </td> 
   <td> <pre>&nbsp;16位整数（2字节，取值范围-32 768～+32 767）</pre> </td> 
  </tr> 
  <tr> 
   <td> <pre>&nbsp;MEDIUMINT(m)</pre> </td> 
   <td> <pre>&nbsp;24位整数（3字节，取值范围-8 388 608～+8 388 607）</pre> </td> 
  </tr> 
  <tr> 
   <td> <pre>&nbsp;INT(m)、INTERGER(m)</pre> </td> 
   <td> <pre>&nbsp;32位整数（4字节，取值范围-2 147 483 648～+2 147 483 647）</pre> </td> 
  </tr> 
  <tr> 
   <td> <pre>&nbsp;BIGINT(m)</pre> </td> 
   <td> <pre>&nbsp;64位整数（8字节，取值范围±9.22*1018</pre> </td> 
  </tr> 
  <tr> 
   <td> <pre>&nbsp;SERIAL</pre> </td> 
   <td> <pre>&nbsp;BIGINT&nbsp;AUTO_INCREMENT&nbsp;NOT&nbsp;NULL&nbsp;PRIMARY&nbsp;KEY的缩写</pre> </td> 
  </tr> 
 </tbody> 
</table>

于是再次更改表的设计：

>     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而是一个溢出的超大整数，注意安全。

[-_-]: http://creativecommons.org/licenses/by-nc-sa/2.5/cn/
[http_chaoqun.17348.com_2008_11_mysql-data-types-int]: http://chaoqun.17348.com/2008/11/mysql-data-types-int/