從MYSQL資料庫的另一張表中提取數據，對現有列進行更改

Question

我有一個MYSQL資料庫（在PHPMyAdmin中），其中有兩個表users和posts。兩個表都有一個username欄位。我想修改posts表中的username列，使其從users表中提取用戶名數據，即posts表中的數據會自動從users表中更新，並在對users表進行任何更新時引用它。我最初認為可以使用外鍵來實現這個功能，但如果我理解正確，外鍵只與父表中的主鍵相關聯，是嗎？我收到一個錯誤消

P粉501683874 · Answer

外鍵是一種資料完整性檢查，僅此而已。它確保子表中的欄位包含父表中引用欄位中出現的值。就是這樣^*。

你不能使用外鍵將一張表的資料合併到另一張表中。然而，JOIN語句剛好可以滿足你的需求，並且與外鍵一起使用可以確保每個貼文都有一個有效的使用者名稱來識別正確的使用者資料。

以這個例子為例：

用戶資料

CREATE TABLE `userdata` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(45) NOT NULL,
  `displayName` varchar(45) NOT NULL,
  `email` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  UNIQUE KEY `username_UNIQUE` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

帖子

CREATE TABLE `posts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(45) NOT NULL,
  `message` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  KEY `fk_posts_userdata_idx` (`username`),
  CONSTRAINT `fk_posts_userdata` FOREIGN KEY (`username`) REFERENCES `userdata` (`username`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

我們可以使用以下查詢將userdata表格與posts表格進行JOIN：

select 
    `posts`.`id` AS `postId`,
    `posts`.`username` AS `username`,
    `posts`.`message` AS `message`,
    `userdata`.`id` AS `userId`,
    `userdata`.`displayName` AS `displayName`,
    `userdata`.`email` AS `email` 
from (`posts` 
         join `userdata` 
         on(`userdata`.`username` = `posts`.`username`));

你也可以進一步建立一個基於這個查詢的VIEW來傳回資料：

CREATE VIEW `posts_users` AS 
  select 
    `posts`.`id` AS `postId`,
    `posts`.`username` AS `username`,
    `posts`.`message` AS `message`,
    `userdata`.`id` AS `userId`,
    `userdata`.`displayName` AS `displayName`,
    `userdata`.`email` AS `email` 
  from (`posts` 
         join `userdata` 
         on(`userdata`.`username` = `posts`.`username`));

使用SELECT語句查詢檢視：

select * from posts_users order by postId

示範：https://www.db-fiddle.com/f/tbBXvthBtwH7CKu1yjzPjQ/0

^* 外鍵也允許更新和刪除父表時級聯到子表，但這超出了本文的範圍。