powershell实战指南第3版pdf_MySQL 5权威指南[第3版]-第8章-3

前言

MySQL 5权威指南[第3版] 的作者 Michael Kofler 在奥地利格拉茨技术大学获得计算机科学博士学位。此书是学习 MySQL 5 的推荐书籍，本人读习此书后，将书中的一些知识点摘取下来，便于后续学习以用。

第 8 章 数据库设计概论

8.3 MySQL 数据类型

8.3.1 整数（xxxINT）

INT 数据类型默认是 正负整数。如果定义了 unsigned 数据，它的取值范围将仅限于正数。注意：对 unsigned 数据列做减法计算的返回值仍将是一个 unsigned 整数，而这可能导致虚假或让人困惑的结果。

tinyint 数据类型的取值范围是 -128~+127.如果使用了 unsigned 属性，取值范围是 0-255。

注解：对于 INT 数据类型，M 参数既不影响它的取值范围，也不影响数据的位数。如 INT(4) 这样的定义并不影响把大于 9999 的数值存入数据列。不过，在一些很少见的特定场合里（例如，当 mysql 在执行一些需要借助于临时数据表才能完成的复杂查询时）临时数据表里的数值有可能会被截短并导致最终结果不正确。

1. auto_increment 整数

如果给某个数据表中的一个整数数据列定义可选的 auto_increment 属性，那么当用户向这个数据表插入一条新记录时，mysql 就会自动地把这个整数数据列的当前最大取值加上 1 之后赋值给新纪录中的这个整数字段。auto_increment 属性的常见用法是定义数据表的主键字段。

使用 auto_increment 属性需要注意的问题：

• 这个属性必须与 not null、primary key 或者 unique 属性同时使用。
• 每个数据表最多只能有一个 auto_increment 数据列。
• MySQL 的这种 ID 值自动生成机制只在用户使用 insert 命令插入新纪录，并且没有为 ID 字段明确地给出一个值或 null 时才起作用。如果用户给出了一个具体的值并且这个值还没有在 ID 列里出现过，MySQL 就将使用这个 ID 值生成一条新数据记录。
• 如果想知道 MySQL 在刚插入数据记录里生成的 auto_increment 值是多少，在执行完 insert 命令后（但还是在本次连接或本个事务里），立刻执行 select last_insert_id() 命令。
• 如果 auto_increment 计算器到达了它的最大值（不同的整数数据类型有不同的最大值），将不再继续递增，数据记录的插入操作也将随之无法继续进行。对 insert 和 delete 操作非常频繁的数据表来说，哪怕数据记录远少于 20亿条，也有可能出现 32 位整数类型的 auto_increment 计算器被耗尽的情况。在定义这种数据表的时候，最好使用 bigint 数据列。

2. 二进制数据（BIT 和 BOOL）

MySQL 关键字 BOOL 是 tinyint 的同义词。从 5.0.3 版开始，BIT 变成了一种可以存储多达 64 位二进制数值的新数据类型。

3. 浮点数（FLOAT 和 DOUBLE）

在 显示/打印 时，FLOAT 和 DOUBLE 数值的数字个数可以用 m 和 d 两个可选参数来设置，其中 m 是十进制数字的总个数，d 是小数点后面的数字个数。表 8-2 对浮点数据类型进行了总结。

8.3.2 定点数（decimal）

MySQL 在把数据保存为 FLOAT 和 DOUBLE 数据类型时会自动进行必要的舍入。decimal 数据类型以字符串的形式来保存数据，并且不允许使用指数形式，所以将占用更多的空间，可以表示的数值范围也比较小。表 8-3 对 MySQL 支持的定点数据类型进行了总结。

参数 p 和 s 分别设定了数据值的数字总个数（表示范围，最大值是 65）和小数点后面的数字个数（精确度，最大值是 30）。比如说，decimal(6, 3) 的可表示范围是 -999.999~999.999。

MySQL 在内部把定点数保存为二进制格式：先把定点数分成小数点前面和小数点后面两个部分，并为它们各自分配 4 个字节，这 4 个字节最多可以表示 9 位数字。也就是说，decimal(6, 3) 和 decimal(18, 9) 都将实际占用 8 个字节。如果还有多出来的数字，就再分配 1 个字节来存储它们（每个字节容纳 2 位数字），但每个 4 个字节容纳 9 位数字。这么计算下来，decimal(19, 9) 将实际占用 9 个字节。

8.3.3 日期与时间（date、time、datetime、timestamp）

表 8-4 对 MySQL 用来存储时间值的数据类型进行了总结。

1. 日期/时间数据的合法性检查

从 5.0.2 版开始，MySQL 对日期和时间数据的合法性检查变得严格了——只有它认为合法的数据才能进入数据库，但仍允许使用 0 作为月份或日期值，如 '0000-00-00'。

对日期和时间数据进行的合法性检查由 MySQL 系统变量 sql_mode 控制。表 8-5 对这个变量的设置值与合法性检查严格程度的关系进行了总结。

2. timestamp 的特点

该类型的字段会在数据记录的其他字段你被修改时自动刷新。为了让 MySQL 自动刷新 timestamp 字段，不能在修改数据记录时给这个字段设置一个具体的值或 null，这样 MySQL 才不会把当前时间插入到这个字段里。

如果同一个数据表里有一个以上的 timestamp 数据列，MySQL 将自动刷新它们当中的第一个，但如果用户在修改数据记录时明确为第一个 timestamp 数据列给出了一个具体的值，则顺延到第二个；以此类推。

表 8-6 timestamp 两个数据

注意

