【细说PHP学习】第十八章 MySQL数据表的设计

18.1 数据表(Table)

数据表是数据库中的基本对象，以记录（行）和字段（列）组成的二维结构用于存储数据数据表由表结构和表内容两个部分组成，先建立表结构，然后才能输入数据。数据表结构设计主要包括字段名、字段类型和字段属性的设置。在关系数据库中，为了确保数据的完整性和一致性，在创建表时除了必须指定字段名称、字段类型和字段属性外，还需要使用约束(constraint)、索引(index)、主键(primary key)和外键(foreign key)等功能属性。

18.2 数据值和列类型

18.2.1 数值类的数据列类型

MySQL中的数值分整型和浮点型两种，而整型中又分为5中整型列数据列表，即TINYINT，SMALLINT，MEDIUMINT，INT和BIGINT。而MySQL也有三种浮点型数据列类型，分别是FLOAT，DOUBLE和DECIMAL。对于浮点数MySQL支持科学计数法，而整型可以是十进制，也可是十六进制数。在整型数据列后加上UNSIGNED属性可以禁止负数，取值从0开始。

DECIMAL与FLOAT和DOUBLE的区别是：DECIMAL类型的值是以字符串的形式被存储起来的， 它的小数位数是固定的。所以它的优点就是不会像FLOAT和DOUBLE类型数据列那样进行四舍五入而产生误差，所以很适合用于财务计算；而它的缺点是：由于它的存储格式不同，CPU不能对它进行直接运算，从而影响运算效率。

18.2.2 字符串类数据列类型

字符串可以用来表示任何一种值，同PHP程序一样，MySQL能识别字符串中的转义序列，转义序列用反斜杠（\）表示。

CHAR类型和VARCHAR类型长度范围都是0~255之间的大小，它们之间的差别在于MySQL处理这个指示器的方式：CHAR把这个大小是为值得准确大小（用空格填补比较短的值），而VARCHAR类型把它视为最大值并且只使用了存储字符串实际上需要的字节数（增加了一个额外的字节记录长度）。因而，较短的值当被插入一个语句为VARCHAR类型的字段时，将不会用空格填补（然而较长的值仍然会被截短）。

BLOB和TEXT类型是可以存放任意大数据的数据类型，指示前者区分大小写，而后者不区分大小写。

ENUM和SET类型是特殊的串类型，其列值必须从固定的串集中选择，二者差别为前者必须是只能选择其中的一个值，而后者可以多选。

通常数据表包括定长表和变长表两种。如果表中的字符串字段包含任何varchar、text等类型，存储的空间会以字符串实际存储的长度为准，是变长字段的数据表，即变长表，反之则为定长表。对于变长表，由于记录大小不同，在其上进行许多删除和更改将会使表中的碎片更多，需要定期运行OPTIMIZE TABLE以保持性能。

使用定长列涉及某种折中
如果在char和varchar类型之间进行选择，需要对时间和空间做出折中。
变长表到定长表的转换，不能只转换一个可变长字段，必须对它们全部进行转换。而且必须使用一个ALTER TABLE语句同时全部转换，否则转换将不起作用。
有时不能使用定长类型，即使想这样也不行
在设计表结构时如果能够使用定长数据类型尽量用定长的，因为定长表的查询、检索、更新速度都很快。

18.2.3 日期和时间型数据列类型

每个时间和日期列类型都有一个零值，当插入非法数值时就用零值来添加。另外，也可以使用整型列类型存储UNIX时间戳，代替日期和时间列类型，这是基于PHP的Web项目中常见的方法。

18.2.4 NULL值

18.2.5 类型转换

和PHP类似，在MySQL的表达式中，如果某个数据值的类型与上下文所要求的类型不相符，MySQL则会根据将要进行的操作自动地对数据值进行类型转换。

18.3 数据字段属性

1. UNSIGNED

该属性只能用于设置数值类型，不允许数据列出现负数

2. ZEROFILL

该属性也只能用于设置数值类型，在数值之前自动用0补齐不足的位数。当一个字段使用ZEROFILL修饰时，该字段自动应用UNSIGNED属性。

3. AUTO_INCREMENT

该属性用于设置字段的自动增量属性，当数值类型的字段设置为自动增量时，每增加一条新记录，该字段的值就自动加1，而且此字段的值不允许重复。

4. NULL和NOT NULL

默认为NULL，即插入值时没有在此字段插入值，默认为NULL值，如果指定了NOT NULL，则必须在插入值时在此字段添入职。

5. DEFAULT

通过此属性来指定一个默认值，如果没有在此列添加值，那么默认添加此值。

18.4 数据表对象管理

18.4.1 创建表（CREATE TABLE）

