binary(binary区分大小写),unsigned,unsigned zerofill关键字介绍

转载

mob604756fe7577 2016-01-04 18:58:00

文章标签 mysql mysql关键字 mysql基础整型数据 文章分类 代码人生

mysql建表时，每个字段的属性有三个选项： binary,unsigned,unsigned zerofill，作用如下：

一、binary

CHAR VARCHAR：值根据缺省字符集以大小写不区分的方式排序和比较，除非给出BINARY关键字。使用BINARY的例子见

mysql查询不区分大小写问题分析和解决

mysql 字符串类型及二进制字符串类型大小写比较

二、unsigned

既为非负数，用此类型可以增加数据长度!

例如如果 tinyint最大是127，那 tinyint unsigned 最大就可以到 127 * 2

unsigned 属性只针对整型，而binary属性只用于char 和varchar。

类型说明	说明	取值范围	存储需求
tinyint[(m)]	非常小的整数	有符号值：-128 到127（- 27 到27 - 1）无符号值：0到255（0 到28 - 1） 1个字节	1字节
smallint[(m)]	较小整数	有符号值：-32768 到32767（- 215 到215 - 1）无符号值：0到65535（0 到21 6 - 1） 2个字节	2字节
mediumint[(m)]	中等大小整数	有符号值：-8388608 到8388607（- 22 3 到22 3 - 1 ）无符号值：0到16777215（0 到22 4 - 1） 3个字节	3字节
int[(m)]	标准整数	有符号值：-2147683648 到2147683647（- 231 到231- 1）无符号值：0到4294967295（0 到232 - 1） 4个字节	4字节
bigint[(m)]	较大整数	有符号值：-9223372036854775808 到9223373036854775807（- 263到263-1）无符号值：0到18446744073709551615（0到264 – 1） 8个字节	8字节
float[(m, d)]	单精度浮点数	最小非零值：±1.175494351e - 38	4字节
double[(m,d)]	双精度浮点数	最小非零值：±2.2250738585072014e - 308	8字节
decimal (m, d)	一个串的浮点数	可变；其值的范围依赖于m 和d	m字节（mysql < 3.23），m+2字节（mysql > 3.23 ）

mysql提供了五种整型： tinyint、smallint、mediumint、int和bigint。int为integer的缩写。这些类型在可表示的取值范围上是不同的。 整数列可定义为unsigned从而禁用负值；这使列的取值范围为0以上。各种类型的存储量需求也是不同的。取值范围较大的类型所需的存储量较大。

mysql 提供三种浮点类型： float、double和decimal。与整型不同，浮点类型不能是unsigned的，其取值范围也与整型不同，这种不同不仅在于这些类型有最大值，而且还有最小非零值。最小值提供了相应类型精度的一种度量，这对于记录科学数据来说是非常重要的（当然，也有负的最大和最小值）。

unsigned注意事项：

1. UNSIGNED

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

在MYSQL中整型范围：

类型大小范围（有符号）范围（无符号）用途

TINYINT 1 字节 (-128，127) (0，255) 小整数值

SMALLINT 2 字节 (-32 768，32 767) (0，65 535) 大整数值

MEDIUMINT 3 字节 (-8 388 608，8 388 607) (0，16 777 215) 大整数值

INT或INTEGER 4 字节 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值

源文档 <javascript:void(0)>

看起来这是一个不错的属性选项，特别是对于主键是自增长的类型，因为一般来说，用户都希望主键是非负数。然而在实际使用中，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操作系统中(windows中也会)，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类型。

以上文字摘自<http://tech.it168.com/a2012/0808/1382/000001382732.shtml>

本人遇到的类似问题：(linux上)

当(a-b)在where子句后时也会出现相同的情况

以下是php使用Mysql查询的结果(每组的第一行是第二行[1]-[2]的结果)

86374

a b

Array ( [1] => 1351843032 [2] => 1351756658 )

-2567

Array ( [1] => 1351843032 [2] => 1351845599 )

86374

Array ([1] => 1351843032 [2] => 1351756658 )

86374

Array ( [1] => 1351843032 [2] => 1351756658 )

-105849

Array ( [1] => 1351650809 [2] => 1351756658 )

86374

Array ( [1] => 1351843032 [2] => 1351756658 )

86374

Array ( [1] => 1351843032 [2] => 1351756658 )

下面在mysql语句中查询select * from table where (a-b)>86374;

结果(按正常思路来讲，结果应该为空，但在Linux是却现出以下结果 )：

Array ( [1] => 1351843032 [2] => 1351845599 )

Array ( [1] => 1351650809 [2] => 1351756658 )

而这个结果恰是[1]-[2]为负数的那两行。

结论：如果使用unsigne并且在where子句后出现两列相减值小于0((a-b)<0)，在查询时，linux上的Mysql会将负数转换成unsigned后再进行查询( (-2576+4294967295+1)>86374, (-105849+4294967295+1)>86374 )。

三、zerofill

那这个int[M]中M是什么意义喃，在定义数值型数据类型的时候，可以在关键字括号内指定整数值（如：int(M)，M的最大值为255）显示最大显示宽度，显示宽度M与数据所占用空间，数值的范围无关。如果在定义字段的时候指定zerofill，那么当数值的显示宽度小于指定的列宽度时候，则默认补充的空格用0代替。

mysql> use test
Database changed
mysql> create table int_12(id int(12) zerofill);
Query OK, 0 rows affected (0.03 sec)

mysql> desc int_12;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type                      | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| id    | int(12) unsigned zerofill | YES  |     | NULL    |       |
+-------+---------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into int_12(id) values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from int_12;
+--------------+
| id           |
+--------------+
| 000000000001 |
+--------------+
1 row in set (0.00 sec)

mysql> alter table int_12 change id id int(23) zerofill;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from int_12;
+-------------------------+
| id                      |
+-------------------------+
| 00000000000000000000001 |
+-------------------------+
1 row in set (0.00 sec)

mysql>