一、MySQL的select語(yǔ)句輸出多行常量的方法

使用union all運(yùn)算符將多個(gè) SELECT 查詢的結(jié)果集組合起來(lái)作為一個(gè)更大的結(jié)果集輸出。

例子：

SELECT 'ABC' AS col1, '123' AS col2UNION ALLSELECT 'DEF' AS col1, '456' AS col2;

在以上代碼中，我們定義了兩個(gè) SELECT 語(yǔ)句，每個(gè) SELECT 語(yǔ)句都選擇了兩個(gè)常量值，并使用 UNION ALL 運(yùn)算符將兩個(gè)結(jié)果集組合起來(lái)。輸出結(jié)果如下：

+------+------+ | col1 | col2 | +------+------+ | ABC  | 123  | | DEF  | 456  | +------+------+

在這個(gè)例子中，我們定義了兩個(gè) SELECT 語(yǔ)句，每個(gè) SELECT 語(yǔ)句都返回兩個(gè)常量值，并使用 UNION ALL 運(yùn)算符將這些結(jié)果集組合在一起，形成一個(gè)包含兩個(gè)行的結(jié)果集。需要注意的是，每個(gè) SELECT 語(yǔ)句返回的列數(shù)和數(shù)據(jù)類型必須相同，否則將會(huì)出現(xiàn)錯(cuò)誤。

二、union all 運(yùn)算符

union all 運(yùn)算符使用同名的物理運(yùn)算符來(lái)實(shí)現(xiàn)。union all 是一種相當(dāng)簡(jiǎn)單的操作，僅在查詢要移動(dòng)大量數(shù)據(jù)時(shí)才應(yīng)并行使用它。

1、并行 union all

要生成并行的 union all，少數(shù)的條件是它的操作數(shù)必須具有相同的度，而無(wú)論這些操作數(shù)具有何種分區(qū)類型。

以下示例（使用表HA2）顯示了要并行處理的 union all 運(yùn)算符。exchange 運(yùn)算符的位置高于 union all 運(yùn)算符，這表示它要由多個(gè)線程共同處理：

create table HA2(a1 int, a2 int, a3 int)partition by hash(a1, a2) (p1, p2)select * from RA2union allselect * from HA2QUERY PLAN FOR STATEMENT 1 (at line 1).Executed in parallel by coordinating process and 2 worker processes.The type of query is SELECT.ROOT:EMIT Operator????|EXCHANGE Operator (Merged)????|Executed in parallel by 2 Producer and 1 Consumer ?????processes.?????|????|   |EXCHANGE:EMIT Operator????|   |????|   |   |UNION ALL Operator has 2 children.????|   |   |????|   |   |   |SCAN Operator????|   |   |   |  FROM TABLE????|   |   |   |  RA2????|   |   |   |  Table Scan.. . . . . . . . . . . . . . . . . . .????|   |   |   |  Executed in parallel with a 2-way ???????????????????partition scan.. . . . . . . . . . . . . . . . . . .????|   |   |????|   |   |   |SCAN Operator????|   |   |   |  FROM TABLE????|   |   |   |  HA2????|   |   |   |  Table Scan.????. . . . . . . . . . . . . . . . . . .????|   |   |   |  Executed in parallel with a 2-way ???????????????????partition scan.

2、串行 union all

在下一示例中，來(lái)自 union 運(yùn)算符每一側(cè)的數(shù)據(jù)分別由每一側(cè)的選擇性謂詞加以限制。

要通過(guò) union all 運(yùn)算符發(fā)送的數(shù)據(jù)量將很少，足以決定不并行運(yùn)行聯(lián)合。相反，通過(guò)在 union 的每一側(cè)都放置 2 對(duì) 1 的 exchange 運(yùn)算符，將對(duì)表 RA2 和 HA2 的每次掃描進(jìn)行組織。結(jié)果操作數(shù)隨后由 union all 操作符并行處理：

select * from RA2where a2 > 2400union allselect * from HA2where a3 in (10,20)

Executed in parallel by coordinating process and 4 worker processes.7 operator(s) under rootThe type of query is SELECT.ROOT:EMIT Operator |UNION ALL Operator has 2 children. | |   |EXCHANGE Operator (Merged) |   |Executed in parallel by 2 Producer and 1??????????????Consumer processes. |   | |   |   |EXCHANGE:EMIT Operator?|   |   | |   |   |   |SCAN Operator |   |   |   |  FROM TABLE |   |   |   |  RA2 |   |   |   |  Table Scan. |   |   |   |  Executed in parallel with a 2-way??????????????????????partition scan. | |   |EXCHANGE Operator (Merged) |   |Executed in parallel by 2 Producer and 1??????????????Consumer processes. |   | |   |   |EXCHANGE:EMIT Operator |   |   | |   |   |   |SCAN Operator |   |   |   |  FROM TABLE |   |   |   |  HA2 |   |   |   | ?Table Scan. |   |   |   |  Executed in parallel with a 2-way??????????????????????partition scan.

延伸閱讀1：MySQL

MySQL是一個(gè)關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，由瑞典MySQL AB 公司開發(fā)，屬于 Oracle 旗下產(chǎn)品。MySQL 是最流行的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)之一，在 WEB 應(yīng)用方面，MySQL是較好的 RDBMS (Relational Database Management System，關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)) 應(yīng)用軟件之一。