主页 / dba / 问题 / 133514
Accepted
Darii Petru
Asked: 2016-03-28 23:37:17 +0800 CST

MySQL 5.5 和 5.6 之间的不同行为

  • 772

我有这个带有数据的模式：

CREATE TABLE `ticket` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  `type` INT(11) NULL,
  PRIMARY KEY (`id`));

CREATE TABLE `task` (
  `id` int(11) NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `ticket_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_task_1_idx` (`ticket_id`),
  CONSTRAINT `fk_task_1` FOREIGN KEY (`ticket_id`) REFERENCES `ticket` (`id`)
) ENGINE=InnoDB;

CREATE TABLE `planing` (
  `id` int(11) NOT NULL,
  `date` datetime DEFAULT NULL,
  `task_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_planing_1_idx` (`task_id`),
  CONSTRAINT `fk_planing_1` FOREIGN KEY (`task_id`) REFERENCES `task` (`id`)
) ENGINE=InnoDB ;

INSERT INTO `ticket` (`id`, `name`, `type`) VALUES (1, 'ticket_1', 1);
INSERT INTO `task` (`id`, `name`, `ticket_id`) VALUES (1, 'task_1', 1);
INSERT INTO `task` (`id`, `name`, `ticket_id`) VALUES (2, 'task_2', 1);
INSERT INTO `planing` (`id`, `date`, `task_id`) VALUES (1, '2016-06-01 05:00', 2);

INSERT INTO `ticket` (`id`, `name`, `type`) VALUES (2, 'ticket_2', 1);
INSERT INTO `task` (`id`, `name`, `ticket_id`) VALUES (3, 'task_3', 2);
INSERT INTO `task` (`id`, `name`, `ticket_id`) VALUES (4, 'task_4', 2);
INSERT INTO `planing` (`id`, `date`, `task_id`) VALUES (2, '2016-06-01 05:00', 3);
INSERT INTO `planing` (`id`, `date`, `task_id`) VALUES (3, '2016-07-01 05:00', 4);

INSERT INTO `ticket` (`id`, `name`, `type`) VALUES (3, 'ticket_3', 1);

当我执行选择时：

SELECT ticket.id AS ticket_id, task.id AS task_id, planing.date as due_date 
FROM ticket
  LEFT JOIN task ON task.ticket_id = ticket.id
  LEFT JOIN planing ON (task.id = planing.task_id AND planing.date IS NOT NULL)
WHERE ticket.type = 1
GROUP BY ticket.id
ORDER BY planing.date ASC
;

在 MySQL 5.5 上我得到

ticket_id | task_id | due_date
1         |1        |null   
3         |null     |null   
2         |3        |2016-06-01 05:00:00

在 MySQL 5.6 上我得到

ticket_id | task_id | due_date
3         |null     |null   
1         |2        |2016-06-01 05:00:00    
2         |3        |2016-06-01 05:00:00

示例链接

如何在 MySQL 5.5 上获得与 MySQL 5.6 相同的行为（应用服务器使用 MySQL 5.5 版本）？

mysql mysql-5.5

2 个回答

  1. Best Answer

    only_full_group_by您只需要在未启用 SQL 模式时停止依赖 GROUP BY 提供的非标准行为。

    通常，在分组时，您无法检索未在 GROUP BY 子句中指定的列，除非您将其包装在聚合函数中。那是因为该列可能有许多不同的值（请记住，我们正在讨论分组行），并且无法指定在输出中应该使用多个值中的哪个值。最近对该行为进行了扩展，根据可以选择哪些列在功能上依赖于 GROUP BY 列而不在 GROUP BY 中指定它们，但 MySQL 到目前为止只提供了两种模式：要么你根本无法在没有聚合的情况下检索非 GROUP BY 列，或者您可以检索任何此类列，而不管功能依赖性如何。

    因此，在这种情况下，您正在检索列task_id，并且due_date每个ticket_id. 最终选择哪个值是你无法控制的，这就是问题所在。解决方案是为自己准确定义分组时要从其他表中获取哪一行，ticket.id并以可以明确遵循的方式实现逻辑。

    例如，如果您想按照您在 MySQL 5.6 中观察到的行为，您可以将其定义为

    最早的非 NULLdue_date及其对应的task_idper ticket_id。

    基于此，您可以定义如下逻辑：

    SELECT
  ticket.id AS ticket_id,
  (
    SELECT
      MIN(p2.task_id)
    FROM
      task AS t2
      INNER JOIN planing AS p2 ON t2.id = p2.task_id
    WHERE
      p2.date = MIN(planing.date)
      AND t2.ticket_id = ticket.id
  ) AS task_id,
  MIN(planing.date) AS due_date
