MySQL - 有效地将两个选择语句与 LIMIT 组合成一个结果

MySQL - Combining two select statements into one result with LIMIT efficiently

提问by Wonka

For a dating application, I have a few tables that I need to query for a single output with a LIMIT 10 of both queries combined. It seems difficult to do at the moment, even though it's not an issue to query them separately, but the LIMIT 10 won't work as the numbers are not exact (ex. not LIMIT 5 and LIMIT 5, one query may return 0 rows, while the other 10, depending on the scenario).

对于约会应用程序，我有几个表需要查询单个输出，两个查询的 LIMIT 10 相结合。目前似乎很难做到，即使单独查询它们不是问题，但 LIMIT 10 将不起作用，因为数字不准确（例如不是 LIMIT 5 和 LIMIT 5，一个查询可能返回 0 行，而其他 10 个，视情况而定）。

members table
member_id | member_name
------------------------
     1         Herb
     2         Karen
     3         Megan

dating_requests
request_id | member1 | member2 | request_time
----------------------------------------------------
     1          1         2      2012-12-21 12:51:45

dating_alerts
alert_id | alerter_id | alertee_id | type | alert_time
-------------------------------------------------------
    5           3            2     platonic  2012-12-21 10:25:32

dating_alerts_status
status_id | alert_id | alertee_id | viewed | viewed_time
-----------------------------------------------------------
     4          5           2          0      0000-00-00 00:00:00 

Imagine you are Karen and just logged in, you should see these 2 items:

假设您是 Karen 并且刚刚登录，您应该看到以下 2 项：

1. Herb requested a date with you.
2. Megan wants a platonic relationship with you.

In one query with a LIMIT of 10. Instead here are two queries that need to be combined:

在一个 LIMIT 为 10 的查询中。相反，这里有两个需要组合的查询：

1. Herb requested a date with you.
   -> query = "SELECT dr.request_id, dr.member1, dr.member2, m.member_name 
               FROM dating_requests dr 
               JOIN members m ON dr.member1=m.member_id 
               WHERE dr.member2=:loggedin_id 
               ORDER BY dr.request_time LIMIT 5";
2. Megan wants a platonic relationship with you.
   -> query = "SELECT da.alert_id, da.alerter_id, da.alertee_id, da.type,
                      da.alert_time, m.member_name
               FROM dating_alerts da
               JOIN dating_alerts_status das ON da.alert_id=das.alert_id
                    AND da.alertee_id=das.alertee_id
               JOIN members m ON da.alerter_id=m.member_id
               WHERE da.alertee_id=:loggedin_id AND da.type='platonic'
                     AND das.viewed='0' AND das.viewed_time<da.alert_time 
               ORDER BY da.alert_time LIMIT 5";

Again, sometimes both tables may be empty, or 1 table may be empty, or both full (where LIMIT 10 kicks in) and ordered by time. Any ideas on how to get a query to perform this task efficiently? Thoughts, advice, chimes, optimizations are welcome.

同样，有时两个表都可能是空的，或者 1 个表可能是空的，或者都已满（LIMIT 10 开始）并按时间排序。关于如何获取查询以有效执行此任务的任何想法？欢迎提出想法、建议、提示和优化。

回答by eggyal

You can combine multiple queries with UNION, but only if the queries have the same number of columns. Ideally the columns are the same, not only in data type, but also in their semantic meaning; however, MySQL doesn't care about the semantics and will handle differing datatypes by casting up to something more generic - so if necessary you couldoverload the columns to have different meanings from each table, then determine what meaning is appropriate in your higher level code (although I don't recommend doing it this way).

您可以将多个查询与 组合在一起UNION，但前提是这些查询具有相同的列数。理想情况下，列是相同的，不仅在数据类型上，而且在语义上；但是，MySQL 并不关心语义，并且会通过转换为更通用的内容来处理不同的数据类型 - 因此，如果有必要，您可以重载列以使每个表具有不同的含义，然后确定在更高级别的代码中什么含义是合适的（虽然我不建议这样做）。

When the number of columns differs, or when you want to achieve a better/less overloaded alignment of data from two queries, you can insert dummy literal columns into your SELECTstatements. For example:

当列数不同时，或者当您希望从两个查询中实现更好/更少重载的数据对齐时，您可以将虚拟文字列插入到您的SELECT语句中。例如：

SELECT t.cola, t.colb, NULL, t.colc, NULL FROM t;

You could even have some columns reserved for the first table and others for the second table, such that they are NULLelsewhere (but remember that the column names come from the first query, so you may wish to ensure they're all named there):

您甚至可以为第一个表保留一些列，为第二个表保留其他列，以便它们在NULL其他地方（但请记住，列名来自第一个查询，因此您可能希望确保它们都在那里命名）：

  SELECT a, b, c, d, NULL AS e, NULL AS f, NULL AS g FROM t1
UNION ALL -- specify ALL because default is DISTINCT, which is wasted here
  SELECT NULL, NULL, NULL, NULL, a, b, c FROM t2;

You could try aligning your two queries in this fashion, then combining them with a UNIONoperator; by applying LIMITto the UNION, you're close to achieving your goal:

您可以尝试以这种方式对齐您的两个查询，然后将它们与UNION运算符结合起来；通过应用LIMIT的UNION，你接近实现你的目标：

  (SELECT ...)
UNION
  (SELECT ...)
LIMIT 10;

The only issue that remains is that, as presented above, 10 or more records from the first table will "push out" any records from the second. However, we can utilise an ORDER BYin the outer query to solve this.

唯一剩下的问题是，如上所述，第一个表中的 10 个或更多记录将“推出”第二个表中的任何记录。但是，我们可以ORDER BY在外部查询中使用 an来解决这个问题。

Putting it all together:

把它们放在一起：

(
  SELECT
    dr.request_time AS event_time, m.member_name,      -- shared columns
    dr.request_id, dr.member1, dr.member2,             -- request-only columns
    NULL AS alert_id, NULL AS alerter_id,              -- alert-only columns
      NULL AS alertee_id, NULL AS type
  FROM dating_requests dr JOIN members m ON dr.member1=m.member_id 
  WHERE dr.member2=:loggedin_id
  ORDER BY event_time LIMIT 10 -- save ourselves performing excessive UNION
) UNION ALL (
  SELECT
    da.alert_time AS event_time, m.member_name,        -- shared columns
    NULL, NULL, NULL,                                  -- request-only columns
    da.alert_id, da.alerter_id, da.alertee_id, da.type -- alert-only columns
  FROM
    dating_alerts da
    JOIN dating_alerts_status das USING (alert_id, alertee_id)
    JOIN members m ON da.alerter_id=m.member_id
  WHERE
    da.alertee_id=:loggedin_id
    AND da.type='platonic'
    AND das.viewed='0'
    AND das.viewed_time<da.alert_time
  ORDER BY event_time LIMIT 10 -- save ourselves performing excessive UNION
)
ORDER BY event_time
LIMIT 10;

Of course, now it's up to you to determine what type of row you're dealing with as you read each record in the resultset (suggest you test request_idand/or alert_idfor NULLvalues; alternatively one could add an additional column to the results that explicitly states from which table each record originated, but it should be equivalent provided those idcolumns are NOT NULL).

当然，现在由您决定在读取结果集中的每条记录时要处理的行类型（建议您测试request_id和/或alert_id获取NULL值；或者，可以在结果中添加一列明确说明每个记录来自哪个表，但如果这些id列是NOT NULL) ，它应该是等效的。