バナー画像

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

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

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

MySQLのテーブル削除の構文

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

drop table employees;

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

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

truncate table employees;

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

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

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

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

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

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

$ mysql -u root -pmy-secret-pw employees -e "show tables" -s -N | sed -e 's/^/drop table if exists /' -e 's/$/;/'
mysql: [Warning] Using a password on the command line interface can be insecure.
drop table if exists current_dept_emp;
drop table if exists departments;
drop table if exists dept_emp;
drop table if exists dept_emp_latest_date;
drop table if exists dept_manager;
drop table if exists employees;
drop table if exists salaries;
drop table if exists titles;

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

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

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

ERROR 1051 (42S02) at line 1: Unknown table 'employees.current_dept_emp'

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

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

ERROR 3730 (HY000) at line 2: Cannot drop table 'departments' referenced by a foreign key constraint 'dept_emp_ibfk_2' on table 'dept_emp'.

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

SELECT F1.table_schema    AS TABLE_SCHEMA, 
       F1.table_name      AS TABLE_NAME, 
       F1.column_name     AS COLUMN_NAME, 
       F2.constraint_type AS CONSTRAINT_TYPE, 
       F2.constraint_name AS CONSTRAINT_NAME 
FROM   information_schema.key_column_usage F1 
       LEFT JOIN information_schema.table_constraints F2 
              ON F1.table_schema = F2.table_schema 
                 AND F1.constraint_name = F2.constraint_name 
WHERE  F2.constraint_type = 'FOREIGN KEY'

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

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

+--------------+--------------+-------------+-----------------+---------------------+
| TABLE_SCHEMA | TABLE_NAME   | COLUMN_NAME | CONSTRAINT_TYPE | CONSTRAINT_NAME     |
+--------------+--------------+-------------+-----------------+---------------------+
| employees    | dept_manager | emp_no      | FOREIGN KEY     | dept_manager_ibfk_1 |
| employees    | dept_manager | dept_no     | FOREIGN KEY     | dept_manager_ibfk_2 |
| employees    | dept_emp     | emp_no      | FOREIGN KEY     | dept_emp_ibfk_1     |
| employees    | dept_emp     | dept_no     | FOREIGN KEY     | dept_emp_ibfk_2     |
| employees    | titles       | emp_no      | FOREIGN KEY     | titles_ibfk_1       |
| employees    | salaries     | emp_no      | FOREIGN KEY     | salaries_ibfk_1     |
+--------------+--------------+-------------+-----------------+---------------------+

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

mysql> drop table departments;
ERROR 3730 (HY000): Cannot drop table 'departments' referenced by a foreign key constraint 'dept_emp_ibfk_2' on table 'dept_emp'.
mysql> set FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)
mysql> drop table departments;
Query OK, 0 rows affected (0.02 sec)
mysql> desc departments;
ERROR 1146 (42S02): Table 'employees.departments' doesn't exist
mysql> set FOREIGN_KEY_CHECKS=1;
Query OK, 0 rows affected (0.00 sec)

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

まとめ

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

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

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

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

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

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

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

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

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

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

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

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