Vagrant – phpMyAdminのインストール – CentOS7

2021-07-25 / tau / コメントする

phpMyAdminのインストール

PHPをRemiリポジトリーからインストールしているので、phpMyAdminもRemiリポジトリーからインストールする。

[vagrant@localhost ~]$ sudo yum install --enablerepo=remi-php73 phpMyAdmin

1	[vagrant@localhost ~]$ sudo yum install --enablerepo=remi-php73 phpMyAdmin

ポートの設定

Vagrrant環境の場合、VagrantfileでApacheで使うポートを設定しておく必要がある。

Apacheの起動確認

Apacheの起動状態を確認。

[vagrant@localhost ~]$ sudo service httpd status
Redirecting to /bin/systemctl status httpd.service
● httpd.service - The Apache HTTP Server
   Loaded: loaded (/usr/lib/systemd/system/httpd.service; disabled; vendor preset: disabled)
   Active: inactive (dead)
     Docs: man:httpd(8)
           man:apachectl(8)

[vagrant@localhost ~]$ sudo service httpd status

Redirecting to /bin/systemctl status httpd.service

● httpd.service - The Apache HTTP Server

Loaded: loaded (/usr/lib/systemd/system/httpd.service; disabled; vendor preset: disabled)

Active: inactive (dead)

Docs: man:httpd(8)

man:apachectl(8)

停止していればApacheを起動。

[vagrant@localhost ~]$ sudo service httpd start
Redirecting to /bin/systemctl start httpd.service
[vagrant@localhost ~]$ sudo service httpd status
Redirecting to /bin/systemctl status httpd.service
● httpd.service - The Apache HTTP Server
   Loaded: loaded (/usr/lib/systemd/system/httpd.service; disabled; vendor preset: disabled)
   Active: active (running) since 日 2021-07-25 15:57:37 JST; 4s ago
     Docs: man:httpd(8)
           man:apachectl(8)
 Main PID: 19378 (httpd)
   Status: "Processing requests..."
   CGroup: /system.slice/httpd.service
           ├─19378 /usr/sbin/httpd -DFOREGROUND
           ├─19379 /usr/sbin/httpd -DFOREGROUND
           ├─19380 /usr/sbin/httpd -DFOREGROUND
           ├─19381 /usr/sbin/httpd -DFOREGROUND
           ├─19382 /usr/sbin/httpd -DFOREGROUND
           └─19383 /usr/sbin/httpd -DFOREGROUND

 7月 25 15:57:36 localhost.localdomain systemd[1]: Starting The Apache HTTP Server...
 7月 25 15:57:36 localhost.localdomain httpd[19378]: AH00558: httpd: Could not reliably determine the serve...age
 7月 25 15:57:37 localhost.localdomain systemd[1]: Started The Apache HTTP Server.
Hint: Some lines were ellipsized, use -l to show in full.

[vagrant@localhost ~]$ sudo service httpd start

Redirecting to /bin/systemctl start httpd.service

[vagrant@localhost ~]$ sudo service httpd status

Redirecting to /bin/systemctl status httpd.service

● httpd.service - The Apache HTTP Server

Loaded: loaded (/usr/lib/systemd/system/httpd.service; disabled; vendor preset: disabled)

Active: active (running) since 日 2021-07-25 15:57:37 JST; 4s ago

Docs: man:httpd(8)

man:apachectl(8)

Main PID: 19378 (httpd)

Status: "Processing requests..."

CGroup: /system.slice/httpd.service

├─19378 /usr/sbin/httpd -DFOREGROUND

├─19379 /usr/sbin/httpd -DFOREGROUND

├─19380 /usr/sbin/httpd -DFOREGROUND

├─19381 /usr/sbin/httpd -DFOREGROUND

├─19382 /usr/sbin/httpd -DFOREGROUND

└─19383 /usr/sbin/httpd -DFOREGROUND

7月 25 15:57:36 localhost.localdomain systemd[1]: Starting The Apache HTTP Server...

7月 25 15:57:36 localhost.localdomain httpd[19378]: AH00558: httpd: Could not reliably determine the serve...age

7月 25 15:57:37 localhost.localdomain systemd[1]: Started The Apache HTTP Server.

Hint: Some lines were ellipsized, use -l to show in full.

403エラー

ここまでの状態でlocalhost:8080/phpMyAdminにアクセスすると、403エラーになった。

phpMyAdmin.confの修正

/etc/httpd/conf.d/phpMyAdmin.confを以下のように修正する。

# phpMyAdmin - Web based MySQL browser written in php
#
# Allows only localhost by default
#
# But allowing phpMyAdmin to anyone other than localhost should be considered
# dangerous unless properly secured by SSL

Alias /phpMyAdmin /usr/share/phpMyAdmin
Alias /phpmyadmin /usr/share/phpMyAdmin

<Directory /usr/share/phpMyAdmin/>
   AddDefaultCharset UTF-8
   AllowOverride all   →追加
   Require all granted →追加

   <IfModule mod_authz_core.c>
     # Apache 2.4
     <RequireAny>
       Require ip 127.0.0.1
       Require ip ::1
     </RequireAny>
   </IfModule>
   <IfModule !mod_authz_core.c>
     # Apache 2.2
     # Order Deny,Allow     →コメントアウト
     # Deny from All        →コメントアウト
     # Allow from 127.0.0.1 →コメントアウト
     # Allow from ::1       →コメントアウト
     Order Allow,Deny       →追加
     Allow from All         →追加
   </IfModule>
</Directory>
.....

# phpMyAdmin - Web based MySQL browser written in php

# Allows only localhost by default

# But allowing phpMyAdmin to anyone other than localhost should be considered

# dangerous unless properly secured by SSL

Alias /phpMyAdmin /usr/share/phpMyAdmin

Alias /phpmyadmin /usr/share/phpMyAdmin

AddDefaultCharset UTF-8

AllowOverride all →追加

Require all granted →追加

# Apache 2.4

Require ip 127.0.0.1

Require ip ::1

</RequireAny>

</IfModule>

# Apache 2.2

# Order Deny,Allow →コメントアウト

# Deny from All →コメントアウト

# Allow from 127.0.0.1 →コメントアウト

# Allow from ::1 →コメントアウト

Order Allow,Deny →追加

Allow from All →追加

</IfModule>

</Directory>

.....

修正後はApacheを再起動。

[vagrant@localhost ~]$ sudo service httpd restart
Redirecting to /bin/systemctl restart httpd.service

1 2	[vagrant@localhost ~]$ sudo service httpd restart Redirecting to /bin/systemctl restart httpd.service

起動成功

localhost:8080/phpMyAdminでログイン画面が表示される。

Laravel – データベース – 準備

2021-06-28 / tau / コメントする

概要

Laravelのプロジェクト開始時の、データベースの準備・設定についてまとめる。

データベースの準備

CREATE DATABASEなどによってプロジェクトで使うデータベースを作成する。以下は新たなデータベースlaravel_test_developmentを作成した例。

mysql> CREATE DATABASE laravel_test;
Query OK, 1 row affected (0.01 sec)

1 2	mysql> CREATE DATABASE laravel_test; Query OK, 1 row affected (0.01 sec)

.envファイルの修正

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laravel_test←作成したデータベース名
DB_USERNAME=root
DB_PASSWORD=ログインパスワード

DB_CONNECTION=mysql

DB_HOST=127.0.0.1

DB_PORT=3306

DB_DATABASE=laravel_test←作成したデータベース名

DB_USERNAME=root

DB_PASSWORD=ログインパスワード

データベース接続確認

