MySQL 偏移量异常

Question

我在使用带有 OFFSET 和 LIMIT 子句的 MySQL 查询时遇到问题。

它似乎根据所选的列数返回不同记录列表。

我做错了什么，还是这是一个已知的“功能”或者我在某个地方误解了？

如果我选择 1 列，偏移量将正常工作。如果我添加第二列，它似乎会在内部将偏移量重置为 0。

这是我的两个看似相似的查询。请注意 id 列中的结果:

连续运行的两个查询如下(请注意，如果您向查询添加 1 个额外的列或多个额外的列并不重要):

mysql> SELECT t0.id, t0.max_retries FROM dtc_queue_job t0 WHERE t0.status = 'running' LIMIT 12 OFFSET 100;
+-----+-------------+
| id  | max_retries |
+-----+-------------+
| 123 |        NULL |
| 124 |        NULL |
| 125 |        NULL |
| 126 |        NULL |
| 127 |        NULL |
| 128 |        NULL |
| 129 |        NULL |
| 130 |        NULL |
| 131 |        NULL |
| 132 |        NULL |
| 133 |        NULL |
| 134 |        NULL |
+-----+-------------+
12 rows in set (0.00 sec)

mysql> SELECT t0.id FROM dtc_queue_job t0 WHERE t0.status = 'running' LIMIT 12 OFFSET 100;
+-----+
| id  |
+-----+
| 108 |
| 109 |
| 110 |
| 111 |
| 112 |
| 113 |
| 114 |
| 115 |
| 116 |
| 117 |
| 118 |
| 119 |
+-----+
12 rows in set (0.00 sec)

背景:

1) 表有 112 条记录。

2)创建表:

  CREATE TABLE `dtc_queue_job` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `worker_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `class_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `method` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `status` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `args` longtext COLLATE utf8_unicode_ci NOT NULL,
  `priority` int(11) DEFAULT NULL,
  `crc_hash` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `when_us` decimal(18,0) DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL,
  `started_at` datetime DEFAULT NULL,
  `finished_at` datetime DEFAULT NULL,
  `elapsed` double DEFAULT NULL,
  `message` longtext COLLATE utf8_unicode_ci,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `max_duration` int(11) DEFAULT NULL,
  `run_id` bigint(20) DEFAULT NULL,
  `stalls` int(11) NOT NULL,
  `max_stalls` int(11) DEFAULT NULL,
  `exceptions` int(11) NOT NULL,
  `max_exceptions` int(11) DEFAULT NULL,
  `failures` int(11) NOT NULL,
  `max_failures` int(11) DEFAULT NULL,
  `retries` int(11) NOT NULL,
  `max_retries` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `job_crc_hash_idx` (`crc_hash`,`status`),
  KEY `job_priority_idx` (`priority`,`when_us`),
  KEY `job_when_idx` (`when_us`),
  KEY `job_status_idx` (`status`,`when_us`)
) ENGINE=InnoDB AUTO_INCREMENT=145 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

3)记录的ID:

mysql> SELECT t0.id as id_1 FROM dtc_queue_job t0;
+------+
| id_1 |
+------+
|  123 |
|  124 |
|  125 |
|  126 |
|  127 |
|  128 |
|  129 |
|  130 |
|  131 |
|  132 |
|  133 |
|  134 |
|   20 |
|   21 |
|   22 |
|   23 |
|   24 |
|   25 |
|   26 |
|   27 |
|   28 |
|   29 |
|   30 |
|   31 |
|   32 |
|   33 |
|   34 |
|   35 |
|   36 |
|   37 |
|   38 |
|   39 |
|   40 |
|   41 |
|   42 |
|   43 |
|   44 |
|   45 |
|   46 |
|   47 |
|   48 |
|   49 |
|   50 |
|   51 |
|   52 |
|   53 |
|   54 |
|   55 |
|   56 |
|   57 |
|   58 |
|   59 |
|   60 |
|   61 |
|   62 |
|   63 |
|   64 |
|   65 |
|   66 |
|   67 |
|   68 |
|   69 |
|   70 |
|   71 |
|   72 |
|   73 |
|   74 |
|   75 |
|   76 |
|   77 |
|   78 |
|   79 |
|   80 |
|   81 |
|   82 |
|   83 |
|   84 |
|   85 |
|   86 |
|   87 |
|   88 |
|   89 |
|   90 |
|   91 |
|   92 |
|   93 |
|   94 |
|   95 |
|   96 |
|   97 |
|   98 |
|   99 |
|  100 |
|  101 |
|  102 |
|  103 |
|  104 |
|  105 |
|  106 |
|  107 |
|  108 |
|  109 |
|  110 |
|  111 |
|  112 |
|  113 |
|  114 |
|  115 |
|  116 |
|  117 |
|  118 |
|  119 |
+------+
112 rows in set (0.00 sec)

4)其他信息:

从官方docker镜像加载MySQL:

root@baf5881b2eb2:/# mysql --version
mysql  Ver 14.14 Distrib 5.7.19, for Linux (x86_64) using  EditLine wrapper
root@baf5881b2eb2:/# mysqld --version
mysqld  Ver 5.7.19 for Linux on x86_64 (MySQL Community Server (GPL))

Answer 1

如果没有为结果集指定排序，则使用 LIMIT 或 OFFSET 没有任何意义。这通常是通过 ORDER BY 子句完成的。这里的基本思想是，您需要告诉 MySQL 根据某些列的顺序获取前 12 条记录。

请注意，MySQL 表没有内部顺序，因此如果您不指定顺序，则无法保证您返回的记录顺序与每个查询相同。可能是，但也可能不是。

如果多次运行以下查询，我希望您会得到相同的结果集，只要基础数据在查询之间不发生更改:

SELECT
    id,
    max_retries
FROM dtc_queue_job
WHERE
    status = 'running'
ORDER BY id
LIMIT 12 OFFSET 100;

这里我们获取关于 id 列的前 12 条记录，偏移量为 100。