mysql - 如何从带有可选联结表的多对多关系中进行选择？

Question

我猜我有一个带有所谓“属性包”的用户设置设置。我想为我的用户存储设置。大多数用户不会更改默认设置，所以我认为我应该为每个设置设置一个“默认值”。这样我就没有为每个用户的每个设置存储 user_setting 记录。

这是我的(简化的)mysql 数据库:

CREATE TABLE `user` (
  `user_id` INT NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`user_id`)
);

CREATE TABLE `setting` (
  `setting_id` INT NOT NULL AUTO_INCREMENT,
  `key` VARCHAR(100) NOT NULL,
  `default_value` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`setting_id`)
);  

CREATE TABLE `user_setting` (
  `user_setting_id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `setting_id` INT NOT NULL,
  `value` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`user_setting_id`),
  CONSTRAINT `fk_user_setting_1`
    FOREIGN KEY (`user_id`)
    REFERENCES `user` (`user_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_user_setting_2`
    FOREIGN KEY (`setting_id`)
    REFERENCES `setting` (`setting_id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);

INSERT INTO `user` VALUES (1, 'username1'),(2, 'username2');
INSERT INTO `setting` VALUES (1, 'key1', 'somevalue'),(2, 'key2', 'someothervalue');

在我的代码中，我可以轻松地为每个用户查找每个设置。通过检查 user_setting 表中是否有一行，我知道这不是默认值。

但是有没有办法获得每个用户所有设置的概览？通常我会左加入用户 -> user_setting -> 为每个用户设置表，但现在我没有每个用户/设置的 user_setting 记录。这可以通过单个查询实现吗？

Answer 1

如果您只是对 setting 进行了 user 的笛卡尔连接，那么每个用户/设置组合都会得到一行。然后简单地加入 user_setting 表，当它存在时你可以选择被覆盖的值。

所以像这样:

SELECT u.user_id, s.key, s.default_value, us.value 
FROM user u, setting s
LEFT JOIN user_setting us 
  ON(us.user_id=u.user_id AND us.setting_id=s.setting_id)
ORDER BY u.user_id, s.key

您可以使用 IFNULL 进一步完善它这样您就可以获得设置的值，无论它是被覆盖还是默认:

SELECT u.user_id, s.key, IFNULL(us.value , s.default_value) AS value
FROM user u, setting s
LEFT JOIN user_setting us 
  ON(us.user_id=u.user_id AND us.setting_id=s.setting_id)
ORDER BY u.user_id, s.key