What's New Qiita

20200328のMySQLに関する記事は14件です。

  • 投稿日：2020-03-28T23:58:26+09:00

LaravelでDBのカラム（mysql）のコメントの作成・確認方法

はじめに

当記事は、Laravelのマイグレーションファイルでカラムにコメントをつける方法、およびmysqlでそのカラムにつけられているコメントを確認する方法を書いています。

経緯

先日、業務内でデータベースのカラムにコメントがつけられていないために、間違った設計を行ってしまったことがありました（自己責任です）。
やはり、テーブル名、カラム名から推測しにくかったり、外部にも同名のカラムがあって、且つ意味するものが違う場合などはカラムにコメントを付けたほうが良いと思います。

マイグレーションファイルの記述

二行目のcomment()でカラムにつけるコメントを記述する。

migration.php
Schema::create('users', function (Blueprint $table) {
    $table->string('name')->comment('名前');
});

MySQLでの確認方法

show full columns from users;

右端のcommentのところに名前と出てますね。

+-------------------+---------------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field             | Type                | Collation          | Null | Key | Default | Extra          | Privileges                      | Comment |
+-------------------+---------------------+--------------------+------+-----+---------+----------------+---------------------------------+---------+
| name              | varchar(255)        | utf8mb4_unicode_ci | NO   |     | NULL    |                | select,insert,update,references | 名前   　|

終わりに

カラムにコメントがあった方が、後々プロジェクトに参加してくるエンジニアさんにも親切ですね。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2020-03-28T22:43:24+09:00

Amazon Aurora MySQL 5.7 互換版で Comprehend 感情分析を試してみる

昨年の JAWS-UG 名古屋 AWS re:Invent2019 大復習（＋忘年会）で「RDS / Aurora 関連アップデート」と題して LT 登壇をしたのですが、その中で表題の内容についても触れました。

本日、JAWS DAYS 2020 オンラインライブ視聴の裏側で「JAWS DAYS 2020 [オンラインハンズオン] はじめての自然言語処理（NLP） powered by LINE API Expert」が行われていたことで冒頭の LT 登壇を思い出したので、今回は日本語を使って試してみました。

【参考リンク】

1. 準備

今回は表題のとおり Aurora MySQL 5.7 を使います（現在は PostgreSQL 互換版でも使えるようになっているようです）。

まずは IAM ロールを Aurora クラスターに追加します。
ac01.png
※この記事を書いている時点ではまだプレビュー中です。
ac02.png
追加されました。
ac03.png
そして、IAM でこのロールの ARN を調べます。
ac04.png
ac05.png
Aurora のクラスターパラメータグループ（なければ新規作成）でaws_default_comprehend_roleにさきほどの ARN を設定します。
ac06.png
このクラスターパラメータグループを対象の Aurora クラスターに適用します。
ac07.png
なお、これらのほかに、以下のいずれかが必要です（Aurora から Comprehend にリクエストを送るため）。

  • NAT ゲートウェイの設置＋Aurora クラスター（各ノード）からのルーティング
  • パブリックアクセス有効化

今回はテストのためパブリックアクセスを有効化しています。
ac08.png

2. 試してみる

わたし自身が年末年始に 2019 年の振り返りと 2020 年の抱負として書いた、以下の Qurnch 記事の文章から、セクションごとの文章について感情分析を行ってみます。

まずデータベース・テーブルを用意します。

DB・テーブル定義
mysql> CREATE DATABASE mltest CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
Query OK, 1 row affected (0.01 sec)

mysql> USE mltest;
Database changed
mysql> CREATE TABLE sentiment_test (
    ->   id INT AUTO_INCREMENT PRIMARY KEY,
    ->   heading VARCHAR(100),
    ->   analyze_text VARCHAR(500)
    -> );
Query OK, 0 rows affected (0.03 sec)

まず、2019 年の振り返りについてデータを挿入していきます。

2019年データ挿入
mysql> INSERT INTO sentiment_test SET
    ->   heading='引っ越し',
    ->   analyze_text='たぶん驚かれるでしょうが、まだ終わってません（笑）。早い 時期に今年中の旧居撤収は諦めました。なぜか…は続きを読んでいただければ、大体 像 がつくかと。';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO sentiment_test SET
    ->   heading='SRE 活動',
    ->   analyze_text='はい、こちらも一旦頓挫しました。やはり「兼業 SRE」は無理 があります。メンバーが落ち着いて勉強する余裕などありませんでした。途中で「再起動」したものの、前途多難であります。';
Query OK, 1 row affected (0.04 sec)
（以降省略）

分析結果を見てみます。

2019年分析結果
mysql> SELECT heading,
    ->   aws_comprehend_detect_sentiment(analyze_text, 'ja') AS sentiment,
    ->   aws_comprehend_detect_sentiment_confidence(analyze_text, 'ja') AS confidence FROM sentiment_test;
+-----------------------------------------------------------------+-----------+--------------------+
| heading                                                         | sentiment | confidence         |
+-----------------------------------------------------------------+-----------+--------------------+
| 引っ越し                                                        | NEUTRAL   | 0.9790685176849365 |
| SRE 活動                                                        | NEGATIVE  | 0.9979338645935059 |
| 登録セキスペ                                                    | NEUTRAL   | 0.9953206181526184 |
| その他の資格・検定関連                                          | NEGATIVE  | 0.9806546568870544 |
| IT イベント・カンファレンス・勉強会への参加                     | NEUTRAL   | 0.9641426801681519 |
| 各種アウトプット                                                | NEUTRAL   | 0.9664930701255798 |
| MySQL（Aurora MySQL 互換含む）に関する技術                      | NEUTRAL   | 0.9988293051719666 |
| MySQL 以外のデータストアに関する技術                            | NEGATIVE  |  0.968559741973877 |
| AWS 環境のアカウント分離                                        | MIXED     | 0.8833306431770325 |
| まとめ                                                          | NEUTRAL   | 0.9767121076583862 |
+-----------------------------------------------------------------+-----------+--------------------+
10 rows in set (0.34 sec)

うっ、NEGATIVE が多い…。しかも confidence の値も高いので、自信を持って（？）判定されてるようです。

気を取り直して、今度は 2020 年の抱負で試してみます。

2020データ挿入
mysql> TRUNCATE TABLE sentiment_test;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO sentiment_test SET
    ->   heading='引っ越し',
    ->   analyze_text='2018 年の 10 月に引っ越しました…引っ越したはず…ですが、まだ旧居の片づけが終わらず。早く片付けねば…とは思うものの、今年もプライベートの時 間が忙しくなりそうなので、無理せず少しずつ進めます。';
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO sentiment_test SET
    ->   heading='会社の SRE 活動',
    ->   analyze_text='メンバー再構成で再起動はしたものの、相変わらず兼業メンバ ーばかりなので、うまくいくイメージが全く持てません（笑）。ただ、「SRE」という名 称にこだわらず、地道な改善を足掛かりにサイト（サービス）の信頼性を確保する（確保しつつスケールさせていく）意識は一部の参加メンバーのおかげで少しずつ定着しつつあるので、1 年では難しいと思いますが地道にやっていきます。とりあえず、1/25 は SRE NEXT 2020 に参加します。※個人スポンサーのところに紛れ込んでいます。';
Query OK, 1 row affected (0.04 sec)
（以降省略）

分析結果を見てみます。

2020年分析結果
mysql> SELECT heading,
    ->   aws_comprehend_detect_sentiment(analyze_text, 'ja') AS sentiment,
    ->   aws_comprehend_detect_sentiment_confidence(analyze_text, 'ja') AS confidence FROM sentiment_test;
+-----------------------------------------------------------------+-----------+--------------------+
| heading                                                         | sentiment | confidence         |
+-----------------------------------------------------------------+-----------+--------------------+
| 引っ越し                                                        | NEUTRAL   | 0.8793296813964844 |
| 会社の SRE 活動                                                 | NEUTRAL   | 0.5283552408218384 |
| 登録セキスペ                                                    | NEUTRAL   | 0.9994049072265625 |
| その他の資格・検定関連                                          | NEUTRAL   | 0.9416397213935852 |
| IT イベント・カンファレンス・勉強会への参加                     | NEUTRAL   | 0.8604051470756531 |
| 各種アウトプット                                                | NEUTRAL   | 0.9865009188652039 |
| 技術的なものへの取り組み                                        | NEUTRAL   | 0.9960585832595825 |
| さいごに                                                        | NEUTRAL   | 0.9857586622238159 |
+-----------------------------------------------------------------+-----------+--------------------+
8 rows in set (0.42 sec)

NEGATIVE はなくなりましたが、POSITIVE もまったくない NEUTRAL おじさんと判定されてしまいました。

LT ではネタ的に扱ってしまいましたが、分析基盤から SQL で蓄積データの取り込みと同時に Comprehend を呼び出すことで効率的な分析ができるようになります。

ただし、調子に乗って使っているうちに課金が積み上がる可能性がありますので、ご利用は計画的に…。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2020-03-28T18:38:13+09:00

MySQL の interval hour/minute 加減算で小数を与えると round される

