读《MySQL技术内幕——SQL编程》笔记（2）

只有学习最纯粹!

---

数据类型

数据类型在数据库中扮演着基础但又非常重要的角色。对数据类型的选择将影响与数据交互的应用程序性能。通常来说，如果一个页内可以存放更多的行，那么数据库的性能就越好。

类型属性：

UNSIGNED和ZEROFILL，是否使用这两个属性对于选择数据类型有着莫大的关系

• UNSIGNED
  UNSIGNED属性就是将数字类型无符号化，与C、C++这些程序语言中的unsigned含义相同。例如，INT的类型范围是-2147438648~2147483647，INT UNSIGNED的范围类型就是0~4294967295。看起来这是一个不错的选项，特别是对于主键是自增长的类型，因为一般来说，用户都希望主键是非负数。然而在实际使用中，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: 0mysql> SELECT * FROM t\G;*********************1. row ********************a: 1b: 21 row in set (0.00 sec)

我们创建了一个表t,存储引擎为InnoDB。表t上有两个UNSIGNED的INT类型。接着执行以下语句：

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 超出了范围，但是我们采用的是INTUNSIGNED？

在另一台Linux操作系统中，运行的结果是：

mysql> SELECT a-b FROM t\G;*********************** 1. row **************************a-b:42949672951 row in set (0.00 sec)

再看C语言的计算结果：

#include <stdio.h>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: -1a-b: 4294967295

可以看到，在C语言中a-b也可以返回一个非常巨大的整数，这个值是INT UNSIGNED的最大值。这是为什么呢？
如果将上述C语言的代码稍微做一下修改：

#include <stdio.h>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: -1,ffffffffa-b: 4294967295,ffffffff

可以看到运行结果的十六进制表示都是0xFFFFFFFF，但是这个十六进制的数值可以表示两种值：
对于无符号的整数值，其是整形数的最大值，即4294967295；
对于有符号的整形数，第一位代表符号，如果是1，表示负数，这时应该取反加1得到负数值，即-1。
所以这个问题的核心是：在MySQL数据库中，对于UNSIGNED数的操作，其返回值都是UNSIGNED的。

---

那么怎么获取其正确的计算结果呢？只要对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:-11 row in set (0.00 sec)

• ZEROFILL
  ZEROFILL属性更像是一个显示的属性。

mysql> SHOW CREATE TABLE t\G;********************* 1. row *********************    Table:tCreate Table: CREATE TABLE `t`(    `a` int(10) unsigned DEFAULT NULL,    `b` int(10) unsigned DEFAULT NULL,)ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.01 sec)

可以看到 int(10)，其实如果没有ZEROFILL这个属性，括号内的数字是毫无意义的。如果设置了ZEROFILL属性后，如果宽度小于设置的宽度，则自动填充0。要注意的是，这只是最后显示的结果，在MySQL中实际存储的还是1。

SQL_MODE设置
SQL_MODE的设置其实是比较冒险的一种设置，因为在这种设置下可以允许一些非法的操作，比如可以将NULL插入NOT NULL的字段中，也可以插入一些非法日期。因此在生产环境中强烈建议开发人员将这个值设置为严格模式。

SQL_MODE可以设置的选项
STRICT_TRANS_TABLES:
在该模式下，如果一个值不能插入到一个事务表（例如表的存储引擎为InnoDB）中，则中断当前的操作不影响非事务表（例如表的存储引擎为MyISAM）。
ALLOW_INVALID_DATES
:该选项并不完全对日期的合法性进行检查，只检查月份是否在1~12月之间，日期是否在1~31之间。该模式仅对DATE和DATETIME类型有效，而对TIMESTAMP无效，因为TIMESTAMP总是要求一个合法的输入。
ANSI_QUOTES :
启用ANSI_QUOTES，不能用双引号来应用字符串，因为它将别解释为识别符。
ERROR_FOR_DIVISION_BY_ZERO:
在INSERT或UPDATE过程中，如果数据被零除（或MOD（X,0）），则产生错误（否则为警告）。如果未给出该模式，那么数据被零除时MySQL返回NULL。如果用到INSERT IGNORE或UPDATE IGNORE中，MySQL生成被0除警告，但操作结果为NULL。
HIGH_NOT_PRECEDENCE NOT
IGNORE_SPACE
NO_AUTO_CREATE_USER
NO_AUTO_VALUE_ON_ZERO
NO_BACKSLASH_ESCAPES
NO_DIR_IN_CREATE
NO_ENGINE_SUBSTITUTION
NO_UNSIGNED_SUBTRACTION
NO_ZERO_DATE
NO_ZERO_IN_DATE
ONLY_FULL_GROUP_BY
PAD_CHAR_TO_FULL_LENGTH
PIPES_AS_CONCAT
REAL_AS_FLOAT
STRICT_ALL_TABLES

