从MYSQL数据库的另一张表中提取数据，对现有列进行更改

Question

我有一个MYSQL数据库（在PHPMyAdmin中），其中有两个表users和posts。两个表都有一个username列。我想修改posts表中的username列，使其从users表中提取用户名数据，即posts表中的数据会自动从users表中更新，并在对users表进行任何更新时引用它。

我最初认为可以使用外键来实现这个功能，但如果我理解正确，外键只与父表中的主键相关联，是吗？

我收到一个错误消息，说以下语法不正确，尽管它没有给出任何提示/解决方案：

ALTER TABLE posts
MODIFY COLUMN username VARCHAR(55) NOT NULL
REFERENCES users(username) ON UPDATE CASCADE

如何修改现有列，使其引用/使用数据库中不同表中的列的数据？

两个表中的username列具有相同的类型、大小和属性，即VARCHAR(55) NOT NULL，并且使用的是innoDB存储引擎。

Answer 1

外键是一种数据完整性检查，仅此而已。它确保子表中的字段包含父表中引用字段中出现的值。就是这样^*。

你不能使用外键来将一张表的数据合并到另一张表中。然而，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

^* 外键还允许更新和删除父表时级联到子表，但这超出了本文的范围。