用PHP和MySQL构建一个数据库驱动的网站(八)

mysql|数据|数据库

摘要

　　在这一章中，我们会对我们的例子进行扩充，学习一些有关MySQL的新知识，并试图理解并掌握关系型数据库所能提供的功能。

(2002-08-29 14:11:39)

--------------------------------------------------------------------------------
By Wing, 出处：Linuxaid


第五章：关系型数据库设计

　　在这篇文章的第二章中，我们已经建立了一个供我们使用的非常简单的笑话数据库，这个库中只包括了一个名叫Jokes的数据表。这作为我们使用MySQL数据库的入门已经是足够了，但是在关系型数据库的设计中还有很多其它的东西。在这一章中，我们会对我们的例子进行扩充，学习一些有关MySQL的新知识，并试图理解并掌握关系型数据库所能提供的功能。

　　首先，我们得说明我们对许多问题的解决只是不正规的（也就是说非正式的）。正如你在许多计算机科学专业中了解的那样，数据库设计是一个严肃的领域，数据库设计必须包括对它的测试并会涉及到一些数学的原理。但这些可能是超过我们这篇文章的范围了。要得到更多的信息，你可以停下来到http://www.datamodel.org/去看看，在那儿你可以看到许多好的书籍，并得到一些关于这个问题的有用的资源。

给予应有的权限

　　在开始之前，让我们回忆一下我们的Jokes数据表的结构，这个表包含三个列：ID、JokeText和 JokeDate。这些列可以使我们标识笑话（ID），明了他们的内容（JokeText）以及他们被加入的时间（JokeDate）。

　　现在我们想要保存我们的笑话中的其它一些信息：提交者的姓名。这看上去很自然，我们需要在我们的Jokes数据表中添加一个新的列。SQL的ALTER命令（我们在之前没看到过这个命令）可以帮助我们完成这件事。使用mysql命令行程序登录到MySQL服务器，选择你的数据库（如果你使用我们在第二章中的命名，数据库名应该是joke），然后输入下面的命令：

mysql>
ALTER TABLE Jokes ADD COLUMN
    -> AuthorName VARCHAR(100);



　　这将会在我们的数据表中增加一个叫AuthorName的列。其数据类型是一个可变长度的字符串，其最大长度是100个字符（这对于最复杂的名字应该也是足够了）。让我们再添加一列用来保存作者的e-mail地址：


mysql> ALTER TABLE Jokes ADD COLUMN
    -> AuthorEMail VARCHAR(100);



　　要得到更多的有关ALTER命令的信息，请参看MySQL参考手册。要确认我们是不是正确地添加了两列，你可以要求MySQL为我们对这个表进行描述：


mysql> DESCRIBE Jokes;
+-------------+--------------+------+-----+-- - -
| Field | Type | Null | Key | Def...
+-------------+--------------+------+-----+-- - -
| ID | int(11) | | PRI | ...
| JokeText | text | YES | | ...
| JokeDate | date | | | ...
| AuthorName | varchar(100) | YES | | ...
| AuthorEMail | varchar(100) | YES | | ...
+-------------+--------------+------+-----+-- - -
5 rows in set (0.01 sec)



　　看上去很不错。明显地，我们需要对我们在第四章中建立的添加新笑话的HTML以及PHP格式的代码进行调整，但是我们会把这留给你作为一个练习。使用UPDATE查询，你现在可以对表中的所有笑话添加作者的详细资料。然而，在你开始接受这个数据结构之前，我们必须考虑一下我们在这儿选择的设计是否确当。在这种情况下，我们会发现一些我们还没有做到的事情。

一个基本的规则：保持事物的分离

　　在你建立数据库驱动的网站的过程中，你已经觉得仅仅是有一个笑话列表是不够的。事实上，除了你自己的笑话以外，你开始接收其他人提交的笑话。你决定做一个让全世界人都可以共享笑话的网站。你有没有听说过Internet电影数据库（IMDB）？实际上你现在做的是Internet笑话数据库（IJDB）！对每一个笑话添加作者的姓名和e-mail地址肯定是最容易想到的办法，但是这种方法会导致一些潜在的问题：

　　如果一个经常投稿的名叫Joan Smith的人改变了她的e-mail地址将会发生什么什么情况呢？她会开始使用新地址来提交新的笑话，但是对于所有的旧笑话，你所能看到的还是旧的地址。从你的数据库来看，你也许只能认为有两人名字都叫Joan Smith的人在向你的数据库中提交笑话。如果她是特别体贴的，她也许会通知你改变地址，你可以将所有的旧笑话改成新的地址，但是如果你遗漏了一个，那就意味着你的数据库中存储了错误的信息。数据库设计专家将这种类型的问题称之为一个“更正异常”。

　　很自然地你会想到从你的数据库中得到所有曾经向你的站点提交过笑话的人的列表。实际上，你可以使用下面的查询很容易地得到这样的列表：

mysql> SELECT DISTINCT AuthorName, AuthorEMail -> FROM Jokes;


　　上面查询中DISTINCT是告诉MySQL不输出重复的结果行。例如，如果Joan Smith向我们的站点提交过20个笑话，如果我们使用了DISTINCT选项，她的名字和e-mail地址将会只在列表中出现一次，否则会出现20次。