php artisan migrateでマイグレーションが実行されればデータベースは正常に接続されている。

[vagrant@localhost laravel_test]$ php artisan migrate
Migration table created successfully.
Migrating: 2014_10_12_000000_create_users_table
Migrated:  2014_10_12_000000_create_users_table (0.03 seconds)
Migrating: 2014_10_12_100000_create_password_resets_table
Migrated:  2014_10_12_100000_create_password_resets_table (0.03 seconds)
Migrating: 2019_08_19_000000_create_failed_jobs_table
Migrated:  2019_08_19_000000_create_failed_jobs_table (0.01 seconds)

[vagrant@localhost laravel_test]$ php artisan migrate

Migration table created successfully.

Migrating: 2014_10_12_000000_create_users_table

Migrated: 2014_10_12_000000_create_users_table (0.03 seconds)

Migrating: 2014_10_12_100000_create_password_resets_table

Migrated: 2014_10_12_100000_create_password_resets_table (0.03 seconds)

Migrating: 2019_08_19_000000_create_failed_jobs_table

Migrated: 2019_08_19_000000_create_failed_jobs_table (0.01 seconds)

マイグレーションの結果生成されたファイル。

[vagrant@localhost laravel_test]$ ls -al database/migrations
合計 12
drwxrwxr-x. 2 vagrant vagrant 166  5月 12 05:47 .
drwxrwxr-x. 5 vagrant vagrant  72  5月 12 05:47 ..
-rw-rw-r--. 1 vagrant vagrant 813  5月 12 05:47 2014_10_12_000000_create_users_table.php
-rw-rw-r--. 1 vagrant vagrant 683  5月 12 05:47 2014_10_12_100000_create_password_resets_table.php
-rw-rw-r--. 1 vagrant vagrant 789  5月 12 05:47 2019_08_19_000000_create_failed_jobs_table.php

[vagrant@localhost laravel_test]$ ls -al database/migrations

合計 12

drwxrwxr-x. 2 vagrant vagrant 166 5月 12 05:47 .

drwxrwxr-x. 5 vagrant vagrant 72 5月 12 05:47 ..

-rw-rw-r--. 1 vagrant vagrant 813 5月 12 05:47 2014_10_12_000000_create_users_table.php

-rw-rw-r--. 1 vagrant vagrant 683 5月 12 05:47 2014_10_12_100000_create_password_resets_table.php

-rw-rw-r--. 1 vagrant vagrant 789 5月 12 05:47 2019_08_19_000000_create_failed_jobs_table.php

MySQL側でもテーブルが生成されている。

mysql> use laravel_test
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_laravel_test |
+------------------------+
| failed_jobs            |
| migrations             |
| password_resets        |
| users                  |
+------------------------+
5 rows in set (0.00 sec)

mysql> use laravel_test

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_laravel_test |

+------------------------+

| failed_jobs |

| migrations |

| password_resets |

| users |

+------------------------+

5 rows in set (0.00 sec)

データベースに接続できないとき

MySQLとPHPのバージョンによっては、認証方法の違いで接続できない場合がある。その対応方法はこちら。

Laravel – データベースに接続できない

2021-06-28 / tau / コメントする

エラー発生

Laravel導入直後にデータベースを接続しようとしたところ、エラーが発生した。

[vagrant@localhost laravel_test]$ php artisan migrate

   Illuminate\Database\QueryException  : SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client (SQL: select * from information_schema.tables where table_schema = laravel_test_development and table_name = migrations and table_type = 'BASE TABLE')

  at /home/vagrant/app/laravel/laravel_test/vendor/laravel/framework/src/Illuminate/Database/Connection.php:669
    665|         // If an exception occurs when attempting to run a query, we'll format the error
    666|         // message to include the bindings with SQL, which will make this exception a
    667|         // lot more helpful to the developer instead of just the database's errors.
    668|         catch (Exception $e) {
  > 669|             throw new QueryException(
    670|                 $query, $this->prepareBindings($bindings), $e
    671|             );
    672|         }
    673|

  Exception trace:

  1   PDOException::("PDO::__construct(): The server requested authentication method unknown to the client [caching_sha2_password]")
      /home/vagrant/app/laravel/laravel_test/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php:70

  2   PDO::__construct("mysql:host=127.0.0.1;port=3306;dbname=laravel_test_development", "root", "vagrant", [])
      /home/vagrant/app/laravel/laravel_test/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php:70

  Please use the argument -v to see more details.

[vagrant@localhost laravel_test]$ php artisan migrate

Illuminate\Database\QueryException : SQLSTATE[HY000] [2054] The server requested authentication method unknown to the client (SQL: select * from information_schema.tables where table_schema = laravel_test_development and table_name = migrations and table_type = 'BASE TABLE')

at /home/vagrant/app/laravel/laravel_test/vendor/laravel/framework/src/Illuminate/Database/Connection.php:669

665| // If an exception occurs when attempting to run a query, we'll format the error

666| // message to include the bindings with SQL, which will make this exception a

667| // lot more helpful to the developer instead of just the database's errors.

668| catch (Exception $e) {

> 669| throw new QueryException(

670| $query, $this->prepareBindings($bindings), $e

671| );

672| }

673|

Exception trace:

1 PDOException::("PDO::__construct(): The server requested authentication method unknown to the client [caching_sha2_password]")

/home/vagrant/app/laravel/laravel_test/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php:70

2 PDO::__construct("mysql:host=127.0.0.1;port=3306;dbname=laravel_test_development", "root", "vagrant", [])

/home/vagrant/app/laravel/laravel_test/vendor/laravel/framework/src/Illuminate/Database/Connectors/Connector.php:70

Please use the argument -v to see more details.

MySQLのバージョンは8.0.23、PHPのバージョンは7.3.28で、MySQLのデフォルトの認証はcacing_sha2_passwordだが、PHPは7.3でもこれに対応しておらずmysql_native_passwordらしい。

MySQLの認証方法変更

認証方法の確認。

mysql> SELECT user, host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
4 rows in set (0.00 sec)

mysql> SELECT user, host, plugin FROM mysql.user;

+------------------+-----------+-----------------------+

| user | host | plugin |

+------------------+-----------+-----------------------+

| mysql.infoschema | localhost | caching_sha2_password |

| mysql.session | localhost | caching_sha2_password |

| mysql.sys | localhost | caching_sha2_password |

| root | localhost | caching_sha2_password |

+------------------+-----------+-----------------------+

4 rows in set (0.00 sec)

rootの認証方法をmysql_native_passwordに変更しようとするが、エラー。

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'vagrant';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

1 2	mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'vagrant'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

パスワードがポリシーに適合していない。これは新たにMySQLをインストールした時にある状況。パスワード検証内容を確認してみる。

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'validate_password%';

+--------------------------------------+--------+

| Variable_name | Value |

+--------------------------------------+--------+

| validate_password.check_user_name | ON |

| validate_password.dictionary_file | |

| validate_password.length | 8 |

| validate_password.mixed_case_count | 1 |

| validate_password.number_count | 1 |

| validate_password.policy | MEDIUM |

| validate_password.special_char_count | 1 |

+--------------------------------------+--------+

7 rows in set (0.00 sec)

設定するパスワードに合わせてポリシーなどを変更。

mysql> SET GLOBAL validate_password.policy=LOW;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL validate_password.length=6;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password.check_user_name    | ON    |
| validate_password.dictionary_file    |       |
| validate_password.length             | 6     |
| validate_password.mixed_case_count   | 1     |
| validate_password.number_count       | 1     |
| validate_password.policy             | LOW   |
| validate_password.special_char_count | 1     |
+--------------------------------------+-------+
7 rows in set (0.00 sec)

