投稿日：2020-09-09T22:09:22+09:00

[SQL]今更ながらSQLのサブクエリに向き合ってみる

皆さんはSQLのサブクエリ書いてますか？

最近は分析目的でSQLを書く機会が増えていますが、意外と生のSQLを書く機会がなかったので意外と初歩的なところでつまづいたりしました。特に、SQLのサブクエリについての理解がかなり曖昧だったので、この機会にしっかりと向き合ってみようと思い基礎から学び直してみました！

本稿の対応

ビュー

サブクエリ

相関サブクエリ

環境設定

docker上で真っさらなmysqlを立ち上げ、下記のテーブルを作成しました。
検証環境はgithubにも載せてます。
-- テーブル生成
CREATE TABLE foods
(
  id                BIGINT          NOT NULL,
  name              VARCHAR(100)    NOT NULL,
  type              VARCHAR(32)     NOT NULL,
  sales_price       INTEGER                 ,
  purchase_price    INTEGER                 ,
  created_at        DATETIME                ,
  updated_at        DATETIME                ,
  PRIMARY KEY (id)
);
一旦サンプルとして下記のデータを流し込んでいます。
-- データ生成
INSERT INTO `foods` (`id`, `name`, `type`, `sales_price`, `purchase_price`, `created_at`, `updated_at`)
VALUES
    (1, 'うどん', '麺類', 400, 50, '2020-09-06 08:47:52', '2020-09-06 08:47:52'),
    (2, 'そば', '麺類', 500, 100, '2020-09-06 08:47:52', '2020-09-06 08:47:52'),
    (3, 'ラーメン', '麺類', 800, 300, '2020-09-06 08:47:52', '2020-09-06 08:47:52'),
    (4, 'ごはん', 'ご飯類', 200, 100, '2020-09-06 08:47:52', '2020-09-06 08:47:52'),
    (5, 'おにぎり', 'ご飯類', 250, 100, '2020-09-06 08:47:52', '2020-09-06 08:47:52'),
    (6, 'チャーハン', 'ご飯類', 400, 200, '2020-09-06 08:47:52', '2020-09-06 08:47:52'),
    (7, '餃子', 'おかず', 300, 200, '2020-09-06 08:47:52', '2020-09-06 08:47:52'),
    (8, '卵焼き', 'おかず', 400, 100, '2020-09-06 08:47:52', '2020-09-06 08:47:52'),
    (9, 'お茶', '飲み物', 200, 60, '2020-09-06 08:47:52', '2020-09-06 08:47:52'),
    (10, 'ジンジャエール', '飲み物', 250, 80, '2020-09-06 08:47:52', '2020-09-06 08:47:52'),
    (11, 'ビール', '飲み物', 400, 200, '2020-09-06 08:47:52', '2020-09-06 08:47:52');
ビュー

ビューを一言で言うと「仮想テーブル」です。テーブルが実データを保持しているのに対して、ビューが保持しているのはSELECT文そのものを保持しています。

利点としては、データ領域を使う必要がないので節約ができることや、頻繁に使うSELECT文を使い回すことができる点です。例えば、毎回複雑な検索条件で絞った結果を、複数の場所で取り扱うような場合はビューの出番です。汎用的に使うSELECT文をビューにしておくことで、メリットを享受することができます。

ビューの定義方法

今回は、foodsテーブルの種別ごとに個数を表示するビューを作成していきます。
CREATE VIEW sum_foods (type, count_foods)
AS
SELECT type, COUNT(*)
FROM foods
GROUP BY type;
DBを実際に見てみるとViewというテーブルが作られています。
mysql> show full tables;
+-----------------------------+------------+
| Tables_in_development_mysql | Table_type |
+-----------------------------+------------+
| foods                       | BASE TABLE |
| sum_foods                   | VIEW       |
+-----------------------------+------------+
2 rows in set (0.00 sec)
ただ、データを格納しているわけではなく、foodsテーブルを参照するSELECT文なのでfoodsテーブルの実データが追加されたり更新されると結果も自動的に変更されます。
mysql> select *
    -> from sum_foods;
+-----------+-------------+
| type      | count_foods |
+-----------+-------------+
| おかず    |           2 |
| ご飯類    |           3 |
| 飲み物    |           3 |
| 麺類      |           3 |
+-----------+-------------+
4 rows in set (0.00 sec)
サブクエリ

本題のサブクエリです。

これを一言でいうと「使い捨てのビュー」です。ビューのように仮想テーブルをDBのなかに作るのではなく、一時的に使うために使うビューのことです。先ほどビューを定義するときのAS以降の文章をそのままFROM句に使うと結果を得ることができます。
SELECT type, count_product
FROM (SELECT type, COUNT(*)
      FROM foods
      GROUP BY type) AS foods_sum;
実行結果は下記のようになります。
mysql> SELECT type, count_foods
    -> FROM (SELECT type, COUNT(*) as count_foods
    ->       FROM foods
    ->       GROUP BY type) AS foods_sum;
+-----------+-------------+
| type      | count_foods |
+-----------+-------------+
| おかず    |           2 |
| ご飯類    |           3 |
| 飲み物    |           3 |
| 麺類      |           3 |
+-----------+-------------+
4 rows in set (0.00 sec)
スカラサブクエリ

スカラサブクエリは1行しか返さないサブクエリのことです。これを利用して比較演算子の入力値として利用する際に活用することがよくあります。以下は金額の平均値を取得して平均より高い食べ物のみを取得しています。
SELECT *
FROM foods
WHERE sales_price > (SELECT AVG(foods.sales_price)
                     FROM foods);
スカラサブクエリのルールとして、単一の結果を返さないサブクエリを書いてしまうとエラーとなるので注意が必要です。

相関サブクエリ

