20200715のMySQLに関する記事は7件です。

投稿日：2020-07-15T21:46:12+09:00

docker-composeによるRails6(APIモード)+MySQLのDocker環境作成(for Mac)

はじめに

バイト先で全面改修を進めていく上で、環境をdocker化しようという話が出ました。
フロントエンドNuxt.js、バックエンドRails6の構成で作ることになり、バックエンド部分を自分が担当することになったのでDockerfileをごりごり書いていきましょー！ということで勉強も兼ねて実装し、出来上がった構成を紹介します。
Railsコンテナの作り方は、Dockerの公式ページに簡単に書いてありますが、今回はRails6に考慮したDockerfileの書き方と、MySQLコンテナの作り方も合わせて紹介していこうと思います。

自身のスキル

個人でrailsを触り始めたのは1年半ほど前、webエンジニアとしての実務での開発経験は1年強と期間的には長くはありません。
しかし、大学でつけた幅広い前提知識や、どんどん色々なことに挑戦する姿勢を利用して素早く成長しています。
前のプロジェクトでもdocker周りには触らせていただいていて、データベースのコンテナの変更(MarinaDB->MySQL)や、初期設定用のシェルスクリプトの作成などを行っていました。
自分で1からdockerを作る機会がなかったので今回挑戦した形です。

docker環境作成

ここから先は実際にdockerによる環境を作っていきます。

ファイル構成

まずは簡単にファイル構成を紹介します。
作成するアプリケーションの名前をsampleとすると、今回関係するファイルは以下のようなファイル構成になっています。
sample 
|- backend --- app
|           |- bin
|           |- config --- ...
|           |          |- database.yml
|           |          |- ...
|           |- ...
|           |- Dockerfile
|           |- Gemfile
|           |- Gemfile.lock
|           |- ...
|- db --- data --- ...
|      |- my.cnf
|- docker-compose.yml
実際はもっと多くのファイルが存在する(作成される)ことになりますが、今回操作するファイルはこれらのファイルのみになります。

必要ファイルの作成

続いてdockerに関するファイルを記述していきます。

backend/Dockerfile

これはRailsのコンテナをビルドするための手順を記したファイルになります。内容を下に載せ、それぞれどのような処理を行っているのかをコメントで説明しています。
backend/Dockerfile
# 使用するイメージとバージョン
FROM ruby:2.7.1

# 必要なライブラリをインストール
RUN apt-get update -qq && \
    apt-get install -y build-essential \ 
                       libpq-dev \        
                       nodejs

# 以下はrails6以降(APIモード除く)で必要
RUN apt-get update && apt-get install -y curl apt-transport-https wget && \
curl -sS https://dl.yarnpkg.com/debian/pubkey.gpg | apt-key add - && \
echo "deb https://dl.yarnpkg.com/debian/ stable main" | tee /etc/apt/sources.list.d/yarn.list && \
apt-get update && apt-get install -y yarn

# コンテナ内にappというフォルダを作成
# ※ローカルPCにフォルダが作成されるわけではない
RUN mkdir /app
# ルートをappディレクトリに変更
ENV APP_ROOT /app
WORKDIR $APP_ROOT

# ローカルPC内のGemfile(.lock)をコンテナ内にコピー
ADD ./Gemfile $APP_ROOT/Gemfile
ADD ./Gemfile.lock $APP_ROOT/Gemfile.lock

# bundle installを実行しローカルPCのファイルたちをコンテナ内にコピー
RUN bundle install
ADD . $APP_ROOT

# コンテナがlistenするポート番号
EXPOSE 3000

# 実行されるコマンド
CMD ["rails", "server", "-b", "0.0.0.0"]
rails6からはWebpackerがデフォルトとなったので、yarnがインストールされていないとrails newをするときにエラーとなってしまいます。
ただし、後述のAPIモードで作成する場合にはyarnのインストールは不要です。

backend/Gemfile

RailsをインストーするためにあらかじめGemfileを記述しておきます。
このファイルは後ほどrails newをするときに書き換えられます。
ここではrails6以降のバージョンを指定しています。
backend/Gemfile
source 'https://rubygems.org'
gem 'rails', '~> 6'
backend/Gemfile.lock

bundle installした際に依存関係を元にライブラリの設計図のようなものを作ってくれます。
空のものを作成しておきます。ファイルが存在しないと後でエラーになりました。
backend/Gemfile.lock
db/data/

ディレクトリだけ用意しておいてあげましょう

db/my.cnf

MySQLの設定に関するファイルです。
my.cnf
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
sql_mode=''

[client]
default-character-set=utf8mb4
文字コードの設定やsql_modeの設定を行っています。
特にsql_modeの設定はしておいた方が良いと思います。
というのも、MySQL5.7からデフォルトのsql_modeが変更となっており、特にonly_full_group_byなどのモードで度々エラーを吐いてしまうことがあるので、どのsql_modeにするかは明示的に書いておきましょう。今回は設定なし(MySQL5.6.5以前のデフォルト)を明示的に指定しています。
(この辺りでも追々記事が書きたい)

docker-compose.yml

いよいよdocker-composeを書きます。
簡単な説明をコメントで記述してあります。
docker-compose.yml
version: "3"
services:
  # MySQL
  db:
    #ビルドするイメージ
    image: mysql:5.7
    # 環境変数の指定
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: spportunity
      MYSQL_USER: root
      MYSQL_PASSWORD: root
      TZ: 'Asia/Tokyo'
    # 文字コードをutf8mb4に設定
    command: mysqld --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
    volumes:
      # db/dataディレクトリをコンテナのmysqlディレクトリにマウント
      - ./db/data/:/var/lib/mysql
      # /db/my.cnfファイルをコンテナ内のmy.cnf設定ファイルにマウント
      - ./db/my.cnf:/etc/mysql/conf.d/my.cnf
    ports:
      - 3306:3306

  #Rails  
  backend:
    # ビルド元の指定
    build:
      context: ./backend
      dockerfile: Dockerfile
    # コンテナ名の指定
    container_name: "sample-backend"
    # 起動時のコマンド指定 前のプロセスを終了してからrails sをしている
    command: bash -c "rm -f tmp/pids/server.pid && rails s -p 3000 -b '0.0.0.0'"
    # backendフォルダ内のファイルをコンテナ内のappディレクトリにマウント
    volumes:
      - ./backend/:/app
    ports:
      - "3000:3000"
    # dbコンテナよりも後に起動
    depends_on:
      - db
これでファイルの準備はできたのでrailsアプリケーションを作成していきます。

railsアプリケーションの作成

docker-composeコマンドを利用してrailsアプリケーションを作成します。
現在のディレクトリがsampleであることを確認してください。

通常モードでrailsを作成(dockerfileでyarnのinstallが必要)
% docker-compose run backend rails new . --force --no-deps --database=mysql --skip-bundle
--force...Gemfileを書き換えます

