MySQL数据类型：两个重要的类型属性-技术开发专区

MySQL数据类型：两个重要的类型属性

作者：姜承尧编辑：王玉圆 2012-08-09 00:05 IT168网站原创

【IT168 技术】数据类型在数据库中扮演着基础但又非常重要的角色。对数据类型的选择将影响与数据库交互的应用程序的性能。通常来说，如果一个页内可以存放尽可能多的行，那么数据库的性能就越好，因此选择一个正确的数据类型至关重要。另一方面，如果在数据库中创建表时选择了错误的数据类型，那么后期的维护成本可能非常大，用户需要花大量时间来进行ALTER TABLE操作。对于一张大表，可能需要等待更长的时间。因此，对于选择数据类型的应用程序设计人员，或是实现数据类型的DBA，又或者是使用这些数据类型的程序员，花一些时间深入学习数据类型、理解它们的基本原理是非常必要的。在选择数据类型时要格外谨慎，因为在生产环境下更改数据类型可能是一种非常危险的操作。

　　建议读者花一点时间学习一下数据类型及它们的基本原理，比如学习“计算机组成原理”这些大学课程，虽然当时学起来可能觉得很枯燥，但是结合当前的数据库类型来看，可能会有另一番感受。此外，读者可以阅读《MySQL技术内幕：InnoDB存储引擎》这本书，其中详细并深入地介绍了某些数据类型的底层实现，如VARCHAR、CHAR和BLOB等类型。本章主要介绍一些与数据库类型相关的SQL编程问题，主要关注日期类型、数字类型及字符类型。

　　类型属性

　　在介绍数据类型前，先来介绍两个属性：UNSIGNED和ZEROFILL，是否使用这两个属性对选择数据类型有着莫大的关系。

　　1. UNSIGNED

　　UNSIGNED属性就是将数字类型无符号化，与C、C++这些程序语言中的unsigned含义相同。例如，INT的类型范围是-2 147 483 648 ～ 2 147 483 647， INT UNSIGNED的范围类型就是0 ～ 4 294 967 295。

　　看起来这是一个不错的属性选项，特别是对于主键是自增长的类型，因为一般来说，用户都希望主键是非负数。然而在实际使用中，UNSIGNED可能会带来一些负面的影响，示例如下：

　　mysql> CREATE TABLE t ( a INT UNSIGNED, b INT UNSIGNED )

　　ENGINE=INNODB;

　　Query OK, 0 rows affected (0.06 sec)

　　mysql> INSERT INTO t SELECT 1,2;

　　Query OK, 1 row affected (0.00 sec)

　　Records: 1 Duplicates: 0 Warnings: 0

　　mysql> SELECT * FROM t\G;

　　*************************** 1. row ***************************

　　a: 1

　　b: 2

　　1 row in set (0.00 sec)

　　我们创建了一个表t，存储引擎为InnoDB。表t上有两个UNSIGNED的INT类型。输入(1，2)这一行数据，目前看来都没有问题，接着运行如下语句：

　　SELECT a - b FROM t

　　这时结果会是什么呢?会是-1吗?答案是不确定的，可以是-1，也可以是一个很大的正值，还可能会报错。在Mac操作系统中，MySQL数据库提示如下错误：

　　mysql> SELECT a-b FROM t;

　　ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`t`.`a` - `test`.`t`.`b`)'

　　这个错误乍看起来非常奇怪，提示BIGINT UNSIGNED超出了范围，但是我们采用的类型都是INT UNSIGNED啊!而在另一台Linux操作系统中，运行的结果却是：

　　mysql> SELECT a -b FROM t\G;

　　*************************** 1. row ***************************

　　a - b: 4294967295

　　1 row in set (0.00 sec)

　　在发生上述这个问题的时候，有开发人员跑来和笔者说，他发现了一个MySQL的Bug，MySQL怎么会这么“傻”呢?在听完他的叙述之后，我写了如下的代码并告诉他，这不是MySQL的Bug，C语言同样也会这么“傻”。

　　#include

　　int main(){

　　unsigned int a;

　　unsigned int b;

　　a = 1;

　　b = 2;

　　printf(a - b: %d\n,a-b);

　　printf(a - b: %u\n,a-b);

　　return 1;

　　}

　　上述代码的运行结果是：

　　a - b: -1

　　a - b: 4294967295

　　可以看到，在C语言中a-b也可以返回一个非常巨大的整型数，这个值是INT UNSIGNED的最大值。难道C语言也发生了Bug?这怎么可能呢?

　　在实际的使用过程中，MySQL给开发人员的印象就是存在很多Bug，只要结果出乎预料或者有开发人员不能理解的情况发生时，他们往往把这归咎于MySQL的 Bug。和其他数据库一样，MySQL的确存在一些Bug，其实并不是MySQL数据库的Bug比较多，去看一下Oracle RAC的Bug，那可能就更多了，它可是Oracle的一款旗舰产品。因此，不能简单地认为这个问题是MySQL的Bug。