上記のように平均より金額よりも高い食べ物を抽出することはサブクエリでできました。今度はさらに「種別ごとに平均金額より高い食べ物を抽出する」として見たときに使えるのが相関サブクエリです。まずはSQLを書いてみます。
SELECT type, name, sales_price
FROM foods as T1
WHERE sales_price > (SELECT AVG(sales_price)
                     FROM foods as T2
                     WHERE T1.type = T2.type
                     GROUP BY type);
結果はこうなります。
mysql> SELECT type, name, sales_price
    -> FROM foods as T1
    -> WHERE sales_price > (SELECT AVG(sales_price)
    ->                      FROM foods as T2
    ->                      WHERE T1.type = T2.type
    ->                      GROUP BY type);
+-----------+-----------------+-------------+
| type      | name            | sales_price |
+-----------+-----------------+-------------+
| 麺類      | ラーメン        |         800 |
| ご飯類    | チャーハン       |         400 |
| おかず    | 卵焼き          |         400 |
| 飲み物    | ビール          |         400 |
+-----------+-----------------+-------------+
4 rows in set (0.00 sec)
なんとなく結果が取れていそうですね！
ただ、一見わかりづらいので少し分解して解説していきます。
まず、WHERE文なしのサブクエリをみていきます。
mysql> SELECT AVG(sales_price)
    -> FROM foods
    -> GROUP BY type;
+------------------+
| AVG(sales_price) |
+------------------+
|         350.0000 |
|         283.3333 |
|         283.3333 |
|         566.6667 |
+------------------+
4 rows in set (0.00 sec)
ふむ、、、種別ごとの平均金額が取れてそうです。
ただこのまま実行してしまうと比較演算子の入力値に複数の値を返すサブクエリとなってしまいます。
SELECT type, name, sales_price
FROM foods as T1
WHERE sales_price > (350.0000, 283.3333, 283.3333, 566.6667);
-- これではエラーになってしまう。。。
これを解決するため、WHERE文を追加します。
このWHERE文によって、「金額と平均金額の比較を、同じ種別ごとに行う」ようにしています。また、テーブルに別名をつけているのはサブクエリ内部のfoodsテーブルと外側のfoodsテーブルを区別するために付与しています。
SELECT type, name, sales_price
FROM foods as T1
WHERE sales_price > (SELECT AVG(sales_price)
                     FROM foods as T2
                     WHERE T1.type = T2.type -- <--これを追加する
                     GROUP BY type);
一見内部動作が見えづらいですが、実際は商品種別ごとに分割し、それぞれの平均値と比較しているだけです。

また、このWHERE文をサブクエリの外側に書くとエラーになってしまいます。それは、サブクエリの外側からサブクエリ内部のテーブルの別名を参照できないからです。
SELECT type, name, sales_price
FROM foods as T1
WHERE T1.type = T2.type --T2が参照できずエラーになる
AND sales_price > (SELECT AVG(sales_price)
                     FROM foods as T2
                     GROUP BY type);
サブクエリはパフォーマンスが悪いのか？

結論、確かにサブクエリを使うとパフォーマンスは良いとはいえないようです。

ビューのところでも説明しましたが、サブクエリはいわば一時的にビューのような仮想テーブルを生成することになります。さらにいうと、このビューにはインデックスも貼られていないため、パフォーマンス自体は通常のクエリと比べると悪いといえます。

そのため、なんでもかんでもサブクエリにするというよりも、集計した結果を条件にしたいなど、サブクエリでなければ実現できない場合時の手段として捉えた上で、使うべきかどうかを考えるようにすると良さそうです。

まとめ

かなり初歩的な内容になりましたが、曖昧になっていた部分が個人的に解消できました。同じくサブクエリをなんとなく使っていた人がいたら、少しでも力になれば幸いです。

最近はパフォーマンス改善を行う機会も多いので、サブクエリのことを意識しつつ知見が溜まったらまた何か書きたいと思います！それではまた！

参考文献

プログラミング学習シリーズ SQL ゼロからはじめるデータベース操作（ミック）｜翔泳社の本

SQLでサブクエリを上手に使う6パターン | by Nyle | Nyle Engineering Blog | Medium

　 Tweet
Qiitaで続きを読む

投稿日：2020-09-09T19:52:24+09:00

【MySQL】dockerで構築したデータベースサーバが日本語対応していないかも、、、

環境

$ docker -v
Docker version 19.03.12

$ rails -v
Rails 6.0.3.2

$ mysql --version
mysql  Ver 14.14 Distrib 5.7.29, for osx10.15 (x86_64) using  EditLine wrapper

データベースに日本語でユーザー名を登録しようとすると、、、、

エラー内容

ActiveRecord::StatementInvalid (Mysql2::Error: Incorrect string value: '\xE3\x83\x8F\xE3\x83\xAA...' for column 'name' at row 1):

あらら、日本語として認識されてない模様
ここは、データベースのcharasetでも見て設定確認しますか。

$ docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                               NAMES
f51c4b953b05        feeder-in_back      "rails server -b 0.0…"   About an hour ago   Up About an hour    0.0.0.0:3000->3000/tcp              back
925b28f3b58d        mysql:5.7           "docker-entrypoint.s…"   About an hour ago   Up About an hour    33060/tcp, 0.0.0.0:3308->3306/tcp   database
ab6efeba59a1        feeder-in_web       "docker-entrypoint.s…"   About an hour ago   Up About an hour    0.0.0.0:8080->3000/tcp              web

$ docker exec -it 925b28f3b58d mysql -u root -p

mysql> show variables like "chara%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.03 sec)

character_set_database | latin1
これを見る限り、MySQLのデフォルトのcharasetになっており、日本語対応になっていない。

MySQLデータベースサーバを日本語対応にする

docker-compose.yml

command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

$ docker-compose down

$ docker-compose up -d --build

データベースの再構築

database.yml

default: &default
  charset: utf8mb4   #追記
  encoding: utf8mb4

$ docker-compose exec back sh
> rake db:drop

> rake db:create
> rails db:migrate