--database=mysql...データベースにMySQLを指定します

--skip-bundle...bundleをスキップします(まだGemfile.lockは変更されません)

APIモードでrailsを作成
% docker-compose run backend rails new . --force --no-deps --database=mysql --skip-bundle --api
--api...APIモードでの作成オプション

完了すると、backend/フォルダ内にrails関連のフォルダやファイルが大量に出来上がります。
APIモードと通常モードの違いやファイルの差分はまた記事にします。

コンテナのビルド
% docker-compose build
このコマンドを実行することで、MySQLのコンテナの作成と、railsコンテナでのbundle installが行われます。
コンソールを見ているとDockerfileの内容を順番に実行している様子が分かるかと思います。

railsのdbへの接続設定
backend/config/database.yml
default: &default
  adapter: mysql2
  encoding: utf8mb4
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: root
  password: root
  host: db

development:
  <<: *default
  database: sample


test:
  <<: *default
  database: sample_test

production:
  <<: *default
  database: sample_production
  username: <%= ENV['APP_DATABASE_USER'] %>
  password: <%= ENV['APP_DATABASE_PASSWORD'] %>
接続情報を、hostを先ほど作成したMySQLコンテナのイメージ、ユーザー名とパスワードを先ほど作成したコンテナの環境変数のものに変更します。

(APIモードでなければ)Webpackerのインストール
% docker-compose run backend rails webpacker:install
データベースの作成
% docker-compose run backend rails db:create
dockerの起動

ここまで来れば設定は完了です。
あとはdockerを起動するのみです！
% docker-compose up
ブラウザでRailsが立ち上がっていること確認

ブラウザで http://localhost:3000/ へアクセスすると、無事Railsが立ち上がっています。

拡張性

今回は言及しませんでしたが、frontendコンテナなどを追加することによってフロントエンドアプリケーションとAPIサーバを兼ね備えた環境を一発で作ることができるようになります。
その際は、同様の手順でDockerfileの作成やdocker-composeへのコンテナの追加などを行っていくことになりまね。
僕はまだフロントエンド初心者なので、ゆくゆくはフロントのコンテナも作っていきたいです。

感想

やっぱりdockerは便利ですね。
自分個人の開発ではdockerは使っていませんでしたが、これからは積極的に使っていこうかなと思っています。
またその際には記事にしてあげていければと思います。

qiitaの記事を書くのが初めてで、どれくらいのものを書けばいいのか悩みながら書いていたら、だいぶ長くなってしまいました。
ただ、とても内容が濃い記事を何本も書いている人がたくさんいて、自分はまだまだだなぁと日々感じています。
僕はまだ内容があるものは頻繁には書けないかもしれませんが、少しずづ知識を記事化していければと思います。

参考にしたもの

以下のqiitaの記事を参考にさせていただきました。
書き方も真似させていただき、かなり参考になりました。
ありがとうございます。

https://qiita.com/azul915/items/5b7063cbc80192343fc0
https://qiita.com/kodai_0122/items/795438d738386c2c1966

上記の記事に加えて、APIモードやMySQLコンテナのマウントや設定なども盛り込んだ内容となっているので、書き方が似ている点はご容赦いただければと思います。

　 Tweet
Qiitaで続きを読む

投稿日：2020-07-15T16:32:31+09:00

PHPでフレームワークを使用せずに投稿サイトを作る

やりたいこと

PHPで管理画面も含めた用語集サイトを作ります。
わかりやすさを優先して、最低限のソースコードにしています。
管理者パスワード設定もないので、第三者が勝手にコンテンツを変更できる仕様です。
セキュリティが緩いので個人情報を取り扱わないでください。

仕様

トップページ（index.php）は用語名、用語のフリガナの一覧を表示する。
トップページの用語のハイパーリンクをクリックすると、用語詳細ページ（item.php）画面に遷移する。
用語詳細ページは、用語名、分類、用語のフリガナ、用語の内容を表示する。
管理画面で用語の新規登録、編集、削除ができる。
管理画面で用語の新規登録、編集、削除ボタンを押すと、確認ページ（xxxx_check.php）に遷移し実行ボタンを押すと、実行結果確認ページ（xxxx_done.php）に遷移する。

その他

サーバーはロリポップを契約、ドメインはムームードメインで取得しています。

ページ構成と画面遷移

ページ一覧

index.php
item.php
list.php
branch.php
add.php
add_check.php
add_done.php
edit.php
edit_check.php
edit_done.php
delete.php
delete_done.php
ng.php

画面遷移図

各ページのソースコード

index.php

<!DOCTYPE html>
<html lang="ja">
<head>
 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 <meta name="description" content="Webサイトのコンテンツ内容">
 <title>Webサイトのタイトル</title>
 <link rel="stylesheet" href="/main.css">
 <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
</head>

<body>
 <div class="wrapper">
  <div id="site_title"><a href="https://XXXX.com/">Webサイトのタイトル</a></div>
   <div class="top_page_title_list">
    <ul>
     <li>
      <?php
       try {
        //DB名、ユーザー名、パスワード
        $dsn = 'mysql:dbname=LAAXXXX-XXXX;host=mysqlXXX.phy.lolipop.lan'; //データベース名。''内は一切スペースを入れてはいけない
        $user = 'LAAXXXX'; //''内にはユーザ名を入力
        $password = 'XXXX';  //''内にはパスワードを入力

        $PDO = new PDO($dsn, $user, $password); //MySQLのデータベースに接続
        $PDO->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //PDOのエラーレポートを表示
        $sql = 'SELECT * FROM contents ORDER BY name asc'; //SELECT文を変数に格納。 contentsテーブルてからname列の値を取り出し、name列順に並び替え
        $stmt = $PDO->query($sql); // SQLステートメントを実行し、結果を変数に格納

        // foreach文で配列の中身を一行ずつ出力
        foreach ($stmt as $row) {
        // 取り出した一行を名称とフリガナで表示し、ハイパーリンク設定
        echo "<div class='title_name'><a href='http://XXXX.com/item.php?name=".$row['name']."'>".$row['name'].
        "<span class='reading'>(".$row['reading'].")</span></a></div>\n";
        }
       } catch (PDOException $e) {
       exit('データベースに接続できませんでした。' . $e->getMessage());
      }
     ?>
    </li>
   </ul>
  </div>
 </div>
</body>
</html>

item.php

<!DOCTYPE html>
<html lang="ja">
<head>
 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 <meta name="description" content="Webサイトのコンテンツ内容">
 <title>Webサイトのタイトル</title>
 <link rel="stylesheet" href="/main.css">
 <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
</head>

