バナー画像

MySQLのtruncateを使う2つの機能、テーブルに格納されたデータを削除するtruncate table文と、小数点を切り捨てするtruncate()関数について、2つまとめて解説します。

truncateとは

MySQLには、truncateを使った2つの機能があります。1つは、テーブルに格納されたデータを削除するtruncate table文であり、これは標準SQLの機能の1つで、MySQL以外のデータベース管理ソフトでも使えます。また、もう1つの機能は指定した桁で切り捨てするtruncate()関数ですが、この関数は標準ではなく、他のデータベース管理ソフトでは利用できません。

そういった機能について解説する前に、まずは、truncateの基本について解説します。

英語のtruncateの意味

truncateは、元は英語の動詞で「先端を切り取る」、「先端を断ち切る」、「(長い文章や文字列を)切り詰める」、「(数字を)切り捨てる」という意味を持っています。

英語のtruncateの意味
「先端を切り取る」、「先端を断ち切る」
「(長い文章や文字列を)切り詰める」
「(数字を)切り捨てる」

なお、truncateの「(数字を)切り捨てる」という意味から、ITでは、数字の切り捨てする意味でTRUNCという関数がよく使われます。例えば、Excelでは、切り捨てのROUNDDOWN関数の他に、小数点以下数字を切り捨てするTRUNC関数も使えます。

テーブルの全てのデータを削除する機能

SQLにはテーブルに格納されたデータを削除するとしたら、レコード単位で削除するdelete文を使います。そして、この他に全てのレコードを一括で削除する機能としてtruncate table文が使います。なお、このSQLのコマンドは、2008年に標準SQLとして採用されました。

もちろん、標準SQLに対応しているMySQLではtruncate table文が使えます。また、OracleやPostgreSQLなどのSQLが使えるデータベース管理ソフトでも使用可能です。

小数点以下を切り捨てする関数

MySQLには、selectで抽出したデータを加工して出力する関数がいろいろと使えますが、小数点以下を切り捨てで表示する関数がtruncate関数です。なお、他のデータベース管理ソフトでは、truncate関数が使えないので注意してください。

また、MySQLで使える数字を丸める関数は、四捨五入がrount()、切り捨てがtruncate()、切り上げがceil()またはceiling()です。

テーブルを初期化する方法

先ほど紹介したように、MySQLにはtruncateを使った機能が2つありますが、そのうちのテーブルを初期化する機能について解説します。なお、2つを区別するために、こちらの機能は「truncate table」として覚えておくと良いでしょう。

truncate tableの文法

truncate tableの文法は、次のとおりです。

truncate [table] テーブル名;

truncate tableのtableを省略して、「truncate テーブル;」と実行することが可能です。ただし、実行するためには、テーブルをdropする権限が必要です。そして、truncate tableを実行すると、auto_incrementの値が初期化されるので注意してください。

なお、MySQLと互換性のあるmariadbのバージョン10.3以降では、ロック待機タイムアウトを指定するwaitを指定できます。また、mariadbのバージョン10.3以降のOracleモードでは、オプションのキーワードresume strageまたはdrop storageといったOracleの機能も使用できます。

truncate tableは早い

テーブルの全てのレコードを削除する場合、delete文を条件無しで実行しても可能です。しかし、truncate tableは、deleteを使う処理よりも、かなり短時間で処理できるのが特徴です。

ただし、delete文でレコードを全て削除した場合、auto_incrementの値は初期化されません。先ほども解説したように、truncate tableでレコードを削除するとauto_incrementの値が初期化されてしまうので、この機能を使っているテーブルでは注意が必要です。

delete文を使った全レコード削除の例

delete from table1;

truncate tableを使った全レコード削除の例

truncate table table1;

外部キーのせいでtruncate table が使えない場合

MySQLでテーブルの内容を効率良く削除できるtruncate tableですが、エラーが表示されて実行できない場合もあります。もし、次のようなメッセージが出たら、これは外部キーが原因です。

Cannot truncate a table referenced in a foreign key constraint

このようなエラーメッセージが出た場合は、外部キー制約を無視する命令を実行すると、truncate tableを実行できます。

外部キー制約を無視する命令
SET FOREIGN_KEY_CHECKS=0;