概要

  • MySQL の時刻計算で interval を使うと時刻の加減算ができる
    • 例: NOW() + interval 1 hour
  • 0.4 hour 0.6 minute のように小数を与えると、整数に四捨五入されて計算される
  • second の場合は四捨五入されない

検証環境

  • Amazon Aurora (5.6.mysql_aurora.1.19.5)
  • MySQL 5.6.47

検証

2020-01-01 00:00:00 に小数の hour, minute, second を足してみる。

hour

MySQL> select '2020-01-01 00:00:00' + interval 0.4 hour;
+-------------------------------------------+
| '2020-01-01 00:00:00' + interval 0.4 hour |
+-------------------------------------------+
| 2020-01-01 00:00:00                       |
+-------------------------------------------+
1 row in set (0.01 sec)

MySQL> select '2020-01-01 00:00:00' + interval 0.5 hour;
+-------------------------------------------+
| '2020-01-01 00:00:00' + interval 0.5 hour |
+-------------------------------------------+
| 2020-01-01 01:00:00                       |
+-------------------------------------------+
1 row in set (0.01 sec)
  • + 0.4 hour = + 0 hour
  • + 0.5 hour = + 1 hour

minute

mysql> select '2020-01-01 00:00:00' + interval 0.4 minute;
+---------------------------------------------+
| '2020-01-01 00:00:00' + interval 0.4 minute |
+---------------------------------------------+
| 2020-01-01 00:00:00                         |
+---------------------------------------------+
1 row in set (0.00 sec)

mysql> select '2020-01-01 00:00:00' + interval 0.5 minute;
+---------------------------------------------+
| '2020-01-01 00:00:00' + interval 0.5 minute |
+---------------------------------------------+
| 2020-01-01 00:01:00                         |
+---------------------------------------------+
1 row in set (0.00 sec)
  • + 0.4 minute = + 0 minute
  • + 0.5 minute = + 1 minute

second

mysql> select '2020-01-01 00:00:00' + interval 0.4 second;
+---------------------------------------------+
| '2020-01-01 00:00:00' + interval 0.4 second |
+---------------------------------------------+
| 2020-01-01 00:00:00.400000                  |
+---------------------------------------------+
1 row in set (0.00 sec)

mysql> select '2020-01-01 00:00:00' + interval 0.5 second;
+---------------------------------------------+
| '2020-01-01 00:00:00' + interval 0.5 second |
+---------------------------------------------+
| 2020-01-01 00:00:00.500000                  |
+---------------------------------------------+

second の場合は round は行われず、小数部分はミリ秒として扱われていた。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2020-03-28T17:56:24+09:00

DockerでMySQLサーバを立ち上げ、.sqlファイルからDBを作るまで

はじめに

環境 ： macOS catalina 10.15.3
Docker Desktop : 2.2.0.4(43472)
Docker Compose : 1.25.4

ローカルファイル構成

以下、図参照
スクリーンショット 2020-03-28 16.49.37.png

自前のsqlファイルを使いたかったので、それをDBフォルダ配下に配置

以下に示すファイルの中身はDockerでMySQLを使ってみるを引用

docker-compose.yml

version: "3"
services:
  mysql:
    build: ./mysql/
    volumes:
      - ./mysql/db:/docker-entrypoint-initdb.d
    image: local_mysql
    environment:
      - MYSQL_ROOT_PASSWORD=

Dockerfile

FROM mysql

EXPOSE 3306

ADD ./my.cnf /etc/mysql/conf.d/my.cnf

CMD ["mysqld"]

my.conf

[mysqld]
character-set-server=utf8

[mysql]
default-character-set=utf8

[client]
default-character-set=utf8

コンテナ起動

$ Docker-Compose up -d --build

# コンテナへログイン
$ docker exec -it Docker-mysql_mysql_1 bash -p

# MySQLを起動
$ mysql -u root -p -h 127.0.0.1  # パスワードはdocker-compose.ymlで記述したやつ

DB作成したい場合

DB作成コマンド

$ create database hoge_db;

DBができているか確認

$ mysql -u root -p -h 127.0.0.1

パスワードはdocker-compose.ymlで記述したやつ
DB内に入れたら

mysql> show databases;

作ったDBがあればOK

.sqlファイルからDBを作る場合

mysqlからはexitして以下のコマンドを叩く

mysql -u root -p < インポートしたいsqlファイル

既存のDBを指定することも可能
success的なメッセージが出れば完了

参考資料

DockerでMySQLを使ってみる

MySQLで.sqlファイルを実行する

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2020-03-28T17:56:24+09:00

DockerでMySQLコンテナを立ち上げ、.sqlファイルからDBを作るまで

はじめに

環境 ： macOS catalina 10.15.3
Docker Desktop : 2.2.0.4(43472)
Docker Compose : 1.25.4

ローカルファイル構成

以下、図参照
スクリーンショット 2020-03-28 16.49.37.png

自前のsqlファイルを使いたかったので、それをDBフォルダ配下に配置

以下に示すファイルの中身はDockerでMySQLを使ってみるを引用

docker-compose.yml

version: "3"
services:
  mysql:
    build: ./mysql/
    volumes:
      - ./mysql/db:/docker-entrypoint-initdb.d
    image: local_mysql
    environment:
      - MYSQL_ROOT_PASSWORD=

Dockerfile

FROM mysql

EXPOSE 3306

ADD ./my.cnf /etc/mysql/conf.d/my.cnf

CMD ["mysqld"]

my.conf

[mysqld]
character-set-server=utf8

[mysql]
default-character-set=utf8

[client]
default-character-set=utf8

コンテナ起動

$ Docker-Compose up -d --build

# コンテナへログイン
$ docker exec -it Docker-mysql_mysql_1 bash -p

# MySQLを起動
$ mysql -u root -p -h 127.0.0.1  # パスワードはdocker-compose.ymlで記述したやつ

DB作成したい場合

DB作成コマンド

$ create database hoge_db;

DBができているか確認

$ mysql -u root -p -h 127.0.0.1

パスワードはdocker-compose.ymlで記述したやつ
DB内に入れたら

mysql> show databases;

作ったDBがあればOK

.sqlファイルからDBを作る場合

mysqlからはexitして以下のコマンドを叩く

mysql -u root -p < インポートしたいsqlファイル

既存のDBを指定することも可能
success的なメッセージが出れば完了

参考資料

DockerでMySQLを使ってみる

MySQLで.sqlファイルを実行する

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2020-03-28T17:56:24+09:00

DockerでMySQLコンテナを立ち上げて.sqlファイルからDBを作る

環境

macOS : catalina 10.15.3
Docker Desktop : 2.2.0.4(43472)
Docker Compose : 1.25.4

ローカルファイル構成

以下、図参照
スクリーンショット 2020-03-28 16.49.37.png

自前のsqlファイルを使いたかったので、それをDBフォルダ配下に配置

以下に示すファイルの中身はDockerでMySQLを使ってみるを引用

docker-compose.yml

version: "3"
services:
  mysql:
    build: ./mysql/
    volumes:
      - ./mysql/db:/docker-entrypoint-initdb.d
    image: local_mysql
    environment:
      - MYSQL_ROOT_PASSWORD=

Dockerfile

FROM mysql

EXPOSE 3306

ADD ./my.cnf /etc/mysql/conf.d/my.cnf

CMD ["mysqld"]

my.conf

[mysqld]
character-set-server=utf8

[mysql]
default-character-set=utf8

[client]
default-character-set=utf8

コンテナ起動

$ Docker-Compose up -d --build

# コンテナへログイン
$ docker exec -it Docker-mysql_mysql_1 bash -p

# MySQLを起動
$ mysql -u root -p -h 127.0.0.1  # パスワードはdocker-compose.ymlで記述したやつ

DB作成したい場合

DB作成コマンド

$ create database hoge_db;

DBができているか確認

$ mysql -u root -p -h 127.0.0.1

パスワードはdocker-compose.ymlで記述したやつ
DB内に入れたら

mysql> show databases;

作ったDBがあればOK

.sqlファイルからDBを作る場合

mysqlからはexitして以下のコマンドを叩く

mysql -u root -p < インポートしたいsqlファイル

既存のDBを指定することも可能
success的なメッセージが出れば完了

参考資料

DockerでMySQLを使ってみる

MySQLで.sqlファイルを実行する

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2020-03-28T14:26:44+09:00

PDOの静的プレースホルダで整数をバインドするとけったいな結果になるケース

PHPの提供するデータベースアクセスライブラリPDOの挙動について調べていて、動的プレースホルダ（Prepared Statementのエミュレーション）の場合については以下の記事にまとめました。

PHP 7.2以降におけるPDO::PARAM_INTの仕様変更

では静的プレースホルダ（Prepared Statement）の場合はどうかというと、中々にカオスな状況になっておりまして、まだまとめられていませんが、特徴的なトピックスについてこちらに書いてみます。

PoCを以下に示します。

<?php
  $db = new PDO("mysql:host=HOST;dbname=DBNAME;charset=utf8", USER, PASSWORD);
  $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);  // 静的プレースホルダ
  $ps = $db->prepare("SELECT :num");
  $ps->bindValue(':num', 2147483648, PDO::PARAM_INT);
  $ps->execute();
  $row = $ps->fetch(PDO::FETCH_NUM);
  echo "result = ${row[0]}\n";