<body>
 <div class="wrapper">
  <div id="site_title"><a href="https://XXXX.com/">Webサイトのタイトル</a></div>
   <div class="detail_page_contents">
    <?php
     try {
      //URLのidの値を取得
      $name = $_GET['name'];

      //DB名、ユーザー名、パスワード
      $dsn = 'mysql:dbname=LAAXXXX-XXXX;host=mysqlXXX.phy.lolipop.lan'; //データベース名。''内は一切スペースを入れてはいけない
      $user = 'LAAXXXX'; //''内にはユーザ名を入力
      $password = 'XXXX';  //''内にはパスワードを入力

      $PDO = new PDO($dsn, $user, $password); //MySQLのデータベースに接続
      $PDO->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

      //名前付けされたプレースホルダを用いてプリペアドステートメントを実行
      // プレースホルダとはSQLを発行する際に後から値を指定する方法のこと。SQLインジェクションを回避してセキュリティを高める
      $result = $PDO->prepare('SELECT * FROM contents WHERE name = :name');
      $result->bindValue(':name', $name);
      $result->execute();

      while($row = $result->fetch(PDO::FETCH_ASSOC)){
       echo "<p>分類 | ".$row['category']."</p>";
       echo "<h1>".$row['name']." (".$row['reading'].")</span></h1>";
       echo "<p>".nl2br($row['description'])."</p>"; //nl2br関数で改行を反映する
      }

     } catch (PDOException $e) {
      exit('データベースに接続できませんでした。' . $e->getMessage());
     }
    ?>
   </div>
  </div>
 </div>
</body>
</html>

list.php

<!DOCTYPE html>
<html lang="ja">
<head>
 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 <meta name="description" content="Webサイトのコンテンツ内容">
 <title>Webサイトのタイトル</title>
  <link rel="stylesheet" href="/main.css">
  <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
</head>

<body>
<?php
//tryはデータベースの接続エラー対策
try
{
  //データベースに接続
  $dsn = 'mysql:dbname=LAAXXXX-XXXX;host=mysqlXXX.phy.lolipop.lan'; //データベース名。''内は一切スペースを入れてはいけない
  $user = 'LAAXXXX'; //''内にはユーザ名を入力
  $password = 'XXXX';  //''内にはパスワードを入力
  $dbh = new PDO($dsn, $user, $password);
  $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //この行は定型文としてこのまま書く

  // SQL分を使ってレコードを読み込み
  $sql ='SELECT name FROM contents WHERE 1'; //'SELECT name FROM contents WHERE 1'を変数$sqlに格納する。contentsテーブルのnameカラムのすべての情報(Where 1で全部という意味)を取得する
  $stmt = $dbh->prepare($sql); // レコードを呼び出す準備。この行は定型文としてこのまま書く
  $stmt->execute(); // SQL文で指令を出すための命令文。この行は定型文としてこのまま書く

  // データベースから切断するプログラム
  $dbh = null;

  // 一覧を表示する
  print '用語一覧<br /><br />';

  print '<form method="post" action="branch.php">';

  While(true){
    $rec = $stmt->fetch(PDO::FETCH_ASSOC); //$stmtから1レコードを取り出す
    if($rec==false){ //もし、$recがなければ（もうデータがなければ）、Whileから抜け出す
      break;
    }
    print '<input type="radio" name="name" value="'.$rec['name'].'">';
    print $rec['name']; //もし、$recがあれば、$recのnameを表示
    print "<br />\n"; //\nはソースコードの改行コード。\nはシングルクオテーションで囲うと文字として出力されてしまうので注意
    // 上記3行は結合すると右記のように表示される。　<input type="radio" name="glossary" value="AA">AA<br />
  }
  print '<input type="submit" name="add" value="追加">';
  print '<input type="submit" name="edit" value="修正">';
  print '<input type="submit" name="delete" value="削除">';
  print '</form>';
}
catch (Exception $e)
{
  print 'ただいま障害により表示できません。';
  exit(); //強制終了の命令
}
?>

</body>
</html>

branch.php

branch.phpを使って、list.phpで押したボタンに応じて遷移先を制御する。

<?php
if(isset($_POST['add'])==true){ //もし「追加」ボタンが押されていて
  header('Location:add.php');
  exit();
}

if(isset($_POST['edit'])==true){ //もし「修正」ボタンが押されていて

  if(isset($_POST['name'])==false){ //もしラジオボタンが何も選択されていなければ
    header('Location:ng.php');
    exit();
  }

  $name=$_POST['name'];
  header('Location:edit.php?name='.$name);
  exit();
}

if(isset($_POST['delete'])==true){ //もし「修正」ボタンが押されていて

  if(isset($_POST['name'])==false){ //もしラジオボタンが何も選択されていなければ
    header('Location: ng.php');
    exit();
  }

  $name=$_POST['name'];
  header('Location:delete.php?name='.$name);
  exit();
}
?>

add.php

<!DOCTYPE html>
<html lang="ja">
<head>
 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 <meta name="description" content="Webサイトのコンテンツ内容">
 <title>Webサイトのタイトル</title>
 <link rel="stylesheet" href="/main.css">
 <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
</head>

<body>
  用語追加<br />
  <br />
  <form method="post" action="glossary_add_check.php"> <!--postメソッドで、glossary_add_check.phpに情報を引き渡す-->
    用語名を入力してください。<br />
    <input type="text" name="name" style="width:100px"><br /> <!--入力された値はtext型。値はnameと名づける-->

    読み方を入力してください。英語はカナ、日本語は平仮名<br />
    <input type="text" name="reading" style="width:100px"><br /> <!--入力された値はtext型。値はreadingと名づける-->

    カテゴリを入力してください。<br />
    <input type="text" name="category" style="width:100px"><br /> <!--入力された値はtext型。値はcategoryと名づける。-->

    用語の説明文を入力してください。<br />
    <textarea name="description" style="width:500px; height:200px;" wrap="soft"></textarea>
    <br />
    <input type="button" onclick="history.back()" value="戻る"> <!--戻るボタンをクリックすると、前の画面に遷移する-->
    <input type="submit" value="OK"> <!--OKボタンをクリックすると、glossary_add_check.phpに遷移する-->
  </form>
</body>
</html>

add_check.php

<!DOCTYPE html>
<html lang="ja">
<head>
 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 <meta name="description" content="Webサイトのコンテンツ内容">
 <title>Webサイトのタイトル</title>
 <link rel="stylesheet" href="/main.css">
 <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
</head>

<body>
<?php
$name=$_POST['name']; //前の画面から入力値を受け取り、$nameに格納
$reading=$_POST['reading']; //前の画面から入力値を受け取り、$readingに格納
$category=$_POST['category']; //前の画面から入力値を受け取り、$categoryに格納
$description=$_POST['description']; //前の画面から入力値を受け取り、$descriptionに格納

