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が使われるもう１つが、小数点以下を切り捨てする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など他のデータベース監視ソフトでは、違う関数名が使われています。うろ覚えの機能を混同せずに、機能をしっかりチェックして使い分けましょう。
