MySQLの集合内包使ってたらすごく遅くなったので、集合を分割して何回かにわけてSQL文飛ばしたらなんか動いた

なぜ動いたのかはよくわからない...
けど、メモリに乗る個数とかに上限があるのかな...知らんけど...
(ただし一回の集合内包をチェックする数減らしすぎるとそれそれで悪くなるっぽい...)

内容

前回の記事でアプリ用のコンテナを作成しました。
今回はDocker上でMySQLのコンテナを構築し実際のデータ投入とアクセスをするまでを行っていきます。

前回までの内容

Dockerで【TypeScript+Vue+Express+MySQL】の環境を構築する方法~Vue編~

作業手順

1. DBコンテナの作成
2. テストデータの投入
3. MySQLへのアクセス

1. DBコンテナの作成

前回作成したappコンテナとは別にdbコンテナを作成していきます。
最終的なディレクトリ構成は下記の通りです。

├─ app
├─ db
│   └─ conf
│        └─ my.cnf     //　追加
│   └─ init_db
│        └─ test.sql   // 追加
├─ docker
│   └─ app
│   └─ db
│      └─ Dockerfile   // 追加
├─ .env                // 追加
└─ docker-compose.yml  // 編集

docker-compose.yml

version: "3"
services:
  app:
    container_name: app_container
    build: ./docker/app
    ports:
      - 8080:8080
    volumes:
      - ./app:/app
    stdin_open: true
    tty: true
    environment:
      TZ: Asia/Tokyo
    command: yarn serve
# 前回のアプリケーションコンテナは編集しない
# 下記を追加
  db:
    container_name: db_container
    build: ./docker/db
    image: mysql:5.7
    ports:
      - 3306:3306
    volumes:
      - ./db/conf/my.cnf:/etc/mysql/conf.d/mysql.cnf  # MySQLの設定をバインド
      - ./db/init_db:/docker-entrypoint-initdb.d      # 初期データ投入用のsqlファイルをバインド
      - test_data:/var/lib/mysql                      # 永続化したデータをバインド
    environment:
      - MYSQL_DATABASE=${MYSQL_DATABASE}              # コンテナの環境変数から各種設定を読み込み
      - MYSQL_USER=${MYSQL_USER}
      - MYSQL_PASSWORD=${MYSQL_PASSWORD}
      - MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}
      - TZ="Asia/Tokyo"

volumes:
  test_data:                                           # データの永続化

上記でappコンテナとdbコンテナを同時に立ち上げる設定が完了です。
アクセスするデータベース名、ユーザー名、パスワードは[environment]からコンテナの環境変数を参照することで実行します。

/docker/db/Dockerfile

FROM mysql

EXPOSE 3306

CMD ["mysqld"]

ポートは3306に設定しておきます。

db/conf/my.cnf

[mysqld]
character-set-server=utf8

[mysql]
default-character-set=utf8

[client]
default-character-set=utf8

MySQLの文字コードを設定。

.env

MYSQL_DATABASE=test_db  //後述するデータベース名を設定
MYSQL_USER={適当なユーザー名を設定}
MYSQL_PASSWORD={MySQLアクセス時のパスワードを設定}
MYSQL_ROOT_PASSWORD={MySQLアクセス時のパスワードを設定}

こちらで設定したものがコンテナ起動時に参照され、コンテナの環境変数に設定されます。
ログインパスワードは上記で設定したパスワードとなります。

※.envファイルはGitに含まれないように.gitignoreで除外しておきましょう。
間違ってもpushしないように！

2.テストデータの投入

db/init_db/test.sql

DROP DATABASE IF EXISTS test_db;
CREATE DATABASE test_db;

USE test_db;

DROP TABLE IF EXISTS test;

CREATE TABLE test (
  id int NOT NULL AUTO_INCREMENT primary key,
  name varchar(30),
  description varchar(255)
);

INSERT INTO test (id, name, description) VALUES (1, 'test1', 'テストデータ1です。');
INSERT INTO test (id, name, description) VALUES (2, 'test2', 'テストデータ2です。');
INSERT INTO test (id, name, description) VALUES (3, 'test3', 'テストデータ3です。');
INSERT INTO test (id, name, description) VALUES (4, 'test4', 'テストデータ4です。');
INSERT INTO test (id, name, description) VALUES (5, 'test5', 'テストデータ5です。');
INSERT INTO test (id, name, description) VALUES (6, 'test6', 'テストデータ6です。');
INSERT INTO test (id, name, description) VALUES (7, 'test7', 'テストデータ7です。');
INSERT INTO test (id, name, description) VALUES (8, 'test8', 'テストデータ8です。');
INSERT INTO test (id, name, description) VALUES (9, 'test9', 'テストデータ9です。');
INSERT INTO test (id, name, description) VALUES (10, 'test10', 'テストデータ10です。');