$name=htmlspecialchars($name,ENT_QUOTES,'UTF-8'); //文字列に変換（セキュリティ対策）
$reading=htmlspecialchars($reading,ENT_QUOTES,'UTF-8'); //文字列に変換（セキュリティ対策）
$category=htmlspecialchars($category,ENT_QUOTES,'UTF-8'); //文字列に変換（セキュリティ対策）
$description=htmlspecialchars($description,ENT_QUOTES,'UTF-8'); //文字列に変換（セキュリティ対策）

//$nameがカラならエラーメッセージを表示する
//$nameが入力されていれば、$nameを表示する
if($name==''){
  print '用語名が入力されていません。<br />';
}
else
{
  print '用語名：';
  print $name;
  print '<br />';
}

//$readingがカラならエラーメッセージを表示する
//$readingが入力されていれば、$readingを表示する
if($reading==''){
  print '読み方が入力されていません。<br />';
}
else
{
  print '読み方：';
  print $reading;
  print '<br />';
}

//$categoryがカラならエラーメッセージを表示する
//$categoryが入力されていれば、$categoryを表示する
if($category==''){
  print 'カテゴリが入力されていません。<br />';
}
else
{
  print 'カテゴリ：';
  print $category;
  print '<br />';
}

//$descriptionがカラならエラーメッセージを表示する
//$descriptionが入力されていれば、$categoryを表示する
if($description==''){
  print '用語説明が入力されていません。<br />';
}
else
{
  print '用語説明：';
  print $description;
  print '<br />';
}

//$name、$reading、$category、$descriptionのいずれかがカラなら、戻るボタンのみを表示する
//入力項目が適切なら、戻るボタンとOKボタンを表示する。
if($name==''|| $reading==''||$category=='' || $description==''){
  print '<form>';
  print '<input type="button" onclick="history.back()" value="戻る">';
  print '<form>';
}
else
{
  print '<form method="post" action="add_done.php">';
  print '<input type="hidden" name="name" value="'.$name.'">'; //'<input type="hidden" name="name" value="'と$nameをドットで連結
  print '<input type="hidden" name="reading" value="'.$reading.'">'; //'<input type="hidden" name="reading" value="'と$nameをドットで連結
  print '<input type="hidden" name="category" value="'.$category.'">'; //'<input type="hidden" name="category" value="'と$categoryをドットで連結
  print '<input type="hidden" name="description" value="'.$description.'">'; //'<input type="hidden" name="description" value="'と$descriptionをドットで連結
  print '<input type="button" onclick="history.back()" value="戻る">';
  print '<input type="submit" value="OK">';
  print '</form>';
}

?>
</body>
</html>

add_done.php

<!DOCTYPE html>
<html lang="ja">
<head>
 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 <meta name="description" content="Webサイトのコンテンツ内容">
 <title>Webサイトのタイトル</title>
 <link rel="stylesheet" href="/main.css">
 <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
</head>

<body>
<?php
//tryはデータベースの接続エラー対策
try
{
  // POSTメソッドで前の画面の入力値を取得する
  $name = $_POST['name'];
  $reading = $_POST['reading'];
  $category = $_POST['category'];
  $description = $_POST['description'];

  //データベースに接続
  $dsn = 'mysql:dbname=LAAXXXX-XXXX;host=mysqlXXXX.lan'; //データベース名。''内は一切スペースを入れてはいけない
  $user = 'LAAXXXX'; //rootにはユーザ名を入力
  $password = 'XXXX';  //''内にはパスワードを入力
  $dbh = new PDO($dsn, $user, $password);
  $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //この行は定型文としてこのまま書く

  // SQL分を使ってレコードを追加
  $sql ='INSERT INTO contents(name,reading,category,description) VALUES (?,?,?,?)'; //'INSERT INTO mst_staff(name,reading,category,description) VALUES (?,?,?,?)'を変数$sqlに格納する
  $stmt = $dbh->prepare($sql); // レコードを追加する準備。この行は定型文としてこのまま書く
  $data[] = $name; // 一つ目の?にセットしたいデータが入っている変数を書く
  $data[] = $reading; // 二つ目の?にセットしたいデータが入っている変数を書く
  $data[] = $category; // 三つ目の?にセットしたいデータが入っている変数を書く
  $data[] = $description; // 四つ目の?にセットしたいデータが入っている変数を書く
  $stmt->execute($data); // SQL文で指令を出すための命令文。この行は定型文としてこのまま書く

  // データベースから切断するプログラム
  $dbh = null;

  // セキュリティ対策入力値を文字列に変換。出力時にエスケープする
  // エスケープ忘れ、多重エスケープ、エスケープ前の文字列が必要になった際にhtmlspecialchars_decode()等で元に戻す必要があるため
  $name = htmlspecialchars($name,ENT_QUOTES,'UTF-8');
  $reading = htmlspecialchars($reading,ENT_QUOTES,'UTF-8');
  $category = htmlspecialchars($category,ENT_QUOTES,'UTF-8');
  $description = htmlspecialchars($description,ENT_QUOTES,'UTF-8');

  //結果を表示
  print $name;
  print 'を追加しました。<br />';

}
catch (Exception $e)
{
  print 'ただいま障害により接続できません。';
  exit(); //強制終了の命令
}
?>

<a href="list.php">戻る</a>
</body>
</html>

edit.php

<!DOCTYPE html>
<html lang="ja">
<head>
 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 <meta name="description" content="Webサイトのコンテンツ内容">
 <title>Webサイトのタイトル</title>
 <link rel="stylesheet" href="/main.css">
 <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
</head>

<body>
<?php
//tryはデータベースの接続エラー対策
try
{
  $name=$_GET['name']; //前画面のlist.phpからGETでglossaryを受け取る

  //データベースに接続
  $dsn = 'mysql:dbname=LAAXXXX-XXXX;host=mysqlXXXX.lan'; //データベース名。''内は一切スペースを入れてはいけない
  $user = 'LAAXXXX'; //''内にはにはユーザ名を入力
  $password = 'XXXX';  //''内にはパスワードを入力
  $dbh = new PDO($dsn, $user, $password);
  $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //この行は定型文としてこのまま書く

  // SQL分を使ってレコードを読み込み
  $sql ='SELECT reading, category, description FROM contents WHERE name=?'; //'SELECT name FROM contents WHERE glossary=?'を変数$sqlに格納する。contentsテーブルのnameカラムのあとで指定する(?のこと)値の情報を取得する
  $stmt = $dbh->prepare($sql); // レコードを検索する準備。この行は定型文としてこのまま書く
  $data[] = $name; // 上の上の行の?の値を設定する
  $stmt->execute($data); // SQL文で指令を出すための命令文。この行は定型文としてこのまま書く

  $rec=$stmt->fetch(PDO::FETCH_ASSOC);
  $reading=$rec['reading'];
  $category=$rec['category'];
  $description=$rec['description'];

  // データベースから切断するプログラム
  $dbh = null;
}
catch (Exception $e)
{
  print 'ただいま障害により表示できません。';
  exit(); //強制終了の命令
}
?>

