Continue(s)

Twitter:@dn0t_ GitHub:@ogrew

MySQL5.7で(database|table|culumn)名を変更する方法まとめ

database名もtable名もcolumn名も(特にすでにデータが入っている場合は)後から変更すべきではない、という前提の元まとめてみました。

※間違いやご指摘等ありましたらTwitterまでご連絡下さい。

まず先にMySQLのバージョンを確認。

> $ mysql --version
mysql  Ver 14.14 Distrib 5.7.19, for osx10.12 (x86_64) using  EditLine wrapper

■ column

[ 構文 ]

mysql> ALTER TABLE (テーブル名) CHANGE (古いカラム名) (新しいカラム名);

Ex) userテーブルの「country」カラムを「city」に変更する。

mysql> desc user;
+------------+-----------------------+------+-----+-------------------+----------------+
| Field      | Type                  | Null | Key | Default           | Extra          |
+------------+-----------------------+------+-----+-------------------+----------------+
| id         | int(11)               | NO   | PRI | NULL              | auto_increment |
| name       | varchar(10)           | NO   |     | NULL              |                |
| sex        | enum('male','female') | YES  |     | NULL              |                |
| country    | varchar(15)           | YES  |     | Kanagawa          |                |
| user_item  | int(11)               | YES  |     | NULL              |                |
| created_at | datetime              | NO   |     | CURRENT_TIMESTAMP |                |
| updated_at | timestamp             | NO   |     | CURRENT_TIMESTAMP |                |
+------------+-----------------------+------+-----+-------------------+----------------+
7 rows in set (0.00 sec)

mysql> alter table user change country city;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

mysql> alter table user change country city varchar(15);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+------------+-----------------------+------+-----+-------------------+----------------+
| Field      | Type                  | Null | Key | Default           | Extra          |
+------------+-----------------------+------+-----+-------------------+----------------+
| id         | int(11)               | NO   | PRI | NULL              | auto_increment |
| name       | varchar(10)           | NO   |     | NULL              |                |
| sex        | enum('male','female') | YES  |     | NULL              |                |
| city       | varchar(15)           | YES  |     | NULL              |                |
| user_item  | int(11)               | YES  |     | NULL              |                |
| created_at | datetime              | NO   |     | CURRENT_TIMESTAMP |                |
| updated_at | timestamp             | NO   |     | CURRENT_TIMESTAMP |                |
+------------+-----------------------+------+-----+-------------------+----------------+
7 rows in set (0.00 sec)

MySQLの場合、ただカラム名を変更する場合でも型指定をする必要があるようです。(PostgreSQLでは多少文法は異なるが型指定は必要ないらしい。)

■ table

以下の記事が参考になりました。

http://atsuizo.hatenadiary.jp/entry/2016/06/16/100000

大きく2通りあります。

[構文]

mysql> RENAME TABLE (旧テーブル名) TO (新テーブル名); -- ①
mysql> ALTER TABLE (旧テーブル名) RENAME TO (新テーブル名); -- ②

①のメリットは複数テーブルの変更がカンマ区切りで一文でも書けるということ。

②のメリットは一テーブルずつしか変更が効かないがそのテーブルに対する他のALTER TABLE操作が同時に可能であるということ。

Ex) purchaseテーブルをpurchase_historyテーブルに変更、さらにitemテーブルをgoodsテーブルに変更する。

mysql> show tables;
+-------------------+
| Tables_in_okwra   |
+-------------------+
| item              |
| purchase          |
| user              |
+-------------------+
3 rows in set (0.00 sec)

mysql> rename table purchase to purchase_history;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table item rename to goods;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+-------------------+
| Tables_in_okwra   |
+-------------------+
| goods             |
| purchase_history  |
| user              |
+-------------------+
3 rows in set (0.00 sec)

■ database

RENAME DATABASEという恐ろしいクエリコマンドも過去のバージョンではあったようですが、一般的には泥臭く旧DBから新DBにmysqldumpさせるのがベタな方法なようです。

[構文]

mysql> CREATE DATABASE (新データベース名);
mysql> EXIT;
$ mysqldump -u[ユーザ名] -p[パスワード] (旧データベース名) > (旧データベース).sql
$ mysql -u[ユーザ名] -p[パスワード] (新データベース名) < (旧データベース).sql

Ex) okawariデータベースをokwraデータベースに変更する。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| okawari            |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.01 sec)

mysql> create database okwra;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| okawari            |
| okwra              |
| performance_schema |
| test               |
+--------------------+
7 rows in set (0.00 sec)

mysql> exit
Bye

ここでトラブル。

> $ mysqldump -uroot -p okawari >okawari.sql
Enter password:
mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'gtid\_mode'': Table 'performance_schema.session_variables' doesn't exist (1146)

ググってみたところ、原因はDBの構造が更新されていないことのよう。

以下のコマンドでDBをUpgradeする。

> $ mysql_upgrade -u root -p
Enter password:
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
(中略)
okawari.goods                                      OK
okawari.purchase_history                           OK
okawari.user                                       OK
(中略)
Upgrade process completed successfully.
Checking if update is needed.

この状態で再トライ。

> $ mysqldump -uroot -p okawari >okawari.sql
Enter password:
mysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'gtid\_mode'': Native table 'performance_schema'.'session_variables' has the wrong structure (1682)

今度はテーブルの構造自体が間違ってるよ、みたいなエラー。

こちらもググった結果、一旦MySQL自体の再起動をしてみることに。

> $ mysql.server stop
Shutting down MySQL
.. SUCCESS!

> $ mysql.server start
Starting MySQL
. SUCCESS!

> $ mysqldump -uroot -p okawari >okawari.sql
Enter password:

成功したぞい。

> $ mysqldump -uroot -p okawari >okawari.sql
Enter password:

> $ mysql -uroot -p okwra < okawari.sql
Enter password:

> $ mysql -uroot -p
Enter password:

中に入って確認。

mysql> use okwra;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------+
| Tables_in_okwra  |
+------------------+
| goods            |
| purchase_history |
| user             |
+------------------+
3 rows in set (0.00 sec)

余談

会社の方のAdvent Calenderに記事を書きました。
クリスマスイブに20ブクマまで行ったので勝ちです。(何が?) techblog.kayac.com