確認用のデータとしてtest_db データベース > test テーブル を作成します。
カラムは適当にID, Name, Descriptionなどのデータを挿入。

3. MySQLへアクセス

ここまでで事前準備が完了です。
ここからは実際にアクセスしてデータが取得できるか確認します。

DBコンテナビルド

$ docker-compose build

DBコンテナ起動

$ docker-compose up -d

コンテナ起動確認

$ docker ps

db_container が起動中であればOK！

コンテナへアクセス

$ docker exec -it db_container sh

MySQLへログイン

$ mysql -u root -p -h 127.0.0.1

Enter password: // .envに記載したパスワードを入力

mysql>          // これが出てくればMysqlへのアクセスが完了！

データベース確認

SHOW DATABASES;

用意したデータベース[test_db]が存在すれば成功！

使用するデータベースを指定

USE test_db;

テーブルを確認

SHOW TABLES;

作成した[test]テーブルが存在すればOK！

testテーブルのデータを確認

SELECT * FROM test;

test テーブルに保存したデータ一覧が表示されれば問題なく完了！

ここまで確認がとれれば問題なくMySQLにアクセスできている状態です！
お疲れ様でした！

mysqlから抜ける

mysql> exit
Bye 
# // こうなっていればMySQLからログアウトしてコンテナにアクセスしている状態

コンテナから抜ける

exit

コンテナを停止

docker-compose stop

お疲れ様でした！
ここまででDockerコンテナ上でMySQLを立ち上げて、データを挿入＆アクセスができる環境構築が完了です！
アプリコンテナと実際に操作するデータベースができたので、次回はデータの操作を行うAPIサーバーコンテナの作成を行っていきます！

次回

Dockerで【TypeScript+Vue+Express+MySQL】の環境を構築する方法~Express編~

参考

Dockerで【TypeScript+Vue+Express+MySQL】の環境を構築する方法~Vue編~
Dockerで【TypeScript+Vue+Express+MySQL】の環境を構築する方法~Sequelize編~

作業メモ

実施日:　2020年 9月19日
環境:　macOS Catalina10.15.6

サクサクできると思っていたら
2日かかってしまったので、メモ代わりに残しておきます。

状況

PHP+MySQLの学習を始めるにあたって購入した教材が一通り終わる。
その際の環境構築は教材に習い、XAMPPをインストールして実施。

せっかくMacを使っているので、MacOS向けのツールを使用した環境構築に切り替えたい。
（つまりはミーハーな理由）

変更ツール

① パッケージをXAMPPからMAMPに変更
（XAMPPのアンインストールは未実施）

② DBのGUIツールをphpMyAdminからSequel Proに変更
（Ruby学習中はSequel Proを使用していたので統一したい）

手順

① MAMPをインストール

・MAMP
　　インストーラーのダウンロードはこちらから

Free Downloadに飛んでインストーラーをダウンロードします。

ポイント：今回はMAMP PROではなく、MAMP(無料版)だけインストールしたいので
インストーラーメニューのインストールの種類という項目で「カスタマイズ」を選択して
MAMP PROのチェックを外しましょう。数MBですが容量を節約します。

・比較的新しいQiita参考記事:
　MAMP(FREE)のインストールから表示まで（MAC ）

インストールが完了したら、Apache ServerとMySQL Serverが立ち上がることを確認します。

② XAMPP環境にあったファイルをMAMP環境にコピーする

XAMPPを使用していたら、もうおなじみかと思いますが
XAMPP内のhtdocs内で作成したディレクトリやファイル群をコピーして
MAMP内のhtdocsにも入れてあげます。

③ MAMP環境でウェルカムページを表示させる。

MAMPでサーバーを立ち上げたら、Open WebStart pageのボタンを押して

localhost:8888/MAMP/?language=English
いわゆるMAMPのウェルカムページに遷移しましょう。

画像のような画面が表示されたらOKです。

④ MAMP環境でhtdocs内のページを表示させる。