Name<br />
<form method="post" action="edit_check.php">
<input type="text" name="name" value="<?php print $name; ?>">
<br /><br />
Reading<br />
<form method="post" action="edit_check.php">
<input type="text" name="reading" value="<?php print $reading; ?>">
<br /><br />
Category<br />
<form method="post" action="edit_check.php">
<input type="text" name="category" value="<?php print $category; ?>">
<br /><br />
Discription<br />
<form method="post" action="edit_check.php">
<textarea name="description" style="width:500px; height:200px;" wrap="soft">
<?php print $description; ?>
</textarea>
<br />
<input type="button" onclick="history.back()" value="戻る">
<input type="submit" value="OK">
</form>
</body>
</html>

edit_check.php

<!DOCTYPE html>
<html lang="ja">
<head>
 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 <meta name="description" content="Webサイトのコンテンツ内容">
 <title>Webサイトのタイトル</title>
 <link rel="stylesheet" href="/main.css">
 <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
</head>

<body>
<?php
$name=$_POST['name']; //前の画面から入力値を受け取り、$nameに格納
$reading=$_POST['reading']; //前の画面から入力値を受け取り、$readingに格納
$category=$_POST['category']; //前の画面から入力値を受け取り、$categoryに格納
$description=$_POST['description']; //前の画面から入力値を受け取り、$escriptionに格納

//$nameがカラならエラーメッセージを表示する
//$nameが入力されていれば、$nameを表示する
if($name==''){
  print '用語名が入力されていません。<br />';
}
else
{
  print '用語名：';
  print $name;
  print '<br /><br />';
}

//$readingがカラならエラーメッセージを表示する
//$readingが入力されていれば、$readingを表示する
if($reading==''){
  print '読み方が入力されていません。<br />';
}
else
{
  print '読み：';
  print $reading;
  print '<br /><br />';
}

//$categoryがカラならエラーメッセージを表示する
//$categoryが入力されていれば、$categoryを表示する
if($category==''){
  print 'カテゴリが入力されていません。<br />';
}
else
{
  print 'カテゴリ：';
  print $category;
  print '<br /><br />';
}

//$descriptionがカラならエラーメッセージを表示する
//$descriptionが入力されていれば、$categoryを表示する
if($description==''){
  print '用語説明が入力されていません。<br /><br />';
}
else
{
  print '用語説明：';
  print $description;
  print '<br /><br />';
}

//$name、$category、$descriptionのいずれかがカラなら、戻るボタンのみを表示する
//入力項目が適切なら、戻るボタンとOKボタンを表示する。
if($name==''|| $reading==''|| $category=='' || $description==''){
  print '<form>';
  print '<input type="button" onclick="history.back()" value="戻る">';
  print '<form>';
}
else
{
  print '<form method="post" action="edit_done.php">';
  print '<input type="hidden" name="name" value="'.$name.'">'; //'<input type="hidden" name="name" value="'と$nameをドットで連結
  print '<input type="hidden" name="reading" value="'.$reading.'">'; //'<input type="hidden" name="namereading" value="'と$readingをドットで連結
  print '<input type="hidden" name="category" value="'.$category.'">'; //'<input type="hidden" name="category" value="'と$categoryをドットで連結
  print '<input type="hidden" name="description" value="'.$description.'">'; //'<input type="hidden" name="description" value="'と$descriptionをドットで連結
  print '<input type="button" onclick="history.back()" value="戻る">';
  print '<input type="submit" value="OK">';
  print '</form>';
}
?>
</body>
</html>

edit_done.php

<!DOCTYPE html>
<html lang="ja">
<head>
 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 <meta name="description" content="Webサイトのコンテンツ内容">
 <title>Webサイトのタイトル</title>
 <link rel="stylesheet" href="/main.css">
 <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
</head>

<body>
<?php
//tryはデータベースの接続エラー対策
try
{
  // POSTメソッドで前の画面の入力値を取得する
  $name = $_POST['name'];
  $reading = $_POST['reading'];
  $category = $_POST['category'];
  $description = $_POST['description'];

  //データベースに接続
  $dsn = 'mysql:dbname=LAAXXXX;host=mysqlXXXX.lan'; //データベース名。''内は一切スペースを入れてはいけない
  $user = 'XXXX'; //rootにはユーザ名を入力
  $password = 'XXXX';  //''内にはパスワードを入力
  $dbh = new PDO($dsn, $user, $password);
  $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //この行は定型文としてこのまま書く

  // SQL分を使ってレコードを追加
  $sql ='UPDATE contents SET name=?,reading=?,category=?,description=? WHERE name=?'; //$sql ='UPDATE contents SET name=?,reading=?,category=?,description=? WHERE name=?'を変数$sqlに格納する
  $stmt = $dbh->prepare($sql); // レコードを追加する準備。この行は定型文としてこのまま書く
  $data[] = $name; // 一つ目の?にセットしたいデータが入っている変数を書く
  $data[] = $reading; // 二つ目の?にセットしたいデータが入っている変数を書く
  $data[] = $category; // 三つ目の?にセットしたいデータが入っている変数を書く
  $data[] = $description; // 四つ目の?にセットしたいデータが入っている変数を書く
  $data[] = $name; // 五つ目の?にセットしたいデータが入っている変数を書く
  $stmt->execute($data); // SQL文で指令を出すための命令文。この行は定型文としてこのまま書く

  // データベースから切断するプログラム
  $dbh = null;

}
catch (Exception $e)
{
  print 'ただいま障害により接続できません。';
  exit(); //強制終了の命令
}
?>

修正しました。　<br /><br />
<a href="list.php">戻る</a>

</body>
</html>

delete.php

<!DOCTYPE html>
<html lang="ja">
<head>
 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 <meta name="description" content="Webサイトのコンテンツ内容">
 <title>Webサイトのタイトル</title>
 <link rel="stylesheet" href="/main.css">
 <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
</head>

<body>
<?php
//tryはデータベースの接続エラー対策
try
{
  $name=$_GET['name']; //前画面のlist.phpからGETでnameを受け取る

  //データベースに接続
  $dsn = 'mysql:dbname=LAAXXXX;host=mysqlXXXX.lan'; //データベース名。''内は一切スペースを入れてはいけない
  $user = 'XXXX'; //''内にはにはユーザ名を入力
  $password = 'XXXX';  //''内にはパスワードを入力
  $dbh = new PDO($dsn, $user, $password);
  $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //この行は定型文としてこのまま書く

  // SQL分を使ってレコードを読み込み
  $sql ='SELECT reading, category, description FROM contents WHERE name=?'; //'SELECT name FROM contents WHERE glossary=?'を変数$sqlに格納する。contentsテーブルのnameカラムのあとで指定する(?のこと)値の情報を取得する
  $stmt = $dbh->prepare($sql); // レコードを検索する準備。この行は定型文としてこのまま書く
  $data[] = $name; // 上の上の行の?の値を設定する
  $stmt->execute($data); // SQL文で指令を出すための命令文。この行は定型文としてこのまま書く

  $rec=$stmt->fetch(PDO::FETCH_ASSOC);
  $category=$rec['reading'];
  $category=$rec['category'];
  $description=$rec['description'];

  // データベースから切断するプログラム
  $dbh = null;
}
catch (Exception $e)
{
  print 'ただいま障害により表示できません。';
  exit(); //強制終了の命令
}
?>

