不同的MySQL版本对子查询中的左连接和顺序提供了不同的结果

Question

自从迁移到MySQL 5.6后，我在连接表的顺序上遇到了问题。这在MySQL 5.1中运行良好

我想为product_cat_pictures中的每个类别选择最后一张图片(最高id)。在每次执行相同的查询时，特定类别的图片顺序是不同的。

SELECT c.id, repo.filename FROM product_cat_container AS c
LEFT JOIN (SELECT * FROM product_cat_pictures ORDER BY id DESC) AS p
  ON c.id = p.cat_id
LEFT JOIN picture_repository AS repo
  ON p.picture_id = repo.id
GROUP BY c.id
ORDER BY c.order


product_cat_container
---------------------
id  name  order


product_cat_pictures 
---------------------
id  cat_id  picture_id


picture_repository 
---------------------
id  filename

Answer 1

您使用了非ANSI SQL：

SELECT c.id, repo.filename FROM product_cat_container AS c
LEFT JOIN (SELECT * FROM product_cat_pictures ORDER BY id DESC) AS p
  ON c.id = p.cat_id
LEFT JOIN picture_repository AS repo
  ON p.picture_id = repo.id
GROUP BY c.id
ORDER BY c.order;

GROUP BY只在c.id上，为repo.filename返回一个随机值(第一个可访问的值)。此行为是特定于MySQL版本的。

符合ANSI的查询没有这种随机行为：

SELECT c.id, MIN(repo.filename) AS filename FROM product_cat_container AS c
LEFT JOIN (SELECT * FROM product_cat_pictures ORDER BY id DESC) AS p
  ON c.id = p.cat_id
LEFT JOIN picture_repository AS repo
  ON p.picture_id = repo.id
GROUP BY c.id
ORDER BY c.order;

在这里，MIN函数负责返回正确的结果(最小值)。

替代方案：

SELECT c.id, repo.filename FROM product_cat_container AS c
LEFT JOIN (SELECT * FROM product_cat_pictures ORDER BY id DESC) AS p
  ON c.id = p.cat_id
LEFT JOIN picture_repository AS repo
  ON p.picture_id = repo.id
GROUP BY c.id,repo.filename
ORDER BY c.order;

在这里，GROUP BY包含所有列，可能导致每个id有超过1个结果，但它仍然是一个有效的ANSI (即: 100%定义的)结果。

您必须选择使用哪种函数/样式来获得您想要的结果。

Answer 2

不要忘记添加外键和索引，以避免表有数百万行时的性能问题。

尝试：

SQL Fiddle demo

mysql> DROP TABLES IF EXISTS `product_cat_container`;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLES IF EXISTS `product_cat_pictures`;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLES IF EXISTS `picture_repository`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `product_cat_container` (
    ->     `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ->     `name` VARCHAR(25),
    ->     `order` INT UNSIGNED
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE IF NOT EXISTS `product_cat_pictures` (
    ->     `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ->     `cat_id` INT UNSIGNED,
    ->     `picture_id` INT UNSIGNED
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `picture_repository` (
    ->     `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ->     `filename` VARCHAR(25)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO `product_cat_container`
    ->     (`name`, `order`)
    -> VALUES
    ->     ('AAA', 1), ('BBB', 2), ('CCC', 3), ('DDD', 4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `picture_repository`
    ->     (`filename`)
    -> VALUES
    ->     ('AAA'), ('BBB'), ('CCC'), ('DDD'), ('EEE');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `product_cat_pictures`
    ->     (`cat_id`, `picture_id`)
    -> VALUES
    ->     (1, 4), (1, 2), (1, 1), (2, 3), (3, 5), (3, 4);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT
    ->   `c`.`id`,
    ->   `repo`.`filename`
    -> FROM
    ->   `product_cat_container` `c`
    ->     LEFT JOIN (
    ->                 SELECT
    ->                   `p`.`cat_id`,
    ->                   `p`.`picture_id`
    ->                 FROM
    ->                   `product_cat_pictures` `p`
    ->                     INNER JOIN (
    ->                                 SELECT
    ->                                   MAX(`id`) `id`
    ->                                 FROM
    ->                                   `product_cat_pictures`
    ->                                 GROUP BY
    ->                                   `cat_id`
    ->                                 ) `d` ON `p`.`id` = `d`.`id`
    ->               ) `p` ON `c`.`id` = `p`.`cat_id`
    ->     LEFT JOIN `picture_repository` `repo` ON `p`.`picture_id` = `repo`.`id`
    -> ORDER BY
    ->   `c`.`order`;
+----+----------+
| id | filename |
+----+----------+
|  1 | AAA      |
|  2 | CCC      |
|  3 | DDD      |
|  4 | NULL     |
+----+----------+
4 rows in set (0.00 sec)