数据库创建以后，使用USE命令选定这个新建的数据库作为默认数据库使用，就可以继续建立其包含的数据表。数据表的创建是使用表的前提，创建数据表主要是定义数据表的结构，包括数据表的名称、字段名、字段类型、约束及其索引等，其基本语法如下：

CREATE TABLE [IF NOT EXISTS] 表名称(   #创建带给定名称的表，您必须有表CREATE权限
    字段名1 列类型 [属性][索引],       #声明表中第一个字段，必须有字段名和列类型
    字段名2 列类型 [属性][索引],
    ……
    字段名n 列类型 [属性][索引]
)[表类型][表字符集];   #在创建表时也可指定可选的表类型和字符集的设置

其中[]中为可选的内容，一个表可以由一个或多个字段组成，在字段名后面一定要注明该字段的数据类型。

CREATE TABLE IF NOT EXISTS users (
id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
userpass VARCHAR(50) NOT NULL,
telno VARCHAR(20) NOT NULL,
sex ENUM(‘男’,’女’) NOT NULL DEFAULT’男’,
birthday DATE NOT NULL DEFAULT ‘0000-00-00’,
PRIMARY KEY(id),
INDEX users_username(username,userpass)
)TYPE=MyISAM DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

---

这里发生一个错误——

去掉最后一句发生另一个错误——

这里我试了好多次，最后得出的结论是MySQL对中文支持不太好……而且最后一句不能加……

---

数据表创建完成后可以在MySQL控制台中使用命令“SHOW TABLES”查看。还可以在控制台中使用“describe表名”或“desc 表名”命令用于显示表的创建结构。

18.4.2 修改表（ALTER TABLE）

ALTER TABLE 表名 ACTION;     #修改表的语法格式

其中ACTION是ALTER TABLE的从句，包括为指定的表添加一新列、为表添加一个索引、更改指定列默认值、更改列类型、删除一列、删除索引、更改表名等语句。

为指定的数据添加一新字段，可以在ACTION从句中使用ADD关键字实现，语法格式如下：

ALTER TABLE 表名 ADD 字段名 <建表语句>[FIRST|AFTER 列名]    #为指定的表添加新列

如果没有指定可选的FIRST或AFTER，则在列尾添加一列，否则在指定列添加新列。

为指定的数据表为了更改原有字段的类型，可使用CHANGE或MODIFY子句。如果原列的名字和新列的名字相同，则change和Modify的作用相同。

ALTER TABLE 表名 CHANGE(MODIFY) 列表 <建表语句>    #为指定的表修改列类型

在CHANGE命令中的列名telno出现了两次，原因是CHANGE除了更改类型外还能更改列名，而MODIFY不能实现这个功能。

如果需要为指定的数据表重新命名，可使用RENAME AS 子句，给出旧表名和新表名即可。

ALTER TABLE 旧表名 RENAME AS 新表名   #为指定的数据表重新命名

18.4.3 删除表（DROP TABLE）

当某个数据表不再需要的时候，可以使用SQL的DROP TABLE 语句删除。

DROP TABLE [IF EXISTS] 表名   #删除不再使用的数据表

18.5 数据表的类型及存储计算

18.5.1 MyISAM数据表

MyISAM数据表类型的特点是成熟、稳定和易于管理。其代价就是你需要经常运行OPTIMIZE TABLE命令来恢复被更新机制所浪费的空间。

18.5.2 InnoDB数据表

InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事务安全存储引擎。另外InnoDB数据表占用的空间比同样内容的MyISAM数据表大很多，另外，这种表类型也不支持全文索引等。

18.5.3 如何选择InnoDB还是MyISAM表类型

如果希望以最节约空间和时间或响应速度快的方式来管理数据表，MyISAM数据表就应该是首选。如果应用程序需要用到事务、使用外键或需要更高的安全性，以及需要允许很多用户同时修改某个数据表里的数据，则InnoDB数据表更值得考虑。

CREATE TABLE t(i INT) ENGINE=INNODB;
CREATE TABLE t(i INT) TYPE=MYISAM;

18.5.4 数据表存储位置

一个MyISAM数据表会有三个文件，它们分别是：以.frm为后缀名的结构定义文件，以.MYD为后缀名的数据文件，和以.MYI为后缀名的索引文件。而InnoDB由于采用表空间的概念来管理数据表，它只用一个与数据库表对应的并以.frm为后缀名的文件，同一个目录下的其他文件表示为表空间。

18.6 数据表的默认字符集

18.6.1 字符集

ASCII

ISO-8859字符集

Unicode字符集

18.6.2 字符集支持原理

18.6.3 创建数据对象时修改字符集

使用CREATE TABLE命令创建数据表时，如果没有明确指定任何字符集，则新创建数据表的字符集将由MySQL配置文件里character-set-server选项的设置决定。例如：在MySQL配置（Linux系统为/etc/my.conf文件，而Windows系统则是my.ini文件）里设置数据表的字符集如下：