用語削除<br /><br />
用語<br />
<?php print $name; ?>
<br />
この用語を削除してよろしいですか？<br /><br />
<form method="post" action="delete_done.php">
<input type="hidden" name="name" style="width:200px" value="<?php print $name?>"><br />
<input type="button" onclick="history.back()" value="戻る">
<input type="submit" value="OK">
</form>

</body>
</html>

delete_done.php

<!DOCTYPE html>
<html lang="ja">
<head>
 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 <meta name="description" content="Webサイトのコンテンツ内容">
 <title>Webサイトのタイトル</title>
 <link rel="stylesheet" href="/main.css">
 <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
</head>

<body>
<?php
//tryはデータベースの接続エラー対策
try
{
  // POSTメソッドで前の画面の入力値を取得する
  $name = $_POST['name'];

  //データベースに接続
  $dsn = 'mysql:dbname=LAAXXXX;host=mysqlXXXX.lan'; //データベース名。''内は一切スペースを入れてはいけない
  $user = 'XXXX'; //rootにはユーザ名を入力
  $password = 'XXXX';  //''内にはパスワードを入力
  $dbh = new PDO($dsn, $user, $password);
  $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); //この行は定型文としてこのまま書く

  // SQL分を使ってレコードを追加
  $sql ='DELETE FROM contents WHERE name=?'; //DELETE FROM contents WHERE name=?を変数$sqlに格納する
  $stmt = $dbh->prepare($sql); // レコードを追加する準備。この行は定型文としてこのまま書く
  $data[] = $name; // 一つ目の?にセットしたいデータが入っている変数を書く
  $stmt->execute($data); // SQL文で指令を出すための命令文。この行は定型文としてこのまま書く

  // データベースから切断するプログラム
  $dbh = null;
}
catch (Exception $e)
{
  print 'ただいま障害により接続できません。';
  exit(); //強制終了の命令
}
?>

削除しました。　<br /><br />
<a href="list.php">戻る</a>

</body>
</html>

ng.php

<!DOCTYPE html>
<html lang="ja">
<!DOCTYPE html>
<html lang="ja">
<head>
 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 <meta name="description" content="Webサイトのコンテンツ内容">
 <title>Webサイトのタイトル</title>
 <link rel="stylesheet" href="/main.css">
 <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
</head>

<body>
用語が選択されていません。
<a href="list.php">戻る</a>
</body>

</html>

　 Tweet
Qiitaで続きを読む

投稿日：2020-07-15T16:06:11+09:00

?【CakePHP2】Controllerで動的に一時テーブルを作成し使用する方法

環境

PHP 7.2.21
CakePHP 2.10.18
MySQL 5.7.27

やりたいこと

テンポラリテーブルを使いたい

やったこと

ModelをuseTable = falseで用意してControllerでテーブル作成しセット

Model

TemporaryTable.php

<?php

App::uses('AppModel', 'Model');

class TemporaryTable extends AppModel
{
    public $useTable    = false;
    public $primaryKey  = '';
    public $useDbConfig = '';

    function setSource($tableName) {
        $this->setDataSource($this->useDbConfig);
        $db = ConnectionManager::getDataSource($this->useDbConfig);
        $db->cacheSources = ($this->cacheSources && $db->cacheSources);

        $this->table = $this->useTable = $tableName;
        $this->tableToModel[$this->table] = $this->alias;
        $this->schema();
    }
}

Controller

HogeController.php

<?php

class HogeController extends AppController {
    public function index() {
        // TEMPORARY TABLE名
        $tempTableName = 'temporary_table';
        // database.phpのDBconfigをModelに設定
        $this->TemporaryTable->useDbConfig = 'admin';
        // TEMPORARY TABLEを作成
        $this->TemporaryTable->query('CREATE TEMPORARY TABLE ' . $tempTableName . ' (id INT(11), name VARCHAR(256));');
        // 作成したTEMPORARY TABLE名をModelに設定
        $this->TemporaryTable->useTable = $tempTableName;

        // 先ずfindしてみる -> result:array(0)
        $data = $this->TemporaryTable->find('all');

        // テストデータをsaveする
        $testData = [
            'id'   => '1',
            'name' => '_test',
        ];
        $this->TemporaryTable->create();
        $this->TemporaryTable->save($testData);
        // テストデータ2をsaveする
        $testData2 = [
            'id'   => '2',
            'name' => 'test_test',
        ];
        $this->TemporaryTable->create();
        $this->TemporaryTable->save($testData2);
        // 再度find -> result:array(2)
        $data = $this->TemporaryTable->find('all');
        var_dump($data);
        /* find結果
            Array
            (
                [0] => Array
                    (
                        [TemporaryTable] => Array
                            (
                                [id] => 1
                                [name] => _test
                            )
                    )
                [1] => Array
                    (
                        [TemporaryTable] => Array
                            (
                                [id] => 2
                                [name] => test_test
                            )
                    )
            )
        */
    }
}

　 Tweet
Qiitaで続きを読む

投稿日：2020-07-15T13:57:45+09:00

Railsのdockerコンテナのよく使うdocker-composeコマンドとMySqlコンテナの繋ぐ方法

これは何？

こちらはdocker composerで作成したRails,MySqlコンテナ、自分がターミナルで操作するコマンドの自分用メモです。

コンテナの操作

コントローラー作成

controller_nameは自分で定義する
$ docker-compose run --rm web rails generate controller controller_name
web　:　docker-compose.ymlに定義したservices名

Model作成

model_nameは自分で定義する引数にname:stringとかでnameカラムを作れます。
$ docker-compose run --rm web rails generate model model_name name:string 
ルーティング変更

config/routes.rbを編集後実行
$ docker-compose run --rm web rake routes
DB作り
$ docker-compose run --rm web rake db:create
マイグレーション
$ docker-compose run --rm web rake db:migrate
seed実行
$ docker-compose run --rm web rake db:seed
DB接続

コンテナへSSH接続〜 mysqlコンテナ内でmysqlコマンド実行

SSHで接続
$ docker exec -it $MYSQL_CONTAINER_NAME bash
でコンテナ内部に入り。

\$MYSQL_CONTAINER_NAME は \$ docker ps で調べる（$ docker-compose ps　ではなく）