単にプレースホルダに2147483648をバインドして、それをSELECTして表示しているだけです。
これをCentOS6, CentOS7, CentOS8のそれぞれ（32ビットが版が存在するCentOS6は、32ビットと64ビットの両方）で動かしてみた結果は以下のとおりです。なお、PHPはCentOSの標準パッケージのものです。

CentOS6 (32ビット)、CentOS8: 2147483648
CentOS6 (64ビット)、CentOS7: -2147483648

なぜそうなるかは調べてあるのですが、まずはクイズとして出題してみます。分かる人、いますか?

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2020-03-28T11:33:38+09:00

GoogleデータポータルでWordPress（MySQL）に接続する方法【レポーティング効率化】

データ集計（分析？）が割とさくっとできて便利なGoogleデータポータル（Google Data Studio）ですが、本日はホスティングサーバ上で動かしているWordPress（MySQL）のデータを連携させる手順についてのまとめです。

GoogleデータポータルでWordPress（MySQL）に接続する方法

【最終イメージ】

表の内容としてはあまり意味を持たないのですが、固定ページ（page）の投稿件数の日毎の合算値を時系列で表示しています。

gds_report_image.jpg

（参考記事）
ちなみに、グーグルデータポータルの使い方は少しだけ過去の記事に記載しておりますので必要に応じて参考にご覧ください。
Googleデータポータルで画像URLから画像表示する方法【リストにして画像を表示】

ホスティングサーバ側の準備

【最外部サービス（Googleサービス）からのアクセス許可】

まずGoogleのサービスからホスティングサーバにアクセスできるように、対象となるIPアドレスに対してアクセス許可を行います。

こちらのページにIPリストが載っているので、確認してみてください。

google_ip_list.jpg

データベースがファイアウォールの内側にある場合、MySQL データベースに接続して照会するには、次のすべての IP アドレスへのアクセスを可能にしてください。

アクセスの許可を与えるために、WordPressを動かしているホスティングサーバの管理画面に入ってください。cPanelが使えるようであれば、DATABASEの項目からRemote MySQLを選択してください。

cpanel-1.jpg
そして、アクセスを許可するホストの情報を登録していってください。

cpanel_add_access_host.jpg

なお、登録にあたっては%をワイルドカードとして使えるよと書かれていたので以下で登録しました。ご参考まで。

108.177.%.%
173.194.%.%
207.126.%.%
209.85.%.%
216.239.%.%
216.58.%.%
64.18.%.%
64.233.%.%
66.102.%.%
66.249.%.%
72.14.%.%
74.125.%.%

【データベース（MySQL）情報の確認】
GoogleデータポータルからMySQLを外部接続しにいくので、データベース情報を確認しておく必要があります。最低限、以下の情報が必要となります。

  1. ホスト名または IP アドレス
  2. ポート（省略可）
  3. データベース
  4. ユーザー名
  5. パスワード

1.のホスト名及びIPアドレスに関しては、ホスティングサーバの管理画面から確認する事ができると思うのでチェックしておいてください。（111.222.333.444の数字12桁を頑張って探してください！！）

その他の情報に関しては、WordPress関連のファイルがまとまったページにデータベース情報が記載されているwp-config.phpというファイルがあるので中身を確認してください。

wp-config.php
/**（途中まで省略）*/

define( 'DB_NAME', '**********' ); /* ③データベース名 */

/** MySQL database username */
define( 'DB_USER', '**********' ); /* ④ユーザ名 */

/** MySQL database password */
define( 'DB_PASSWORD', '**********' );　/* ⑤パスワード */

/** MySQL hostname */
define( 'DB_HOST', 'localhost' );

これでホスティングサーバ側での事前準備・確認は終了です。

Googleデータポータルからのデータ参照の設定

それではGoogleデータポータルにログインして、データソースの追加から[MySQL]を選んでください。

google_data_portal_mysql.jpg

そして、先程確認しておいたデータベース情報を入力します。
gds_dbinfo.jpg

その後は、カスタムクエリを選んでデータ表示したいクエリを絞ってください。今回は特に何もこだわりがないので、SELECT * FROM wp_postsとしてwp_postsテーブルのカラムを全て選ぶようにしています。

dds_query.jpg

データベースに接続をしたあとは、レポートを作成を押下を押してレポート作成に進みましょう。レポートの作成については、詳しく述べませんがとてきたカラムデータを使ってお好きなようにデータの描画を行うようにしてください。（以下、参考まで）

gds_report_setting.jpg

手順は以上になります。おつかれさまでした！！

WordPress（MySQL）のデータをGoogleデータスタジオ上で表示するまでの手順をご説明しました。WordPress自体がデータの保持の仕方をわりとトリッキーにやってたりするので、クエリ考えたりするので慣れるまでは時間を取られそうですね…

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2020-03-28T10:58:08+09:00

AWS RDSに作成したMySQLに対して外部から接続可能にする

概要

今回は初心者向けにAWSのRDSに作成したMySQLのDBに対して外部から接続する方法まで説明します。
外部から接続するにあたり、A5Mk2を使用します。

では、さっそく捌いていくっ！

データベースの作成

後にも先にもまずは、データベースを作成しましょう。
画像を例に進めてみましょう。

01_データベースの作成.png

まずは、｢データベースの作成方法を選択」から「標準作成」を選択します。
｢エンジンのオプション」として、今回はMySQLを使用します。
｢バージョン｣は任意で構いません。特に理由がない場合は、最新バージョンを選びましょう。

テンプレート選択と接続情報設定

「テンプレート」は用途によって選択してください。
筆者は「テンプレート」を「本番稼働用」を選択しました。
※ 欲張ったスペックにすると課金料金が大変な事になるので、しっかり料金を下調べすることを強く推奨します。

｢DBインスタンス識別子｣は何でもいいので、自身がわかる名前を適当につけてください。

｢マスターユーザー名」は、DB接続時に利用するよくある「root｣や｢administrator｣的なやつです。
ありきたりな名前は、個人的にセキュリティよろしくないと思うので、マニアックな名前にしておきましょう。

｢マスターパスワード｣は、｢マスターユーザー｣のパスワードです。
忘れないでね！

ここで設定してた「マスターユーザー名」と｢マスターパスワード｣を後に外からDBに接続するときに利用します。

02_テンプレート選択と接続情報の設定.png

接続関連の設定

AWSのVPCでEC2などに接続するならいいけど、外部のEC２や、自分のPCなどからDBに接続したい場合、よく初心者が設定を忘れる箇所があります。
それは、「パブリックアクセス可能」を「あり」にすること。
大抵、「つながらないな、ポートも開けているのにな」って言うときの原因はここです。

あとは、｢VPCセキュリティグループ｣を画像を参考に設定します。
「VPCセキュリティグループ名」は、適当に付けてください。

はい。
ここでまた重要なところがあります。
それは、「データベースポート」です。
デフォルトのままでも利用は可能ですが、セキュリティを意識するなら最低限ポート番号は変えておきましょう。
ここのポート番号は、後にセキュリティグループで使用します。
セキュリティグループはEC2の時のように自身のグローバルIPアドレスや、EC2のStaticIPアドレスのようなグローバルアクセスだけピンポイントで許可しましょう。

03_接続.png

間違っても｢0.0.0.0｣に許可しないように。
「0.0.0.0」のままにすると、全世界からアクセス可能になります。

ホント、セキュリティはシビアになりましょう。
「動けばいいや」は後で命取りになります。
後でGRANTなどでさらにセキュリティを強固にしましょう。
何重にもセキュリティ対策を。

固有名詞は出しませんが、セキュリティ意識を疎かにすると理由は様々ですが
大手コンビニ「6の次のペイ」の事件や、炊飯器やポットで有名な｢エレファントマークのショッピングサイトの個人情報流出」事件になりかねません。

余談ですが、筆者は「エレファントマーク個人情報流出事件」の被害者です。
たまに詐欺まがいなメールや、不審なDM郵便が来て困ったもんです。
僕、炊飯器のフタ買っただけなのに、個人情報悪用されるなんて。。。。

『僕は嫌だ！』

はい、話はズレましたが次！

データベース認証と追加設定

｢データベース認証｣は、簡単に「パスワード認証」としています。
本当は、IAMを利用した方がいいと思いますが手順が長くなるので割愛。

そして「最初のデータベース名」を任意のデータベース名にしましょう。
これは、「CREATE DATABASE XXXXXX」のXXXXXXの部分にあたります。

先ほどのマスターユーザ、パスワード、ここでのデータベース名で後に外から接続します。

04_データベース名.png

他はデフォルトのままです。
セキュリティグループは、各々の環境に合わせて設定しておいてください。

以上、これでDBのインスタンスを作成して起動されるまでしばし待ちましょう。。。

テンテケテケテン♪

外部ツールからDBに接続してみる

作成したデータベースの起動がAWS RDS上で確認できた場合、接続先の情報(ホスト名)をコピーしましょう。
東京リージョンの場合は、下記の画像のように｢ap-northest-1.rds.amazonaws.com｣で終わっている長ったらしい文字列の部分です。