• 不要选用 timpstamp 数据列来保存“真正的”日期/时间数据，那么做的最佳选择是 datetime 数据类型。
• 如果偶尔在修改某条记录的时候不想让 timestamp 数据列自动刷新，就必须向下面这样明确地给出一个日期/时间：
  update tablename set col='new value, ts=ts';
• 在 MySQL 4.0 及更早的版本里，timestamp 值的格式是 YYYYMMDDHHMMSS 而不是像现在这样的 YYYY-MM-DD HH:MM:SS。这种格式的 timestamp 数据在处理时可能会导致兼容问题。如果更喜欢这种老格式，就需要在有关命令里加上一个 0（就像这样：select ts+0 from table;）。

微秒。在未来 MySQL 版本里，timestamp 数据类型可能会增加一个微秒部分。

3. 日期/时间数据的处理和排版

MySQL 处理日期/时间值的格式函数 date_format()，示例：

select data_format(birthdate, '%Y %M %e') from students

1977 September 3

1981 October 25

8.3.4 字符串（char、varchar、xxxTEXT）

表 8-7 MySQL 字符串的数据类型总结。

char 类型的字符串其长度是有严格限制的。不管字符串的实际长度是多少，char(20) 字段在每条记录里都将占用 20 个字节。（字符串的前导空空个在存储前将被去掉。比较短的字符串在尾部加空格补足——MySQL 在读出数据时会自动删除这些空格。但这么做的后果是 MySQL 数据库不能保存尾部确实有空格字符的字符串。）

varchar 和 xxxTEXT 类型的字符串其长度是可变的，它们占用的存储空间由它们的实际长度决定。

区别：varchar 数据列的最大字符长度（0-65535 之间的某个整数）必须声明数据表时设置，超长的字符串将在存储前被截短；xxxTEXT 数据列根本不允许为它设置一个最大长度（唯一的限制是特定文本类型本身的最大长度）。

注意：在创建新数据表的时候，MySQL 经常会把数据列的定义改成一种对 MySQL 来说更有效率的形式。MySQL 文档把这种自动方式的修改成为默许的数据列修改（silent colun changes），它们对 char 和 varchar 数据列都有影响：

• 如果 n<4，varchar(n) 将被修改为 char(n)；
• 如果 n>3 并且在同一个数据表里还有其它的 varchar、text 或 blob 数据列，char(n) 将被修改为 varchar(n)。如果数据表里只有固定长度的数据列，char(n) 将不发生变化。

varchar 的新特性。

• 在 MyISAM 数据表里，varchar 数据列的最大长度现在是 65535 个字节（以前是 255 个字节），但最大字符个数还要取决于具体的字符集——许多字符集要用一个以上的字节来表示一个字符。
• varchar 值的前导空格和尾部空格现在可以存入数据表了，即 insert into tablename (varcharcolumn) values('abc') 命令现在将把 "abc"（注意，有一个前导空格和一个尾部空格）保存到数据列里。（过去，MySQL 会把 varchar 值的尾部空格去掉——这违反了 ANSI 标准中的有关规定。）

binary 属性。如果给 char 和 varchar 类型的数据列加上可选的 binary 属性，MySQL 就会把它们视同 BLOB 数据列来处理。

binary 属性优点：MySQL 在排序时将把字符视为二进制数（而不是字幕），而这样就可以把字母的大小写形式区分开。这在没有引入 binary 属性时是做不到的。与字符串相比，二进制字符串的内部管理工作简单，需要花费的时间也更短。

表 8-8 字符集和排序方式组合说明。

8.3.5 二进制数据（xxxBLOB 和 BIT）

使用 BLOB 数据列的好处是提高了数据库里的数据集成程度（提高了安全性、简化了备份工作、能够以统一的形式访问所有数据），缺点是数据库的响应速度往往会大大降低。把短小的数据（字符串、整数等）与长的数据（BLOB 和长文本）混合存放在同一个数据表里是一种非常不好的做法，因为这会导致所有数据记录的存取速度变慢。

BLOB 数据必须以一个整体来读写和传输。也就是说，如果某个 BLOB 数据的长度是 800 kb，想直接读取它的最后 100 kb 是不可能的，它读取的还是 800 kb。

1. BIT 数据类型

5.0.3 版本，select 查询在遇到 bit 数据列的时候将返回二进制数据，但客户端无法把这些值正确的显示出来。如有必要，先使用 select bitcolumn+0 命令把二进制值转换为整数，再使用 select bin(bitcolumn+0) 命令把这些整数显示为二进制值。

如果插入 bit 数据列的数值引起了下溢出或上溢出，所有的二进制位都将被设置为 1。

2. 其他数据类型

enum 数据类型定义的是一个字符串集合。取值只能是这个集合中的某一个成员（不允许是不同成员的一个组合）；相当于数学意义上的“排序”。

set 数据类型是集合成员你的任意组合（但数量不得超过 64 个）；相当于数学意义上的“组合”。set 数据类型的空间占用量比 enum 的大。最多可以有 64 个字符串参加组合（此时的空间占用量是 8 b）。

两者的缺点。首先，用 PHP 来管理它们相当复杂（例如，如果想知道 enum 字段的取值是哪一个字符串，就不得不遍历那个枚举集合）；其次，绝大多数其他的数据库系统根本不知道 enum 和 set 是什么，后面移植到其他数据库系统会非常复杂。因此，多创建一个关联数据表来取代 enum 和 set 数据列的做法往往更有实用价值。

8.3.6 选项和数据

MySQL 不允许使用函数来设置默认值。

来源：MySQL 5权威指南[第3版]

-- 完结 --