作成したMySQLユーザでmysqlコマンドをいれる
$ mysql -uroot -p db_name
MySql workbenchで接続

docker-compose.ymlのportを
.
.
.
ports:
      - "3306:3306"
で設定して（別のポート使うとworkbenchが接続できなくなる）、
workbenchのconnectionsの設定は以下のように

Connection Method: Standard を選択
Hostname : 127.0.0.1
Port : 3306（docker run時の特に指定してなければ）
Username : root
Password : docker run時の指定した環境変数
参考資料

serverコンテナ参考
 dbコンテナ参考
 seedとは
 $docker run 公式説明

　 Tweet
Qiitaで続きを読む

投稿日：2020-07-15T13:57:45+09:00

RailsとMysqlのdockerコンテナのよく使うコマンド

これは何？

こちらはdockerで作成したRails,MySqlコンテナ、自分がターミナルで操作するコマンドの自分用メモです。

コンテナの操作

コントローラー作成

controller_nameは自分で定義する
$ docker-compose run --rm web rails generate controller controller_name
web　:　docker-compose.ymlに定義したservices名

Model作成

model_nameは自分で定義する引数にname:stringとかでnameカラムを作れます。
$ docker-compose run --rm web rails generate model model_name name:string 
ルーティング変更

config/routes.rbを編集後実行
$ docker-compose run --rm web rake routes
DB作り
$ docker-compose run --rm web rake db:create
マイグレーション
$ docker-compose run --rm web rake db:migrate
seed実行
$ docker-compose run --rm web rake db:seed
DB接続

コンテナへSSH接続〜 mysqlコンテナ内でmysqlコマンド実行

SSHで接続
$ docker exec -it $MYSQL_CONTAINER_NAME bash
でコンテナ内部に入り。

\$MYSQL_CONTAINER_NAME は \$ docker ps で調べる（$ docker-compose ps　ではなく）

作成したMySQLユーザでmysqlコマンドをいれる
$ mysql -uroot -p db_name
MySql workbenchで接続

docker-compose.ymlのportを
.
.
.
ports:
      - "3306:3306"
で設定して（別のポート使うとworkbenchが接続できなくなる）、
workbenchのconnectionsの設定は以下のように

Connection Method: Standard を選択
Hostname : 127.0.0.1
Port : 3306（docker run時の特に指定してなければ）
Username : root
Password : docker run時の指定した環境変数
参考資料

serverコンテナ参考
 dbコンテナ参考
 seedとは
 $docker run 公式説明

　 Tweet
Qiitaで続きを読む

投稿日：2020-07-15T13:07:06+09:00

MySQL(MariaDB)にログイン

XAMPPのコントロール・パネル(アプリケーション・マネジャー)

から「shell」を選択。

→コマンド・プロンプトのような(黒い)画面が

　開きますので、「mysql」と入力。MySQLが起動。

→抜ける(終了する)時は「quit」もしくは「exit」と入力。

・データベース作成
　create database データベース名;

・データベース一覧表示
　show databases;

・テーブル作成
　create table テーブル名 (カラム名　データ型,カラム名　データ型,‥);

・テーブル一覧表示
　show tables;

・テーブルへのデータの挿入
　１、insert into テーブル名 (カラム名, カラム名) values (値, 値);

　２、すべてのカラムに値を指定してデータを追加する場合はカラム名の指定を省略可
　　　insert into テーブル名 values (値,値,値,‥);
　
・テーブルデータの表示
　１、select カラム名, カラム名,カラム名 from テーブル名;

　２、すべてのカラムの値を取得する
　　　select * from テーブル名;

◆参考にしたサイト
データベース作成
https://www.dbonline.jp/mysql/database/
データベース一覧表示
https://www.dbonline.jp/mysql/database/index2.html
テーブル作成
https://www.dbonline.jp/mysql/table/
テーブル一覧表示
https://www.dbonline.jp/mysql/table/index2.html
テーブルへのデータの挿入
https://www.dbonline.jp/mysql/insert/index1.html
テーブルデータの表示
https://www.dbonline.jp/mysql/select/index1.html

　 Tweet
Qiitaで続きを読む

投稿日：2020-07-15T12:01:43+09:00

MySQL 8.0.21 では Multi-Table Trick が必要なくなったらしい

7/13 に MySQL 8.0.21 がリリースされました。

リリースノートを読んでいて、Optimizer Notes の 2 つ目に、

A single-table UPDATE or DELETE statement that uses a subquery having a [NOT] IN or [NOT] EXISTS predicate can now in many cases make use of a semijoin transformation or subquery materialization. This can be done when the statement does not use LIMIT or ORDER BY, and when semijoin or subquery materialization is allowed by any optimizer hints used in the subquery, or by the value of the optimizer_switch server system variable.

というものがあり、「これは何だろう？」と思っていたところ、Oracle モ MySQL モデキル DBA の方の呟き によって謎が解けましたので、実際に試してみました。

Multi-Table Trick とは

先の呟きにもリンクがありましたが、こちらを見ていただくとわかりやすいです。

A Multi-Table Trick to Speed up Single-Table UPDATE/DELETE Statements（Øystein on MySQL Optimizer）

MySQL 5.6（あたり？）から、SELECTのWHERE ... IN のサブクエリで、

セミジョイン（準結合）
マテリアライズ（実体化）

によって（特にサブクエリ側のテーブルにたくさんデータがあるときに）処理が高速化されるようになりましたが、これはUPDATE・DELETEでは素直に SQL（文）を書いても適用されませんでした。

それを、例えば

UPDATE t1 SET t1.a=value WHERE t1.a IN (SELECT t2.a FROM t2);

　　↓

UPDATE t1, (SELECT 1) dummy SET t1.a=value WHERE t1.a IN (SELECT t2.a FROM t2);

のように「加工」することによって、UPDATE・DELETEにも適用する（結果として SQL（文）の実行が速くなる）のが Multi-Table Trick ということらしいです。

試してみた

以下のような構造のテーブルに、各 50 行ずつデータを入れてEXPLAINしてみます。

テーブル構造