そして、さっき設定しておいた「マスターユーザー名」「パスワード」「データベース名」「ポート番号」をツールに入力します。
フレームワークをお使いの方は、設定ファイル的なものに記述しましょう。
スクラッチで開発されているかたは、ソースにぶち込んでください。

あとは「テスト接続」を押下して「接続に成功しました」って出れば完了です。

0006.png

この後は、各々CREATE TABLE を実行したり、マイグレーションを実行したり、イジイジしてみてください。

おまけ

クラウドは便利でどこからでも使えていいのですが、ガチでセキュリティは意識しないと大変なことになります。
最低限今回のサービスに限らず、接続元のIPは絞っておきましょう。

と、言っても個人だと固定グローバルIP持っている人は少ないでしょう。

個人でも固定IPアドレスが持てる方法を一部お知らせします。
固定IP持ってても、ルーターの設定などしっかりやらないと大変な事になります。

プロバイダ：アサヒネットを利用

オプションで800円～1000円くらい払えば、個人でも固定グローバルIPが付与されます。

YAMADA SIM

これはモバイルルーターの話ですが、オプションで月80円から100円くらいで固定のグローバルIPが付与されます。

以上！

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2020-03-28T10:42:19+09:00

OracleからMySQLへのテーブル定義（DDL）の移行

OracleからMySQLにテーブル定義をまとめて移行する方法です。

経緯など

開発環境以上はOracle。
そこそこ制約が厳しいので、個々の開発の段階で、より自由に操作できるDB環境を整えるため。
移行対象のテーブルは650以上。
OracleとMySQLでは、構文が異なる部分があるため、SQLines SQL Converter Tool（以下、Sqlines）というフリーのコンバーターを使って、DDL変換することにしました。

環境と前提条件

  • OS（ローカルPC）：Windows10
  • OS（MySQLインストール先）：CentOS6.8
  • 移行元DB：Oracle Database 12c
  • 移行先DB：MySQL8.0（database（Oracleでいうところのschema）は作成済とする）

使用ツール

  • Oracleからのデータエクスポート：SQL Developer
  • Oracle → MySQLへDDLコンバート：Sqlines
  • インポートファイルの移動（ローカルPC → MySQLインストール先）：WinSCP

作業イメージ

作業イメージは以下の通り。
image.png

手順

1. 事前準備

使用するツールをダウンロード & インストールしておきます。

1-1. SQL Developerのダウンロード ～ 接続設定