上のWelcome to MAMP画面が表示されたら
アドレスバーでパス指定してhtdocsにコピーしたファイルが表示されるか確認しましょう。

この時はDB接続が必要ないTOP画面のファイルが無難です。
とりあえずはhtmlファイルでも大丈夫です。

初期設定時はhtdocsより下の階層からパスで指定する必要があります

大体の人は
localhost:8888/ディレクトリ名/ファイル名
の流れで表示されると思います。

⑤ XAMPP環境のDB情報をコピーする。

ここからはXAMPPのDB情報をMAMP環境へコピーしていきます。

まずはMAMPのサーバーを一旦停止してXAMPPでサーバーを立ち上げます。
立ち上がったらphpMyAdminへ飛びましょう。

XAMPP環境でphpMyAdminに入れたら
DBのdumpファイルを作成していきます。

画面左側でコピーするデータベース名を選択します。
次は画面上部にあるエクスポートのメニューを選択します。

画面に以下のような表示がされていることを確認しましょう。

データベース "○○" からテーブルをエクスポート
（""の中にはデータベースの名称が入ります）

表示に問題なければ

エクスポート方法：簡易
フォーマット：SQL
エンコーディングへの変換：なし

で、エクスポートの実行をしましょう。
sql形式のファイルが作成されるはずです。

⑥ MAMP環境でDB情報を読み込む

前の章で作成したsqlファイルを利用して
MAMP側のDBを整えていきます。

まずはXAMPPのサーバーを停止してMAMPのサーバーを立ち上げましょう。
立ち上がったらウェルカムページからphpMyAdminに飛びます。

MAMP環境でphpMyAdminに入れたら
XAMPPと同じデータベース名、同じ形式でMAMP側にもデータベースを作成します。

同じ名称のデータベースが作成できたら
XAMPP側で作成したsqlファイルをインポートしていきます。

エクスポートした時と同じように
MAMP側のphpMyAdminで新しく作成したデータベース名を選択したら
上部メニューのインポートを選択しましょう。

ファイルを選択のボタンからXAMPP側のDBで作成したsqlファイルをアップロードし
その他はとりあえずデフォルトで実行ボタンを押してください。
sql内に書かれていたクエリが複数実行されると思います。

全部のクエリが実行され、XAMPP側と同じテーブル、同じレコードが作成されたら
MAMP環境へのDB情報の移行は終了です。

⑦ MAMP環境のMySQLへSequel Proで接続

最後にSequel Proを利用して、MAMP環境のMySQLへ接続していきます。

・Sequel Pro
　インストーラーのダウンロードはこちらから

・比較的新しいQiita参考記事
　Mac MySQL Sequel Proの導入方法

インストールが完了したらSequel Proを立ち上げます。

画像と同じ画面が表示されたらソケットタブにて情報を入力していきます。

名前: localhost
ユーザー名: root
パスワード: root
データベース:
ソケット: /Applications/MAMP/tmp/mysql/mysql.sock

【ポイント】
・ユーザー名とパスワードはMAMPインストール時から初期設定のままであれば、どちらもrootです。
・データベースはとりあえず空欄推奨。
・ソケットはmysql.sockまでのパスを入力します。

以上の情報を入力したら接続をテストボタンを押して
テストが成功したら接続ボタンを実行しましょう。

接続が完了したら
Sequel Pro画面左上のデータベースを選択をクリックして
MAMPのphpMyAdminにて先ほど作成したデータベース名が表示されると思います。

そちらを選択してphpMyAdminと同じ情報が表示されていればOKです。

これにて記事タイトルの内容は終了です。

その他おまけ

【注意事項】

① MAMP側のhtdocs配下に置くソースコードはXAMPP側からコピーした後に一部修正する必要があります。
DBへ接続するPDOに関する記述は要修正です。

例：　new PDO()の中のpasswordに関する部分。

XAMPP環境ではpasswordがデフォルトで空だったと思いますが
MAMP環境ではデフォルトでrootになっています。

② サーバーのポート番号を変更する場合はMAMPアプリケーションのメニューから行うことはオススメしない

こちらのメニューにある
Set Web & MySQL ports to 80 & 3306　ボタンを押すと
一発でApache,Nginx,MySQLのポート番号が切り替わるのですが
このボタンを押してポート番号を変更すると、WebサーバーからDBサーバーへ、うまく接続できませんでした。
（私だけであればいいのですが...。）

【Mac】MAMPのエラーでポート番号を変更できない場合の解決方法
私はこちらの記事を参考にしました。