FROM
  ticket 
  LEFT JOIN task ON task.ticket_id = ticket.id
  LEFT JOIN planing ON task.id = planing.task_id
WHERE
  ticket.type = 1
GROUP BY
  ticket.id
ORDER BY
  MIN(planing.date) ASC
;

    这肯定比您的原始查询编码更多，但额外的代码并不是多余的，因为这样您就可以使您的结果可预测。

    可能模拟 MySQL 5.6 行为的唯一更简单的替代方法可能是像这样重写查询：

    SELECT
  ticket.id AS ticket_id,
  task_id AS task_id,
  planing.date as due_date
FROM
  ticket 
  LEFT JOIN
    task
    INNER JOIN planing ON task.id = planing.task_id
  ON task.ticket_id = ticket.id
WHERE
  ticket.type = 1
GROUP BY
  ticket.id
ORDER BY
  MIN(planing.date) ASC
;

    在我的测试中，对于 5.5，上述查询返回的结果与您的示例中 5.6 的原始结果相同。尽管这两个查询在逻辑上应该给出等价的结果，但它们并不完全等价：重写后的查询显式地改变了连接的逻辑顺序。也许这促使 MySQL 选择了一条不同的数据路径。

    但无论输出不同的原因是什么，请记住，不能保证行为会匹配每个场景。我会重复一遍，您需要停止依赖未记录的行为，或者实际上记录为未定义的行为：

    服务器可以从每个组中自由选择任何值，因此除非它们相同，否则选择的值是不确定的。

    • 4

  2. 简短版：你不能。至少不是那个查询。

    您依赖于 MySQL 的扩展，该扩展允许您 SELECT 未聚合列的列，也未列在 GROUP BY 子句中。

    具体来说，对于ticket_id=1，在对它们进行分组之前，您返回了 2 行。一个有task_id=1，另一个task_id=2。

    根据文档：

    服务器可以从每个组中自由选择任何值，因此除非它们相同，否则选择的值是不确定的。此外，从每个组中选择值不会受到添加 ORDER BY 子句的影响。

    这就是你所看到的。您从 5.5 升级到 5.6 的事实无关紧要，因为服务器可能会在任一版本上返回任一结果，并在文档中明确说明它将这样做。

    你可以试试这个查询：

    SELECT ticket.id AS ticket_id, task_planing.task_id AS task_id, task_planing.date as due_date 
FROM ticket
LEFT OUTER JOIN (
  SELECT
  task.ticket_id,
  task.id AS task_id,
  planing.date
  FROM task
  INNER JOIN planing
  ON task.id = planing.task_id
  LEFT OUTER JOIN planing later_planing
  ON later_planing.task_id = planing.task_id
  AND later_planing.date > planing.date
  WHERE later_planing.task_id IS NULL
) AS task_planing
ON task_planing.ticket_id = ticket.id
WHERE ticket.type = 1
GROUP BY ticket.id
ORDER BY task_planing.date ASC
;

    它假设表中的列planing实际上不是 NULL，因为它们不在您的示例数据中。如果某些数据实际上是空的，那么您将需要一些不同的东西，因为您选择允许空值会使这样的查询复杂化。

    • 3

相关问题