主页 / dba / 问题 / 61718
Accepted
Marco
Asked: 2014-03-26 10:35:45 +0800 CST

如何在表中使用键以使两列唯一

  • 772

我想创建一个处理照片的基本数据库。另外，我想向用户展示当天的照片。我创建了一个名为“照片”的表格和另一个表格“今天的照片”，就像这样

CREATE TABLE `photo` (
`photo_id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
`title` VARCHAR(100) NOT NULL,
`caption` VARCHAR(255) NOT NULL,
`filename` CHAR(29) NOT NULL,
PRIMARY KEY (`photo_id`)
)

CREATE TABLE `photooftheday` (
`photo_id` SMALLINT(5) UNSIGNED NOT NULL,
`date` DATE NOT NULL
)

我没有为表photooftheday添加自动增量列，因为我认为没有必要（也许我错了）。

我的问题是关于表格“今日照片”。我不知道如何处理密钥，所以每天都包含一张在另一天尚未选择的照片。在这种情况下，我对主键、唯一键、键、外键、列索引感到困惑……

此外，如果有帮助，我选择当天照片的查询将使用日期列，例如：

$todays_date = date('Y-m-d');

$q = "SELECT p.photo_id, p.title, p.caption, p.filename
FROM photooftheday pd
JOIN photo p ON pd.photo_id = p.photo_id
WHERE DATE(pd.date) = '$todays_date'";

我如何处理表 photooftheday 中的列，以便对其进行优化？

谢谢

mysql

2 个回答

  1. 如果我理解正确的话，一张给定的照片在当天的照片中最多有一条记录，因此您可以使用 photo_id 作为该表的主键。这将确保唯一性。

    编辑：根据第一条评论添加

    基本上你还需要一个索引：

    ALTER TABLE `photooftheday`
ADD UNIQUE INDEX `idx_date` (`date`);

    这将确保列中没有两个值date相同。就查询优化而言，通过添加唯一索引，您将涵盖它：您希望在连接中使用的列上建立索引（photo_id来自每个表，作为 PK，已经建立索引）和WHERE子句（具有只有date，我们刚刚编制了索引）。

    • 2
  2. Best Answer

    正如 mdoyle 已经提到的，如果您真的希望每张图片始终只显示一次，您可以在photo_id. 并从评论中提取您还想确保每个日期在您的表中只存在一次。

    我想提一下其他几个问题。

    您可能希望使用 InnoDB 作为存储引擎。这样您就可以添加一个外键（或外索引）并确保 的列仅包含 的列photo_id中的photooftheday有效值。数据库将在插入和更新时检查这是否为真。您还希望放置一个普通索引以加快查找速度，但由于您希望在此列上有一个唯一索引，因此您可以跳过普通索引。photo_idphotophotooftheday.date

    四种类型的索引（或键）是：

    • 唯一键——如果你想限制受该索引影响的值只允许一次。
    • 普通索引——用于更快的查找。不需要扫描整个数据，而是使用这个普通索引代替，这样会快很多。
    • 外键——用于确保插入的数据适合另一个（外部）表中的数据。
    • 主键——用于唯一标识表中的每一行。这可能是一个自动递增列，但也可以是任何其他列。在您的示例中，一行由dateor 或dateand唯一标识photo_id（这取决于您想要什么......）。您也可以添加此索引。如果你想戴上它date（从你的评论到现在这似乎是合理的），你可以跳过我添加的唯一键。主键确保所有值都是唯一的，因此额外的唯一键仅意味着额外的开销。

    我已将存储引擎定义、唯一索引和外键添加到您的创建表语句中：

    CREATE TABLE `photo` (
  `photo_id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(100) NOT NULL,
  `caption` VARCHAR(255) NOT NULL,
  `filename` CHAR(29) NOT NULL,
  PRIMARY KEY (`photo_id`)
) engine=innodb;

CREATE TABLE `photooftheday` (
  `photo_id` SMALLINT(5) UNSIGNED NOT NULL,
  `date` DATE NOT NULL,
  UNIQUE INDEX (`photo_id`),
  UNIQUE INDEX (`date`),
  FOREIGN KEY (`photo_id`) REFERENCES `photo` (`photo_id`)
) engine=innodb;

    您的 select 语句也可以改进。首先 MySQL 也提供了日期函数，因此不需要使用 php 函数将值插入到 select 语句中。您可以使用 mysql 函数NOW()，它返回当前日期时间戳。

    另一方面有点复杂。您有一个WHERE子句来查找今天的所有行，但是您在 上应用了一个函数date。这使得数据库无法在 上使用您新创建的索引date。幸运的是，您不需要调用该函数！您的date专栏已经是类型DATE，所以基本上包含yyyy-mm-dd。你只需要一些东西来比较这个值，这将是NOW(). NOW()返回类似的东西yyyy-mm-dd hh:ii:ss（也是时间），但你只需要日期部分。您可以使用DATE()mysql 函数提取它。那就把这两个函数放在一起吧。整个查询看起来像：

    SELECT p.photo_id, p.title, p.caption, p.filename
FROM photooftheday pd
JOIN photo p ON pd.photo_id = p.photo_id
WHERE pd.date = DATE(NOW())

    并且将比原始版本执行得更快。

    • 1

相关问题