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'
実行するとこうなりました。
+--------------+--------------+-------------+-----------------+---------------------+ | 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できない場合は、外部キー制約を一時的に無効にする
