一時テーブル
一時テーブルは、クエリ結果を再利用するための手法と考えることができます。
書店のアプリケーションで最年長の著者について知りたい場合は、最年長の著者のリストを使用する複数のクエリを作成できます。
たとえば、次のステートメントを使用して、テーブルauthorsから最年長の上位 50 名の著者を取得できます。
SELECT a.id, a.name, (IFNULL(a.death_year, YEAR(NOW())) - a.birth_year) AS age
FROM authors a
ORDER BY age DESC
LIMIT 50;
結果は次のとおりです。
+------------+---------------------+------+
| id | name | age |
+------------+---------------------+------+
| 4053452056 | Dessie Thompson | 80 |
| 2773958689 | Pedro Hansen | 80 |
| 4005636688 | Wyatt Keeling | 80 |
| 3621155838 | Colby Parker | 80 |
| 2738876051 | Friedrich Hagenes | 80 |
| 2299112019 | Ray Macejkovic | 80 |
| 3953661843 | Brandi Williamson | 80 |
...
| 4100546410 | Maida Walsh | 80 |
+------------+---------------------+------+
50 rows in set (0.01 sec)
後続のクエリの便宜のために、このクエリの結果をキャッシュする必要があります。ストレージに汎用テーブルを使用する場合は、異なるセッション間でのテーブル名の重複の問題を回避する方法と、中間結果を時間内にクリーンアップする必要があることに注意する必要があります。これらのテーブルはバッチ クエリの後に使用されない可能性があるためです。
一時テーブルを作成する
中間結果をキャッシュするために、一時テーブル機能が TiDB v5.3.0 で導入されました。 TiDB は、セッション終了後にローカル一時テーブルを自動的に削除します。これにより、中間結果の増加による管理上の問題を心配する必要がなくなります。
一時テーブルの種類
TiDB の一時テーブルは、ローカル一時テーブルとグローバル一時テーブルの 2 種類に分けられます。
- ローカル一時テーブルの場合、テーブルの定義とテーブル内のデータは、現在のセッションにのみ表示されます。このタイプは、セッション内の中間データを一時的に格納するのに適しています。
- グローバル一時テーブルの場合、テーブル定義は TiDB クラスター全体に表示され、テーブル内のデータは現在のトランザクションにのみ表示されます。このタイプは、トランザクションの中間データを一時的に格納するのに適しています。
ローカル一時テーブルを作成する
ローカル一時テーブルを作成する前に、現在のデータベース ユーザーに権限をCREATE TEMPORARY TABLES追加する必要があります。
- SQL
- Java
CREATE TEMPORARY TABLE <table_name>ステートメントを使用して一時テーブルを作成できます。デフォルトのタイプは、現在のセッションにのみ表示されるローカル一時テーブルです。
CREATE TEMPORARY TABLE top_50_eldest_authors (
id BIGINT,
name VARCHAR(255),
age INT,
PRIMARY KEY(id)
);
一時テーブルを作成したら、 INSERT INTO table_name SELECT ...ステートメントを使用して、上記のクエリの結果を作成したばかりの一時テーブルに挿入できます。
INSERT INTO top_50_eldest_authors
SELECT a.id, a.name, (IFNULL(a.death_year, YEAR(NOW())) - a.birth_year) AS age
FROM authors a
ORDER BY age DESC
LIMIT 50;
結果は次のとおりです。
Query OK, 50 rows affected (0.03 sec)
Records: 50 Duplicates: 0 Warnings: 0
public List<Author> getTop50EldestAuthorInfo() throws SQLException {
List<Author> authors = new ArrayList<>();
try (Connection conn = ds.getConnection()) {
Statement stmt = conn.createStatement();
stmt.executeUpdate("""
CREATE TEMPORARY TABLE top_50_eldest_authors (
id BIGINT,
name VARCHAR(255),
age INT,
PRIMARY KEY(id)
);
""");
stmt.executeUpdate("""
INSERT INTO top_50_eldest_authors
SELECT a.id, a.name, (IFNULL(a.death_year, YEAR(NOW())) - a.birth_year) AS age
FROM authors a
ORDER BY age DESC
LIMIT 50;
""");
ResultSet rs = stmt.executeQuery("""
SELECT id, name FROM top_50_eldest_authors;
""");
while (rs.next()) {
Author author = new Author();
author.setId(rs.getLong("id"));
author.setName(rs.getString("name"));
authors.add(author);
}
}
return authors;
}
グローバル一時テーブルを作成する
- SQL
- Java
グローバル一時テーブルを作成するには、 GLOBALキーワードを追加してON COMMIT DELETE ROWSで終了します。これは、現在のトランザクションの終了後にテーブルが削除されることを意味します。
CREATE GLOBAL TEMPORARY TABLE IF NOT EXISTS top_50_eldest_authors_global (
id BIGINT,
name VARCHAR(255),
age INT,
PRIMARY KEY(id)
) ON COMMIT DELETE ROWS;
グローバル一時テーブルにデータを挿入する場合、トランザクションの開始をBEGINで明示的に宣言する必要があります。そうしないと、 INSERT INTOステートメントの実行後にデータがクリアされます。自動コミット モードでは、 INSERT INTOステートメントの実行後にトランザクションが自動的にコミットされ、トランザクションが終了するとグローバル一時テーブルがクリアされるためです。
グローバル一時テーブルを使用する場合は、最初に自動コミット モードをオフにする必要があります。 Java では、これをconn.setAutoCommit(false);ステートメントで実行でき、トランザクションをconn.commit();で明示的にコミットできます。トランザクション中にグローバル一時テーブルに追加されたデータは、トランザクションがコミットまたはキャンセルされた後にクリアされます。
public List<Author> getTop50EldestAuthorInfo() throws SQLException {
List<Author> authors = new ArrayList<>();
try (Connection conn = ds.getConnection()) {
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
stmt.executeUpdate("""
CREATE GLOBAL TEMPORARY TABLE IF NOT EXISTS top_50_eldest_authors (
id BIGINT,
name VARCHAR(255),
age INT,
PRIMARY KEY(id)
) ON COMMIT DELETE ROWS;
""");
stmt.executeUpdate("""
INSERT INTO top_50_eldest_authors
SELECT a.id, a.name, (IFNULL(a.death_year, YEAR(NOW())) - a.birth_year) AS age
FROM authors a
ORDER BY age DESC
LIMIT 50;
""");
ResultSet rs = stmt.executeQuery("""
SELECT id, name FROM top_50_eldest_authors;
""");
conn.commit();
while (rs.next()) {
Author author = new Author();
author.setId(rs.getLong("id"));
author.setName(rs.getString("name"));
authors.add(author);
}
}
return authors;
}
一時テーブルをビューする
SHOW [FULL] TABLESステートメントを使用すると、既存のグローバル一時テーブルのリストを表示できますが、リストにローカル一時テーブルは表示されません。今のところ、TiDB には、一時テーブル情報を格納するための同様のinformation_schema.INNODB_TEMP_TABLE_INFOシステム テーブルがありません。
たとえば、グローバル一時テーブルtop_50_eldest_authors_globalはテーブル リストに表示されますが、 top_50_eldest_authorsテーブルは表示されません。
+-------------------------------+------------+
| Tables_in_bookshop | Table_type |
+-------------------------------+------------+
| authors | BASE TABLE |
| book_authors | BASE TABLE |
| books | BASE TABLE |
| orders | BASE TABLE |
| ratings | BASE TABLE |
| top_50_eldest_authors_global | BASE TABLE |
| users | BASE TABLE |
+-------------------------------+------------+
9 rows in set (0.00 sec)
一時テーブルのクエリ
一時テーブルの準備ができたら、通常のデータ テーブルとしてクエリを実行できます。
SELECT * FROM top_50_eldest_authors;
複数テーブルの結合クエリを介して、一時テーブルからクエリへのデータを参照できます。
EXPLAIN SELECT ANY_VALUE(ta.id) AS author_id, ANY_VALUE(ta.age), ANY_VALUE(ta.name), COUNT(*) AS books
FROM top_50_eldest_authors ta
LEFT JOIN book_authors ba ON ta.id = ba.author_id
GROUP BY ta.id;
見るとは異なり、一時テーブルをクエリすると、データ挿入で使用された元のクエリを実行する代わりに、一時テーブルから直接データが取得されます。場合によっては、これによりクエリのパフォーマンスが向上する可能性があります。
一時テーブルをドロップする
セッションのローカル一時テーブルは、セッションの終了後に、データとテーブル スキーマの両方と共に自動的に削除されます。トランザクション内のグローバル一時テーブルは、トランザクションの終了時に自動的にクリアされますが、テーブル スキーマは残っているため、手動で削除する必要があります。
ローカル一時テーブルを手動で削除するには、 DROP TABLEまたはDROP TEMPORARY TABLE構文を使用します。例えば：
DROP TEMPORARY TABLE top_50_eldest_authors;
グローバル一時テーブルを手動で削除するには、 DROP TABLEまたはDROP GLOBAL TEMPORARY TABLE構文を使用します。例えば：
DROP GLOBAL TEMPORARY TABLE top_50_eldest_authors_global;
制限
TiDB の一時テーブルの制限については、 他の TiDB 機能との互換性の制限を参照してください。