これで日本語対応のデータベースになった！

　 Tweet
Qiitaで続きを読む

投稿日：2020-09-09T18:38:33+09:00

DockerでRuby on rails6のアプリケーションを環境構築

目標

ruby on rails6 のアプリケーションをdockerで新規にアプリを立ち上げたい
DBはMySQLで設定

前提

Docker on mac
Rails tutorial完了などruby on rails に関する基礎知識

1.作業ディレクトを作成し、移動する

MacBook-Air ~ % mkdir アプリ名
MacBook-Air ~ % cd アプリ名
MacBook-Air アプリ名 %

2.Dockerfileを定義する

FROM ruby:2.6.3
RUN apt-get update -qq && apt-get install -y nodejs

# yarnパッケージ管理ツールをインストール
# https://classic.yarnpkg.com/en/docs/install/#debian-stable
RUN curl -sS https://dl.yarnpkg.com/debian/pubkey.gpg | apt-key add -
RUN echo "deb https://dl.yarnpkg.com/debian/ stable main" | tee /etc/apt/sources.list.d/yarn.list
RUN apt-get update && apt-get install yarn

WORKDIR /アプリ名
COPY Gemfile /アプリ名/Gemfile
COPY Gemfile.lock /アプリ名/Gemfile.lock
RUN bundle install
COPY . /アプリ名

# Add a script to be executed every time the container starts
COPY entrypoint.sh /usr/bin/
RUN chmod +x /usr/bin/entrypoint.sh
ENTRYPOINT ["entrypoint.sh"]
EXPOSE 3000

# Start the main process.
CMD ["rails", "server", "-b", "0.0.0.0"]

3.Gemfileを作成する

source 'https://rubygems.org'
gem 'rails', '~>6'

4.空のGemfile.lockを生成する

MacBook-Air アプリ名 % touch Gemfile.lock

5.entrypoint.shを作成する

２つ目のところ、自身のアプリ名を入れることに気をつける

#!/bin/bash
set -e

# Remove a potentially pre-existing server.pid for Rails.
rm -f /アプリ名/tmp/pids/server.pid

# Then exec the container's main process (what's set as CMD in the Dockerfile).
exec "$@"

6.docker-compose.ymlを作成する

webのvolumesのところを自分のアプリ名を入れる

docker-compose.yml

version: "3"

services:
  db:
    image: mysql:8.0
    command: mysqld --default-authentication-plugin=mysql_native_password
    environment:
      MYSQL_ROOT_PASSWORD: root
    volumes:
      - ./tmp/db:/var/lib/mysql
  web:
    build: .
    command: bash -c "rm -f tmp/pids/server.pid && bundle exec rails s -p 3000 -b '0.0.0.0'"
    environment:
      MYSQL_HOST: db
    volumes:
      - .:/アプリ名
    ports:
      - "3000:3000"
    depends_on:
      - db

7.Rails newをする

ここではオプションとして、オプションでリンクしたサービスを起動しない設定、DBをmysqlにする設定を追加

MacBook-Air アプリ名 % docker-compose run web rails new . --force --no-deps --database=mysql
~
Starting アプリ名_db_1 ... done
Building web
~
Successfully built 36d2fef9a8a6
Successfully tagged アプリ名_web:latest

8. DBと接続する

passwordとhostに環境変数を設定する

database.yml

~
default: &default
  adapter: mysql2
  encoding: utf8mb4
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: root
  password: <%= ENV.fetch("MYSQL_ROOT_PASSWORD", "root") %>
  host: <%= ENV.fetch("MYSQL_HOST", "db") %>
~

9.コンテナを起動する

MacBook-Air アプリ名 % docker-compose up
~
web_1  | => Booting Puma
web_1  | => Rails 6.0.3.2 application starting in development 
web_1  | => Run `rails server --help` for more startup options
web_1  | Puma starting in single mode...
web_1  | * Version 3.12.6 (ruby 2.6.3-p62), codename: Llamas in Pajamas
web_1  | * Min threads: 5, max threads: 5
web_1  | * Environment: development
web_1  | * Listening on tcp://0.0.0.0:3000
web_1  | Use Ctrl-C to stop

この表示がでたらブラウザで http://localhost:3000/ にアクセスする
Yay! You’re on Rails! と表示がでたらコンテナ起動成功

エラー: No such file or directory @ rb_sysopen - /アプリ名/config/webpacker.yml Errno::ENOENT)発生時

MacBook-Air アプリ名 % docker-compose run web rails webpacker:install
~
Webpacker successfully installed ? ?

参考文献

https://docs.docker.com/compose/rails/
https://railsdoc.com/rails

　 Tweet
Qiitaで続きを読む

投稿日：2020-09-09T18:38:33+09:00

DockerでRuby on rails6のアプリケーションを環境構築したメモ

目標

Ruby on Rails6 のアプリケーションをDockerで新規にアプリを立ち上げたい
DBはMySQL

前提知識

Docker on mac
Rails tutorial完了などRuby on rails に関する基礎知識

1.作業ディレクトを作成し、移動する

MacBook-Air ~ % mkdir アプリ名
MacBook-Air ~ % cd アプリ名
MacBook-Air アプリ名 %

2.Dockerfileを定義する

Dockerfile

FROM ruby:2.6.3
RUN apt-get update -qq && apt-get install -y nodejs

# yarnパッケージ管理ツールをインストール
# https://classic.yarnpkg.com/en/docs/install/#debian-stable
RUN curl -sS https://dl.yarnpkg.com/debian/pubkey.gpg | apt-key add -
RUN echo "deb https://dl.yarnpkg.com/debian/ stable main" | tee /etc/apt/sources.list.d/yarn.list
RUN apt-get update && apt-get install yarn

WORKDIR /アプリ名
COPY Gemfile /アプリ名/Gemfile
COPY Gemfile.lock /アプリ名/Gemfile.lock
RUN bundle install
COPY . /アプリ名