　　对于上述这个问题，正如上述所分析的，如果理解整型数在数据库中的表示方法，那么这些就非常好理解了，这也是为什么之前强调需要看一些计算机组成原理方面相关书籍的原因。将上面的C程序做一些修改：

　　#include

　　int main(){

　　unsigned int a;

　　unsigned int b;

　　a = 1;

　　b = 2;

　　printf(a - b: %d,%x\n,a-b,a-b);

　　printf(a - b: %u,%x\n,a-b,a-b);

　　return 1;

　　}

　　这次不仅打印出a-b的结果，也打印出a-b的十六进制结果，运行程序后的结果如下所示：

　　a - b: -1,ffffffff

　　a - b: 4294967295,ffffffff

　　可以看到结果都是0xFFFFFFFF，只是0xFFFFFFFF可以代表两种值：对于无符号的整型值，其是整型数的最大值，即4 294 967 295;对于有符号的整型数来说，第一位代表符号位，如果是1，表示是负数，这时应该是取反加1得到负数值，即-1。

　　这个问题的核心是，在MySQL数据库中，对于UNSIGNED数的操作，其返回值都是UNSIGNED的。而正负数这个问题在《MySQL技术内幕：InnoDB存储引擎》中有更深入的分析，有兴趣的可以进一步研究。

　　那么，怎么获得-1这个值呢?这并不是一件难事，只要对SQL_MODE这个参数进行设置即可，例如：

　　mysql>SET sql_mode='NO_UNSIGNED_SUBTRACTION';

　　Query OK, 0 rows affected (0.00 sec)

　　mysql> SELECT a-b FROM t\G;

　　*************************** 1. row ***************************

　　a-b: -1

　　1 row in set (0.00 sec)

　　后面会对SQL_MODE进一步讨论，这里不进行深入的讨论。笔者个人的看法是尽量不要使用UNSIGNED，因为可能会带来一些意想不到的效果。另外，对于INT类型可能存放不了的数据，INT UNSIGNED同样可能存放不了，与其如此，还不如在数据库设计阶段将INT类型提升为BIGINT类型。

${PageNumber}

　　2. ZEROFILL

　　ZEROFILL属性非常有意思，更像是一个显示的属性。很多初

　　mysql> SHOW CREATE TABLE t\G;

　　*************************** 1. row ***************************

　　Table: t

　　Create Table: CREATE TABLE `t` (

　　`a` int(10) unsigned DEFAULT NULL,

　　`b` int(10) unsigned DEFAULT NULL

　　) ENGINE=InnoDB DEFAULT CHARSET=latin1

　　1 row in set (0.01 sec)

　　学者往往对MySQL数据库中数字类型后面的长度值很迷茫。下面通过SHOW CREATE TABLE命令来看一下t表的建表语句。

　　可以看到int(10)，这代表什么意思呢?整型不就是4字节的吗?这10又代表什么呢?其实如果没有ZEROFILL这个属性，括号内的数字是毫无意义的。a和b列就是前面插入的数据，例如：

　　mysql> SELECT * FROM t\G;

　　*************************** 1. row ***************************

　　a: 1

　　b: 2

　　1 row in set (0.00 sec)

　　但是在对列添加ZEROFILL属性后，显示的结果就有所不同了，例如对表t进行ALTER TABLE修改：

　　mysql> ALTER TABLE t CHANGE COLUMN a a int(4) UNSIGNED ZEROFILL;

　　Query OK, 0 rows affected (0.05 sec)

　　Records: 0 Duplicates: 0 Warnings: 0

　　这里对a列进行了修改，为其添加了ZEROFILL属性，并且将默认的int(10)修改为int(4)，这时再进行查找操作，返回的结果如下：

　　mysql> SELECT a,b FROM t\G;

　　*************************** 1. row ***************************

　　a: 0001

　　b: 2

　　1 row in set (0.00 sec)

　　这次可以看到a的值由原来的1变为0001，这就是ZEROFILL属性的作用，如果宽度小于设定的宽度(这里的宽度为4)，则自动填充0。要注意的是，这只是最后显示的结果，在MySQL中实际存储的还是1。为什么是这样呢?我们可以用函数HEX来证明。

　　mysql> SELECT a,HEX(a) FROM t\G;

　　*************************** 1. row ***************************

　　a: 0001

　　hex(a): 1

　　1 row in set (0.00 sec)

　　可以看到在数据库内部存储的还是1，0001只是设置了ZEROFILL属性后的一种格式化输出而已。进一步思考，如果数据库内部存储的是0001这样的字符串，又怎么进行整型的加、减、乘、除操作呢?

关注我们