Extraire les données d'une autre table de la base de données MYSQL, en apportant des modifications aux colonnes existantes

Question

J'ai une base de données MYSQL (en PHPMyAdmin) avec deux tables users和posts。两个表都有一个username列。我想修改posts表中的username列，使其从users表中提取用户名数据，即posts表中的数据会自动从users表中更新，并在对usersLes tables sont référencées lors d'une mise à jour.

Je pensais au départ pouvoir utiliser des clés étrangères pour réaliser cette fonctionnalité, mais si je comprends bien, les clés étrangères ne sont associées qu'à la clé primaire de la table parent, n'est-ce pas ?

Je reçois une erreur indiquant que la syntaxe suivante est incorrecte, même si elle ne donne aucun indice/solution :

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

Comment modifier une colonne existante pour qu'elle référence/utilise les données d'une colonne d'une autre table de la base de données ?

username列具有相同的类型、大小和属性，即VARCHAR(55) NOT NULL des deux tables, et utilise le moteur de stockage innoDB.

Answer 1

Les clés étrangères sont un contrôle de l'intégrité des données, rien de plus. Il garantit que les champs de la table enfant contiennent des valeurs qui apparaissent dans les champs référencés de la table parent. C'est tout ^*.

Vous ne pouvez pas utiliser de clés étrangères pour fusionner les données d'une table dans une autre table. Cependant, l'instruction JOIN fait exactement ce dont vous avez besoin et, utilisée avec des clés étrangères, garantit que chaque publication possède un nom d'utilisateur valide pour identifier les données utilisateur correctes.

Prenons cet exemple :

Données utilisateur

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;

Messages

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;

Nous pouvons utiliser la requête suivante pour comparer la table userdata avec la table posts 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`));

Vous pouvez en outre créer un VIEW basé sur cette requête pour renvoyer des données :

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`));

Utilisez l'instruction SELECT pour interroger la vue :

select * from posts_users order by postId

Démo : https://www.db-fiddle.com/f/tbBXvthBtwH7CKu1yjzPjQ/0

^* Les clés étrangères permettent également aux mises à jour et aux suppressions de la table parent de se répercuter sur la table enfant, mais cela dépasse le cadre de cet article.