---

日期和时间类型
MySQL数据库中有五种与日期和时间有关的数据类型，各种日期数据类型所占空间如表2-1所示。

类型 所占空间 DATETIME 8字节 DATE 3字节 TIMESTAMP 4字节 YEAR 1字节 TIME 3字节

DATETIME和DATE
DATETIME占用8字节，是占用空间最多的一种日期类型。它既显示日期，同时也显示了时间。其可以表达的日期范围为1000-01-01 00:00:00到9999-12-31 23:59:59

DATE占用3字节，可显示的日期范围为1000-01-01到9999-12-31


TIMESTAMP

• TIMESTAMP和DATETIME显示的结果是一样的，都是固定的“YYYY-MM-DD HH：MM：SS”的形式。不同的是，TIMESTAMP占用4字节，显示的范围为`1970-01-01 00:00:00 UTC到2038-01-19 03:14:07 UTC。其实存储的内容为“1970-01-01 00:00:00”到当前的时间的毫秒数。（UTC是世界统一时间）
• 在建表的时候，列为TIMESTAMP的日期类型可以设置一个默认值，而DATETIME不行。
• 在更新表的时候，可以设置TIMESTAMP类型的列自动更新时间为当前时间。

YEAR和TIME
YEAR类型占用1个字节，在定义时可以指定显示的宽度为YEAR（4）或YEAR（2）。
对于YEAR（4），显示的范围为1901~2155；
对于YEAR（2），显示的范围为1970~2070，在YEAR（2）的设置下，00~69代表2000~2069年。

TIME类型占用3个字节，显示的范围为-838:59:59~838:59:59,因为TIME类型不仅可以用来保存一天中的时间，也可以用来保存时间间隔，所有TIME类型存储的时间可以大于23并且可以是负数。

---

数字类型
整型
整数类型占用空间和取值范围

类型 占用空间（字节） 最小值（Signed/Unsigned） 最大值（Signed/Unsigned） TINYINT 1 -128
0 127
255 SMALLINT 2 -32768
0 32767
65535 MEDIUMINT 3 -8388608
0 8388607
16777215 INT 4 -2147483648
0 2147483647
4294967295 BIGINT 8 -9223372036854775808
0 9223372036854775807
18446744073709551615

浮点型（非精确类型）
MySQL数据库支持两种浮点类型：单精度的FLOAT类型及双精度的DOUBLE PRECLSION类型。这两种类型都是非精确的类型，经过一些操作后并不能保证运算的正确性，例如M*G/G不一定等于M，虽然数据库内部的算法已经使其尽可能的正确，但是结果还是有偏差。财务软件在其数据库内使用FLOAT类型作为工资类型，个人觉得并不是一件值得推崇的事情。

FLOAT类型用于表示近似数值数据类型。SQL标准允许在关键字FLOAT后面的括号内用位来指定精度（但不是为指数范围）。MySQL还支持可选的只用于确定存储大小的精度规定。0到23的精度对应FLOAT列的4字节单精度，24到53的精度对应DOUBLE列的8字节双精度

为了保证最大的可移植性，需要使用近似数值数据值存储的代码，使用FLOAT或者DOUBLE PRECISION，并不规定精度或者位数。

高精度类型
DECIMAL和NUMERIC类型在MySQL中被视为相同的类型，用于保存必须为确切精度的值。

DECIMAL或NUMERIC的最大位数是65，但是具体的DECIMAL或NUMERIC列的实际范围受具体列的精度或者标度约束。如果分配给此类列的值的小数点后位数超过指定的标度允许的范围，值将按该标度进行转换。（具体操作与操作系统有关，一般结果均被截取到允许的位数）。