MySQLのポート番号はMAMPアプリケーションで変更し、Apacheのポート番号は
/アプリケーション/MAMP/conf/apache/httpd.conf

のソースコードを書き換える事で無事解決できました。

最後に

ザッと手順を思い出しながら書きましたので、もしかしたら間違いがあるかもしれません。
その際はコメントにて教えていただけると助かります。

前提と準備

C#の記事

• C#でMySQLを使う - 1.開発環境インストール
• C#でMySQLを使う - 2.SELECT・画面表示【この記事】

前回はVisual Studio 2019にMySQL Connectors .NETを用いて、開発環境を準備する作業でしたが、実際にMySQL Connectors .NETでMySQLに接続して、SELECTを用いてデータの内容を画面に表示します(˶ ･ᴗ･ )੭

環境

• OS：Windows 10 Pro
• 開発環境：Visual Studio 2019 (MySQL Connectors .NET利用)
• データベース：MySQL 5.7

前提

前回のように、Visual Studio 2019とMySQLをインストールし、かつMySQL Connectors .NETがインストールされていること(Visual Studioは自動でMySQL Connectorsのライブラリを認識してくれる)

作業手順

Visual Studio 2019でのコーディング

コンポーネント・コントロールの配置

今回はボタンを押下→MySQLでデータを拾ってくる→画面に表示
という、簡単な使い方について記事にしました。

とりあえず、WindowsのC#フォームのプロジェクトを新規作成し、Form1.csがデフォルトで開かれると思うので、まずはフォームに、

• 左上：データグリッド dataGridView1
• 右上：リストボックス2つ listBox1・listBox2
• 左下：SQLを読み込むボタン button1

これらを貼り付けます。
データグリッドとリストボックスはSQLのデータを表示させるために配置しました

MySQL Connector .NETの参照を追加

前回の記事の通り、参照を追加していきます。

「アセンブリ」の中に実際自動で認識してくれるので、参照マネージャーで、右上の検索画面に「mysql」を入力すると、↑の画面のように「MySql.Data」が何行も出てくるので、とりあえず1つだけ、どれでもいいので選択して(行左にマウスを当てると出てくるチェックボックスをON)「OK」を選択すると…

「MySql.Data」が追加されました(*˘꒳˘*)
この中にMySQLを扱うC#オブジェクトが入っているのです。

ボタンクリック時のソースを記述する

今回はSQLをボタンクリックで読み込むだけなので、Form1.csのコード部分だけをコーディングするので、button1コントロールをダブルクリックして、クリックイベントを自動で生成するので、そのイベント動作をコーディングします。

