Extrahieren Sie Daten aus einer anderen Tabelle in der MYSQL-Datenbank und nehmen Sie Änderungen an vorhandenen Spalten vor

Question

Ich habe eine MYSQL-Datenbank (in PHPMyAdmin) mit zwei Tabellen. users和posts。两个表都有一个username列。我想修改posts表中的username列，使其从users表中提取用户名数据，即posts表中的数据会自动从users表中更新，并在对usersDie Tabellen werden bei jeder Aktualisierung referenziert.

Ich dachte ursprünglich, ich könnte Fremdschlüssel verwenden, um diese Funktionalität zu erreichen, aber wenn ich das richtig verstehe, sind Fremdschlüssel nur mit dem Primärschlüssel in der übergeordneten Tabelle verknüpft, oder?

Ich erhalte die Fehlermeldung, dass die folgende Syntax falsch ist, obwohl sie keine Hinweise/Lösungen enthält:

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

Wie ändere ich eine vorhandene Spalte so, dass sie Daten aus einer Spalte in einer anderen Tabelle in der Datenbank referenziert/verwendet?

username列具有相同的类型、大小和属性，即VARCHAR(55) NOT NULL der beiden Tabellen und verwendet die innoDB-Speicher-Engine.

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

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