MySQL ORDER BY 奇怪的排序

Question

问题:mysql查询返回结果的顺序很奇怪，看起来像是随机的。但只有一台主机(本地主机)和另一台主机运行良好时才会发生这种情况。想知道为什么会发生这种情况以及如何预防它。

架构:

CREATE TABLE `product` (
  `product_id` int(11) NOT NULL AUTO_INCREMENT,
  `sort_order` int(11) NOT NULL DEFAULT '0',
  `status` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`product_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into product (sort_order, status)
values
(0, 1),
(0, 1),
(0, 1),
(0, 1),
(0, 1);

CREATE TABLE `product_description` (
  `product_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`product_id`),
  KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into product_description
values
(1, 'product_1'),
(2, 'product_2'),
(3, 'product_3'),
(4, 'product_4'),
(5, 'product_5');

CREATE TABLE `product_to_category` (
  `product_id` int(11) NOT NULL,
  `product_category_id` int(11) NOT NULL,
  PRIMARY KEY (`product_id`,`product_category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into product_to_category
values
(1, 1),
(2, 1),
(3, 1),
(4, 1),
(5, 1);

CREATE TABLE `product_category_path` (
  `product_category_id` int(11) NOT NULL,
  `path_id` int(11) NOT NULL,
  `level` int(11) NOT NULL,
  PRIMARY KEY (`product_category_id`,`path_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

insert into product_category_path values (1, 1, 0);

查询:

SELECT p.product_id, pc.product_category_id, pd.name
    FROM `product` p
        LEFT JOIN `product_description` pd ON pd.product_id = p.product_id
        LEFT JOIN `product_to_category` pc ON pc.product_id = p.product_id
    WHERE p.status = 1 AND pc.product_category_id IN (SELECT product_category_id FROM `product_category_path` WHERE path_id = 1)
    ORDER BY p.sort_order ASC;

在本地主机和托管结果始终相同:1,2,3,4,5。但在托管时，它显示 1,3,2,5,4 或 2,1,5,3,4 以及每次新的排序。为什么？

更新

http://dev.mysql.com/doc/refman/5.5/en/order-by-optimization.html

http://s.petrunia.net/blog/?p=24

Answer 1

SQL 系统(任何品牌和型号)都可以按照它们认为方便的任何顺序返回结果集行，除非您专门指定顺序。换句话说，结果集的顺序在形式上是不可预测的，除非在 ORDER BY 中指定。换句话说，在您的 localhost 服务器上，您的结果按照您认为应有的顺序完全是偶然的。表没有固有的顺序。

您真的很幸运，您的生产服务器如此快地在查询中暴露了这个缺陷。通常，开发人员直到他们的表增长到数万行时才发现这些东西。

如查询的此修改所示 ( http://sqlfiddle.com/#!2/211536/2/0 )，结果集中的所有行都具有相同的 SORT_ORDER 值。

查询:

SELECT p.sort_order, p.product_id, pc.product_category_id, pd.name
    FROM `product` p
        LEFT JOIN `product_description` pd ON pd.product_id = p.product_id
        LEFT JOIN `product_to_category` pc ON pc.product_id = p.product_id
    WHERE p.status = 1 AND pc.product_category_id IN (SELECT product_category_id FROM `product_category_path` WHERE path_id = 1)
    ORDER BY p.sort_order ASC

结果:

| SORT_ORDER | PRODUCT_ID | PRODUCT_CATEGORY_ID |      NAME |
|------------|------------|---------------------|-----------|
|          0 |          1 |                   1 | product_1 |
|          0 |          2 |                   1 | product_2 |
|          0 |          3 |                   1 | product_3 |
|          0 |          4 |                   1 | product_4 |
|          0 |          5 |                   1 | product_5 | 

您已经告诉 SQL 以这种方式对它们进行排序。两台服务器都这样做了。

如果您需要按 PRODUCT_ID 以及 SORT_ORDER 排序，只需指定它 ( http://sqlfiddle.com/#!2/211536/4/0 )。

    ORDER BY p.sort_order ASC, p.product_id ASC;