MySQL:如何使用 3 个键查找最新行？ - 不像听起来那么简单

Question

我有一个包含 3 列的产品表，可用于确定产品版本:major_version、minor_version 和 release_date。

我需要一个尽可能快地返回具有最新产品版本的行的查询。

在我看来，最新版本可以这样确定:

a) 新的主要版本:

a.major_version = MAX(major_version) AND
a.major_version > any other major_version

b) 新的次要版本:

a.major_version = MAX(major_version) AND
a.major_version = b.major_version AND
a.minor_version > b.minor_version AND
a.release_date >= b.release_date

c) 现有版本的“静默更新”:

a.major_version = MAX(major_version) AND
a.major_version = b.major_version AND
a.minor_version = b.minor_version AND
a.release_date > b.release_date

示例数据:

CREATE TABLE mytest
(
  id                int(10)         NOT NULL,
  major_version     int(10)         NOT NULL,
  minor_version     int(10)         NOT NULL,
  release_date      datetime        NOT NULL,
  PRIMARY KEY(id)
);

truncate table mytest;
insert into mytest values(1,1,1,'2012-02-26');
insert into mytest values(2,1,2,'2012-02-26');
insert into mytest values(3,1,3,'2012-02-26');
insert into mytest values(4,2,1,'2012-02-26');
insert into mytest values(5,2,2,'2012-02-26');
insert into mytest values(6,2,2,'2012-02-27');

我的大脑陷入了僵局.. 单个查询可以完成吗？

Answer 1

如果您愿意通过添加额外的列来更改表格，我为您提供了一个简单的解决方案。

设置如下

• 添加一个名为 major_minor 的列，其中包含值 major_version * 100 + minor_version。
• 添加关于 major_minor 和发布日期的索引
• 现在只获取索引后面的最大值

首先让我们根据我建议的更改制作您的样本数据

use test
DROP TABLE IF EXISTS mytest;
CREATE TABLE mytest 
( 
    id                int(10)         NOT NULL, 
    major_version     int(10)         NOT NULL, 
    minor_version     int(10)         NOT NULL, 
    major_minor       int(10)         DEFAULT 0,
    release_date      datetime        NOT NULL, 
    PRIMARY KEY(id),
    KEY release_order_ndx (major_minor,release_date)
); 
insert into mytest (id,major_version,minor_version,release_date) values
(1,1,1,'2012-02-26'),(2,1,2,'2012-02-26'),(3,1,3,'2012-02-26'),
(4,2,1,'2012-02-26'),(5,2,2,'2012-02-26'),(6,2,2,'2012-02-27'); 
UPDATE mytest SET major_minor = major_version * 100 + minor_version;

这里加载

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS mytest;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE mytest
    -> (
    ->     id                int(10)         NOT NULL,
    ->     major_version     int(10)         NOT NULL,
    ->     minor_version     int(10)         NOT NULL,
    ->     major_minor       int(10)         DEFAULT 0,
    ->     release_date      datetime        NOT NULL,
    ->     PRIMARY KEY(id),
    ->     KEY release_order_ndx (major_minor,release_date)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> insert into mytest (id,major_version,minor_version,release_date) values
    -> (1,1,1,'2012-02-26'),(2,1,2,'2012-02-26'),(3,1,3,'2012-02-26'),
    -> (4,2,1,'2012-02-26'),(5,2,2,'2012-02-26'),(6,2,2,'2012-02-27');
Query OK, 6 rows affected (0.06 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> UPDATE mytest SET major_minor = major_version * 100 + minor_version;
Query OK, 6 rows affected (0.07 sec)
Rows matched: 6  Changed: 6  Warnings: 0

mysql>

现在只需查询表并按 major_minor desc，release_date desc 排序

mysql> select * from mytest order by major_minor desc, release_date desc;
+----+---------------+---------------+-------------+---------------------+
| id | major_version | minor_version | major_minor | release_date        |
+----+---------------+---------------+-------------+---------------------+
|  6 |             2 |             2 |         202 | 2012-02-27 00:00:00 |
|  5 |             2 |             2 |         202 | 2012-02-26 00:00:00 |
|  4 |             2 |             1 |         201 | 2012-02-26 00:00:00 |
|  3 |             1 |             3 |         103 | 2012-02-26 00:00:00 |
|  2 |             1 |             2 |         102 | 2012-02-26 00:00:00 |
|  1 |             1 |             1 |         101 | 2012-02-26 00:00:00 |
+----+---------------+---------------+-------------+---------------------+
6 rows in set (0.02 sec)

最后直接查表，按major_minor desc,release_date desc limit 1排序

mysql> select * from mytest order by major_minor desc, release_date desc limit 1;
+----+---------------+---------------+-------------+---------------------+
| id | major_version | minor_version | major_minor | release_date        |
+----+---------------+---------------+-------------+---------------------+
|  6 |             2 |             2 |         202 | 2012-02-27 00:00:00 |
+----+---------------+---------------+-------------+---------------------+
1 row in set (0.00 sec)

mysql>

试一试!!!

如果您不想添加 major_minor，那么让我们使用您的原始表

您需要在三列上添加索引

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS mytest;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE mytest
    -> (
    ->     id                int(10)         NOT NULL,
    ->     major_version     int(10)         NOT NULL,
    ->     minor_version     int(10)         NOT NULL,
    ->     release_date      datetime        NOT NULL,
    ->     PRIMARY KEY(id),
    ->     KEY release_order_ndx (major_version,minor_version,release_date)
    -> );
Query OK, 0 rows affected (0.13 sec)

mysql> insert into mytest (id,major_version,minor_version,release_date) values
    -> (1,1,1,'2012-02-26'),(2,1,2,'2012-02-26'),(3,1,3,'2012-02-26'),
    -> (4,2,1,'2012-02-26'),(5,2,2,'2012-02-26'),(6,2,2,'2012-02-27');
Query OK, 6 rows affected (0.10 sec)
Records: 6  Duplicates: 0  Warnings: 0

直接查表，按major_version desc, minor_version desc, release_date desc limit 1排序

mysql> select * from mytest order by major_version desc,minor_version desc, release_date desc limit 1;
+----+---------------+---------------+---------------------+
| id | major_version | minor_version | release_date        |
+----+---------------+---------------+---------------------+
|  6 |             2 |             2 | 2012-02-27 00:00:00 |
+----+---------------+---------------+---------------------+
1 row in set (0.00 sec)

mysql>