　　如果因为某种原因，你决定要从数据库中删除某个特定的作者所提交的所有笑话，但是，与此同时，你将不能再通过e-mail与他们联系！而你的e-mail清单可能是你的网站的收入的主要来源，所以你并不想只因为你不喜欢他们提交的笑话，就删除他们的e-mail地址。数据库设计专家将这称之为“删除异常”。

　　你并不能保证不会出现这样的情况：Joan Smith输入的姓名一会儿是“Joan Smith”，一会儿是“J. Smith”，一会儿又是“Smith, Joan”。这将使得你要确定一个特定的作者变得非常困难（特别是Joan Smith又经常使用几个不同的email地址的时候）。

　　这些问题的解决其实很简单。只要你不再将作者的信息存储到Jokes数据表中，而是建立一个新的数据表来存储作者列表。因为我们在Jokes数据表中使用了一个叫ID的列来用一个数据标识每个笑话，所以我们在新的数据表中使用了同样名字的列来标识我们的作者。我们可以在我们的Jokes表中使用“author ID's”来建立笑话和他的作者之间的关联。全部的数据库设计应该是这样的：

　　上面的两个表包含了三个笑话和两个作者。Jokes表的AID列（“Author ID”的缩写）提供了两个表之间的关联（指出Kevin Yank 提交了笑话1和笑话2，Joan Smith提交了笑话3）。在这里，你还需要注意到每一个作者只会在数据库中出现一次，而且他们是独立于他们提交的笑话而存在的，因此我们已经解决了我们上面提出的那些问题。

　　这个数据库设计的最重要的特征是，因为我们要存储两种类型的事物（笑话和作者），所以我们设计两个表。这是我们在数据库设计中要遵守的一个基本规则：对于每一个要存储其信息的实体（或事物），我们都应该给他一个自己的表。

　　重新生成上面的数据是非常简单的（只要使用两个CREATE TABLE 查询就行了），但是因为我们想要在做这些变动时不会有破坏性的效果（也就是说不会丢失我们已经存入的笑话），所以我们需要再次使用ALTER命令。 首先，我们删除Jokes表中有关作者的列：


mysql> ALTER TABLE Jokes DROP COLUMN AuthorName;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE Jokes DROP COLUMN AuthorEMail;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
    现在我们建立我们的新的数据表：
mysql> CREATE TABLE Authors (
    -> ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> Name VARCHAR(100),
    -> EMail VARCHAR(100)
    -> );
    最后，我们在我们的Jokes表中添加AID列：
mysql> ALTER TABLE Jokes ADD COLUMN AID INT;



　　现在剩下来的就是向新的表中添加一些作者，并通过填充AID列来对数据库中已经存在的笑话指定作者。

处理多个表

　　现在我们的数据被分布在两个表当中，要从其中获得数据看上去变得更加复杂了。例如，我们最初的目标是：显示一个笑话的列表并在每一个笑话后面显示作者的姓名和e-mail地址。在我们的单表结构中，要获得所有的信息，只需要在我们的PHP代码中使用一个SELECT语句就行了：


$jokelist = mysql_query(
  "SELECT JokeText, AuthorName, AuthorEMail ".
  "FROM Jokes");

while ($joke = mysql_fetch_array($jokelist)) {
  $joketext = $joke["JokeText"];
  $name = $joke["AuthorName"];
  $email = $joke["AuthorEMail"];

  // Display the joke with author information
  echo( "

$joketext
" .
        "(by ＜HREF='mailto:$email'＞$name)

" );
}



　　在我们的新系统中，这样做初看起来是不可能了。因为有关每个笑话的作者的详细资料不是存储在Jokes表中，我们可能想到的一个解决方案是我们对于我们想要显示的笑话单独地获得这些资料。代码将是这样的：


// Get the list of jokes
$jokelist = mysql_query(
  "SELECT JokeText, AID FROM Jokes");

while ($joke = mysql_fetch_array($jokelist)) {

  // Get the text and Author ID for the joke
  $joketext = $joke["JokeText"];
  $aid = $joke["AID"];
  // Get the author details for the joke
  $authordetails = mysql_query(
    "SELECT Name, Email FROM Authors WHERE ID=$aid");
  $author = mysql_fetch_array($authordetails);
  $name = $author["Name"];
  $email = $author["EMail"];

  // Display the joke with author information
  echo( "

$joketext
" .
        "(by ＜A HREF='mailto:$email'＞$name)

" );
}



　　很混乱，而且对于每一个显示的笑话都包含了一个对数据库的查询，这将会我们的页面的显示非常缓慢。现在看来，“老方法”可能是更好的解决方案，尽管它有其自身的弱点。

　　幸运的是，关系型数据库可以很容易地处理多个表中的数据！在SELECT语句中使用一个新的被称之为“join”的格式，我们可以找到两全其美的办法。连接可以使我们象对存储在单个表中的数据那样对待多个表中的关联数据。一个连接的格式应该是这样的：


mysql> SELECT FROM
    -> WHERE