# Add a script to be executed every time the container starts
COPY entrypoint.sh /usr/bin/
RUN chmod +x /usr/bin/entrypoint.sh
ENTRYPOINT ["entrypoint.sh"]
EXPOSE 3000

# Start the main process.
CMD ["rails", "server", "-b", "0.0.0.0"]

3.Gemfileを作成する

Gemfile

source 'https://rubygems.org'
gem 'rails', '~>6'

4.空のGemfile.lockを生成する

MacBook-Air アプリ名 % touch Gemfile.lock

5.entrypoint.shを作成する

２つ目のところ、自身のアプリ名を入れることに気をつける

entrypoint.sh

#!/bin/bash
set -e

# Remove a potentially pre-existing server.pid for Rails.
rm -f /アプリ名/tmp/pids/server.pid

# Then exec the container's main process (what's set as CMD in the Dockerfile).
exec "$@"

6.docker-compose.ymlを作成する

webのvolumesのところを自分のアプリ名を入れる

docker-compose.yml

version: "3"

services:
  db:
    image: mysql:8.0
    command: mysqld --default-authentication-plugin=mysql_native_password
    environment:
      MYSQL_ROOT_PASSWORD: root
    volumes:
      - ./tmp/db:/var/lib/mysql
  web:
    build: .
    command: bash -c "rm -f tmp/pids/server.pid && bundle exec rails s -p 3000 -b '0.0.0.0'"
    environment:
      MYSQL_HOST: db
    volumes:
      - .:/アプリ名
    ports:
      - "3000:3000"
    depends_on:
      - db

7.Rails newをする

ここではオプションとして、オプションでリンクしたサービスを起動しない設定、DBをmysqlにする設定を追加

MacBook-Air アプリ名 % docker-compose run web rails new . --force --no-deps --database=mysql
~
Starting アプリ名_db_1 ... done
Building web
~
Successfully built 36d2fef9a8a6
Successfully tagged アプリ名_web:latest

8. DBと接続する

passwordとhostに環境変数を設定する

database.yml

~
default: &default
  adapter: mysql2
  encoding: utf8mb4
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: root
  password: <%= ENV.fetch("MYSQL_ROOT_PASSWORD", "root") %>
  host: <%= ENV.fetch("MYSQL_HOST", "db") %>
~

9.コンテナを起動する

MacBook-Air アプリ名 % docker-compose up
~
web_1  | => Booting Puma
web_1  | => Rails 6.0.3.2 application starting in development 
web_1  | => Run `rails server --help` for more startup options
web_1  | Puma starting in single mode...
web_1  | * Version 3.12.6 (ruby 2.6.3-p62), codename: Llamas in Pajamas
web_1  | * Min threads: 5, max threads: 5
web_1  | * Environment: development
web_1  | * Listening on tcp://0.0.0.0:3000
web_1  | Use Ctrl-C to stop

この表示がでたらブラウザで http://localhost:3000/ にアクセスする
Yay! You’re on Rails! と表示がでたらコンテナ起動成功

エラー: No such file or directory @ rb_sysopen - /アプリ名/config/webpacker.yml Errno::ENOENT)発生時

MacBook-Air アプリ名 % docker-compose run web rails webpacker:install
~
Webpacker successfully installed ? ?

参考文献

https://docs.docker.com/compose/rails/
https://railsdoc.com/rails

　 Tweet
Qiitaで続きを読む

投稿日：2020-09-09T18:38:33+09:00

DockerでRuby on rails6 × MySQLのアプリケーションを環境構築したメモ

目標

Ruby on Rails6 のアプリケーションをDockerで新規にアプリを立ち上げたい
DBはMySQL

前提知識

Docker on mac
Rails tutorial完了などRuby on rails に関する基礎知識

1.作業ディレクトを作成し、移動する

MacBook-Air ~ % mkdir アプリ名
MacBook-Air ~ % cd アプリ名
MacBook-Air アプリ名 %

2.Dockerfileを定義する

Dockerfile

FROM ruby:2.6.3
RUN apt-get update -qq && apt-get install -y nodejs

# yarnパッケージ管理ツールをインストール
# https://classic.yarnpkg.com/en/docs/install/#debian-stable
RUN curl -sS https://dl.yarnpkg.com/debian/pubkey.gpg | apt-key add -
RUN echo "deb https://dl.yarnpkg.com/debian/ stable main" | tee /etc/apt/sources.list.d/yarn.list
RUN apt-get update && apt-get install yarn

WORKDIR /アプリ名
COPY Gemfile /アプリ名/Gemfile
COPY Gemfile.lock /アプリ名/Gemfile.lock
RUN bundle install
COPY . /アプリ名

# Add a script to be executed every time the container starts
COPY entrypoint.sh /usr/bin/
RUN chmod +x /usr/bin/entrypoint.sh
ENTRYPOINT ["entrypoint.sh"]
EXPOSE 3000

# Start the main process.
CMD ["rails", "server", "-b", "0.0.0.0"]

3.Gemfileを作成する

Gemfile

source 'https://rubygems.org'
gem 'rails', '~>6'

4.空のGemfile.lockを生成する

MacBook-Air アプリ名 % touch Gemfile.lock

5.entrypoint.shを作成する

２つ目のところ、自身のアプリ名を入れることに気をつける

entrypoint.sh

#!/bin/bash
set -e

# Remove a potentially pre-existing server.pid for Rails.
rm -f /アプリ名/tmp/pids/server.pid

# Then exec the container's main process (what's set as CMD in the Dockerfile).
exec "$@"

6.docker-compose.ymlを作成する

webのvolumesのところを自分のアプリ名を入れる

docker-compose.yml

version: "3"

