MySQLのデータベース作成 renameやdrop時に気をつける点は？

MySQLのデータベース作成の方法について、サンプルSQLを紹介しながらまとめています。

以下、データベースとして、MySQLのサンプルデータベースEmployeesを使っています。SQL実行結果の表示にはphpMyAdminを使用しています。

MySQLのデータベース作成の構文

CREATE DATABASE データベース名
CHARACTER SET = デフォルトの文字コード
COLLATE = 照合順序

照合順序は、文字列比較やソート時に使われるルールです。

例えば、文字コードをUTF-8で、照合順序を一般的なものにしたい場合は、mysqlコマンドのプロンプトから、以下を実行します。

mysql> CREATE DATABASE test_db DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

utf8_general_ciは、デフォルトで使われる照合順序で、「UTF-8」の多言語（general）で、大文字小文字を無視する（ci）という意味合いになります。

実行後は、「Query OK, 1 row affected (0.00 sec)」という簡素なメッセージのみ表示され、詳細は

作成したデータベース情報は、以下のSQLで確認します。

mysql> select * from INFORMATION_SCHEMA.SCHEMATA where SCHEMA_NAME='test_db';
+--------------+-------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+-------------+----------------------------+------------------------+----------+
| def          | test_db     | utf8                       | utf8_general_ci        | NULL     |
+--------------+-------------+----------------------------+------------------------+----------+
1 row in set (0.00 sec)

DEFAULT_CHARACTER_SET_NAME（デフォルトの文字コード）がutf8、DEFAULT_COLLATION_NAME（デフォルトの照合順序）がutf8_general_ciであることが確認できました。

MySQLのデータベース操作方法

MySQLでデータベース名を変更するには、全テーブルのリネーム

MySQLではデータベースをリネームする「renamae database」がMySQL5.1.23以降削除されました。危険な状態に陥るケースがあるから、という理由のようです。

そのため、一度作ったデータベースの名前を変更するには、mysqldumpコマンドで全データをダンプし、別名のデータベースに戻す必要があります。

ただし、データーベースのサイズがあまりにも大きい場合は、この方法は現実的ではありません。

代替方法として、全テーブルに対して、rename tableをおこなうことで、結果データベース名の変更が可能です。

RENAME TABLE 旧データベース名.table_name TO 新データベース名.table_name

具体的にはshow tablesでテーブル名一覧を取得し、上記のSQLを全テーブルに対して実行するSQL文のファイルを作成して、実行すると良いでしょう。

全テーブルをリネーム用のSQLを生成する方法が、以下のページで公開されていました。

参考）MySQLでdumpせずにRENAME DATABASE を実現する – Qiita

MySQLでデータベースを削除しても、ユーザ権限は残る

MySQLでは、drop databaseでデータベースを削除後も、データベースに紐付いているユーザ権限は削除されずにそのまま残ります。

例えば、データベースtest_dbにユーザtest_userが権限を持っているケースを見てみます。

mysql> show grants for test_user;
+--------------------------------------------------------+
| Grants for test_user@%                                 |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test_user'@'%'                  |
| GRANT ALL PRIVILEGES ON `test_db`.* TO 'test_user'@'%' |
+--------------------------------------------------------+

show grantsで確認すると、test_userは、データベースtest_dbの全てのテーブルに権限を持ってます。

データベース削除後のユーザ権限を確認してみましょう。

mysql> drop database if exists test_db;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for test_user;
+--------------------------------------------------------+
| Grants for test_user@%                                 |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test_user'@'%'                  |
| GRANT ALL PRIVILEGES ON `test_db`.* TO 'test_user'@'%' |
+--------------------------------------------------------+

あデータベース削除後も、ユーザの権限は変わらず残っています。

なお、以下のSQLは、対象のデータベース名が存在するときのみデーターベースを削除します。

DROP DATABASE IF EXISTS データベース名;

データベースに対する権限を削除するには、revokeで権限を削除します。

show grantsで権限が削除されたことが確認できました。

mysql> revoke all on test_db.* from test_user;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for test_user;
+---------------------------------------+
| Grants for test_user@%                |
+---------------------------------------+
| GRANT USAGE ON *.* TO 'test_user'@'%' |
+---------------------------------------+

まとめ

ポテパンダの一言メモ

• MySQLでデータベースを作成するにはcreate databse
• 作成後の文字コードや照合順序を参照するには、INFORMATION_SCHEMA.SCHEMATAテーブルをselectする
• データベースをリネームするには、全テーブルにrename tableを実行
• データベースを削除後も、ユーザの権限（grants）は残るので、revokeを実行して削除する必要がある