今日はなにの日。

気になったこと勉強になったことのメモ。

今日は、MySQLのTRUNCATEとDELETEのコアな違いの日。

目次

とある日

とあるテーブル検証をしてて、TRUNCATEを実行したときに起こったお話。

TRUNCATEを実行したテーブルには、AUTO_INCREMENTを設定したカラムが存在した。

TRUNCATE実行後INSERTを実行した。

その時、AUTO_INCREMENTを設定したカラムの値が1の初期値に戻っていた。

あれ、TRUNCATEってAUTO_INCREMENTリセットされるの!?

ってことで、調べます。

結論

先に結論を述べます。

なぜなら、公式リファレンスに記載されていたから。

TRUNCATE TABLE Statement

Any AUTO_INCREMENT value is reset to its start value. This is true even for MyISAM and InnoDB, which normally do not reuse sequence values.

↓翻訳

AUTO_INCREMENTの値はすべてその開始値にリセットされます。これは、通常はシーケンス値を再利用しないMyISAMInnoDBにも当てはまります。

なので、TRUNCATEではAUTO_INCREMENTがリセットされます。

だから、AUTO_INCREMENTをリセットさせたくないかつデータ削除したいのであれば、DELETEを選択してください。

環境

今回それぞれ使用するアプリケーション等のバージョンを明記しています。

VirtualBox 内の仮想環境下で、MySQL を構築しています。

Oracle VM VirtualBox

Oracle VM VirtualBox - ダウンロード

項目名
バージョン 6.0.14 r133895 (Qt5.6.2)

DBeaver

DBeaver Community

項目名
バージョン 7.2.0.202008302047

Tera Term

定番のターミナルエミュレーター

項目名
Version 4.102

ホスト PC

項目名
OS Windows 10 Home
CPU Intel(R) Core(TM) i7-8550U CPU @ 1.80GHz
メモリ 8.0 GB

ゲスト PC

VirtualBox の仮想環境内に、テスト環境を構築しています。

OS

mysql> \! cat /etc/os-release
NAME="CentOS Linux"
VERSION="8 (Core)"
ID="centos"
ID_LIKE="rhel fedora"
VERSION_ID="8"
PLATFORM_ID="platform:el8"
PRETTY_NAME="CentOS Linux 8 (Core)"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:centos:centos:8"
HOME_URL="https://www.centos.org/"
BUG_REPORT_URL="https://bugs.centos.org/"

CENTOS_MANTISBT_PROJECT="CentOS-8"
CENTOS_MANTISBT_PROJECT_VERSION="8"
REDHAT_SUPPORT_PRODUCT="centos"
REDHAT_SUPPORT_PRODUCT_VERSION="8"

メモリ

2048MB

ストレージ

20.00 GB

MySQL

mysql> status;
--------------
mysql  Ver 8.0.21 for Linux on x86_64 (Source distribution)

Connection id:          27
Current database:       hobby
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.21 Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /var/lib/mysql/mysql.sock
Binary data as:         Hexadecimal
Uptime:                 11 hours 56 min 31 sec

Threads: 5  Questions: 1817  Slow queries: 0  Opens: 409  Flush tables: 3  Open tables: 312  Queries per second avg: 0.042

検証

検証内容

DELETETRUNCATEAUTO_INCREMENTの違いが存在するのか。

検証テーブル作成

AUTO_INCREMENTが設定されていればなんでもいいカラムを定義する。

PRIMARY KEYを設定するのを忘れずに。

create table auto_increment_test (
    id int primary key auto_increment
);
insert into auto_increment_test select null from information_schema.TABLES limit 10 ;

LIMITはお好きな件数を設定してください。

削除するので少なくていいと思いますが。

mysql> SELECT * from auto_increment_test ;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
+----+
10 rows in set (0.00 sec)

データが存在を確認。

AUTO_INCREMENTの値を確認。

mysql> show create table auto_increment_test\G ;
*************************** 1. row ***************************
       Table: auto_increment_test
Create Table: CREATE TABLE `auto_increment_test` (
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

ERROR:
No query specified

AUTO_INCREMENT=16 は本来10件挿入したので次の値を示すAUTO_INCREMENT=11になるはずなのに、16になっている件はのところにある記事を参照してください。

今回の記事の内容には関係ないことなのでここでの解説は省略します。

TRUNCATE

Truncate table auto_increment_test ;

TRUNCATEでデータ削除。

mysql> SELECT * from auto_increment_test order by id desc;
Empty set (0.00 sec)

mysql> show create table auto_increment_test \G;
*************************** 1. row ***************************
       Table: auto_increment_test
Create Table: CREATE TABLE `auto_increment_test` (
  `id` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

DEFAULTに変更されている。

insert into auto_increment_test select null from information_schema.TABLES limit 10 ;

再度INSERTを実行する。

mysql> SELECT * from auto_increment_test order by id desc;
+----+
| id |
+----+
| 10 |
|  9 |
|  8 |
|  7 |
|  6 |
|  5 |
|  4 |
|  3 |
|  2 |
|  1 |
+----+
10 rows in set (0.00 sec)

AUTO_INCREMENTの値がリセットされている。

DELETE

同じテーブルを使用する。

DELETE FROM  auto_increment_test where 0 = 0;
commit;

WHEREはDBeaverでやるとWHEREなしで実行すると警告が出るので、それを対策として適当にTUREになる条件式を当てはめている。

設定によっては、COMMITされていないのでちゃんとCOMMITを行うこと。

mysql> SELECT * from auto_increment_test order by id desc;
Empty set (0.00 sec)

再度INSERT実行。

insert into auto_increment_test select null from information_schema.TABLES limit 10 ;

SELECT実行。

mysql> SELECT * from auto_increment_test order by id desc;
+----+
| id |
+----+
| 25 |
| 24 |
| 23 |
| 22 |
| 21 |
| 20 |
| 19 |
| 18 |
| 17 |
| 16 |
+----+
10 rows in set (0.00 sec)

AUTO_INCREMENTがリセットされていない事がわかる。

ちなみに、値が16からになっているのはなぜかわからない。

原因究明中。

に、原因究明記事あり。

意外と知らなかった、DELETETURNCATEの違いでした。

両者の違いをググってもあまりフォーカスを受けてない違いだった。

自分も全然知らなかった。

調べていく中で、TRUNCATEで試したい内容とかもあったので今後やろうと思います。

今回の件は活用することはなさそうですが、知らないとひょっとするとひょっとした自体になるかもしれない件ですね。

検証してて出会った謎現象については今調べてるので今後Blogにしようと思います。

2021年1月11日追記:謎現象については解決し解説したものを記事にしました。

updraft.hatenadiary.com