mysql> SET GLOBAL validate_password.policy=LOW;

Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL validate_password.length=6;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'validate_password%';

+--------------------------------------+-------+

| Variable_name | Value |

+--------------------------------------+-------+

| validate_password.check_user_name | ON |

| validate_password.dictionary_file | |

| validate_password.length | 6 |

| validate_password.mixed_case_count | 1 |

| validate_password.number_count | 1 |

| validate_password.policy | LOW |

| validate_password.special_char_count | 1 |

+--------------------------------------+-------+

7 rows in set (0.00 sec)

改めてrootユーザーの認証方法を変更・確認。

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'パスワード';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT USER, host, plugin FROM mysql.user;
+------------------+-----------+-----------------------+
| USER             | host      | plugin                |
+------------------+-----------+-----------------------+
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | mysql_native_password |
+------------------+-----------+-----------------------+
4 rows in set (0.00 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'パスワード';

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT USER, host, plugin FROM mysql.user;

+------------------+-----------+-----------------------+

| USER | host | plugin |

+------------------+-----------+-----------------------+

| mysql.infoschema | localhost | caching_sha2_password |

| mysql.session | localhost | caching_sha2_password |

| mysql.sys | localhost | caching_sha2_password |

| root | localhost | mysql_native_password |

+------------------+-----------+-----------------------+

4 rows in set (0.00 sec)

これでPHP7.3以下で接続可能になる。

MySQL – パスワード検証

2021-06-28 / tau / コメントする

概要

MySQLでユーザーのパスワードを設定する際に、その長さや使われるべき文字数などについて求める内容を設定できる。設定内容は以下のとおり。

ポリシー：長さ、文字種類、辞書のいずれを使うか
最小長さ
大文字・小文字の最小数
数字の最小数
記号の最小数
辞書ファイルへのパス

パスワード検証の確認

現在のパスワード検証の内容は以下で確認できる。

SHOW VARIABLES LIKE 'validate_password%';

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'validate_password%';

+--------------------------------------+--------+

| Variable_name | Value |

+--------------------------------------+--------+

| validate_password.check_user_name | ON |

| validate_password.dictionary_file | |

| validate_password.length | 8 |

| validate_password.mixed_case_count | 1 |

| validate_password.number_count | 1 |

| validate_password.policy | MEDIUM |

| validate_password.special_char_count | 1 |

+--------------------------------------+--------+

7 rows in set (0.00 sec)

それぞれの指定内容は以下のとおり。

check_user_name

ユーザー名と一致する場合に拒否するかどうか。

dictionary_file

パスワードチェックに用いる辞書へのパス。デフォルトでは空で辞書チェックは行われない。

length

パスワードの最小文字数。

mixed_case_count

パスワードポリシーがMEDIUM以上の場合に求める大文字・小文字の最小数。

number_count

パスワードポリシーがMEDIUM以上の場合に求める数字の最小数

policy

LOW(0)：長さのみ
MEDIUM(1)：長さ、数値・大文字・小文字・特殊文字
STRONG(2)：長さ、数値・大文字・小文字・特殊文字、辞書ファイル

special_char_count

ポリシーがMEDIUM以上の場合に求める記号の最小数。

パスワード検証内容の変更

それぞれの検証内容は、対応する変数の値をセットすることで変更する。

パスワードポリシー

SET GLOBAL validate_password.policy=LOW/MEDIUM/STRONG;

最小長さ

SET GLOBAL validate_password.length=最小長さ;

など。

MySQL – リレーションを持つCSVのインポート

2021-06-20 / tau / コメントする

概要

参照関係を持つテーブルにCSVデータをインポートすることを考える。

親テーブルの主キーにAUTO_INCREMENTが設定されていて、子テーブルがそのキーを参照するリレーションを対象とする
データの追加ではなく、空のテーブルに新たにデータを登録するケースを対象とする

テーブル構成

以下のようなテーブル構造とする。

branchesテーブルは支社に関する情報を記録する
- AUTO_INCREMENTの支社ID(プライマリーキー)
- 支社名
warehousesテーブルは支社に属する倉庫に関する情報を記録する
- AUTO_INCREMENTの倉庫ID(プライマリーキー)
- 倉庫が属する支社を参照する支社ID(外部キー)
- 倉庫名
customersテーブルは倉庫から出荷される先の顧客に関する情報を記録する
- AUTO_INCREMENTの顧客ID(プライマリーキー)
- 顧客を担当する倉庫のID(外部キー)
- 顧客名

ExcelでCSVファイルの作成

元データの入力

以下の3つのテーブルデータを別のシートに入力する。

＜branchesテーブル＞

id	name
1	関東支社
2	関西支社

＜warehousesテーブル＞

id	branch_id	name
1	1	千葉倉庫
2	1	八王子倉庫
3	2	東大阪倉庫
4	2	神戸倉庫

＜customersテーブル＞

id	warehouse_id	customer
1	1	山田商会
2	1	田中屋
3	2	中川商店
4	2	川井製作所
5	3	井戸商店
6	3	戸田商会
7	4	田村屋
8	4	村山製作所

CSVファイルの保存

3つのシートそれぞれをCSVファイルで保存する。

名前を付けて保存
ファイルの種類は”CSV UTF-8 (コンマ区切り)”
シートごとに別々のCSVファイルに保存

BOMなしへの変換

ExcelのUTF-8はBOM付きで保存されるので、これをBOMなしのUTF-8に変換する。

たとえば、Windowsのメモ帳で読み込んでUTF-8(BOM付きではない方)で保存し直すなど。

ファイルの提供

外部サーバーの場合、FTPで所定の場所にCSVファイルをアップロード。

Vagrant仮想環境の場合、保存されたCSVファイルを共有ディレクトリーにコピーまたは移動。

MySQLでの操作

ローカルファイルを許可してログイン

MySQLにログインするのに、--enable-local-infileを指定してローカルファイルからの入力を許可。

[vagrant@localhost ~]$ mysql -u root -p --enable-local-infile

1	[vagrant@localhost ~]$ mysql -u root -p --enable-local-infile

MySQL内でローカルファイルの入力許可

@@local_infile変数の内容をチェックする。0の場合はローカルファイル入力が許可されていないので、SET GLOBALで許可する。

mysql> SELECT @@local_infile;
+----------------+
| @@local_infile |
+----------------+
|              0 |
+----------------+
1 row in set (0.00 sec)

mysql> SET GLOBAL local_infile=on;

mysql> SELECT @@local_infile;
+----------------+
| @@local_infile |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT @@local_infile;

+----------------+

| @@local_infile |

+----------------+

| 0 |

+----------------+

1 row in set (0.00 sec)

mysql> SET GLOBAL local_infile=on;

mysql> SELECT @@local_infile;

+----------------+

| @@local_infile |

+----------------+

| 1 |

+----------------+

1 row in set (0.00 sec)

テーブルの準備

CREATE TABLEでCSVデータを読み込むテーブルを準備する。

CREATE TABLE branches (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20)
);

CREATE TABLE warehouses (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  branch_id INT,
  name VARCHAR(20),
  FOREIGN KEY(branch_id) REFERENCES branches(id)
);

CREATE TABLE customers (
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  warehouse_id INT,
  name VARCHAR(20),
  FOREIGN KEY(warehouse_id) REFERENCES warehouses(id)
);

CREATE TABLE branches (

id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(20)

);

CREATE TABLE warehouses (

id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

branch_id INT,

name VARCHAR(20),

FOREIGN KEY(branch_id) REFERENCES branches(id)

);

