MySQLのtruncateとは?テーブル削除と切り捨ての2つの機能について解説
  • facebookページ
  • twitterページ
  • 2020.01.30

    MySQLのtruncateとは?テーブル削除と切り捨ての2つの機能について解説

    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文を使った全レコード削除の例

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

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

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

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

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

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

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

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

    truncate関数の文法

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

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

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

    truncate関数を使った例

    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の例

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

    ceil関数の使用例

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

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



    優良フリーランス案件多数掲載中!
    フリーランスエンジニアの案件をお探しなら
    ポテパンフリーランス

    この記事をシェア

    • Facebookシェア
    • Twitterシェア
    • Hatenaシェア
    • Lineシェア
    pickup









    ABOUT US

    ポテパンはエンジニアと企業の最適なマッチングを追求する企業です。

    READ MORE