Ekstrak data daripada jadual lain dalam pangkalan data MYSQL, membuat perubahan pada lajur sedia ada

Question

Saya mempunyai pangkalan data MYSQL (dalam PHPMyAdmin) dengan dua jadual users和posts。两个表都有一个username列。我想修改posts表中的username列，使其从users表中提取用户名数据，即posts表中的数据会自动从users表中更新，并在对usersJadual dirujuk apabila sebarang kemas kini dibuat.

Saya pada mulanya fikir saya boleh menggunakan kunci asing untuk mencapai fungsi ini, tetapi jika saya faham dengan betul, kunci asing hanya dikaitkan dengan kunci utama dalam jadual induk, bukan?

Saya mendapat ralat mengatakan sintaks berikut tidak betul, walaupun ia tidak memberikan sebarang petunjuk/penyelesaian:

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

Bagaimana untuk mengubah suai lajur sedia ada supaya ia merujuk/menggunakan data daripada lajur dalam jadual berbeza dalam pangkalan data?

username列具有相同的类型、大小和属性，即VARCHAR(55) NOT NULL daripada dua jadual, dan menggunakan enjin storan innoDB.

Answer 1

Kunci asing ialah semakan integriti data, tidak lebih. Ia memastikan bahawa medan dalam jadual anak mengandungi nilai yang muncul dalam medan rujukan dalam jadual induk. Itu sahaja ^*.

Anda tidak boleh menggunakan kekunci asing untuk menggabungkan data dari satu jadual ke jadual lain. Walau bagaimanapun, pernyataan JOIN melakukan apa yang anda perlukan, dan digunakan dengan kunci asing memastikan setiap siaran mempunyai nama pengguna yang sah untuk mengenal pasti data pengguna yang betul.

Ambil contoh ini:

Data Pengguna

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;

Catatan

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;

Kita boleh menggunakan pertanyaan berikut untuk membandingkan jadual data pengguna dengan jadual 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`));

Anda boleh membuat VIEW selanjutnya berdasarkan pertanyaan ini untuk mengembalikan data:

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

Gunakan pernyataan SELECT untuk menanyakan pandangan:

select * from posts_users order by postId

Demo: https://www.db-fiddle.com/f/tbBXvthBtwH7CKu1yjzPjQ/0

^* Kekunci asing juga membenarkan kemas kini dan pemadaman daripada jadual induk mengalir ke jadual anak, tetapi ini di luar skop artikel ini.