CREATE TABLE customers (

id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,

warehouse_id INT,

name VARCHAR(20),

FOREIGN KEY(warehouse_id) REFERENCES warehouses(id)

);

作成したテーブルの構造。

mysql> DESCRIBE branches;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> DESCRIBE warehouses;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int         | NO   | PRI | NULL    | auto_increment |
| branch_id | int         | YES  | MUL | NULL    |                |
| name      | varchar(20) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> DESCRIBE customers;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int         | NO   | PRI | NULL    | auto_increment |
| warehouse_id | int         | YES  | MUL | NULL    |                |
| customer     | varchar(20) | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> DESCRIBE branches;

+-------+-------------+------+-----+---------+----------------+

+-------+-------------+------+-----+---------+----------------+

| id | int | NO | PRI | NULL | auto_increment |

+-------+-------------+------+-----+---------+----------------+

2 rows in set (0.00 sec)

mysql> DESCRIBE warehouses;

+-----------+-------------+------+-----+---------+----------------+

+-----------+-------------+------+-----+---------+----------------+

| id | int | NO | PRI | NULL | auto_increment |

| branch_id | int | YES | MUL | NULL | |

+-----------+-------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)

mysql> DESCRIBE customers;

+--------------+-------------+------+-----+---------+----------------+

+--------------+-------------+------+-----+---------+----------------+

| id | int | NO | PRI | NULL | auto_increment |

| warehouse_id | int | YES | MUL | NULL | |

+--------------+-------------+------+-----+---------+----------------+

3 rows in set (0.00 sec)

CSVファイルの読み込み

アップロードまたは共有されたCSVファイルをLOAD DATA LOCAL INFILEクエリーで読み込む。

カンマ区切り→FILES TERMINATED BY ','
改行コード→LINES TERMINATED BY '\r\n'
1行目が見出し行の場合→IGNORE 1 LINES

LOAD DATA LOCAL INFILE "~/branches.csv"
INTO TABLE branches
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

LOAD DATA LOCAL INFILE "~/warehouses.csv"
INTO TABLE warehouses
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

LOAD DATA LOCAL INFILE "~/customers.csv"
INTO TABLE customers
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

LOAD DATA LOCAL INFILE "~/branches.csv"

INTO TABLE branches

FIELDS TERMINATED BY ','

LINES TERMINATED BY '\r\n'

IGNORE 1 LINES;

LOAD DATA LOCAL INFILE "~/warehouses.csv"

INTO TABLE warehouses

FIELDS TERMINATED BY ','

LINES TERMINATED BY '\r\n'

IGNORE 1 LINES;

LOAD DATA LOCAL INFILE "~/customers.csv"

INTO TABLE customers

FIELDS TERMINATED BY ','

LINES TERMINATED BY '\r\n'

IGNORE 1 LINES;

読み込み結果の確認

各テーブルデータの確認

mysql> SELECT * FROM branches;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 関東支社     |
|  2 | 関西支社     |
+----+--------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM warehouses;
+----+-----------+-----------------+
| id | branch_id | name            |
+----+-----------+-----------------+
|  1 |         1 | 千葉倉庫        |
|  2 |         1 | 八王子倉庫      |
|  3 |         2 | 東大阪倉庫      |
|  4 |         2 | 神戸倉庫        |
+----+-----------+-----------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM customers;
+----+--------------+-----------------+
| id | warehouse_id | customer        |
+----+--------------+-----------------+
|  1 |            1 | 山田商会        |
|  2 |            1 | 田中屋          |
|  3 |            2 | 中川商店        |
|  4 |            2 | 川井製作所      |
|  5 |            3 | 井戸商店        |
|  6 |            3 | 戸田商会        |
|  7 |            4 | 田村屋          |
|  8 |            4 | 村山製作所      |
+----+--------------+-----------------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM branches;

+----+--------------+

| id | name |

+----+--------------+

| 1 | 関東支社 |

| 2 | 関西支社 |

+----+--------------+

2 rows in set (0.00 sec)

mysql> SELECT * FROM warehouses;

+----+-----------+-----------------+

| id | branch_id | name |

+----+-----------+-----------------+

| 1 | 1 | 千葉倉庫 |

| 2 | 1 | 八王子倉庫 |

| 3 | 2 | 東大阪倉庫 |

| 4 | 2 | 神戸倉庫 |

+----+-----------+-----------------+

4 rows in set (0.00 sec)

mysql> SELECT * FROM customers;

+----+--------------+-----------------+

| id | warehouse_id | customer |

+----+--------------+-----------------+

| 1 | 1 | 山田商会 |

| 2 | 1 | 田中屋 |

| 3 | 2 | 中川商店 |

| 4 | 2 | 川井製作所 |

| 5 | 3 | 井戸商店 |

| 6 | 3 | 戸田商会 |

| 7 | 4 | 田村屋 |

| 8 | 4 | 村山製作所 |

+----+--------------+-----------------+

8 rows in set (0.00 sec)

参照関係の確認

外部キーによる参照関係の確認。各外部キーと親テーブルのキーでテーブルを結合。

SELECT
  branches.id,
  branches.name,
  warehouses.id,
  warehouses.name,
  customers.id,
  customers.name
FROM customers
INNER JOIN warehouses
ON warehouses.id = warehouse_id
INNER JOIN branches
ON branches.id = branch_id;

SELECT

branches.id,

branches.name,

warehouses.id,

warehouses.name,

customers.id,

customers.name

FROM customers

INNER JOIN warehouses

ON warehouses.id = warehouse_id

INNER JOIN branches

ON branches.id = branch_id;

実行結果。

mysql> SELECT
    ->   branches.id,
    ->   branches.name,
    ->   warehouses.id,
    ->   warehouses.name,
    ->   customers.id,
    ->   customers.name
    -> FROM customers
    -> INNER JOIN warehouses
    -> ON warehouses.id = warehouse_id
    -> INNER JOIN branches
    -> ON branches.id = branch_id;
+----+--------------+----+-----------------+----+-----------------+
| id | name         | id | name            | id | name            |
+----+--------------+----+-----------------+----+-----------------+
|  1 | 関東支社     |  1 | 千葉倉庫        |  1 | 山田商会        |
|  1 | 関東支社     |  1 | 千葉倉庫        |  2 | 田中屋          |
|  1 | 関東支社     |  2 | 八王子倉庫      |  3 | 中川商店        |
|  1 | 関東支社     |  2 | 八王子倉庫      |  4 | 川井製作所      |
|  2 | 関西支社     |  3 | 東大阪倉庫      |  5 | 井戸商店        |
|  2 | 関西支社     |  3 | 東大阪倉庫      |  6 | 戸田商会        |
|  2 | 関西支社     |  4 | 神戸倉庫        |  7 | 田村屋          |
|  2 | 関西支社     |  4 | 神戸倉庫        |  8 | 村山製作所      |
+----+--------------+----+-----------------+----+-----------------+
8 rows in set (0.00 sec)

mysql> SELECT

-> branches.id,

-> branches.name,

-> warehouses.id,

-> warehouses.name,

-> customers.id,

-> customers.name

-> FROM customers

-> INNER JOIN warehouses

-> ON warehouses.id = warehouse_id

-> INNER JOIN branches

-> ON branches.id = branch_id;

+----+--------------+----+-----------------+----+-----------------+

| id | name | id | name | id | name |

+----+--------------+----+-----------------+----+-----------------+

| 1 | 関東支社 | 1 | 千葉倉庫 | 1 | 山田商会 |

| 1 | 関東支社 | 1 | 千葉倉庫 | 2 | 田中屋 |

