MariaDBでは一時テーブルを使ったことがなかったので、確認のためのメモをしておきます。
ロゴ画像 © MariaDB Foundation（CC-BY-SA）
前提条件
- MariaDB 11.4.5 (Docker Official Image)
- Ubuntu 24.04.2 LTS (WSL2) からdockerコンテナへ接続
これからやること
- サンプルデータベース作成
- サンプルテーブル作成
- サンプルデータ作成
- 一時テーブル作成
- 一時テーブルへのレコード挿入
- 別のデータベースセッションでの確認
- セッション再接続後の一時テーブル存否確認
サンプルデータ作成
サンプルデータベース作成
create database sample;
use sample;サンプルテーブル作成
▼１．ユーザーテーブル
CREATE TABLE `users` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ユーザーid',
`name` varchar(255) NOT NULL COMMENT 'ユーザー名',
`email` varchar(255) NOT NULL COMMENT 'メールアドレス',
`password` varchar(255) NOT NULL COMMENT 'パスワード',
`created_at` timestamp NULL DEFAULT NULL COMMENT '作成日',
`updated_at` timestamp NULL DEFAULT NULL COMMENT '更新日',
PRIMARY KEY (`id`),
UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;▼２．投稿テーブル
CREATE TABLE `posts` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '投稿ID',
`user_id` bigint(20) unsigned NOT NULL COMMENT 'ユーザーID',
`title` varchar(255) NOT NULL COMMENT 'タイトル',
`content` text NOT NULL COMMENT '内容',
`public` tinyint(4) NOT NULL COMMENT '公開フラグ',
`created_at` timestamp NULL DEFAULT NULL COMMENT '作成日',
`updated_at` timestamp NULL DEFAULT NULL COMMENT '更新日',
PRIMARY KEY (`id`),
KEY `posts_user_id_foreign` (`user_id`),
CONSTRAINT `posts_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;サンプルレコード作成
▼１．ユーザーレコード
INSERT INTO `users` VALUES
(1,'テスト ユーザー','test@example.com','$2y$12$Yj54hIx1.1xbH4u1LFHhrOEnA4xTmR43xB9wRrX0eMrVu1oPo2D1W',NOW(),NOW());▼２．投稿レコード
INSERT INTO `posts` VALUES
(1,1,'タイトル１','投稿１です。',1,NOW(),NOW()),
(2,1,'タイトル２','投稿２です。',0,NOW(),NOW()),
(3,1,'タイトル３','投稿３です。',1,NOW(),NOW());一時テーブル作成の書式
CREATE [OR REPLACE] [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...) [table_options ]... [partition_options]
CREATE [OR REPLACE] [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)] [table_options ]... [partition_options]
select_statement
CREATE [OR REPLACE] [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_table_name | (LIKE old_table_name) }
select_statement:
[IGNORE | REPLACE] [AS] SELECT ... (Some legal select statement)MySQLとの違いは「OR REPLACE」が使える箇所でしょうか。
今回、一時テーブル作成時のカラム定義は面倒なので行いません。
次の２パターンで攻めたいと思います。
１．LIKEで作成
２．SELECTで作成
一時テーブル作成：LIKEで作成
posts テーブルを基に一時テーブル t_posts_like を作成してみます。
MariaDB [blog]> CREATE OR REPLACE TEMPORARY TABLE t_posts_like LIKE posts;
Query OK, 0 rows affected (0.004 sec)
MariaDB [blog]> show tables;
+----------------+
| Tables_in_blog |
+----------------+
| t_posts_like |
| posts |
| users |
+----------------+
3 rows in set (0.001 sec)
MariaDB [blog]>空の一時テーブルが作成されました。
MariaDB [blog]> select * from t_posts_like;
Empty set (0.001 sec)
MariaDB [blog]>カラム定義を見てみます。
MariaDB [blog]> describe posts;
+------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+----------------+
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| user_id | bigint(20) unsigned | NO | MUL | NULL | |
| title | varchar(255) | NO | | NULL | |
| content | text | NO | | NULL | |
| public | tinyint(4) | NO | | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
+------------+---------------------+------+-----+---------+----------------+
7 rows in set (0.001 sec)
MariaDB [blog]> describe t_posts_like;
+------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+----------------+
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| user_id | bigint(20) unsigned | NO | MUL | NULL | |
| title | varchar(255) | NO | | NULL | |
| content | text | NO | | NULL | |
| public | tinyint(4) | NO | | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
+------------+---------------------+------+-----+---------+----------------+
7 rows in set (0.001 sec)
MariaDB [blog]> カラム定義は属性含めて引き継がれていますね。
テーブルの詳細も見てみます。
MariaDB [blog]> show table status like 'posts' \G
*************************** 1. row ***************************
Name: posts
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 3
Avg_row_length: 5461
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: 4
Create_time: 2025-04-24 12:49:08
Update_time: 2025-04-24 13:22:02
Check_time: NULL
Collation: utf8mb4_unicode_ci
Checksum: NULL
Create_options:
Comment:
Max_index_length: 0
Temporary: N
1 row in set (0.001 sec)
MariaDB [blog]> show table status like 't_posts_like' \G
*************************** 1. row ***************************
Name: t_posts_like
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 2
Avg_row_length: 8192
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 6291456
Auto_increment: 4
Create_time: NULL
Update_time: 2025-04-24 14:31:34
Check_time: NULL
Collation: utf8mb4_unicode_ci
Checksum: NULL
Create_options:
Comment:
Max_index_length: 0
Temporary: Y
1 row in set (0.001 sec)
MariaDB [blog]> EngineやCollation等も引き継がれていますね。
一時テーブルにレコード挿入してみます。
MariaDB [blog]> INSERT INTO t_posts_like SELECT * FROM posts WHERE public=1;
Query OK, 2 rows affected (0.002 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [blog]> select * from t_posts_like;
+----+---------+-----------------+--------------------+--------+---------------------+---------------------+
| id | user_id | title | content | public | created_at | updated_at |
+----+---------+-----------------+--------------------+--------+---------------------+---------------------+
| 1 | 1 | タイトル１ | 投稿１です。 | 1 | 2025-04-24 13:22:02 | 2025-04-24 13:22:02 |
| 3 | 1 | タイトル３ | 投稿３です。 | 1 | 2025-04-24 13:22:02 | 2025-04-24 13:22:02 |
+----+---------+-----------------+--------------------+--------+---------------------+---------------------+
2 rows in set (0.000 sec)
MariaDB [blog]> 一時テーブル作成：SELECTで作成
postsからSELECTして一時テーブルを作成します。
MariaDB [blog]> CREATE OR REPLACE TEMPORARY TABLE t_posts_select SELECT * FROM posts WHERE public=1;
Query OK, 2 rows affected (0.001 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [blog]> show tables;
+----------------+
| Tables_in_blog |
+----------------+
| t_posts_select |
| t_posts_like |
| posts |
| users |
+----------------+
4 rows in set (0.000 sec)
MariaDB [blog]> select * from t_posts_select;
+----+---------+-----------------+--------------------+--------+---------------------+---------------------+
| id | user_id | title | content | public | created_at | updated_at |
+----+---------+-----------------+--------------------+--------+---------------------+---------------------+
| 1 | 1 | タイトル１ | 投稿１です。 | 1 | 2025-04-24 13:22:02 | 2025-04-24 13:22:02 |
| 3 | 1 | タイトル３ | 投稿３です。 | 1 | 2025-04-24 13:22:02 | 2025-04-24 13:22:02 |
+----+---------+-----------------+--------------------+--------+---------------------+---------------------+
2 rows in set (0.001 sec)
MariaDB [blog]> 一時テーブル作成と同時にレコードが挿入されています。
カラム定義を見てみます。
MariaDB [blog]> describe posts;
+------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+----------------+
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| user_id | bigint(20) unsigned | NO | MUL | NULL | |
| title | varchar(255) | NO | | NULL | |
| content | text | NO | | NULL | |
| public | tinyint(4) | NO | | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
+------------+---------------------+------+-----+---------+----------------+
7 rows in set (0.001 sec)
MariaDB [blog]> describe t_posts_select;
+------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+-------+
| id | bigint(20) unsigned | NO | | 0 | |
| user_id | bigint(20) unsigned | NO | | NULL | |
| title | varchar(255) | NO | | NULL | |
| content | text | NO | | NULL | |
| public | tinyint(4) | NO | | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
+------------+---------------------+------+-----+---------+-------+
7 rows in set (0.001 sec)
MariaDB [blog]> PRIMARY KEY, FOREIGN KEY, auto increment等の属性は当然引き継がれません。
テーブルの詳細も見てみます。
MariaDB [blog]> show table status like 'posts' \G
*************************** 1. row ***************************
Name: posts
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 3
Avg_row_length: 5461
Data_length: 16384
Max_data_length: 0
Index_length: 16384
Data_free: 0
Auto_increment: 4
Create_time: 2025-04-24 12:49:08
Update_time: 2025-04-24 13:22:02
Check_time: NULL
Collation: utf8mb4_unicode_ci
Checksum: NULL
Create_options:
Comment:
Max_index_length: 0
Temporary: N
1 row in set (0.001 sec)
MariaDB [blog]> show table status like 't_posts_select' \G
*************************** 1. row ***************************
Name: t_posts_select
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 2
Avg_row_length: 8192
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 6291456
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_general_ci
Checksum: NULL
Create_options:
Comment:
Max_index_length: 0
Temporary: Y
1 row in set (0.001 sec)
MariaDB [blog]> EngineやCollationも引き継がれています。
別のデータベースセッションでの確認
上記のセッションをそのまま保持した状態で、
もう一つのデータベースセッションを確立して
テーブル一覧に先程の一時テーブルが無いことを確認します。（重要）
※MariaDBコンテナのターミナルから接続
root@31790c6e9b56:/# mariadb -h localhost -u root -p -D blog
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 38
Server version: 11.4.5-MariaDB-ubu2404-log mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [blog]> show tables;
+----------------+
| Tables_in_blog |
+----------------+
| posts |
| users |
+----------------+
2 rows in set (0.001 sec)
MariaDB [blog]> 一時テーブルが無いことが確認できます。
ついでに、先程と同じ一時テーブル名で、
OR REPLACEを付けずに一時テーブルを作成してみます。
MariaDB [blog]> CREATE TEMPORARY TABLE t_posts_like LIKE posts;
Query OK, 0 rows affected (0.001 sec)
MariaDB [blog]> CREATE TEMPORARY TABLE t_posts_select
-> SELECT * FROM posts WHERE public=0;
Query OK, 1 row affected (0.001 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [blog]> show tables;
+----------------+
| Tables_in_blog |
+----------------+
| t_posts_select |
| t_posts_like |
| posts |
| users |
+----------------+
4 rows in set (0.000 sec)
MariaDB [blog]> エラーなく作成できています。
t_posts_like は空テーブル、
t_posts_select は「public=0」のレコードが入っています。
MariaDB [blog]> select * from t_posts_like;
Empty set (0.001 sec)
MariaDB [blog]> select * from t_posts_select;
+----+---------+-----------------+--------------------+--------+---------------------+---------------------+
| id | user_id | title | content | public | created_at | updated_at |
+----+---------+-----------------+--------------------+--------+---------------------+---------------------+
| 2 | 1 | タイトル２ | 投稿２です。 | 0 | 2025-04-24 13:22:02 | 2025-04-24 13:22:02 |
+----+---------+-----------------+--------------------+--------+---------------------+---------------------+
1 row in set (0.000 sec)
MariaDB [blog]> 最初に一時テーブルを作成した方のデータベースセッション側で作成した一時テーブルのレコードはそのまま保持されていることが確認できます。
※最初のデータベースセッション側でSELECT
MariaDB [blog]> select * from t_posts_like;
+----+---------+-----------------+--------------------+--------+---------------------+---------------------+
| id | user_id | title | content | public | created_at | updated_at |
+----+---------+-----------------+--------------------+--------+---------------------+---------------------+
| 1 | 1 | タイトル１ | 投稿１です。 | 1 | 2025-04-24 13:22:02 | 2025-04-24 13:22:02 |
| 3 | 1 | タイトル３ | 投稿３です。 | 1 | 2025-04-24 13:22:02 | 2025-04-24 13:22:02 |
+----+---------+-----------------+--------------------+--------+---------------------+---------------------+
2 rows in set (0.000 sec)
MariaDB [blog]> select * from t_posts_select;
+----+---------+-----------------+--------------------+--------+---------------------+---------------------+
| id | user_id | title | content | public | created_at | updated_at |
+----+---------+-----------------+--------------------+--------+---------------------+---------------------+
| 1 | 1 | タイトル１ | 投稿１です。 | 1 | 2025-04-24 13:22:02 | 2025-04-24 13:22:02 |
| 3 | 1 | タイトル３ | 投稿３です。 | 1 | 2025-04-24 13:22:02 | 2025-04-24 13:22:02 |
+----+---------+-----------------+--------------------+--------+---------------------+---------------------+
2 rows in set (0.000 sec)
MariaDB [blog]> 以上、MariaDBでの一時テーブルの作り方と挙動の簡単な確認でした。
あー、バッチ作成がんばろうっと。
コメント