services:
  db:
    image: mysql:8.0
    command: mysqld --default-authentication-plugin=mysql_native_password
    environment:
      MYSQL_ROOT_PASSWORD: root
    volumes:
      - ./tmp/db:/var/lib/mysql
  web:
    build: .
    command: bash -c "rm -f tmp/pids/server.pid && bundle exec rails s -p 3000 -b '0.0.0.0'"
    environment:
      MYSQL_HOST: db
    volumes:
      - .:/アプリ名
    ports:
      - "3000:3000"
    depends_on:
      - db

7.Rails newをする

ここではオプションとして、オプションでリンクしたサービスを起動しない設定、DBをmysqlにする設定を追加

MacBook-Air アプリ名 % docker-compose run web rails new . --force --no-deps --database=mysql
~
Starting アプリ名_db_1 ... done
Building web
~
Successfully built 36d2fef9a8a6
Successfully tagged アプリ名_web:latest

8. DBと接続する

passwordとhostに環境変数を設定する

database.yml

~
default: &default
  adapter: mysql2
  encoding: utf8mb4
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: root
  password: <%= ENV.fetch("MYSQL_ROOT_PASSWORD", "root") %>
  host: <%= ENV.fetch("MYSQL_HOST", "db") %>
~

9.コンテナを起動する

MacBook-Air アプリ名 % docker-compose up
~
web_1  | => Booting Puma
web_1  | => Rails 6.0.3.2 application starting in development 
web_1  | => Run `rails server --help` for more startup options
web_1  | Puma starting in single mode...
web_1  | * Version 3.12.6 (ruby 2.6.3-p62), codename: Llamas in Pajamas
web_1  | * Min threads: 5, max threads: 5
web_1  | * Environment: development
web_1  | * Listening on tcp://0.0.0.0:3000
web_1  | Use Ctrl-C to stop

この表示がでたらブラウザで http://localhost:3000/ にアクセスする
Yay! You’re on Rails! と表示がでたらコンテナ起動成功

エラー: No such file or directory @ rb_sysopen - /アプリ名/config/webpacker.yml Errno::ENOENT)発生時

MacBook-Air アプリ名 % docker-compose run web rails webpacker:install
~
Webpacker successfully installed ? ?

参考文献

https://docs.docker.com/compose/rails/
https://railsdoc.com/rails

　 Tweet
Qiitaで続きを読む

投稿日：2020-09-09T13:18:31+09:00

Laravel MySQL 既存カラムのNULL許容とデフォルト値をNULLに設定する

目的

LaravelとMySQLの組み合わせで「カラムへのNULL格納を許容する」と「デフォルト値をNULLに設定する」方法をまとめる。

実施環境

ハードウェア環境

項目情報

OS macOS Catalina(10.15.5)

ハードウェア MacBook Pro (13-inch, 2020, Four Thunderbolt 3 ports)

プロセッサ 2GHzクアッドコアIntel Core i5

メモリ 32 GB 3733 MHz LPDDR4

グラフィックス Intel Iris Plus Graphics 1536 MB

ソフトウェア環境

項目情報備考

PHPバージョン 7.4.3 Homwbrewを用いて導入

Laravelバージョン 7.0.8 commposerを用いてこちらの方法で導入→Mac Laravelの環境構築を行う

MySQLバージョン 8.0.19 for osx10.13 on x86_64 Homwbrewを用いてこちらの方法で導入→Mac HomebrewでMySQLをインストールする

前提条件

マイグレーションをすることができる状態のLaravelアプリが存在していること。

前提情報

本記事では都合上、マイグレーションファイルのカラムに変更を与える処理のみ記載する。

本来はマイグレーションファイルの作成、マイグレーションファイルの記載、マイグレーションなどの作業が必要である。

例

下記の内容をマイグレーションファイルのupに記載する必要がある。

マイグレーション後から現在の状態に戻すためのdownの処理も記載する必要がある。

nullの許容設定のマイグレーションファイルへの記載文を下記に記載する。
// nullを許容したい時
$table->カラムタイプ('カラム名')->nullable()->change();

// nullを許容しない時
$table->カラムタイプ('カラム名')->nullable(false)->change();
デフォルト値null設定のマイグレーションファイルへの記載文を下記に記載する。
$table->カラムタイプ('カラム名')->default(null)->change();
参考文献

Laravel 7.x データベース：マイグレーション

項目	情報
OS	macOS Catalina(10.15.5)
ハードウェア	MacBook Pro (13-inch, 2020, Four Thunderbolt 3 ports)
プロセッサ	2GHzクアッドコアIntel Core i5
メモリ	32 GB 3733 MHz LPDDR4
グラフィックス	Intel Iris Plus Graphics 1536 MB

項目	情報	備考
PHPバージョン	7.4.3	Homwbrewを用いて導入
Laravelバージョン	7.0.8	commposerを用いてこちらの方法で導入→Mac Laravelの環境構築を行う
MySQLバージョン	8.0.19 for osx10.13 on x86_64	Homwbrewを用いてこちらの方法で導入→Mac HomebrewでMySQLをインストールする

　 Tweet
Qiitaで続きを読む

投稿日：2020-09-09T12:00:56+09:00

expressのapiでmysqlを使う

mysqlをインストールする

mysqlのインストール

$ npm install mysql --save

get(select)

データを取得して返す。

api/index.js

const express = require('express')
const app = express()

app.get('/', function(req, res) {
  const mysql = require('mysql')
  const connection = mysql.createConnection({
    host: 'localhost',
    database: 'mysql',
    user: 'root',
    password: '',
  })
  connection.connect()
  const sql = 'select id, message from my_table limit ?;'
  const values = [Number.parseInt(req.query.limit)]
  connection.query(sql, values, (error, results, fields) => {
    if (error) throw error
    const data = results.map(result => {
      return {
        id: result.id,
        message: result.message
      }
    })
    res.json({ data })
  })
  connection.end()
})

module.exports = {
  path: '/api',
  handler: app
}

pages/index.vue

export default {
  async asyncData({ $axios }) {
    const response = await $axios.get('http://localhost:3000/api?limit=10')
    return {
      data: response.data.data
    }
  }
}