| 1 | 関東支社 | 2 | 八王子倉庫 | 3 | 中川商店 |

| 1 | 関東支社 | 2 | 八王子倉庫 | 4 | 川井製作所 |

| 2 | 関西支社 | 3 | 東大阪倉庫 | 5 | 井戸商店 |

| 2 | 関西支社 | 3 | 東大阪倉庫 | 6 | 戸田商会 |

| 2 | 関西支社 | 4 | 神戸倉庫 | 7 | 田村屋 |

| 2 | 関西支社 | 4 | 神戸倉庫 | 8 | 村山製作所 |

+----+--------------+----+-----------------+----+-----------------+

8 rows in set (0.00 sec)

新規追加の確認

たとえば新たな支社を1つ追加し、AUTO_INCREMENTが機能していることを確認。

mysql> INSERT INTO branches (name) VALUES ('九州支社');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM branches;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 関東支社     |
|  2 | 関西支社     |
|  3 | 九州支社     |
+----+--------------+
3 rows in set (0.00 sec)

mysql> INSERT INTO branches (name) VALUES ('九州支社');

Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM branches;

+----+--------------+

| id | name |

+----+--------------+

| 1 | 関東支社 |

| 2 | 関西支社 |

| 3 | 九州支社 |

+----+--------------+

3 rows in set (0.00 sec)

外部結合してみる。

SELECT
  branches.id,
  branches.name,
  warehouses.id,
  warehouses.name,
  customers.id,
  customers.name
FROM customers
RIGHT OUTER JOIN warehouses
ON warehouses.id = warehouse_id
RIGHT OUTER JOIN branches
ON branches.id = branch_id;

SELECT

branches.id,

branches.name,

warehouses.id,

warehouses.name,

customers.id,

customers.name

FROM customers

RIGHT OUTER JOIN warehouses

ON warehouses.id = warehouse_id

RIGHT OUTER JOIN branches

ON branches.id = branch_id;

mysql> SELECT
    ->   branches.id,
    ->   branches.name,
    ->   warehouses.id,
    ->   warehouses.name,
    ->   customers.id,
    ->   customers.name
    -> FROM customers
    -> RIGHT OUTER JOIN warehouses
    -> ON warehouses.id = warehouse_id
    -> RIGHT OUTER JOIN branches
    -> ON branches.id = branch_id;
+----+--------------+------+-----------------+------+-----------------+
| id | name         | id   | name            | id   | name            |
+----+--------------+------+-----------------+------+-----------------+
|  1 | 関東支社     |    1 | 千葉倉庫        |    1 | 山田商会        |
|  1 | 関東支社     |    1 | 千葉倉庫        |    2 | 田中屋          |
|  1 | 関東支社     |    2 | 八王子倉庫      |    3 | 中川商店        |
|  1 | 関東支社     |    2 | 八王子倉庫      |    4 | 川井製作所      |
|  2 | 関西支社     |    3 | 東大阪倉庫      |    5 | 井戸商店        |
|  2 | 関西支社     |    3 | 東大阪倉庫      |    6 | 戸田商会        |
|  2 | 関西支社     |    4 | 神戸倉庫        |    7 | 田村屋          |
|  2 | 関西支社     |    4 | 神戸倉庫        |    8 | 村山製作所      |
|  3 | 九州支社     | NULL | NULL            | NULL | NULL            |
+----+--------------+------+-----------------+------+-----------------+
9 rows in set (0.00 sec)

mysql> SELECT

-> branches.id,

-> branches.name,

-> warehouses.id,

-> warehouses.name,

-> customers.id,

-> customers.name

-> FROM customers

-> RIGHT OUTER JOIN warehouses

-> ON warehouses.id = warehouse_id

-> RIGHT OUTER JOIN branches

-> ON branches.id = branch_id;

+----+--------------+------+-----------------+------+-----------------+

| id | name | id | name | id | name |

+----+--------------+------+-----------------+------+-----------------+

| 1 | 関東支社 | 1 | 千葉倉庫 | 1 | 山田商会 |

| 1 | 関東支社 | 1 | 千葉倉庫 | 2 | 田中屋 |

| 1 | 関東支社 | 2 | 八王子倉庫 | 3 | 中川商店 |

| 1 | 関東支社 | 2 | 八王子倉庫 | 4 | 川井製作所 |

| 2 | 関西支社 | 3 | 東大阪倉庫 | 5 | 井戸商店 |

| 2 | 関西支社 | 3 | 東大阪倉庫 | 6 | 戸田商会 |

| 2 | 関西支社 | 4 | 神戸倉庫 | 7 | 田村屋 |

| 2 | 関西支社 | 4 | 神戸倉庫 | 8 | 村山製作所 |

+----+--------------+------+-----------------+------+-----------------+

9 rows in set (0.00 sec)

Vagrant – MySQLインストール – FreeBSD

2021-06-20 / tau / コメントする

pkgによるインストール

FreeBSDのバージョンが古いと言われたが、ミスマッチを無視することができるようなので無視して続行。

$ sudo pkg install mysql57-server
Updating FreeBSD repository catalogue...
Fetching meta.conf: 100%    163 B   0.2kB/s    00:01
Fetching packagesite.txz: 100%    6 MiB   1.5MB/s    00:04
Processing entries:   0%
Newer FreeBSD version for package libbigwig:
To ignore this error set IGNORE_OSVERSION=yes
- package: 1104001
- running kernel: 1102000
Ignore the mismatch and continue? [y/N]: y
Processing entries: 100%
FreeBSD repository update completed. 29008 packages processed.
All repositories are up to date.
Updating database digests format: 100%
The following 8 package(s) will be affected (of 0 checked):

New packages to be INSTALLED:
        cyrus-sasl: 2.1.27_1
        libedit: 3.1.20210216,1
        libevent: 2.1.12
        liblz4: 1.9.3,1
        mysql57-client: 5.7.34
        mysql57-server: 5.7.34
        perl5: 5.32.1_1
        protobuf: 3.14.0,1

Number of packages to be installed: 8

The process will require 286 MiB more space.
35 MiB to be downloaded.

Proceed with this action? [y/N]: y
[1/8] Fetching mysql57-server-5.7.34.txz: 100%   14 MiB   2.5MB/s    00:06
[2/8] Fetching perl5-5.32.1_1.txz: 100%   15 MiB   3.1MB/s    00:05
[3/8] Fetching protobuf-3.14.0,1.txz: 100%    3 MiB   2.9MB/s    00:01
[4/8] Fetching libevent-2.1.12.txz: 100%  320 KiB 327.7kB/s    00:01
[5/8] Fetching libedit-3.1.20210216,1.txz: 100%  138 KiB 141.0kB/s    00:01
[6/8] Fetching mysql57-client-5.7.34.txz: 100%    2 MiB   1.1MB/s    00:02
[7/8] Fetching cyrus-sasl-2.1.27_1.txz: 100%  964 KiB 986.7kB/s    00:01
[8/8] Fetching liblz4-1.9.3,1.txz: 100%  131 KiB 134.1kB/s    00:01
Checking integrity... done (0 conflicting)
[1/8] Installing perl5-5.32.1_1...
[1/8] Extracting perl5-5.32.1_1: 100%
[2/8] Installing protobuf-3.14.0,1...
[2/8] Extracting protobuf-3.14.0,1: 100%
[3/8] Installing libevent-2.1.12...
[3/8] Extracting libevent-2.1.12: 100%
[4/8] Installing libedit-3.1.20210216,1...
[4/8] Extracting libedit-3.1.20210216,1: 100%
[5/8] Installing cyrus-sasl-2.1.27_1...
*** Added group `cyrus' (id 60)
*** Added user `cyrus' (id 60)
[5/8] Extracting cyrus-sasl-2.1.27_1: 100%
[6/8] Installing liblz4-1.9.3,1...
[6/8] Extracting liblz4-1.9.3,1: 100%
[7/8] Installing mysql57-client-5.7.34...
[7/8] Extracting mysql57-client-5.7.34: 100%
[8/8] Installing mysql57-server-5.7.34...
===> Creating groups.
Creating group 'mysql' with gid '88'.
===> Creating users
Creating user 'mysql' with uid '88'.
===> Creating homedir(s)
[8/8] Extracting mysql57-server-5.7.34: 100%
=====
Message from cyrus-sasl-2.1.27_1:

