目次
とある日
とあるテーブル検証をしてて、TRUNCATE
を実行したときに起こったお話。
TRUNCATE
を実行したテーブルには、AUTO_INCREMENT
を設定したカラムが存在した。
TRUNCATE
実行後INSERT
を実行した。
その時、AUTO_INCREMENT
を設定したカラムの値が1の初期値に戻っていた。
あれ、TRUNCATE
ってAUTO_INCREMENT
リセットされるの!?
ってことで、調べます。
結論
先に結論を述べます。
なぜなら、公式リファレンスに記載されていたから。
Any
AUTO_INCREMENT
value is reset to its start value. This is true even forMyISAM
andInnoDB
, which normally do not reuse sequence values.↓翻訳
AUTO_INCREMENTの値はすべてその開始値にリセットされます。これは、通常はシーケンス値を再利用しないMyISAMやInnoDBにも当てはまります。
なので、TRUNCATE
ではAUTO_INCREMENT
がリセットされます。
だから、AUTO_INCREMENT
をリセットさせたくないかつデータ削除したいのであれば、DELETE
を選択してください。
環境
今回それぞれ使用するアプリケーション等のバージョンを明記しています。
VirtualBox 内の仮想環境下で、MySQL を構築しています。
Oracle VM VirtualBox
項目名 | 値 |
---|---|
バージョン | 6.0.14 r133895 (Qt5.6.2) |
DBeaver
項目名 | 値 |
---|---|
バージョン | 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
検証
検証内容
DELETE
とTRUNCATE
でAUTO_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からになっているのはなぜかわからない。
原因究明中。
〆に、原因究明記事あり。
〆
意外と知らなかった、DELETE
とTURNCATE
の違いでした。
両者の違いをググってもあまりフォーカスを受けてない違いだった。
自分も全然知らなかった。
調べていく中で、TRUNCATE
で試したい内容とかもあったので今後やろうと思います。
今回の件は活用することはなさそうですが、知らないとひょっとするとひょっとした自体になるかもしれない件ですね。
検証してて出会った謎現象については今調べてるので今後Blogにしようと思います。
2021年1月11日追記:謎現象については解決し解説したものを記事にしました。