post(insert)

データを挿入する。

api/index.js

const express = require('express')
const bodyParser = require('body-parser')
const app = express()

app.use(bodyParser.json())

app.post('/', function(req, res) {
  const mysql = require('mysql')
  const connection = mysql.createConnection({
    host: 'localhost',
    database: 'mysql',
    user: 'root',
    password: '',
  })
  connection.connect()
  const sql = 'insert into my_table set ?'
  const values = { message: req.body.message }
  connection.query(sql, values, (error, results, fields) => {
    if (error) throw error
    res.json({ id: results.insertId })
  })
  connection.end()
})

module.exports = {
  path: '/api',
  handler: app
}

pages/index.vue

export default {
  methods: {
    async insert() {
      const response = await this.$axios.post('http://localhost:3000/api', {
        message: 'hello'
      })
      return response.data.id
    }
  }
}

put(update)

データを更新する。

api/index.js

const express = require('express')
const bodyParser = require('body-parser')
const app = express()

app.use(bodyParser.json())

app.put('/:id', function(req, res) {
  const mysql = require('mysql')
  const connection = mysql.createConnection({
    host: 'localhost',
    database: 'mysql',
    user: 'root',
    password: '',
  })
  connection.connect()
  const sql = 'update my_table set message = ? where id = ?'
  const values = [req.body.message, req.params.id]
  connection.query(sql, values, (error, results, fields) => {
    if (error) throw error
    res.json({ count: results.changedRows })
  })
  connection.end()
})

module.exports = {
  path: '/api',
  handler: app
}

pages/index.vue

export default {
  methods: {
    async update(id) {
      const response = await this.$axios.put(`http://localhost:3000/api/${id}`, {
        message: 'こんにちは'
      })
      return response.data.count
    }
  }
}

delete(delete)

データを削除する。

api/index.js

const express = require('express')
const app = express()

app.delete('/:id', function(req, res) {
  const mysql = require('mysql')
  const connection = mysql.createConnection({
    host: 'localhost',
    database: 'mysql',
    user: 'root',
    password: '',
  })
  connection.connect()
  const sql = 'delete from my_table where id = ?'
  const values = [req.params.id]
  connection.query(sql, values, (error, results, fields) => {
    if (error) throw error
    res.json({ count: results.affectedRows })
  })
  connection.end()
})

module.exports = {
  path: '/api',
  handler: app
}

pages/index.vue

export default {
  methods: {
    async remove(id) {
      const response = await this.$axios.delete(`http://localhost:3000/api/${id}`)
      return response.data.count
    }
  }
}

　 Tweet
Qiitaで続きを読む

投稿日：2020-09-09T11:56:40+09:00

[MySQL] 月ごとのカウント抽出で歯抜けになっている月を埋める

やりたいこと

月ごとのレコード数のカウントを取りたいが、レコードのない月でもカウント0で出力したい
日付マスタみたいなテーブルは作りたくない。SQLだけで完結させたい

対象のテーブル

データ

usersテーブル

created_at

2020-09-01

2020-09-01

2020-07-01

2020-07-01

2020-05-01

普通にSELECTした場合の出力

月カウント

202009 2

202007 2

202005 1

以下のような出力が欲しい

月カウント

202009 2

202008 0

202007 2

202006 0

202005 1

歯抜け対応のSQL

月一覧を出力する文を作成して、UNION ALLすることで対応する
SELECT
    EXTRACT(YEAR_MONTH FROM DATE_ADD(NOW(), INTERVAL(months.series) MONTH )) AS yearMonth
  FROM (
    SELECT 0 AS series
    FROM DUAL
    WHERE (@num := 1) * 0
    UNION ALL
    SELECT @num := @num - 1
    FROM information_schema.COLUMNS
    LIMIT 12
  ) AS months
最終的なSQLはこちら

月一覧取得の方に0 AS count を追加
SELECT
  s.yearMonth AS yearMonth,
  SUM(s.count) AS count
FROM (
  SELECT
    EXTRACT(YEAR_MONTH FROM DATE_ADD(NOW(), INTERVAL(months.series) MONTH )) AS yearMonth,
    0 AS count
  FROM (
    SELECT 0 AS series
    FROM DUAL
    WHERE (@num := 1) * 0
    UNION ALL
    SELECT @num := @num - 1
    FROM information_schema.COLUMNS
    LIMIT 12
  ) AS months
  UNION ALL
  SELECT
    EXTRACT(YEAR_MONTH FROM users.created_at) AS yearMonth,
    COUNT(*) AS count
  FROM
    users
  GROUP BY yearMonth
) AS s
GROUP BY s.yearMonth
ORDER BY s.yearMonth DESC
LIMIT 12

created_at
2020-09-01
2020-09-01
2020-07-01
2020-07-01
2020-05-01

月	カウント
202009	2
202007	2
202005	1

月	カウント
202009	2
202008	0
202007	2
202006	0
202005	1

　 Tweet
Qiitaで続きを読む

投稿日：2020-09-09T09:45:57+09:00

外部キーとして参照されている列にauto_incrementをつけ忘れた時の解決法

既に構築したテーブルの主キー列にAUTO_INCREMENTをつけ忘れていた。
後からAUTO_INCREMENTを付与したい状況。
しかし、列が他のテーブルから外部キー制約で参照されていると、単純にALTER TABLEしようとしても以下のようなエラーが発生する。

＊親テーブル：employee_info
*子テーブル：employee_state
*AUTO_INCREMENTを付与したい列：employee_id
Error Code: 1833. Cannot change column 'employee_id': used in a foreign key constraint 'employee_state_ibfk_1' of table 'management.employee_state'
対処法
・関連テーブルのWRITEロック
・子テーブルの外部キー制約を一時的に削除
・ALTER TABLEで親テーブルの列にAUTO_INCREMENTを追加
・外部キー制約を戻す（子テーブルにもう一度外部キー制約をつけ直す）
・テーブルをアンロック
LOCK TABLES employee_info WRITE, employee_state WRITE;