Oracle SQL Developer ダウンロードより、SQL Developerをダウンロードし、移行元DB(Oracle）に接続できるようにしておきます。
詳細な手順は、SQL Developerの使い方～ダウンロード、インストール、DB接続、SQL実行をご覧ください。

1-2. Sqlinesのダウンロード ～ 解凍

Sqlinesより、Sqlinesをダウンロードし、解凍しておきます。
手順は以下の通り。
(1)Sqlinesにアクセスし、画面右端の「Migration to MySQL」下の「Oracle to MySQL」をクリックします。
image.png

(2)「SQLines SQL Converter Tool」下の「Desktop Version」をクリックします。
image.png

(3)Windows版の最新版をクリックしてダウンロード。
image.png

(4)ローカルPCの任意の場所に展開します。フォルダの中は以下のようになっています。
image.png

※現時点で、のMySQLの対応バージョンは5系ですが、インポート後にアップグレードするため、問題ない認識です。

1-3. WinSCPのダウンロード ～ 接続設定

WinSCPオフィシャルサイトより、WinSCPをDLし、移行先DB（MySQL）の入った環境に接続できるようにしておきます。
詳細な手順は、WinSCP インストールから基本的の使い方をご覧ください。

2. テーブル定義（DDL）エクスポート from Oracle

Oracleからテーブル定義（DDL）をエクスポートします。

(1) SQL Developerを起動し、「ツール」タブ（①） > 「データベース・エクスポート」をクリックします（②）。
image.png

(2)エクスポート・ウィザードが立ち上がったら、接続DBを選択します（①）。
今回はDDLのみが対象なので、「データのエクスポート」（②）のチェックを外しておきます。
移行先に合わせたエンコーディングを選択し（③）、「参照」を押下して、エクスポート先とファイル名を指定し（④）、「次」をクリックします。
image.png

(3)「エクスポートするタイプ」の選択です。デフォルトでは、「標準オブジェクト型」のすべてにチェックが入っていますが、「表」のみにし（①）、「次」（②）をクリックします。
image.png

(4)「オブジェクトの指定」画面に切り替わったら、「増やす」をクリックします。
image.png

(5)対象のスキーマを選択し（①）、「参照」をクリック（②）。
image.png

(6)対象のテーブルを確認し（①）、「>>」をクリック。
※一部のテーブルのみ移行する場合は、対象のテーブルを選択し（複数選択できます）、「>」をクリックしてください。
image.png

(6)内容を確認し（①）、「次」(②）をクリック(②）。
image.png

(7)「エクスポートのサマリー」画面に移動したら、内容を確認し、「終了」をクリックします。
image.png

(8)DDLのエクスポートが始まります。量が多い場合は、「バックグラウンドで実行」してもよいでしょう。
image.png
※650強のテーブルのエクスポートで5分ほどかかりました。

(9)終了すると、エクスポートされたファイルが開くので内容を確認します。
image.png

※量が多い場合は以下のメッセージが出て開きませんが、指定した場所にはエクスポートされているのでご安心を。
image.png

3. テーブル定義（DDL）コンバート from Oracle to MySQL

エクスポートされたDDLはOracle用なので、MySQL用にコンバート（変換）します。

(1)解凍したSqlinesのフォルダを開き、「sqlinesw.exe」をダブルクリックします。
image.png

(2)Sourceの「Database」で「Oracle」を選択し（①）、「Input SQL Scripts」でエクスポートしたファイルを入力します（②）。
Targetの「Database」で「MySQL」を選択し（③）、「Output SQL Scripts」で任意のファイル名を入力し、「Convert」（⑤）をクリックします。
image.png
※②, ④では別ウィンドウが開きますが、フォルダの選択までしかできないので、ファイル名は手動で入力してください。

(3)Convertが実行されるとログが出ます。
image.png

※こちらは即完了！1秒かかったか、かからなかったかというレベル。

(4)(2)の④「Output SQL Scripts」で指定した格納先にコンバートされてできたファイル（以下、インポートファイル）が存在することを確認します。
image.png

4. テーブル定義（DDL）インポート to MySQL

(1)WinSCPで手順3でできたインポートファイルを移行先DB（MySQL）の入ったサーバの任意のディレクトリにコピーします。

(2)移行先DB（MySQL）の入ったサーバにrootユーザーでログインし、MySQLが起動していることを確認します。

# service mysqld status

(3)でインポートファイルを配置したディレクトリに移動します。
/home/hogeに配置した場合は以下の通り。

# cd /home/hoge

(3)以下のコマンドを実行し、DDLをインポートします。

# mysql -u root -p -f < インポートファイル名

(4)終了したらMySQLをアップグレード＆再起動します。

# service mysqld restart --upgrade=FORCE

※「mysql_upgrade」はMySQL8ではdeprecated（非推奨）なので、代わりに「--upgrade=FORCE」を使います。

(5)MySQLにログインし、テーブルがインポートされていることを確認します（以下、rootでログインの場合）。

# mysql -u root -p 
# use 対象のデータベース名;
# show tables;

終わりに

SQL ServerやPostgres SQLなど → MySQLの移行、MySQL → Oracleの移行ツールは検索すればわりとすぐに出てくるのですが、Oracle → MySQLは一筋縄ではいかず、方法探しと選定だけでも丸1日以上かかりました。
（考えてみれば同じOracle社管轄で高価なOracle Database → 安価なMySQLの移行方法がやたら出回るわけないのですが。）

MySQLインポート時にエラーが出る場合があるのですが、その対策方法についてはまた別途書きたいと思います（エラー発生分以外は全てインポートされるので、その辺はご安心を）。

Sqlinesは、他DB → 他DBへのコンバートもできるようなので（例：SQL Server → Postgres SQL）機会があればやってみたいです。

参考

以下を参考にさせていただきました！ありがとうございました！

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2020-03-28T09:01:51+09:00

PHPでポートフォリオ作成：カレンダー

記事の概要

作った背景、工夫したところ、機能をまとめました。

下にコードを記載しました。

背景

一般的なカレンダー（家の壁にかかっているものを想像してほしいです）を作成したときに、自分のスケジュールを書き込めると、カレンダーとしての価値がアップするのでないかなと思い作成にいたりました。

エクセルのようなレイアウトで日付とスケジュールを同時に表示するようなカレンダーを作成します。

工夫としては、当たり前ですが見た目を分かりやすく、使用者が使いやすいようにシンプルにしました。

目的

・PHPを用いて簡単なアプリケーションを作る
・見やすくて使いやすいアプリを作る
・カレンダー機能+スケジュール機能で単純なカレンダーに価値をつける

スペック

言語
PHP 7.4.2
データーベース
MariaDB 10.4.11
開発環境
Apache　2.4.41

ビジュアルスタジオコード使用

主な機能

・今月の日付の表示機能
・来月、前月のカレンダーへのリンク
リンクをクリックすると、来月もしくわ前月のカレンダーに飛びます

・スケジュールの表示機能
カレンダーの日付の横にスケジュールの時間と内容を表示する

・スケジュールの保存機能
日付、時間、スケジュールという情報をデータベースに保存する

・スケジュールの更新機能
スケジュールを更新する

・スケジュールの削除機能
スケジュールを削除する

※データベースのカラムの情報があると説明しやすいので、詳しくは下部で説明します。

開発手順

1、要件定義
2、環境選定
3、データベース設計
4、設計
5、コーディング

1、要件定義

今回のアプリでの主要な機能は
・今月の日付の表示
・スケジュールの表示
・スケジュールの保存、更新、削除

今月の日付が表示できるということは、月（month）の部分を変更することで来月、前月のカレンダーが表示可能になります。
ということは、すべての日付のカレンダーが作成可能になります。

スケジュールの保存には、データベースが必要です。

環境選定

PHP,MySQL,apacheを使用。

Windowsローカル上に環境を構築しました。

データベース設計

記入例

id day time 　 message 　
5 2018-12-15 18:00 予定を書く

スケジュールの情報をデータベースに保存。
スケジュールの情報としては
id,day,time,messageとして
idにはオートインクリメントを設定して、idの値がかぶることはないようにしました。
dayには2020-03-27の形式で年月日を保存
timeにはtextタイプなので時間の表現の仕方は自由です
messageにはスケジュールの内容を保存します

設計

のちほど追加予定

　5.コーディング

設計を書いたのですが、後々変更することは多かったです。
理由は、コーディングしていて「このように表示する方が使用者にとっては、使いやすいのではないか」という考えの結果、レイアウトの変更をしました。

5.1データベース作成

ローカル環境のMysqlサーバーに
データーベースを作成。テーブルを作成。

5.2データベース接続確認

PDOを用いて、データーベースの接続。
try~catchを用いて、成功しているか判断

5.3今月の日付の表示機能

GIF 2020-03-28 8-39-33.gif

urlに?t=2020-04のような表記があれば、その年月（この場合2020-03）で取得し、表記がなければ今月の年月を取得する
これで、年月日の年月の情報を取得したので、年月の情報からDatePeriodを使って、年月日における日の部分の情報を取得します。
これで年月日の情報がそろったのでエクセルや表のような形式で表現します

5.4・来月、前月のカレンダーへのリンク

リンクをクリックすると、来月もしくわ前月のカレンダーに飛びます

GIF 2020-03-28 8-42-58.gif

5.3にて年月日の情報を取得しています。なのでリンクをその月（month）に+1すれば翌月、-1すれば前月へ飛ぶように設定しました。

5.5スケジュールの表示機能

カレンダーの日付の横にスケジュールの時間と内容を表示する

if文で取得している年月日とデータベースにあるdayの部分で一致するレコード（行部分）が存在したら、そのレコードのスケジュール時間と内容を日付の横に表示する

5.6スケジュールの保存機能

日付、時間、スケジュールという情報をデータベースに保存する
この時、日付の形式が2020-03-27のような形式でない場合はerrorとし
errorが存在する場合はエラーメッセージを表示する

GIF 2020-03-28 8-48-45.gif

5.7スケジュールの更新機能

スケジュールを更新する

GIF 2020-03-28 8-53-15.gif

構造上、日付1つに対して1つのメッセージになるため、
更新機能を使う場合、既存のスケジュールの情報を削除してから、また新しくスケジュールをデータベースに保存する方法を使用

※1つの日付に対して、複数のメッセージがある場合はidが新しいメッセージが表示される。
※また、ある日付に対してメッセージが存在するにも関わらず、メッセージを作成した場合は、メッセージは既存のメッセージが表示されるので、新しく作ったmemberテーブルは表示されない

5.8スケジュールの削除機能

スケジュールを削除する

削除ボタンを作って、削除を実行する
この時、日付に該当する箇所を変更した場合、その日付のメッセージが削除される。

5.9レイアウト

新しくスケジュールを作る機能はアイコンにして、右上に固定しました。
これは、下にスクロールしたとしても、スケジュール作成できるためです
配色の統一、シンプルにわかりやすいをこころがけました

課題点

・「1つの日付に対して、1つのスケジュール」でなく複数のスケジュールを表示すべきだが、技術的にできなかった。
・わかりやすく,シンプルに作ったつもりであるが、その分、説明不足で慣れれ宇までに時間がかかる可能性がある
・スケジュールの保存に関して、使用者の制約があり親切でない、
1999-03-20のような形式でなくタイプをradio形式（複数の選択肢から1つを選択する形式）で年、月、日に分けて表現すれば良かったかもしれない。
・スケジュールの削除に関して、入力されている年月日のデータの削除があることを説明していない
・オブジェクト思考がなってない。機能を別ファイルでプログラミングして、呼び出すことができてない

code

index.php
画像

index.php
<?php 
require('dbconnect.php');

//セキュリティ対策
function h($s){
  return htmlspecialchars($s,ENT_QUOTES,'UTF-8');
}
//

//今月を取得する
try{
if(!isset($_GET['t']) || !preg_match('/\A\d{4}-\d{2}\Z/',$_GET['t'])){
  throw new Exception();
}
  $thisMonth=new DateTime($_GET['t']);
}catch(Exception $e){
  $thisMonth = new DateTime('first day of this month');
}


//今月から来月と翌月を取得する
  $dt= clone $thisMonth;
  $prev = $dt->modify('-1 month')->format('Y-m');
  $dt= clone $thisMonth;
  $next=$dt->modify('+1 month')->format('Y-m');
//


$yearMonth=$thisMonth->format('F Y');

//日付、メッセージの表示機能
  $period=new DatePeriod(
    new DateTime('first day of'.$yearMonth),
    new DateInterval('P1D'),
    new DateTime('first day of'.$yearMonth.'+1 month')
  );

  //複数のメッセージをつけたいがつけれなかった
  $body='';
  $today=new DateTime('today');
  foreach($period as $day){

    $messages=$db->prepare('SELECT * FROM posts WHERE day=?');
    $messages->execute(array($day->format('Y-m-d')));
    $message=$messages->fetch();

    $todayClass=($day->format('Y-m-d') === $today->format('Y-m-d'))?'today':'';
    $body .= sprintf('<tr><td class="youbi_%d %s">%d</td><td>%s</td><td class="message"><a   href="message.php?t=%d-%02d-%2d">%s</a></td></tr>',$day->format('w'),$todayClass,$day->format('j'),$message['time'],$day->format('Y'),$day->format('n'),$day->format('d'),$message['message']);
  }
//




//cssでのレイアウト
//オブジェクト思考


//終了。前月次月のリンク.todayのリンク,データベースに保存する
//今日を太文字、土日は色変える,時間の指定もあり、消去、変更

?>



<!DOCTYPE html>
<html lang='ja'>
<head>
  <meta charset='utf-8'>
  <title>カレンダー</title> 
  <link rel='stylesheet' href='styles.css'>
</head>
  <body>
      <a class='write' href="message.php" name='write_message'><img  src="write.png"  alt="写真"></a>
    <table>
      <thead>
        <tr>
          <th><a href="/test/index.php?t=<?php echo h($prev); ?>">&laquo;</a></th>
          <th><?php echo $yearMonth; ?></th>
          <th><a href="/test/index.php?t=<?php echo h($next); ?>">&raquo;</a></th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <td>day</td>
          <td>time</td>
          <td>message</td>
        </tr>   
        <?php echo $body;?>
      </tbody>
    </table>
  </body>
</html>

message.php
画像

message.php
<?php
require('dbconnect.php');


//入力機能は取得して、それをインサートするだけ

if(!empty($_POST)){
    //すでにメッセージが存在する場合、そのメッセージを消去する
    //先に消去をおした場合にはメッセージを消去してexitする
    if($_POST['delete'] ==='1'){
      $deletes=$db->prepare('DELETE FROM posts WHERE day=? ');
      $deletes->execute(array($_POST['day']));
      header('Location:index.php');
      exit();
    }


    if(!empty($_POST)){

      $posts=$db->prepare('SELECT * FROM posts WHERE day=?');
      $posts->execute(array($_POST['day']));
      $post=$posts->fetch();

      if(!empty($post['message'])){
      $deletes=$db->prepare('DELETE FROM posts WHERE day=? ');
      $deletes->execute(array($_POST['day']));
    }


  //日付の設定はシビアで形式が決まっているので、形式が違えばエラーをだす
  if( preg_match('/\A\d{4}-\d{2}-\d{2}\z/',$_POST['day'])){
    $message=$db->prepare('INSERT INTO posts SET day=? ,message=?,time=?');
    $message->execute(array(
      $_POST['day'],
      $_POST['message'],
      $_POST['time']
    ));

    header('Location:index.php');
    exit();
  }else{
    $error['day']='format';
  }

}


}
//messageの再取得、
  $messages=$db->prepare('SELECT * FROM posts WHERE day=?');
  $messages->execute(array($_REQUEST['t']));
  $message=$messages->fetch();

?>
<!DOCTYPE html>
<html lang='ja'>
<head>
  <meta charset='utf-8'>
  <title>カレンダー</title> 
  <link rel='stylesheet' href='styles.css'>
</
head>
  <body>
    <div class='title'>
      <a href="index.php"><<　ホーム</a>
    </div>
    <div class='content'>

      <form action="" method='post'>
        <p class='delete_p'>メッセージを消去＞＞<button type='subimt' class='delete'  name='delete' value='1'>消去</button></p>
        <p >日付　（※2020-01-01の形式で入力してください）</p>
        <input name='day' type="text" value="<?php echo $_REQUEST['t'];?>" >
        <?php if($error['day'] === 'format'): ?>
          <p class='error'>※2020-01-01の形式で入力してください。</p>
          <?php endif;?>
          <p>時間</p> 
          <input name='time' type="text" value='<?php echo $message['time'] ?>'>
          <p>メッセージ</p>

          <textarea name="message" id="" cols="30" rows="10" ><?php echo $message['message']; ?></textarea>
          <button type='submit'>メッセージを保存する</button>




    </div>
  </form> 

  </body>
</html>
  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2020-03-28T00:47:10+09:00

MySQL Workbenchの使い方を画像付きで解説する

この記事について

MySQLをGUIでわかりやすく操作するツールがMySQL Workbenchです。
ですがこのツールは日本語化されておらず、ツールバーやボタンの説明が全て英語なので、使い慣れていないとなかなか直感的に操作することが難しいかな……という印象です。
この記事では、クエリを実行する・explainを実行するといった超基本的な操作について、Workbench上でどう行えばいいか、スクリーンショットを用いて解説します。

使用する環境・バージョン

  • OS : MacOS Mojave ver 10.14.5
  • MySQL : Ver 8.0.18 for osx10.14 on x86_64 (Homebrew)
  • MySQL Workbench : Version 8.0.18

前提条件

  • MySQLとWorkbenchのインストールは完了済み
  • localhostのDBにrootユーザーで接続できる状態にすでになっているとする

読者に要求する前提知識

  • ターミナルで問題なくコマンド実行ができること
  • 基本的なクエリが書けること
  • 基本的なDB用語の意味がわかること

主要操作

DB接続

1.MySQL Workbenchを起動

MySQL Workbenchを初めて起動すると、以下のような画面になります。
start1.png
MySQL Connectionsという文字の右側にある+ボタンを押して、接続するDBを選びます。

DBの登録

+ボタンを押したらこのような画面になります。
start2.png
今回はlocalhostDBにrootユーザーで接続することにするので、フォームに以下のように入力します。

  • HostName : localhostのIP(デフォルトでフォームに入力されているはず)
  • Port : MySQLのデフォルトポートである3306番(デフォルトでフォームに入力されているはず)
  • UserName : root(デフォルトでフォームに入力されているはず)
  • Connection Name : 識別しやすい適当な名前でいいです。上図ではlocalhost

これで右下のOKボタンを押すと、以下の画面に遷移します。
start3.png
MySQL Connectionsの下に、先ほどlocalhostと名前をつけたコネクションが表示されています。

3.MySQLサーバーの起動

DBに接続する前に、DBサーバーを起動しなければなりません。
ターミナルを開いて、以下のコマンドを打ちます。

$ mysql.server start
Starting MySQL
. SUCCESS!

4.DBへ接続

サーバーが起動できたら、MySQL Connectionsの下にあるlocalhostをクリックします。
start3.png
すると、rootユーザーでDBに入るためのパスワードが要求されるので、もともと設定してあるPWを入力、OKボタンをクリックします。
start4.png
以下の操作画面になれば接続は成功です。
start5.png

外部ファイル読み込み

MySQLの公式ページからダウンロードしたテストデータをlocalhostのDBに入れます。
メニューバーのServerからData Importを選択します。
import1.png
開いたタブのImport Optionsで、Import from Self-Contained Fileを選択し、その隣のテキストフォームに公式ページから落としたテストデータのsqlファイルのパスを指定します。
import2.png
また、Default Schema to be Imported Toのタブで、データを入れるスキーマを指定します。
Default Target Schemaの右にあるNewボタンを押して、新しくworldスキーマを作成・指定します。
スクリーンショット 2020-01-24 21.44.37.png
Import Progressのタブを開いて、右下のStart Importボタンを押すと、worldスキーマに3つのデータ入りテーブルが作成されます。
import3.png

デフォルトスキーマの変更

上で作成したworldスキーマ上でクエリを実行するためには、worldスキーマをデフォルトスキーマに指定する必要があります。
指定するためには、左のnavigatorタブにあるworldスキーマを右クリックして、Set as Default Schemaを選択します。
スクリーンショット 2020-01-24 21.52.41.png
ちなみに、現在のデフォルトスキーマは太文字で表示されています。
スクリーンショット 2020-02-01 16.36.31.png

外部ファイルの表示

外部のsqlファイルの中身を表示したいときには、一番左のファイルアイコンを押します。
スクリーンショット 2020-01-25 15.04.04.png
すると、ファイル選択画面が表示されるので、見たいファイルを選択して右下Openボタンを押します。
スクリーンショット 2020-01-25 15.07.52.png
無事に選択したworld.sqlの中身が表示されます。

また、上タブの左から二番目(フォルダ+ボタン)を押しても同様の手順で外部ファイルを表示させることができます。
スクリーンショット 2020-01-25 15.40.00.png

ファイル内検索/文字列置換

ファイル内検索/文字列置換を行うためには、右側の虫眼鏡アイコンを押します。
1.png

検索

ファイル内検索を行うためには、左のFindタブを選択し、検索窓に探したい文字列を入力します。
2.png

すると、ヒットした箇所がカーソル選択されます。複数箇所見つかっているときは、検索窓の左にある矢印ボタンで移動することができます。
(Doneボタンを押すと検索窓が閉じてしまうので注意)

文字列置換

ファイル内検索を行うためには、左のReplaceタブを選択し、検索窓上に置換したい文字列、検索窓下に置換後の文字列を入力します。
スクリーンショット 2020-01-25 15.17.04.png

新規ファイル作成

一番左のファイル+アイコンを押すことで、新しいタブ(ここではSQL File 3)が開きそこでファイルの中身を編集することができます。
スクリーンショット 2020-01-25 15.44.35.png

ファイルの保存

ファイルを保存したいときは、フロッピーディスクのアイコンを押すことで保存動作に入れます。
スクリーンショット 2020-01-25 15.55.20.png

新規スキーマの作成

DBの絵に+が載っているアイコンをクリックすることで、新しいスキーマを作成する画面を出すことができます。
1.png
applyを押すと、これから実行する動作がSQL文ベースで表示されます。所望の動作ならば、下のApplyを押すことでそれが実行されます。
2.png

クエリの実行

通常のクエリ実行

書いたクエリ文を実行するためには、赤丸の中の稲妻アイコンを押します。
1.png
すると、書いてある通りcityテーブルの全てのデータが画面下部に表示されます。
2.png

クエリの一部を実行

クエリ文の中で一部を実行したいという場合があることがあります。
例えば、下の例だと、カーソルで選択してあるcountryテーブルの全取得だけを実行したいという場合です。
その場合、真ん中の稲妻+カーソルアイコンを押します。
3.png
すると、他のselect文が無視されて、カーソル指定した分だけが実行・結果が表示されます。
4.png

explainの実行

visual explain

explainを使い、クエリがどのように実行・検索されているのかを調べます。
調べたいクエリ文をexplainを使わずにそのまま書き、一番右側の稲妻+虫眼鏡アイコンを押します。
5.png
すると、クエリの実行中にテーブルにどのようにアクセスされているのかが図示されます。
今回の場合、テーブル中の全データを取得するためにfull scanが行われていることがわかります。

tabular explain

ターミナルでexplainを実行したときのような表が欲しい場合は、プルダウンメニューをVisual ExplainからTabular Explainに変更すればいつものあの表が見られます。
スクリーンショット 2020-01-25 19.43.09.png

Viewの確認

Viewというのは、定義した構文をもとに実体のない仮テーブルを作成する機能です。つまり、あらかじめ書いておいたselect文をテーブルのように残しておくことができます。

定義の確認

あるviewがどのようなselect文で定義されていたのかを確認するためには、view名の右側にあるレンチボタンを押します。
1.png
すると、タブの中にview定義文が表示されます。

中身の確認

viewテーブルが実際どんなデータが入っているのか表形式で確認したいときは、一番右側の稲妻ボタンを押します。
2.png
すると、viewデータの全取得のクエリ文が表示・実行されます。

ちょっと便利な機能

特殊文字の可視化

空白・改行・タブなどの特殊文字を、右から二番目のボタンを押すことで可視化させることができます。
スクリーンショット 2020-01-25 15.26.50.png

窓端での折り返し

ファイル表示窓からはみ出る文をちょうどよく折り返して表示させるには、一番右側のボタンを押します。
スクリーンショット 2020-01-25 15.34.16.png

綺麗なコードに変換

右上のほうきボタンを押すことで、クエリ文をある程度整ったフォーマットに直してくれます。
1.png
例えば、上のコードに対して使ってみます。
スクリーンショット 2020-01-25 15.50.18.png
すると、改行や小文字→大文字などの変換を勝手にしてくれます。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2020-03-28T00:27:16+09:00

WordPressの独自テーブルに格納したデータを記事内で表示する方法【$wpdbとショートコードの利用】

WordPressのホスティングサーバで動作するMySQLの独自テーブルに格納したでデータを、記事内で表示する方法について説明します。

WordPressの独自テーブルに格納したデータを記事内で表示する方法

ということで、前回の説明で活用した独自テーブルのデータ表示を例として説明したいと思います。

（参考）
スプレットシートのデータをWordPressのMySQLにコピーする方法【PHPでまるっと処理】

具体的にはwp_member_listという独自テーブルに格納した以下のデータを表示することを目指します。
wordpress_mysql_show_data_01.jpg

また、独自テーブルを活用する際には、wp-db.phpファイルの更新も必要になるので、分からない方はこちらの記事もご確認ください。

$wpdbを使うショートコードの作成

以下の通り、[外観] → [テーマエディター]から、更にfunctions.phpを選択し、ファイル最下部に以下のコードを貼り付けてください。いわゆるショートコードを作る作業です。
wordpress_mysql_show_data_02.jpg

【貼布するPHPコード】
ここでははshow_tableという名前のショートコードを作成しています。

functions.php
function show_table_func()
{
    global $wpdb;
    $rows = $wpdb->get_results("SELECT * FROM $wpdb->member_list");

    echo "<br> 独自テーブルのデータを表示 <br><hr>";

    // データベースのレコード毎に出力処理を行う。![wordpress_mysql_show_data_05.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/609490/b71e36f4-12e0-92bb-c2be-de1b55201744.jpeg)

    if ($rows) {
        foreach ($rows as $row) {
            echo "<img border='0' src='".$row->image."' width='50' height='50'>";
            echo "<p>名前:".$row->name."</p>";
            echo "<p>出身地:".$row->birthplace."</p>";
            echo "<p>主な実績:".$row->performance."</p>";
            echo "<hr>";
        }
    }
    return;
}
add_shortcode('show_table', 'show_table_func');

ショートコードによる独自テーブルデータの表示

新規投稿を選択して、本文に[show_table]と入力してください。これにより、先ほど作成したショートコードを呼び出します。あとは通常通り公開でOKです。

wordpress_mysql_show_data_04.jpg

【アップロードした投稿の表示確認】
期待通りに実行が完了していれば、以下のようにデータベースに格納したデータがリストで表示されます。
wordpress_mysql_show_data_05.jpg

細かいデザインなどは、CSSなど調整してお好みで変えてください！！

ということで、WordPressの独自テーブルに格納したデータを記事内で表示する方法についての説明でした。

WordPress上でfunctions.phpを編集すると構文チェックなど最低限のエラーチェックをしてくれるので、割と便利です。ただし、誤ったコードを書くと、最悪の場合WordPress自体が動かなくなってしまったりするので、バックアップを事前にとるなどリスクへの対処はお忘れなくです。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む
  • 投稿日：2020-03-28T00:01:47+09:00

MySQL公式テストデータ徹底解説

この記事について

DBやSQLを実際に触って練習しようとなったときに真っ先に困ることといえば、ある程度まとまった大きさのデータが入手しにくい！ということです。
適当にWebサイトスクレイピングしてSQLのinsert文に加工→それをDBに突っ込むでもいいのですが、時間がかかる上に作業が面倒です。
そんな人のために、MySQLでは公式ページでテストデータが配布されています。これをインポートするだけで、SQLのクエリを叩くのにいい練習になりそうなちょうどいいテーブルを簡単に作ることができます。
本記事では、テストデータをインポートする方法・各テーブル・Culumnの意味といったデータの詳細な情報について記述し、すぐにテストデータを触っ手のSQLの練習ができるようになるようサポートします。

使用する環境・バージョン

  • OS : MacOS Mojave ver 10.14.5
  • mysql : Ver 8.0.18 for osx10.14 on x86_64 (Homebrew)

使用する公式テストデータは以下のリンクから入手可能です。
MySQL :: Other MySQL Documentation

読者に要求する前提知識

  • 主キー、外部キー、E-R図といったDBの基本的な知識があること。

ファイルのインポート

上述したURLから、公式データのSQLファイルを入手します。
そのSQLファイルが置いてあるディレクトリにcdコマンドで移動してから以下のコマンドを実行します。

$ mysql -u root -p
パスワード入力
mysql> source [ファイル名].sql

world database

world内には、世界の国・都市・言語の情報が3個のテーブルに分かれて格納されています。

E-R図と各テーブルの概要

テーブルに格納されているデータ概要は以下の通り。
world.png

  1. country : 世界の国の基本情報
  2. city : 世界の都市の基本情報
  3. countrylanguage : 各国で使われている言語の情報

1.country(データ数：239)

Column一覧

注：以下、テーブルの主キーを太字+斜体で、外部キーとその参照先の対応を色文字で表す。

  • Code : アルファベット大文字3つからなる国コード
  • Name : 国の英名
  • Continent : どこの大陸にあるか
    ['Asia','Europe','North America','Africa','Oceania','Antarctica','South America']の中から選択
  • Region : 国の大まかな位置
    (例) Eastern Europe(東ヨーロッパ), Middle East(中東)など
  • SurfaceArea : 領土面積[$km^2$]
  • IndepYear : 独立年
  • Population : 人口
  • LifeExpentancy : 平均寿命
  • GNP : 国民総生産(単位、該当年不明)
  • GNPOld : 前年の国民総生産(単位、該当年不明)
  • LocalName : 現地語での国名
  • GovernmentForm : 政治体制
    (例) Constitutional Monarchy(立憲君主制)、Republic(共和制)など
  • HeadOfState : 国家元首の名前
  • Capital : 首都のcityテーブルでのID
  • Code2 :アルファベット大文字2つからなる国コード

(例)

Code Name LocalName Capital Code2
JPN Japan Nihon/Nippon 1532 JP
DEU Germany Deutschland 3068 DE

2.city(データ数：4079)

Column一覧

  • ID : 自然数の通し番号
  • Name : 都市名
  • CountryCode : アルファベット大文字3つからなる国コード
    • countryテーブル"Code"の外部キー
  • District : その都市がある行政区

(例)

ID Name CountryCode District
1536 Sapporo JPN Hokkaido
3796 Houston USA Texas

3.countrylanguage(データ数：984)

Column一覧

  • CountryCode : アルファベット大文字3つからなる国コード
    • countryテーブル"Code"の外部キー
  • Language : 言語名
  • IsOfficial : 公用語かどうか
  • Percentage : 使用率

(例)フィンランドの場合

Country Language IsOfficial Percentage
FIN Estonian F 0.2
FIN Finnish T 92.7
FIN Russian F 0.4
FIN Saame F 0.0
FIN Swedish T 5.7

sakila database

sakila内には、ある架空の貸しDVD屋の業務データが16個のテーブルに分かれて格納されています。

E-R図と各テーブルの概要

テーブルに格納されているデータ概要は以下の通り。
sakila.png

  1. actor : 俳優の情報
  2. address : 住所録
  3. category : 映画に付けられるカテゴリ一覧(アクション・ホラーなど)
  4. city : 世界の都市の情報
  5. country : 世界の国の情報
  6. customer : 顧客情報
  7. film : 映画の情報
  8. film_actor : 映画に出演している俳優の情報 ← actorとfilmの中間テーブル
  9. film_category : 映画のカテゴリ情報 ← categoryとfilmの中間テーブル
  10. film_text : 映画の説明文の一覧
  11. inventory : どの店にどの映画DVDの在庫があるかの一覧
  12. language : 言語の一覧
  13. payment : 支払い記録
  14. rental : レンタル先・日時の記録
  15. staff : スタッフ名簿
  16. store : 店舗情報

1.actor(データ数：200)

Column一覧

  • actor_id : 俳優1人1人につく通し番号
  • first_name : ファーストネーム(英大文字)
  • last_name : 下の名前(英大文字)
  • last_update : データが追加されたタイムスタンプ

2.address(データ数：603)

Column一覧

  • adress_id : 自然数の通し番号
  • adress : 住所(いわゆるAddress Line1)
  • adress2 : 住所その２(Address Line2)
  • district : 所在地区(州)
  • city_id : 所在地の都市id
    • cityテーブル"city_id"の外部キー
  • postal_code : 郵便番号
  • phone : 電話番号
  • location : 所在地の位置情報
  • last_update : データが追加されたタイムスタンプ

(例)

address_id address district city_id postal_code phone
6 1121 Loja Avenue California 449 17886 838635286649
10 1795 Santiago de Compostela Way Texas 295 18743 860452626434

3.category(データ数：16)

Column一覧

  • category_id : 自然数の通し番号
  • name : カテゴリ名
  • last_update : データが追加されたタイムスタンプ

4.city(データ数：600)

Column一覧

  • city_id : 自然数の通し番号
  • city : 都市名
  • country_id : 都市のある国id
    • countryテーブル"country_id"の外部キー
  • last_update : データが追加されたタイムスタンプ

(例)

city_id city country_id
89 Braslia 15
205 Hiroshima 50

5.country(データ数：109)

Column一覧

  • country_id : 自然数の通し番号
  • country : 国名
  • last_update : データが追加されたタイムスタンプ

6.customer(データ数：599)

Column一覧

  • customer_id : 自然数の通し番号
  • store_id : よく使う店舗(ホーム)
    • storeテーブル"store_id"の外部キー
  • first_name : ファーストネーム
  • last_name : 下の名前
  • email : メールアドレス
  • address_id : 住所id
    • adressテーブル"address_id"の外部キー
  • active : 在籍しているか退会しているか(0or1の値)
  • create_date : DB登録日
  • last_update : データが追加されたタイムスタンプ

(例)

customer_id store_id first_name last_name address_id active create_date
4 2 BARBARA JONES 8 1 2006-02-14 22:04:36
271 1 PENNY NEAL 276 0 2006-02-14 22:04:36

7.film(データ数：1000)

Column一覧

  • film_id : 自然数の通し番号
  • title : 映画タイトル
  • description : 概要文
  • release_year : 公開年
  • language_id : 言語id
    • languageテーブル"language_id"の外部キー
  • original_language_id : 翻訳前の元言語id
    • languageテーブル"language_id"の外部キー
  • rental_duration : レンタル期間[day]
  • rental_rate : rental_duration日の間DVDを借りる値段[$]
  • length : 映画の長さ[min]
  • replacement_cost : 期限までに返却されない時・DVDを壊れた状態で返却した時の追加料金[$]
  • rating : 公開レーティング
    ['G','PG','PG-13','R','NC-17']のどれか
  • special_features : DVD特典映像の種類
    ['Trailers','Commentaries','Deleted Scenes','Behind the Scenes']から複数選択
  • last_update : データが追加されたタイムスタンプ

(例)

film_id title language_id rental_duration rental_rate length rating special_features
86 BOOGIE AMELIE 1 6 4.99 121 R Commentaries,Behind the Scenes
674 PET HAUNTING 1 3 0.99 99 PG Trailers,Commentaries

8.film_actor(データ数：5462)

Column一覧

  • actor_id : 出演役者のid
    • actorテーブル"actor_id"の外部キー
  • film_id : 映画のid
    • filmテーブル"film_id"の外部キー
  • last_update : データが追加されたタイムスタンプ

9.film_category(データ数：1000)

Column一覧

  • film_id : 映画のid
    • filmテーブル"film_id"の外部キー
  • category_id : カテゴリid
    • categoryテーブル"category_id"の外部キー
  • last_update : データが追加されたタイムスタンプ

10.film_text(データ数：1000)

Column一覧

  • film_id : 映画id
  • title : タイトル
  • description : 説明文

これはテーブル7"film"の3つの列を抜き出したものと同じです。データの追加・更新・削除もトリガーで同期されています。
MySQL5.6.10以前のInnoDBでは、descriptionで使われているデータ構造"text"の検索がサポートされていなかったため、該当箇所だけMyISAMで作り直したのがこの"film_text"テーブルです。

11.inventory(データ数：4581)

Column一覧

  • inventory_id : 自然数の通し番号
  • film_id :
    • filmテーブル"film_id"の外部キー
  • store_id :
    • storeテーブル"store_id"の外部キー
  • last_update : データが追加されたタイムスタンプ

(例)

inventory_id film_id store_id
1 1 1
2 1 1
5 1 2

12.language(データ数：6)

Column一覧

  • language_id : 自然数の通し番号
  • name : 言語名
  • last_update : データが追加されたタイムスタンプ

13.payment(データ数：16049)

Column一覧

  • payment_id : 自然数の通し番号
  • customer_id : 支払いをした顧客番号
    • customerテーブル"customer_id"の外部キー
  • staff_id : 対応した店員番号
    • staffテーブル"staff_id"の外部キー
  • rental_id : 支払いがレンタル料金の支払いだった場合、そのレンタルid
    (延長料金の支払いなどで対応していない場合はなし)
    • rentalテーブル"rental_id"の外部キー
  • amount : 支払い額[$]
  • payment_date : 取引日
  • last_update : データが追加されたタイムスタンプ

(例)

payment_id customer_id staff_id rental_id amount payment_date
56 2 1 14475 4.99 2005-08-21 13:24:32

14.rental(データ数：16044)

Column一覧

  • rental_id : 自然数の通し番号
  • rental_date : 貸出日
  • inventory_id : 貸したDVDのid
    • inventoryテーブル"inventory_id"の外部キー
  • customer_id : DVDを借りた顧客番号
    • customerテーブル"customer_id"の外部キー
  • return_date : 返却日
  • staff_id : 対応した店員番号
    • staffテーブル"staff_id"の外部キー
  • last_update : データが追加されたタイムスタンプ

(例)

rental_id rental_date inventory_id customer_id return_date staff_id
88 2005-05-25 14:13:54 2221 53 2005-05-29 09:32:54 2

15.staff(データ数：2)

Column一覧

  • staff_id : 自然数の通し番号
  • first_name : ファーストネーム
  • last_name : 下の名前
  • address_id : 住所id
    • adressテーブル"address_id"の外部キー
  • picture : 写真(blob型)
  • email : メールアドレス
  • store_id : いつも務めている店のid
    • storeテーブル"store_id"の外部キー
  • active : 在籍しているかどうか。0or1
  • username : 業務システムでのユーザーネーム
  • password : 業務システムでのPW
  • last_update : データが追加されたタイムスタンプ

(例)

staff_id first_name last_name address_id active username password
4 BARBARA JONES 8 1 2006-02-14 22:04:36
271 PENNY NEAL 276 0 2006-02-14 22:04:36

16.store(データ数：2)

Column一覧

  • store_id : 自然数の通し番号。主キー
  • manager_staff_id : 店長の店員id
    • staffテーブル"staff_id"の外部キー
  • address_id : 住所id
    • adressテーブル"address_id"の外部キー
  • last_update : データが追加されたタイムスタンプ

(例)

store_id manager_staff_id address_id
1 1 1
2 2 2

employee database

employee内には、架空の会社の人事情報が6個のテーブルに分かれて格納されています。

E-R図と各テーブルの概要

テーブルに格納されているデータ概要は以下の通り。
employ.png

  1. departments : 会社の部署一覧
  2. dept_emp : 社員の部署所属一覧　←departmentsとemployeesの中間テーブル
  3. dept_manager : 部署のマネージャー一覧　←departmentsとemployeesの中間テーブル
  4. employees : 社員名簿
  5. salaries : 社員の給与履歴
  6. titles : 社員の役職履歴

1.departments(データ数：9)

Column一覧

  • dept_no : 部署の通し番号
  • dept_name : 部署名

(例)

dept_no dept_name
d001 Marketing
d007 Sales

2.dept_emp(データ数：331603)

Column一覧

  • emp_no : 社員の通し番号
    • employeesテーブル"emp_no"の外部キー
  • dept_no : 社員の所属部署の番号
    • departmentsテーブル"dept_no"の外部キー
  • from_date : 勤務開始日
  • to_date : 勤務終了日

(例)

emp_no dept_no from_date to_date
10001 d005 1986-06-26 9999-01-01
10011 d009 1990-01-22 1996-11-09

3.dept_manager(データ数：24)

Column一覧

  • emp_no : 部長を務める社員の通し番号
    • employeesテーブル"emp_no"の外部キー
  • dept_no : 部署の番号
    • departmentsテーブル"dept_no"の外部キー
  • from_date : 勤務開始日
  • to_date : 勤務終了日

4.employees(データ数：300024)

Column一覧

  • emp_no : 社員の通し番号
  • birth_date : 誕生日
  • first_name : 名字
  • last_name : 下の名前
  • gender : 性別。MかF
  • hire_date : 雇用開始日

(例)

emp_no birth_date first_name last_name gender hire_date
10044 1961-09-21 Mingsen Casley F 1994-05-21

5.salaries(データ数：2844047)

Column一覧

  • emp_no : 社員の通し番号
  • salary : 給料
  • from_date : 給料の試算開始日
  • to_date : 給料の試算終了日

(例)

emp_no salary from_date to_date
12658 73254 1985-03-28 1986-03-28

6.titles(データ数：443308)

Column一覧

  • emp_no : 社員の通し番号
  • title : 職種
    (例) Engineer, Technique Leader, Staff など
  • from_date : 着任日
  • to_date : 解任日

(例)

emp_no title from_date to_date
13959 Engineer 1985-05-15 1992-05-15
14005 Technique Leader 1985-05-10 9999-01-01

さいごに

これで、公式データの各テーブル間の関係・各Columnの意味がわかったかと思います。これがわかれば、テーブル結合を使ったSQL文の練習もガンガンできるのではないでしょうか。
また、公式データにはインデックスやパーティションといった、効率的に利用するための各種設定が最初から行われています。ですので、

  • どうしてこの列にはインデックスが張ってある/ないんだろう？
  • この2つの列の間には関連があるのに、どうして外部キー設定がないんだろう？
  • どうしてデータを1つのテーブルにまとめないで2つに分けたんだろう？

といったことを考察すると、DB構成に関する理解が深まると思います。

  • このエントリーをはてなブックマークに追加
  • Qiitaで続きを読む

日付別

タグ一覧