mysql> USE multitable_test;
Database changed
mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `key` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `key` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `val` int NOT NULL,
  `key` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `valkey` (`val`,`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

まずは、MySQL 8.0.20 で実行してみます。

8.0.20の結果

mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN SELECT * FROM t1 WHERE t1.key IN (SELECT t2.key FROM t2 WHERE t2.val IN (40, 60, 100));
+----+--------------+-------------+------------+-------+---------------+--------+---------+-----------------+------+----------+--------------------------+
| id | select_type  | table       | partitions | type  | possible_keys | key    | key_len | ref             | rows | filtered | Extra                    |
+----+--------------+-------------+------------+-------+---------------+--------+---------+-----------------+------+----------+--------------------------+
|  1 | SIMPLE       | <subquery2> | NULL       | ALL   | NULL          | NULL   | NULL    | NULL            | NULL |   100.00 | NULL                     |
|  1 | SIMPLE       | t1          | NULL       | ref   | key           | key    | 4       | <subquery2>.key |    1 |   100.00 | Using index              |
|  2 | MATERIALIZED | t2          | NULL       | range | valkey        | valkey | 4       | NULL            |   15 |   100.00 | Using where; Using index |
+----+--------------+-------------+------------+-------+---------------+--------+---------+-----------------+------+----------+--------------------------+
3 rows in set, 1 warning (0.01 sec)

mysql> EXPLAIN UPDATE t1 SET t1.key = 0 WHERE t1.key IN (SELECT t2.key FROM t2 WHERE t2.val IN (40, 60, 100));
+----+--------------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type        | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+--------------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | UPDATE             | t1    | NULL       | index | NULL          | PRIMARY | 4       | NULL |   50 |   100.00 | Using where              |
|  2 | DEPENDENT SUBQUERY | t2    | NULL       | range | valkey        | valkey  | 4       | NULL |   15 |    10.00 | Using where; Using index |
+----+--------------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

SELECTでは有効だったマテリアライズが、UPDATEでは効きません。

次に、MySQL 8.0.21 で実行してみます。

8.0.20の結果

mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN SELECT * FROM t1 WHERE t1.key IN (SELECT t2.key FROM t2 WHERE t2.val IN (40, 60, 100));
+----+--------------+-------------+------------+-------+---------------+--------+---------+-----------------+------+----------+--------------------------+
| id | select_type  | table       | partitions | type  | possible_keys | key    | key_len | ref             | rows | filtered | Extra                    |
+----+--------------+-------------+------------+-------+---------------+--------+---------+-----------------+------+----------+--------------------------+
|  1 | SIMPLE       | <subquery2> | NULL       | ALL   | NULL          | NULL   | NULL    | NULL            | NULL |   100.00 | NULL                     |
|  1 | SIMPLE       | t1          | NULL       | ref   | key           | key    | 4       | <subquery2>.key |    1 |   100.00 | Using index              |
|  2 | MATERIALIZED | t2          | NULL       | range | valkey        | valkey | 4       | NULL            |   15 |   100.00 | Using where; Using index |
+----+--------------+-------------+------------+-------+---------------+--------+---------+-----------------+------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)

mysql> EXPLAIN UPDATE t1 SET t1.key = 0 WHERE t1.key IN (SELECT t2.key FROM t2 WHERE t2.val IN (40, 60, 100));
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+------------------------+------+----------+--------------------------+
| id | select_type  | table       | partitions | type   | possible_keys       | key                 | key_len | ref                    | rows | filtered | Extra                    |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+------------------------+------+----------+--------------------------+
|  1 | UPDATE       | t1          | NULL       | ALL    | key                 | NULL                | NULL    | NULL                   |   44 |   100.00 | Using where              |
|  1 | SIMPLE       | <subquery2> | NULL       | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 4       | multitable_test.t1.key |    1 |   100.00 | NULL                     |
|  2 | MATERIALIZED | t2          | NULL       | range  | valkey              | valkey              | 4       | NULL                   |   15 |   100.00 | Using where; Using index |
+----+--------------+-------------+------------+--------+---------------------+---------------------+---------+------------------------+------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)

UPDATEでもマテリアライズが適用されました。

時間を比べてみる

先ほどのテーブル内のデータを約 10 万行まで増やして試します。環境は MySQL 8.0.21 です。

各テーブルデータ行数

mysql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
|   102400 |
+----------+
1 row in set (0.01 sec)

mysql> SELECT COUNT(*) FROM t2;
+----------+
| COUNT(*) |
+----------+
|   102400 |
+----------+
1 row in set (0.29 sec)

まずはオプティマイザスイッチでマテリアライズを無効化してみます。

マテリアライズ無効

mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN UPDATE t1 SET t1.key = 0 WHERE t1.key IN (SELECT /*+ SUBQUERY(INTOEXISTS) */ t2.key FROM t2 WHERE t2.val IN (40, 60, 100));
+----+--------------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
| id | select_type        | table | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra                    |
+----+--------------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
|  1 | UPDATE             | t1    | NULL       | index | NULL          | PRIMARY | 4       | NULL | 102567 |   100.00 | Using where              |
|  2 | DEPENDENT SUBQUERY | t2    | NULL       | range | valkey        | valkey  | 4       | NULL |     15 |    10.00 | Using where; Using index |
+----+--------------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> UPDATE t1 SET t1.key = 0 WHERE t1.key IN (SELECT /*+ SUBQUERY(INTOEXISTS) */ t2.key FROM t2 WHERE t2.val IN (40, 60, 100));
Query OK, 24576 rows affected (2.71 sec)
Rows matched: 24576  Changed: 24576  Warnings: 0

mysql> ROLLBACK;
Query OK, 0 rows affected (0.95 sec)

MySQL 8.0.20 と同じ実行計画になりました。3 回計測した平均は 2.78 秒でした。

次に、マテリアライズが有効な状態で試してみます（MySQL 8.0.21 のデフォルト）。

マテリアライズ有効

mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN UPDATE t1 SET t1.key = 0 WHERE t1.key IN (SELECT t2.key FROM t2 WHERE t2.val IN (40, 60, 100));
+----+--------------+-------------+------------+-------+---------------+--------+---------+-----------------+------+----------+--------------------------+
| id | select_type  | table       | partitions | type  | possible_keys | key    | key_len | ref             | rows | filtered | Extra                    |
+----+--------------+-------------+------------+-------+---------------+--------+---------+-----------------+------+----------+--------------------------+
|  1 | SIMPLE       | <subquery2> | NULL       | ALL   | NULL          | NULL   | NULL    | NULL            | NULL |   100.00 | NULL                     |
|  1 | UPDATE       | t1          | NULL       | ref   | key           | key    | 4       | <subquery2>.key |    1 |   100.00 | NULL                     |
|  2 | MATERIALIZED | t2          | NULL       | range | valkey        | valkey | 4       | NULL            |   15 |   100.00 | Using where; Using index |
+----+--------------+-------------+------------+-------+---------------+--------+---------+-----------------+------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)

mysql> UPDATE t1 SET t1.key = 0 WHERE t1.key IN (SELECT t2.key FROM t2 WHERE t2.val IN (40, 60, 100));
Query OK, 24576 rows affected (1.08 sec)
Rows matched: 24576  Changed: 24576  Warnings: 0

mysql> ROLLBACK;
Query OK, 0 rows affected (0.99 sec)

3 回計測した平均は 1.07 秒でした。

2 ～ 3 倍ほど高速化されました。

Qiitaに投稿したMySQL 8.0関連記事
MySQL 8.0 の薄い本（無料で配布中！）

　 Tweet
Qiitaで続きを読む