Form1.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace MySqlFormsTest
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        /** Visual Studioではボタンコントロールをダブルクリックすると、自動で生成される
         * 接続ボタン(MySQLサーバーに接続し、一覧データを取得する)
         * */
        private void button1_Click(object sender, EventArgs e)
        {
            string connStr = "server=127.0.0.1;user id=test;password=test1;database=manutest";
            MySqlConnection conn = new MySqlConnection(connStr);

            try
            {
                // 接続を開く
                conn.Open();

                // データを取得するテーブル
                DataTable tbl = new DataTable();

                // SQLを実行する
                MySqlDataAdapter dataAdp = new MySqlDataAdapter("SELECT id, name, memo FROM testtb", conn);
                dataAdp.Fill(tbl);

                // データグリッドに表示させる
                dataGridView1.DataSource = tbl;

                // 実行結果を1行ずつ参照する場合
                for (int i = 0; i < tbl.Rows.Count; i++)
                {
                    DataRow row = tbl.Rows[i];  // データ行

                    // 右のリストボックスにアイテムを追加
                    listBox1.Items.Add(row[1]);
                    listBox2.Items.Add(row[2]);
                }

                // 接続を閉じる
                conn.Close();
            }
            catch (MySqlException mse)
            {
                MessageBox.Show(mse.Message, "データ取得エラー", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
    }
}

MySQL ConnectorsのC#に関する公式の根拠が出回っていなかったので、第三者の情報と実際Visual Studioのコーディングでポップアップされるトピックを頼りにするしかなかったのです…( ´ •̥ ̫ •̥ ` )…なので、データの取得に「MySqlDataAdapter」だったり「MySqlCommand」だったり統一されずに混在している状態で、用途もはっきりしていないが、私はMySqlDataAdapterを使っています

string connStr = "server=127.0.0.1;user id=test;password=test1;database=manutest";

これが接続コマンドで、serverはサーバーのホスト、user idにMySQLのID、passwordはパスワード、そしてdatabaseは接続するDB名ですが、他の記事を見ていると、uidだったりUserIdだったり混在していて、あまり統一されていないように見えました。。。

MySqlDataAdapter dataAdp = new MySqlDataAdapter("SELECT id, name, memo FROM testtb", conn);
dataAdp.Fill(tbl);

ここの部分は「SELECT id, name, memo FROM testtb」というSQLを実行し、実行結果をデータテーブルに格納する部分です。

// データグリッドに表示させる
dataGridView1.DataSource = tbl;

この部分は左上のデータグリッドdataGridView1にSELECTで取得したデータを表示することを意味し、DataSourceにSQLを実行したテーブルをセットするだけで簡単に表示できます。

一方、

// 実行結果を1行ずつ参照する場合
for (int i = 0; i < tbl.Rows.Count; i++)
{
    DataRow row = tbl.Rows[i];  // データ行

    // 右のリストボックスにアイテムを追加
    listBox1.Items.Add(row[1]);
    listBox2.Items.Add(row[2]);
}

こんなふうに、取得したテーブルtblの各行は、tbl.Rows[i]で行を取得することができて、その中の各データを配列の形で取り出すことができるんです。このことはC#のMySQLに限らずC#のDataRowやDataTableの話になるのであまり細かいことは触れず。

それぞれの取得した個別のデータを右上のlistBoxに表示させています。

実行結果

次回

Visual StudioでMySQLのデータを表示できたので、次はデータの追加や修正、削除を予定しています٩(.› ‹.♡)۶

参考文献

1. 【Visual Studio】C#からMySqlの使い方　接続するには？SQL文を実行するには？ - 困ったー

はじめに やりたいこと

タイトル通り「挿入後のテーブルの一番お尻の行のidの値を取得」したい。

次のようなテーブルがあるとする。

• テーブル名：user_list

id	ユーザー名
1	はじめ君
2	次子ちゃん
3	三版君
4	四手ちゃん
5	五点さん

※idはオートインクリメントの属性があるため挿入のたびに自動で+1されるとする。

上記テーブルを下記のようにした際に一番お尻のidの値(今回だと6)を取得する方法を紹介する

-- (コメント)：id=1のユーザーを一番おしりに挿入。
INSERT INTO user_list (ユーザー名)
SELECT ユーザー名 FROM user_list WHERE id = 1;

※ちなみに上のようにINSERTとSELECTを組み合わせる場合はVALUESはいらないのです！(INSERT...SELECT構文)

• テーブル名：user_list

id	ユーザー名
1	はじめ君
2	次子ちゃん
3	三版君
4	四手ちゃん
5	五点さん
6	はじめ君

[やり方1] idの大きい順に並べ替えて一番最初の行のidを取得

まず並べ替えの方法を復習しましょう。
あるカラム(列)を基準にソート(並べ替え)するにはORDER BY句を用います。また逆順のソートをするにはDESCキーワードを追加します。また、LIMIT 句を使用すると、SELECT ステートメントによって返される行数を制約させることが出来ます。

ORDER BY 基準のカラム(列) DESC LIMIT 制限数;

※参考：SELECT 構文/MySQL公式

以上から(1)「idをORDER BY句とDESCで大きい順に並べて」(2)「LIMIT句で1に限定」すれば、idの一番大きい値が取得出来ることがわかるでしょう。

実際にやってみましょう。

SELECT id FROM user_list ORDER BY id DESC LIMIT 1;
->6

[やり方2] 情報関数を用いて挿入されたidの値を取得

MySQLでは標準で便利な関数が数多く用意されています。今回はそういった関数の中の情報関数の一つであるLAST_INSERT_ID()を用いて『前回の INSERT での AUTOINCREMENT カラムの値』を取得しましょう。

SELECT LAST_INSERT_ID();

こちらのほうが圧倒的に簡単ですね。

ただし公式ドキュメントにもある通りLAST_INSERT_ID関数は『AUTOINCREMENT カラムの値』を取得してくれるだけであって『最後にインサートしたレコードのID』では無いことに注意しましょう。
※参考：MySQLのlast_insert_id()は「最後にインサートしたレコードのID」じゃなかった/個人ブログ

本日は以上です。

ini_set('display_errors', 1);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);