MySQL UNION 中语句求值和变量赋值的顺序

Question

问题：在下面的 UNIONized 查询中，如何在评估依赖的派生查询之前强制分配 @col？要求：需要在一次查询中完成。

CREATE TABLE tbl (col CHAR(1) NOT NULL UNIQUE);

INSERT INTO tbl (col) VALUES ('a'), ('b'), ('c'), ('d'), ('e'), ...;

-- Now, for some value of "col", fetch that record and the
-- immediately preceding and following records as ordered by "col"
--
-- If you care to test this, be sure to SET @col := NULL before
-- subsequent executions to simulate a "fresh" MySQL session...
--
SELECT @col := col AS col    -- Fetch particular record given a value of
  FROM tbl                   -- "col".
 WHERE col = 'd'
UNION ALL
SELECT col                   -- Fetch the immediately preceding record,
  FROM (  SELECT col         -- ordered by "col"
            FROM tbl
           WHERE col < @col
        ORDER BY col DESC
          LIMIT 1) preceding
UNION ALL
SELECT col                   -- Fetch the immediately following record,
  FROM (  SELECT col         -- ordered by "col"
            FROM tbl
           WHERE col > @col
        ORDER BY col ASC
          LIMIT 1) following
ORDER BY col ASC;

背景：从上面的 UNIONized 查询中，我期望获得三个记录：一条与精确且唯一的“col”值匹配的记录，以及按“col”排序的紧邻的前一个和后一个记录。

但是，第一次运行查询仅生成一条记录，该记录与用户提供的“col”值相匹配。随后的运行给了我我期望的三个。我的推断是，直到派生查询 preceding 和 following 被评估之后，@col 才会被分配 - 这不是从左到右的-我期望的从上到下的评估顺序。

（我试图完善这个问题的答案，但遇到了这个困难。）

Answer 1

不要将 @col 的分配与其他查询联合起来。

使用一个查询为 @col 分配一个值，并使用一个单独的查询将该记录包含在结果中。

SELECT @col := col AS col    -- Fetch particular record given a value of
  FROM tbl                   -- "col", assigning the identifier to @col.
 WHERE col = 'd'



SELECT col                   -- Now include the above record in the
  FROM tbl                   -- Final result-set
WHERE col = @col

UNION ALL

SELECT col                   -- Fetch the immediately preceding record,
  FROM (  SELECT col         -- ordered by "col"
            FROM tbl
           WHERE col < @col
        ORDER BY col DESC
          LIMIT 1) preceding

UNION ALL

SELECT col                   -- Fetch the immediately following record,
  FROM (  SELECT col         -- ordered by "col"
            FROM tbl
           WHERE col > @col
        ORDER BY col ASC
          LIMIT 1) following
ORDER BY col ASC;

Answer 2

SELECT @col := col AS col    -- Fetch particular record given a value of
  FROM tbl                   -- "col".
 WHERE col = 'd'
UNION ALL
SELECT col                   -- Fetch the immediately preceding record,
  FROM

(@colx:=col), -- Assigne it here

       (  SELECT col         -- ordered by "col"
            FROM tbl
           WHERE col < @colx
        ORDER BY col DESC
          LIMIT 1) preceding
UNION ALL
SELECT col                   -- Fetch the immediately following record,
  FROM

(@colx:=col), -- Assign it here also

       (  SELECT col         -- ordered by "col"
            FROM tbl
           WHERE col > @colx
        ORDER BY col ASC
          LIMIT 1) following
ORDER BY col ASC;