ALTER TABLE employee_state DROP FOREIGN KEY employee_state_ibfk_1;

ALTER TABLE employee_info MODIFY employee_id INT AUTO_INCREMENT;

ALTER TABLE employee_state ADD FOREIGN KEY(employee_state_ibfk_1) 
REFERENCES employee_info(employee_id);

UNLOCK TABLES;
結果、無事にAUTO_INCREMENTを付与できました！
SHOW COLUMNS FROM employee_info;
●参照
https://unk-pizza.hatenadiary.org/entry/20170530/p1

　 Tweet
Qiitaで続きを読む

投稿日：2020-09-09T02:32:29+09:00

AWSを使ってアプリケーションを公開する手順(4)データベースの作成

はじめに

AWSを使ってアプリケーションを公開する手順を記載していく。
この記事ではデータベースの作成を行う。

データベースを作成する

WEBアプリケーションのデータを保存するためにデータベースを作成する。

データベースの種類

データベースの種類には以下のようなものがある。
- 階層型データベース
- ネットワーク型データベース
- リレーショナルデータベース

この中で最も利用されているのがリレーショナルデータベースである。
リレーショナルデータベースを管理するソフトウェアをリレーショナルデータベースマネジメントシステム(RDBMS)という。
RDBMSの一つにMySQLがある。
MySQLはOracle社が開発・提供をしているRDBMSで、データベースの作成、編集、削除などを行うことができる。
オープンソースソフトウェアとして公開されており、誰でも無償で利用することができます。
Ruby on Railsと併せて利用することができるため、本稿ではMySQLを用いてデータベースを作成していく。

MySQLをインストールする

以下のコマンドを実行し、MySQLをインストールする。
今回はバージョン5.6をインストールする。
sudo yum -y install mysql56-server mysql56-devel mysql56
MySQLを起動する

以下のコマンドを実行し、MySQLを起動する。
sudo service mysqld start
以下のコマンドを実行し、「running」と表示されれば無事起動できている。
sudo service mysqld status
serviceコマンドとは

UNIX系(LinuxやMacなど)で使えるコマンドで、サービスを起動したり停止したりできる。

mysqldとは

上記のコマンドはmysqlではなくmysqldとなっていて、末尾に「d」がついている。
このdはLinuxの用語「デーモン(daemon)」の略で、「サーバ」を意味する。

MySQLのパスワード設定

インストールしたMySQLにはデフォルトでrootユーザーでアクセス出来るようになっているが、
パスワードが設定されていないので、パスワードを設定する。
例えば'AbCdEf1234'というパスワードにするときは以下のコマンドを実行する。
sudo /usr/libexec/mysql56/mysqladmin -u root password 'AbCdEf1234'
MySQLの接続確認

以下のコマンドを実行し、MySQLに接続できるか確認する。
パスワードの入力を求められるので先ほど設定したパスワードを入力する。
エラーが出なければOK。
mysql -u root -p
MySQLコマンドのオプションについて

上記MySQLコマンドのオプションは次のような意味である。
-u. ユーザ名指定
-p. パスワード指定

　 Tweet
Qiitaで続きを読む

投稿日：2020-09-09T01:18:17+09:00

laravelのバージョンアップを経験して

はじめに

当方、フリーでPHPエンジニアをやっています。

最近Laravelのvar5.2からvar6にバージョンアップするプロジェクトに携わる機会があり、そこで得た経験を大雑把ではありますが、まとめてみました。
これからバージョンアップを計画している方に少しでも参考になれば幸いです！

尚、テストコードの実装があまりさせていない環境でのバージョンアップでしたが、ちゃんとテストコードを書いている場合は、テスト回して確認する事をおすすめします！

基本的には公開されているアップデートガイドを見て進めるのが良いです。

確認する事

サーバー

laravelのドキュメント、サーバー要件を確認

mysql

バージョン確認する

PHP

バージョン確認する

PHPライブラリ

バージョンアップ後も動くか確認。ライブラリのバージョンを上げなければいけない時、既存のシステムが正常に動くか確認

ライブラリによってLaravelのバージョンアップ後、サポートしてない可能性もある為、注意が必要

laravelのヘルパー関数

飛び級のバージョンアップ時はだいぶ内容も変わっているので使っている際は注意が必要

collection

メソッドの内容の変更、廃止されたメソッドなど

Eloquent クエリビルダ

Eloquentの方が採用率は高いかもしれませんが、混合で使用していたりする場合は両方注意が必要

jobs failed_jobsテーブルの変更

5.3からjobsとfailed_jobsのテーブル構成に変更が入っています

Laravel Mix

css、jsの圧縮をされている際はバージョン確認

セッション

5.3からコンストラクタでセッションにアクセスできなくなってます

インスタンス化

今はDIを使っている所も多いかと思いますが、appでインスタンス化していて変数を渡している場合、キーを指定して渡して上げないとエラーになります

Log

飛び級のバージョンアップの場合、機能も充実して設定方法も変わっているので、注意が必要。slack等にエラーログ飛ばしている場合など。

vender配下をオーバーライドしている場合

私が携わったプロジェクトで一番ネックだった所です。あまりしないかもしれませんが、セッションやauth 、その他vender配下のメソッドをオーバーライドして独自に処理を加えている場合、基本的に機能しないと考えておいた方が良いです。

プロジェクトによってはまだまだ確認する所もあるかと思いますが、システムを良く理解している方と相談して進めていくと良いです！

バージョンアッププロジェクトを走らせる前に

注意事項をリストアップしておくと良いです。
例えば、PHPのバージョンアップが必要でPHP関数を普段良く使っている場合、非推奨になっている関数等ありますので、そういったリストと、修正内容を統一するために、補填としてどういった対応が必要か残しておくとチームとして動きやすくなります。

