一時テーブル
一時テーブルは、クエリ結果を再利用するための手法と考えることができます。
書店アプリケーション内の最年長の著者について何かを知りたい場合は、最年長の著者のリストを使用する複数のクエリを作成できます。
たとえば、次のステートメントを使用すると、 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)
後続のクエリの便宜のために、このクエリの結果をキャッシュする必要があります。storageに一般的なテーブルを使用する場合は、バッチ クエリの後にこれらのテーブルが使用されない可能性があるため、異なるセッション間でのテーブル名の重複の問題を回避する方法と、中間結果を時間内にクリーンアップする必要性に注意する必要があります。
一時テーブルを作成する
中間結果をキャッシュするために、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 機能との互換性制限を参照してください。