--
You can use sasldb2 for authentication, to add users use:

        saslpasswd2 -c username

If you want to enable SMTP AUTH with the system Sendmail, read
Sendmail.README

NOTE: This port has been compiled with a default pwcheck_method of
      auxprop.  If you want to authenticate your user by /etc/passwd,
      PAM or LDAP, install ports/security/cyrus-sasl2-saslauthd and
      set sasl_pwcheck_method to saslauthd after installing the
      Cyrus-IMAPd 2.X port.  You should also check the
      /usr/local/lib/sasl2/*.conf files for the correct
      pwcheck_method.
      If you want to use GSSAPI mechanism, install
      ports/security/cyrus-sasl2-gssapi.
      If you want to use SRP mechanism, install
      ports/security/cyrus-sasl2-srp.
      If you want to use LDAP auxprop plugin, install
      ports/security/cyrus-sasl2-ldapdb.
=====
Message from mysql57-client-5.7.34:

--
This is the mysql CLIENT without the server.
for complete server and client, please install databases/mysql57-server
=====
Message from mysql57-server-5.7.34:

--
Initial password for first time use of MySQL is saved in $HOME/.mysql_secret
ie. when you want to use "mysql -u root -p" first you should see password
in /root/.mysql_secret

MySQL57 has a default /usr/local/etc/mysql/my.cnf,
remember to replace it wit your own
or set `mysql_optfile="$YOUR_CNF_FILE` in rc.conf.

100

101

102

103

104

105

$ sudo pkg install mysql57-server

Updating FreeBSD repository catalogue...

Fetching meta.conf: 100% 163 B 0.2kB/s 00:01

Fetching packagesite.txz: 100% 6 MiB 1.5MB/s 00:04

Processing entries: 0%

Newer FreeBSD version for package libbigwig:

To ignore this error set IGNORE_OSVERSION=yes

- package: 1104001

- running kernel: 1102000

Ignore the mismatch and continue? [y/N]: y

Processing entries: 100%

FreeBSD repository update completed. 29008 packages processed.

All repositories are up to date.

Updating database digests format: 100%

The following 8 package(s) will be affected (of 0 checked):

New packages to be INSTALLED:

cyrus-sasl: 2.1.27_1

libedit: 3.1.20210216,1

libevent: 2.1.12

liblz4: 1.9.3,1

mysql57-client: 5.7.34

mysql57-server: 5.7.34

perl5: 5.32.1_1

protobuf: 3.14.0,1

Number of packages to be installed: 8

The process will require 286 MiB more space.

35 MiB to be downloaded.

Proceed with this action? [y/N]: y

[1/8] Fetching mysql57-server-5.7.34.txz: 100% 14 MiB 2.5MB/s 00:06

[2/8] Fetching perl5-5.32.1_1.txz: 100% 15 MiB 3.1MB/s 00:05

[3/8] Fetching protobuf-3.14.0,1.txz: 100% 3 MiB 2.9MB/s 00:01

[4/8] Fetching libevent-2.1.12.txz: 100% 320 KiB 327.7kB/s 00:01

[5/8] Fetching libedit-3.1.20210216,1.txz: 100% 138 KiB 141.0kB/s 00:01

[6/8] Fetching mysql57-client-5.7.34.txz: 100% 2 MiB 1.1MB/s 00:02

[7/8] Fetching cyrus-sasl-2.1.27_1.txz: 100% 964 KiB 986.7kB/s 00:01

[8/8] Fetching liblz4-1.9.3,1.txz: 100% 131 KiB 134.1kB/s 00:01

Checking integrity... done (0 conflicting)

[1/8] Installing perl5-5.32.1_1...

[1/8] Extracting perl5-5.32.1_1: 100%

[2/8] Installing protobuf-3.14.0,1...

[2/8] Extracting protobuf-3.14.0,1: 100%

[3/8] Installing libevent-2.1.12...

[3/8] Extracting libevent-2.1.12: 100%

[4/8] Installing libedit-3.1.20210216,1...

[4/8] Extracting libedit-3.1.20210216,1: 100%

[5/8] Installing cyrus-sasl-2.1.27_1...

*** Added group `cyrus' (id 60)

*** Added user `cyrus' (id 60)

[5/8] Extracting cyrus-sasl-2.1.27_1: 100%

[6/8] Installing liblz4-1.9.3,1...

[6/8] Extracting liblz4-1.9.3,1: 100%

[7/8] Installing mysql57-client-5.7.34...

[7/8] Extracting mysql57-client-5.7.34: 100%

[8/8] Installing mysql57-server-5.7.34...

===> Creating groups.

Creating group 'mysql' with gid '88'.

===> Creating users

Creating user 'mysql' with uid '88'.

===> Creating homedir(s)

[8/8] Extracting mysql57-server-5.7.34: 100%

=====

Message from cyrus-sasl-2.1.27_1:

You can use sasldb2 for authentication, to add users use:

saslpasswd2 -c username

If you want to enable SMTP AUTH with the system Sendmail, read

Sendmail.README

NOTE: This port has been compiled with a default pwcheck_method of

auxprop. If you want to authenticate your user by /etc/passwd,

PAM or LDAP, install ports/security/cyrus-sasl2-saslauthd and

set sasl_pwcheck_method to saslauthd after installing the

Cyrus-IMAPd 2.X port. You should also check the

/usr/local/lib/sasl2/*.conf files for the correct

pwcheck_method.

If you want to use GSSAPI mechanism, install

ports/security/cyrus-sasl2-gssapi.

If you want to use SRP mechanism, install

ports/security/cyrus-sasl2-srp.

If you want to use LDAP auxprop plugin, install

ports/security/cyrus-sasl2-ldapdb.

=====

Message from mysql57-client-5.7.34:

This is the mysql CLIENT without the server.

for complete server and client, please install databases/mysql57-server

=====

Message from mysql57-server-5.7.34:

Initial password for first time use of MySQL is saved in $HOME/.mysql_secret

ie. when you want to use "mysql -u root -p" first you should see password

in /root/.mysql_secret

MySQL57 has a default /usr/local/etc/mysql/my.cnf,

remember to replace it wit your own

or set `mysql_optfile="$YOUR_CNF_FILE` in rc.conf.

インストール直後の主要ファイルの存在状況は以下の通り。

/usr/local/etc/mysql/my.cnf　→　存在

$HOME/.mysql_secret　→　存在しない

/root/.mysql_secret　→　存在しない

/usr/local/libdata/ldconfig/mysql57-server　→　存在

/usr/local/share/mysql/mysql.server　→　存在

MySQLサーバー起動

/etc/rc.confに以下の1行を追加してvagrant reload

mysql_enable="YES"

1	mysql_enable="YES"

サーバーの実行状況を確認。

$ sudo /usr/local/etc/rc.d/mysql-server status
mysql is running as pid 1909.

1 2	$ sudo /usr/local/etc/rc.d/mysql-server status mysql is running as pid 1909.

サーバーを再起動する場合。

sudo /usr/local/etc/rc.d/mysql-server start/restart

1	sudo /usr/local/etc/rc.d/mysql-server start/restart

サーバーを起動すると.mysql_secretファイルが作成される。

初回ログインとパスワード変更

初期パスワードの確認

rootユーザーの初期パスワードは/root/.mysql_secretファイルに書かれている。

$ sudo cat /root/.mysql_secret
# Password set for user 'root@localhost' at 2021-06-09 13:34:04
jN<&;9LMQr)E <<これが初期パスワード

$ sudo cat /root/.mysql_secret

# Password set for user 'root@localhost' at 2021-06-09 13:34:04

jN<&;9LMQr)E <<これが初期パスワード

初回ログイン

$ mysql -u root -p
Enter password:<<確認したパスワードを入力
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34-log

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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.

root@localhost [(none)]>

$ mysql -u root -p

Enter password:<<確認したパスワードを入力

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.7.34-log

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.

root@localhost [(none)]>

パスワードを変更

SET PASSWORDクエリーでパスワードを変更。

oot@localhost [(none)]> SET PASSWORD FOR root@localhost=password('新しいパスワード');
Query OK, 0 rows affected, 1 warning (0.00 sec)

1 2	oot@localhost [(none)]> SET PASSWORD FOR root@localhost=password('新しいパスワード'); Query OK, 0 rows affected, 1 warning (0.00 sec)

日本語の設定

さくらサーバーでの日本語の扱い

インストールしたままの状態だと、ロケールやエンコーディングの設定が日本語の扱いに適していないので変更する必要がある。さくらサーバーのMySQLでの扱いは以下のようになっている。

OSのロケール

ロケールは日本(ja_JP)で文字コードはUTF-8で設定されている。

% locale
LANG=ja_JP.UTF-8
LC_CTYPE="ja_JP.UTF-8"
LC_COLLATE="ja_JP.UTF-8"
LC_TIME="ja_JP.UTF-8"
LC_NUMERIC="ja_JP.UTF-8"
LC_MONETARY="ja_JP.UTF-8"
LC_MESSAGES="ja_JP.UTF-8"
LC_ALL=

% locale

LANG=ja_JP.UTF-8

LC_CTYPE="ja_JP.UTF-8"

LC_COLLATE="ja_JP.UTF-8"

LC_TIME="ja_JP.UTF-8"

LC_NUMERIC="ja_JP.UTF-8"

LC_MONETARY="ja_JP.UTF-8"

LC_MESSAGES="ja_JP.UTF-8"

LC_ALL=

MySQLの文字コード

systemがutf8、databaseとserverがutf8mb4で設定されている。

mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | ujis                       |
| character_set_connection | ujis                       |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | ujis                       |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'char%';

+--------------------------+----------------------------+

| Variable_name | Value |

+--------------------------+----------------------------+

| character_set_client | ujis |

| character_set_connection | ujis |

| character_set_database | utf8mb4 |

| character_set_filesystem | binary |

| character_set_results | ujis |

| character_set_server | utf8mb4 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

8 rows in set (0.00 sec)

MySQLの照合順序

databaseとserverの照合順序はutf8mb4_general_ciに設定されている。

mysql> SHOW VARIABLES LIKE 'col%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | ujis_japanese_ci   |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.01 sec)

mysql> SHOW VARIABLES LIKE 'col%';

+----------------------+--------------------+

| Variable_name | Value |

+----------------------+--------------------+

| collation_connection | ujis_japanese_ci |

| collation_database | utf8mb4_general_ci |

| collation_server | utf8mb4_general_ci |

+----------------------+--------------------+

3 rows in set (0.01 sec)

Vagrantの設定

さくらサーバー上のMySQLの設定に合わせる。ロケール、文字コード、照合順序の詳細については以下を参照。

ロケール設定

/etc/profileに以下2行を追加してvagrantをリロード。

export LC_ALL=ja_JP.UTF-8
export LANG=ja_JP.UTF-8

1 2	export LC_ALL=ja_JP.UTF-8 export LANG=ja_JP.UTF-8

※’=’の前後にはスペースを入れない。

＜変更後のロケール＞

$ locale
LANG=ja_JP.UTF-8
LC_CTYPE="ja_JP.UTF-8"
LC_COLLATE="ja_JP.UTF-8"
LC_TIME="ja_JP.UTF-8"
LC_NUMERIC="ja_JP.UTF-8"
LC_MONETARY="ja_JP.UTF-8"
LC_MESSAGES="ja_JP.UTF-8"
LC_ALL=ja_JP.UTF-8

$ locale

LANG=ja_JP.UTF-8

LC_CTYPE="ja_JP.UTF-8"

LC_COLLATE="ja_JP.UTF-8"

LC_TIME="ja_JP.UTF-8"

LC_NUMERIC="ja_JP.UTF-8"

LC_MONETARY="ja_JP.UTF-8"

LC_MESSAGES="ja_JP.UTF-8"

LC_ALL=ja_JP.UTF-8

文字コードと照合順序の設定

/usr/local/etc/mysql/my.cnfの[mysql]セクションと[mysqld]セクションに各々以下を追加してMySQLをリロード(sudo /usr/local/etc/rc.d/mysql-server restart)。

[mysql]
.....
default-character-set = utf8
.....
[mysqld]
character-set-server = utf8mb4
collateion-server = utf8mb4_general_ci

[mysql]

.....

default-character-set = utf8

.....

[mysqld]

character-set-server = utf8mb4

collateion-server = utf8mb4_general_ci

＜変更後の文字コード＞

root@localhost [(none)]> show variables like 'char%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | utf8mb4                          |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8mb4                          |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/share/mysql/charsets/ |
+--------------------------+----------------------------------+
8 rows in set (0.00 sec)

root@localhost [(none)]> show variables like 'char%';

+--------------------------+----------------------------------+

| Variable_name | Value |

+--------------------------+----------------------------------+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8mb4 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8mb4 |

| character_set_system | utf8 |

| character_sets_dir | /usr/local/share/mysql/charsets/ |

+--------------------------+----------------------------------+

8 rows in set (0.00 sec)

＜変更後の照合順序＞

root@localhost [(none)]> show variables like 'col%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8_general_ci    |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)

root@localhost [(none)]> show variables like 'col%';

+----------------------+--------------------+

| Variable_name | Value |

+----------------------+--------------------+

| collation_connection | utf8_general_ci |

| collation_database | utf8mb4_general_ci |

| collation_server | utf8mb4_general_ci |

+----------------------+--------------------+

3 rows in set (0.00 sec)

MySQL – CSVファイルへのエクスポート

2021-06-15 / tau / コメントする

コマンド

以下のコマンドで、指定したテーブルのデータをCSVファイルに書きだす。

SELECT * FROM テーブル名
INTO OUTFILE "ファイル名.csv"
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"';

SELECT * FROM テーブル名

INTO OUTFILE "ファイル名.csv"

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"';

エラー対応

下記のようなエラーが出る場合。

mysql> SELECT * FROM data_table INTO OUTFILE "data.csv" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

1 2	mysql> SELECT * FROM data_table INTO OUTFILE "data.csv" FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'; ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

グローバル変数を確認する。

SELECT @@GLOBAL.secure_file_priv;

1	SELECT @@GLOBAL.secure_file_priv;

mysql> SELECT @@GLOBAL.secure_file_priv;
+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
| /var/lib/mysql-files/     |
+---------------------------+
1 row in set (0.00 sec)

mysql> SELECT @@GLOBAL.secure_file_priv;

+---------------------------+

| @@GLOBAL.secure_file_priv |

+---------------------------+

| /var/lib/mysql-files/ |

+---------------------------+

1 row in set (0.00 sec)

この場合、表示されたディレクトリーにのみCSVファイルを書き込むことが可能なので、以下のように指定する。

SELECT * FROM テーブル名
INTO OUTFILE "/var/lib/mysql-files/data.csv"
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"';

SELECT * FROM テーブル名

INTO OUTFILE "/var/lib/mysql-files/data.csv"

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"';

結果のファイルdata.csvは/var/lib/mysql-filesディレクトリー下に保存される。

MySQL – テーブルのカラムの変更

2021-06-12 / tau / コメントする

カラム名の変更

changeの場合

ALTER TABLE [テーブル名] CHANGE [現在のカラム名] [新しいカラム名] [型];

1	ALTER TABLE [テーブル名] CHANGE [現在のカラム名] [新しいカラム名] [型];

mysql> describe test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> alter table test change age years_old int;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe test;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| name      | varchar(20) | YES  |     | NULL    |       |
| years_old | int         | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> describe test;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| age | int | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.01 sec)

mysql> alter table test change age years_old int;

Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> describe test;

+-----------+-------------+------+-----+---------+-------+

+-----------+-------------+------+-----+---------+-------+

| years_old | int | YES | | NULL | |

+-----------+-------------+------+-----+---------+-------+

2 rows in set (0.01 sec)

カラムの型の変更

changeの場合

ALTER TABLE … CHANGEで変更するが、新しいカラムでも現在のカラム名を指定する必要がある。

ALTER TABLE [テーブル名] CHANGE [現在のカラム名] [新しいカラム名｝[変更後の型];

1	ALTER TABLE [テーブル名] CHANGE [現在のカラム名] [新しいカラム名｝[変更後の型];

mysql> describe test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table test change age age tinyint;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | tinyint     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> describe test;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| age | int | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql> alter table test change age age tinyint;

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> describe test;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| age | tinyint | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

modifyの場合

ALTER TABLE test MODIFY [カラム名] [変更後の型];

1	ALTER TABLE test MODIFY [カラム名] [変更後の型];

mysql> describe test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int         | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table test modify age tinyint;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | tinyint     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> describe test;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| age | int | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql> alter table test modify age tinyint;

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> describe test;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| age | tinyint | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

MySQL – テーブルのカラムの削除

2021-06-12 / tau / コメントする

1つのカラムの削除

ALTER TABLE [テーブル名] DROP COLUMN [カラム名];

1	ALTER TABLE [テーブル名] DROP COLUMN [カラム名];

mysql> describe test;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name    | varchar(20)  | YES  |     | NULL    |       |
| age     | int          | YES  |     | NULL    |       |
| address | varchar(100) | YES  |     | NULL    |       |
| phone   | varchar(15)  | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table test drop column age;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe test;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name    | varchar(20)  | YES  |     | NULL    |       |
| address | varchar(100) | YES  |     | NULL    |       |
| phone   | varchar(15)  | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> describe test;

+---------+--------------+------+-----+---------+-------+

+---------+--------------+------+-----+---------+-------+

| age | int | YES | | NULL | |

+---------+--------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

mysql> alter table test drop column age;

Query OK, 0 rows affected (0.03 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> describe test;

+---------+--------------+------+-----+---------+-------+

+---------+--------------+------+-----+---------+-------+

+---------+--------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

複数のカラムの削除

ALTER TABLE [テーブル名]
  DROP COLUMN [カラム名1],
  DROP COLUMN [カラム名2],
  .....

ALTER TABLE [テーブル名]

DROP COLUMN [カラム名1],

DROP COLUMN [カラム名2],

.....

mysql> describe test;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name    | varchar(20)  | YES  |     | NULL    |       |
| age     | int          | YES  |     | NULL    |       |
| address | varchar(100) | YES  |     | NULL    |       |
| phone   | varchar(15)  | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> alter table test drop column age, drop column address;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
| phone | varchar(15) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> describe test;

+---------+--------------+------+-----+---------+-------+

+---------+--------------+------+-----+---------+-------+

| age | int | YES | | NULL | |

+---------+--------------+------+-----+---------+-------+

4 rows in set (0.01 sec)

mysql> alter table test drop column age, drop column address;

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> describe test;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

MySQL – テーブルへのカラムの追加

2021-06-12 / tau / コメントする

最後に追加

ALTER TABLE [テーブル名] ADD [カラム名] [型];

1	ALTER TABLE [テーブル名] ADD [カラム名] [型];

mysql> describe test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
| phone | varchar(15) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table test add address varchar(100);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe test;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name    | varchar(20)  | YES  |     | NULL    |       |
| phone   | varchar(15)  | YES  |     | NULL    |       |
| address | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> describe test;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql> alter table test add address varchar(100);

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> describe test;

+---------+--------------+------+-----+---------+-------+

+---------+--------------+------+-----+---------+-------+

+---------+--------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

先頭に追加

ALTER TABLE [テーブル名] ADD [カラム名] [型] FIRST;

1	ALTER TABLE [テーブル名] ADD [カラム名] [型] FIRST;

mysql> describe test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
| phone | varchar(15) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table test add id int first;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| phone | varchar(15) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> describe test;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql> alter table test add id int first;

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> describe test;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

| id | int | YES | | NULL | |

+-------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

指定したカラムの後に挿入

ALTER TABLE [テーブル名] ADD [カラム名] [型] AFTER [指定カラム名];

1	ALTER TABLE [テーブル名] ADD [カラム名] [型] AFTER [指定カラム名];

mysql> describe test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
| phone | varchar(15) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table test add address varchar(100) after name;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe test;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name    | varchar(20)  | YES  |     | NULL    |       |
| address | varchar(100) | YES  |     | NULL    |       |
| phone   | varchar(15)  | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> describe test;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql> alter table test add address varchar(100) after name;

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> describe test;

+---------+--------------+------+-----+---------+-------+

+---------+--------------+------+-----+---------+-------+

+---------+--------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

複数のカラムを追加

ALTER TABLE [テーブル名] ADD ([カラム名] [型], [カラム名] [型], ...);

1	ALTER TABLE [テーブル名] ADD ([カラム名] [型], [カラム名] [型], ...);

mysql> describe test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(20) | YES  |     | NULL    |       |
| phone | varchar(15) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table test add (age int, address varchar(100));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe test;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name    | varchar(20)  | YES  |     | NULL    |       |
| phone   | varchar(15)  | YES  |     | NULL    |       |
| age     | int          | YES  |     | NULL    |       |
| address | varchar(100) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> describe test;

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

+-------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

mysql> alter table test add (age int, address varchar(100));

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> describe test;

+---------+--------------+------+-----+---------+-------+

+---------+--------------+------+-----+---------+-------+

| age | int | YES | | NULL | |

+---------+--------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

なお、複数カラムを特定のカラムの後に挿入する場合、ADD (...) afterではエラーになるため、以下のようなクエリーにする。

ALTER TABLE [テーブル名] ADD
  [カラム名1] [型] AFTER [指定カラム],
  [カラム名2] [型] AFTER [カラム名1],
  [カラム名3] [型] AFTER [カラム名2],
  .....

ALTER TABLE [テーブル名] ADD

[カラム名1] [型] AFTER [指定カラム],

[カラム名2] [型] AFTER [カラム名1],

[カラム名3] [型] AFTER [カラム名2],

.....