外部キー制約を無視してテーブルの内容を削除する例

set foreign_key_checks=0;
truncate table table1;

小数点以下を切り捨てするtruncate関数

MySQLでtruncateが使われるもう1つが、小数点以下を切り捨てするtruncate関数です。Webシステムでデータベースを使う場合は、プログラムで簡単に数字の表示を変えられるので、わざわざSQLで実行することは少ないかもしれません。しかし、このような計算は、副次問い合わせの中で使ったり、検索条件として使うことも可能です。MySQLを使うなら、覚えておきたい関数の一つです。

truncate関数の文法

小数点以下を切り捨てするtruncate関数の文法は、次のとおりです。

truncate ( 切り捨て対象の数字, 小数点以下の桁数 )

小数点以下の桁数が0の場合は、整数を返します。また、小数点以下の桁数を負の数にすると、小数点の左側の桁が0になった数字に変換されます。

truncate関数を使った例

select truncate( 123.45, 1 );
123.4

select trunvate( 1.234, 0 );
123

select trunvate( 1.234, -1 );
120

Oracleの切り捨てはちょっと違う

MySQLをはじめ、多くのデータベース管理ソフトが標準SQLに対応しているので、前出のtruncate tableは、OracleやPostgreSQLでも利用できます。しかし、小数点以下を切り捨てするtruncate関数は、標準ではありません。そのため、truncate関数が使えるのはMySQLだけです。

なお、OracleやPostgreSQLで小数点以下を切り捨てする関数はtrunc()です。もし、SQLを移植する場合は注意してください。

小数点以下を切り捨てする関数
MySQL truncate()
Oracle trunc()
PostgreSQL trunc()

MySQLの数字を丸める関数

先ほど解説したように、MySQLの切り捨てはtruncate関数ですが、この他に数字を丸める関数として、四捨五入のround関数も使えます。そしてround関数の文法は、truncate関数と同じく、第1引数に四捨五入対象の数字を、また第2引数に桁を指定します。

ただし、MySQLには切り上げの関数はありません。truncate関数を活用しましょう。

小数点以下を切り上げするSQLの例

select truncate(12.345 + .9, 0)
13

また、小数点以下だけを切り下げして、整数部分だけを出力するceil関数も使えます。

ceil関数の使用例

select ceil(12.345);
13

同じtruncateでも機能は全く違う

MySQLには、同じtruncateを使う、テーブルの全ての要素を削除するtruncate tableと、切り捨ての関数truncate()があります。そして、truncate tableはSQL標準の機能の1つですが、切り捨てする関数はMySQL独自の関数で、Oracleなど他のデータベース監視ソフトでは、違う関数名が使われています。うろ覚えの機能を混同せずに、機能をしっかりチェックして使い分けましょう。

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ 」

ポテパンキャンプは卒業生の多くがWebエンジニアとして活躍している実践型プログラミングスクールです。 1000名以上が受講しており、その多くが上場企業、ベンチャー企業のWebエンジニアとして活躍しています。

基礎的な学習だけで満足せず、実際にプログラミングを覚えて実践で使えるレベルまで学習したいという方に人気です。 プログラミングを学習し実践で使うには様々な要素が必要です。

それがマルっと詰まっているポテパンキャンプでプログラミングを学習してみませんか?

卒業生の多くがWebエンジニアとして活躍

卒業生の多くがWeb企業で活躍しております。
実践的なカリキュラムをこなしているからこそ現場でも戦力となっております。
活躍する卒業生のインタビューもございますので是非御覧ください。

経験豊富なエンジニア陣が直接指導

実践的なカリキュラムと経験豊富なエンジニアが直接指導にあたります。
有名企業のエンジニアも多数在籍し品質高いWebアプリケーションを作れるようサポートします。

満足度高くコスパの高いプログラミングスクール「ポテパンキャンプ」

運営する株式会社ポテパンは10,000人以上のエンジニアのキャリアサポートを行ってきております。
そのノウハウを活かして実践的なカリキュラムを随時アップデートしております。

代表の宮崎もプログラミングを覚えサイトを作りポテパンを創業しました。
本気でプログラミングを身につけたいという方にコスパ良く受講していただきたいと思っておりますので、気になる方はぜひスクール詳細をのぞいてくださいませ。