character-set-server=gbk   #设置MySQL服务器的字符集
collation-server=gbk_chinese_ci   #设置排序方式

CREATE DATABSE IF NOT EXISTS mydb DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

18.7 创建索引

18.7.1 主键索引（PRIMARY KEY）

主键索引是关系数据库中最常见的索引类型，主要作用是确定数据表里一条特定的数据记录的位置。数据表会根据主键的唯一性来唯一标识每条记录，任意两条记录里的主键字段不允许是同样的内容，这样可以加快寻址定位时的速度。

在上例中，创建图书分类表cats时，声明一个整型字段cid，设置其属性为NOT NULL和AUTO_INCREMENT，并在字段后使用PRIMARY KEY设置该字段为主键索引。

18.7.2 唯一索引（UNIQUE）

唯一索引与主键索引一样，都可以防止创建重复的值。但是，不同之处在于，每个数据表中只能有一个主键索引，但可以有多个唯一索引。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引时，就应该使用关键字UNIQUE把它定义为一个唯一索引。

18.7.3 常规索引（INDEX）

常规索引技术是关系数据查询中最重要的技术，如果要提升数据库的性能，索引优化是首先应该考虑的，因为它能使我们的数据库得到最大性能方面的提升。
不要建立太多索引，索引是会消耗系统资源的，要适可而止。
创建常规索引可以使用关键词KEY或INDEX随表一同创建，KEY通常是INDEX的同义词。也可以在创建表之后使用CREATE INDEX或ALTER TABLE命令来创建。

CRATE INDEX ind ON carts(uid,bid);   #创建名称为ind的索引为carts表的两个列

创建索引之后，可以通过SHOW INDEX FROM carts命令为表cates生成一份索引的清单。如果不再需要索引，还可以使用DROP INDEX ind ON carts命令删除索引，其中ind是索引名称。

18.7.4 全文索引（FULLTEXT）

全文索引在MySQL中是一个FULLTEXT类型索引，但FULLTEXT索引只能用于MyISAM表，并且只可以在CHAR、VARCHAR或TEXT类型的列上创建，也允许创建在一个或多个数据列上。

虽然创建全文索引非常类似于创建其他类型的索引，但基于全文索引的获取查询却有所不同。当基于全文索引获取数据时，在SELECT语句中需要使用MATCH()和AGAINST()两个特殊的MySQL函数。

SELECT book_name,price FROM books WHERE MATCH(detail) AGAINST('hello');
SELECT MATCH(detail) AGAINST('hello')FROM books;

18.8 规范化

18.8.1 起点

18.8.2 第一范式

（1）内容相似的数据列必须消除（“消除”的方法是再创建一个数据表来存放它们）
（2）必须为每一组相关数据分别创建一个数据表
（3）每条数据记录必须用一个主键来识别

18.8.3 第二范式

只要数据列里的内容出现重复，就意味着应该把数据表拆分为多个子表
拆分形成的数据表必须用外键关联起来。

18.8.4第三范式

第三范式只有一条规则，就是与主键没有直接关系的数据列必须消除（“消除”的办法就是再创建一个数据表存放它们）。

18.8.5 规范化理论

范式缺点是：数据表的个数越多，把从网页上的表单输入的数据分门别类德存入这些数据表的复杂性就越大
范式的优点是：冗余意味着存储空间的浪费，严格按照范式设计出来的数据库能够提供最丰富、最灵活的查询选项。

对于数据库设计推荐的几个简单方法：

• 在设计数据库时一定要给自己充足的世界。
• 如果发现自己给数据列起的名字里有序号，需要警惕
• 在第一时间内想数据库输入一些测试用途的数据，而且尽可能包含一些特殊情况。
• 注意发现和运用各数据表之间的关联/引用关系
• 掌握SQL语言

18.9 数据库的设计技巧

18.9.2 起名字的技巧

• MySQL对数据库列的名字不区分字母的大小写形式，但对数据库和数据表的名字却区分。
• 数据库、数据表和数据列的名字最多可以是64个字符长
• 按照一定的规范系统给数据列起名有助于减少粗心产生的错误

18.9.3 数据库具体设计工作中的技巧

• 从一批数量相对较少的测试性数据入手去尝试把他们纳入一个或多个数据表
• 在第一次尝试时最好不要立刻去创建和使用真正的MySQL数据表，应该先在Excel或OpenOffice Cale等电子表格程序里用一些工作表把MySQL数据表勾勒出来。在这个阶段，应该把注意力放在要把哪些数据安排到哪些数据表和哪些数据列，还不到考虑数据列格式和索引等数据库设计细节的时候。

18.10 小结

本章需要拓展的内容
- 使用PHPMyAdmin管理数据表对象
- 为项目设计数据表的流程