MySQLのテーブル削除はdrop table 外部キー制約は一時的に無効化する
  • facebookページ
  • twitterページ
  • 2020.01.30

    MySQLのテーブル削除はdrop table 外部キー制約は一時的に無効化する

    MySQLのテーブル削除についてまとめています。

    以下の解説は、MySQL8.0以降を対象にしています。また、サンプルデータベースとしてEmployeesを使用しています。

    MySQLのテーブル削除は、drop table

    MySQLのテーブル削除の構文

    テーブル自体を構造含めて削除するには、drop tableを使います。

    上記のSQLは、employeesテーブルを削除します。

    テーブルを構造は残して、中身のデータだけを全削除するにはtruncateを使います。

    truncateは、全データを削除します。削除条件の指定はできません。ただし、削除時にログへの書き込みを行わないため、大量データを高速に削除できます。

    【関連記事】
    SQL truncate 高速データ削除 deleteやdropとの違いとDBMS間の差異

    条件指定つきで、テーブルのデータを削除するにはdelete文を使います。ログへの書き込みを行いながら削除するため、大量データの削除には時間がかかります。

    【関連記事】
    MySQL deleteのサンプルコード集 複数テーブルの削除やエイリアス制限とは

    一括でテーブルドロップをする方法

    以下のコマンドで、全テーブルをdrop tableするSQLを出力します。

    上記を実行すると、employeesデータベースのテーブル全てをdrop tableするSQLを出力します。この出力を、人間の目で確認してからmysqlコマンドラインにコピペして使うのが良いでしょう。

    シェル化してしまうと、誤って実行したときにデータベースの全テーブルがdropされてしまうので注意が必要です。

    また、上記コマンドは通常テーブルのほかに、VIEWも含みます。VIEWにdrop tableを実行すると以下のエラーが出力されます。

    ただし、上記のSQLでは、if existsを使用しているため、上記エラーが回避でき、結果としてVIEWはdropせず、通常テーブルのみをdropできます。

    また、外部キー(foreign key)設定のあるテーブルをdropしようとすると、以下のエラーが出力されます。

    foreign key設定のあるテーブルは、以下のSQLで確認できます。

    参考)とあるSEの備忘録: MySQLの外部キー 一覧

    実行するとこうなりました。

    これらのforeign keyをalter tableで削除しても良いのですが、set FOREIGN_KEY_CHECKS=0として、外部キー制約を一時的に無効にする方法が簡単です。

    drop後は、set FOREIGN_KEY_CHECKS=1を実行して、外部キー制約を有効にしておきましょう。

    まとめ

    ポテパンダの一言メモ
    • テーブル自体を構造含めて削除するには、drop table
    • テーブルの中身(データ)だけを削除するには、truncate table。条件付きで削除するならdelete文を使用。
    • mysqlのテーブル一覧をsedで加工して、一括drop用のSQLを生成可能
    • Foreign keyのためにtable dropできない場合は、外部キー制約を一時的に無効にする


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

    この記事をシェア

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









    ABOUT US

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

    READ MORE