MySQLで大量のテーブルを作るに当たって、Oracleみたいにテキストファイルを渡して処理を実行できないかなぁと思って調べたら、やはり同じ様な方法は存在したので、メモ。
コマンドライン上でファイルに記載したSQLを実行
$ mysql 「DB名」 < 「対象ファイル」
※対象ファイルの中で`use 「DB名」`を実行している場合は「DB名」を省略可能
実施例
[mysql8@vmora112 ~]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.13 MySQL Community Server - GPL
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)
mysql> exit
Bye
[mysql8@vmora112 ~]$
[mysql8@vmora112 ~]$ cat create_user.ddl ★実行するコマンドの中身★
CREATE USER 'iwate'@'%' identified by 'Mysql_8!';
GRANT ALL PRIVILEGES ON *.* TO 'iwate'@'%' WITH GRANT OPTION;
[mysql8@vmora112 ~]$
[mysql8@vmora112 ~]$ mysql -uroot -p < create_user.ddl ★スクリプトファイルを渡して実行★
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.13 MySQL Community Server - GPL
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| iwate | % | ★作成したユーザーが存在★
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
MySQLに接続した状態で実行
> source 「対象ファイル」
もしくは
> \. 「対象ファイル」
sourceコマンド実施例
[mysql8@vmora112 ~]$ cat createDB.ddl ★実行するコマンド★
create database test;
[mysql8@vmora112 ~]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.13 MySQL Community Server - GPL
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> source createDB.ddl ★sourceコマンドでスクリプトファイルを実行★
Query OK, 1 row affected (0.11 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test | ★作成したDBが存在★
+--------------------+
5 rows in set (0.00 sec)
\. 方式実施例
[mysql8@vmora112 ~]$
[mysql8@vmora112 ~]$ cat create_table_juve.ddl ★実行するコマンド★
CREATE TABLE juve(
id_no numeric(6) NOT NULL COMMENT 'ID',
no VARCHAR(5) NOT NULL COMMENT '背番号',
fir_name VARCHAR(25) NULL COMMENT '名前',
fam_name VARCHAR(25) NULL COMMENT '姓',
position VARCHAR(3) NOT NULL COMMENT 'ポジション',
PRIMARY KEY (id_no)
)COMMENT 'ユベントス';
[mysql8@vmora112 ~]$
[mysql8@vmora112 ~]$ mysql -uiwate -p test
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.13 MySQL Community Server - GPL
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show tables;
Empty set (0.00 sec) ★テーブルは存在しない★
mysql>
mysql> \. create_table_juve.ddl ★「\.」でスクリプトファイルを実行★
Query OK, 0 rows affected (0.08 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| juve | ★テーブルが作成されている★
+----------------+
1 row in set (0.00 sec)
showコマンドを実行するときにoracleのクセでsho ○○と打ってエラーに出しちゃう人って結構いそうな気がする･･･。
少なくとも自分は結構な頻度でやってしまう(笑)。