また、必要作業もリストアップしておくのをおすすめします。

最後に

テストコードを普段から書いておくとバージョンアップも楽です！

　 Tweet
Qiitaで続きを読む

投稿日：2020-09-09T00:46:50+09:00

MySQLのトランザクション処理ではまった話

概要

とある外部システムからの連携データを取り込む際に、うまくトランザクション処理ができなかったので、その際調べたことをまとめる。

発生した事象

とある業務で外部システムからの連携データ（CSVファイル）を一定の期間毎にテーブルに反映するという要件があり、下記のように一旦全レコードを削除してから連携データを挿入しようとした。
TRUNCATE TABLE table1;
LOAD DATA LOCAL INFILE 'hoge.csv' INTO TABLE table1;
上記の処理を1つのトランザクション処理として記述した所、loadに失敗した場合でもtable1が空になっており、ロールバックされない事象が発生してしまった。

原因1

よくよく調べてみると、下記のような記述を発見。

切り捨て操作は暗黙的なコミットを発生させるため、ロールバックできません。

(引用元：https://dev.mysql.com/doc/refman/5.6/ja/truncate-table.html)

truncateのようないわゆるDDLはトランザクション処理の外で実行されるためロールバックできないとのこと・・・。
そこで、truncateをdeleteに変更し、下記のように実装してみた。
DELETE FROM table1;
LOAD DATA LOCAL INFILE 'hoge.csv' INTO TABLE table1;
これでいけるだろうと思いきや、またしてもロールバックできず・・・。

原因2

さらに調べるとMySQLでは下記のようにストレージエンジンによってトランザクション処理自体が使えない場合があるとのこと（これまでSQL Server中心に触ってきたので知らなかった）。

・InnoDB：行ロック、トランザクション処理可能
・MyISAM：テーブルロック、トランザクション処理不可

今回対象にしていたテーブルのストレージエンジンを調べるとMyISAM・・・。そもそも、トランザクション処理できませんでしたというお話(T_T)

対処法

トランザクション処理ができないので、結局、先に挿入してから差分を削除する実装に変更。
テンポラリーテーブルにデータを全部持ってきて、挿入時に現在時刻を入れておくことで、後から時刻を比較して更新されていないものを削除する方式。
CREATE TEMPORARY TABLE tmp AS SELECT * FROM table1;
LOAD DATA LOCAL INFILE 'hoge.csv' REPLACE INFILE table1 SET created_at = now();
DELETE t1 FROM table1 AS t1 LEFT OUTER JOIN tmp AS t ON t1.key = t.key WHERE t1.created_at = t.created_at
根本的な解決になっていないが、とりあえずロードに失敗してもテーブルが空になることは避けられた。
本当はストレージエンジンをinnoDBに変更してトランザクション処理をかけたいが、常時稼働中のテーブルのため難しかった。

おまけ

MySQLやOracle以外のSQLではDDLもトランザクション処理の対象になっているらしい。しばらくはMySQL中心だろうから試すことはなさそう。

コメント

SQLとかDB周りはアプリ開発に必要な部分をちょこちょことつまんでるだけなので、知識が断片的になりがちだなと感じた。一度、体系的に勉強したほうがいいなあ。

　 Tweet
Qiitaで続きを読む

20200909のMySQLに関する記事は12件です。

[SQL]今更ながらSQLのサブクエリに向き合ってみる

本稿の対応

環境設定

ビュー

ビューの定義方法

サブクエリ

スカラサブクエリ

相関サブクエリ

サブクエリはパフォーマンスが悪いのか？

まとめ

参考文献

【MySQL】dockerで構築したデータベースサーバが日本語対応していないかも、、、

環境

データベースに日本語でユーザー名を登録しようとすると、、、、

MySQLデータベースサーバを日本語対応にする

データベースの再構築

DockerでRuby on rails6のアプリケーションを環境構築

目標

前提

1.作業ディレクトを作成し、移動する

2.Dockerfileを定義する

3.Gemfileを作成する

4.空のGemfile.lockを生成する

5.entrypoint.shを作成する

6.docker-compose.ymlを作成する

7.Rails newをする

8. DBと接続する

9.コンテナを起動する

エラー: No such file or directory @ rb_sysopen - /アプリ名/config/webpacker.yml Errno::ENOENT)発生時

参考文献

DockerでRuby on rails6のアプリケーションを環境構築したメモ

目標

前提知識

1.作業ディレクトを作成し、移動する

2.Dockerfileを定義する

3.Gemfileを作成する

4.空のGemfile.lockを生成する

5.entrypoint.shを作成する

6.docker-compose.ymlを作成する

7.Rails newをする

8. DBと接続する

9.コンテナを起動する

エラー: No such file or directory @ rb_sysopen - /アプリ名/config/webpacker.yml Errno::ENOENT)発生時

参考文献

DockerでRuby on rails6 × MySQLのアプリケーションを環境構築したメモ

目標

前提知識

1.作業ディレクトを作成し、移動する

2.Dockerfileを定義する

3.Gemfileを作成する

4.空のGemfile.lockを生成する

5.entrypoint.shを作成する

6.docker-compose.ymlを作成する

7.Rails newをする

8. DBと接続する

9.コンテナを起動する

エラー: No such file or directory @ rb_sysopen - /アプリ名/config/webpacker.yml Errno::ENOENT)発生時

参考文献

Laravel MySQL 既存カラムのNULL許容とデフォルト値をNULLに設定する

目的

実施環境

前提条件

前提情報

例

参考文献

expressのapiでmysqlを使う

mysqlをインストールする

get(select)

post(insert)

put(update)

delete(delete)

[MySQL] 月ごとのカウント抽出で歯抜けになっている月を埋める

やりたいこと

対象のテーブル

データ

普通にSELECTした場合の出力

以下のような出力が欲しい

歯抜け対応のSQL

最終的なSQLはこちら