カテゴリー: SQL

すべらないSQLによる本番マイグレーション

2021年1月13日2021年1月19日 | by 優 | にコメントを残す

仕様変更によってremarksにあるデータをlogin_infoに移す本番マイグレーションがありました。事業部に伝わる秘技ポイント作業前バックアップ →ヤバくなったら戻せる立会いによるレビュートランザクションとカウントを駆使したマイグレーションマイグレーションを本番サーバ内のトランザクションを利用することで、更新対象を常に確認しながら実行することで精度を確 …

の続きを読む

SQL

GROUP BYとHAVING COUNT(*) > 1で重複を探す

2020年6月16日2020年6月16日 | by 優 | にコメントを残す

GROUP BY 
  id,
  name
HAVING COUNT(*) > 1;

GROUP BY

id,

name

HAVING COUNT(*) > 1;

重複しない検索結果を検証する方法爆速レンタルサーバならConoHa WING サーバはプロに全部お任せ！「仕事」に専念したいあなたにおすすめです。 ConoHa VPSで運営してま🐱 サーバの勉強がしたいあなたにおすすめ！現役エンジニアの管理人が選んだ、WordPres …

の続きを読む

MySQL, トラブルシューティング

MySQLデッドロックロックしているスレッドを殺す

2020年5月25日2020年5月31日 | by 優 | にコメントを残す

(none)> SELECT * FROM  sys.innodb_lock_waits\G

***************************[ 1. row ]***************************
wait_started                 | 2020-05-25 06:26:01
wait_age                     | 0:00:13
wait_age_secs                | 13
locked_table                 | `homestead`.`samples`
locked_index                 | PRIMARY
locked_type                  | RECORD
waiting_trx_id               | 47694
waiting_trx_started          | 2020-05-25 06:26:01
waiting_trx_age              | 0:00:13
waiting_trx_rows_locked      | 1
waiting_trx_rows_modified    | 0
waiting_pid                  | 1274
waiting_query                | insert into `samples` (`hoge_ ... at`) values (?, ?, ?, ?, ?, ?)
waiting_lock_id              | 47694:2144:3:1
waiting_lock_mode            | X
blocking_trx_id              | 47675
blocking_pid                 | 744 ←●注目
blocking_query               | <null>
blocking_lock_id             | 47675:2144:3:1
blocking_lock_mode           | X
blocking_trx_started         | 2020-05-24 16:54:51
blocking_trx_age             | 13:31:23
blocking_trx_rows_locked     | 40
blocking_trx_rows_modified   | 2
sql_kill_blocking_query      | KILL QUERY 744
sql_kill_blocking_connection | KILL 744
1 row in set
Time: 0.009s

(none)> SELECT * FROM sys.innodb_lock_waits\G

***************************[ 1. row ]***************************

wait_started | 2020-05-25 06:26:01

wait_age | 0:00:13

wait_age_secs | 13

locked_table | `homestead`.`samples`

locked_index | PRIMARY

locked_type | RECORD

waiting_trx_id | 47694

waiting_trx_started | 2020-05-25 06:26:01

waiting_trx_age | 0:00:13

waiting_trx_rows_locked | 1

waiting_trx_rows_modified | 0

waiting_pid | 1274

waiting_query | insert into `samples` (`hoge_ ... at`) values (?, ?, ?, ?, ?, ?)

waiting_lock_id | 47694:2144:3:1

waiting_lock_mode | X

blocking_trx_id | 47675

blocking_pid | 744 ←●注目

blocking_query | <null>

blocking_lock_id | 47675:2144:3:1

blocking_lock_mode | X

blocking_trx_started | 2020-05-24 16:54:51

blocking_trx_age | 13:31:23

blocking_trx_rows_locked | 40

blocking_trx_rows_modified | 2

sql_kill_blocking_query | KILL QUERY 744

sql_kill_blocking_connection | KILL 744

1 row in set

Time: 0.009s

逝ったれや！🐱

(none)> kill 744;
Query OK, 0 rows affected

1 2	(none)> kill 744; Query OK, 0 rows affected

@see 爆速レンタルサーバならConoHa WING サーバはプロに全部お任せ！「仕事」に専念したいあなたにおすすめです。 ConoHa VPSで運営してま🐱 サーバの勉強がしたいあなたにおすすめ！現役 …

の続きを読む

RDB, SQL

EXISTSとINの使い分け【SQL】

2020年5月23日2020年5月23日 | by 優 | にコメントを残す

EXISTSとINは場合によって使い分ける。間違うと遅くなる…🐱 結論として従属表の選択性が高い場合 → 従属表から先に絞り込むIN 従属表の選択性が低い場合 → 主表から先に絞り込むEXISTS 両方の選択性が低い場合 → EXISTS 本の検索による例世界の本のデータベース bookを主表、book_categoryを従属表とする主表 bookテ …

の続きを読む

MySQL, SQL

SQL グループの中で最新の日付のデータを取得する MySQL

2020年5月16日2021年1月6日 | by 優 | にコメントを残す

●tb_user_section user_id section_id created 1 1 2020-05-11 20:00:00 1 2 2020-05-12 20:00:00 1 3 2020-05-13 20:00:00 2 1 2020-05-15 20:00:00 3 2 2020-05-18 20:00:00 3 4 2020-05-19 20:00:00 user_idのグループ …

の続きを読む

SQL

Laravel クエリスコープを使おう！🐱 Query Scope

2020年5月16日2020年5月17日 | by 優 | にコメントを残す

ORM？流行りものでしょ SQLで良くね？😨 → クエリスコープで保守しやすくなるぞ🐱 ●抽象的に内容がわかるから保守性アップ ●条件分岐と組みあわせてクエリの組み立ての柔軟性 ●コードが短く目と頭にやさしい#駆け出しエンジニア #プログラミング初心者 https://t.co/5sbnGp5iYW — 優さん🌷わくわく開発YouTuber (@yuu13n6) May 16, 2020 まずはS …

の続きを読む

MySQL, SQL, PHP

Laravel SQLを実行ヒアドキュメントクロス集計

2020年5月6日2020年5月13日 | by 優 | にコメントを残す

手の込んだ集計などはSQLで取った方がシンプルになることも多いヒアドキュメントを利用して記述するのが良いですね🐱

<?php


namespace App\Services;

use App\Services\Service;
use Illuminate\Support\Facades\DB;

class GetsectionParentGoalService extends Service
{
    const MY_section_PARENT_INDEX = 0; // 集計部署取得クエリのINDEX

    /**
     * 集計対象部署の半期のKPIを返却
     *
     * @param int $sectionParentId
     * @param int $year
     * @param string $period
     * @return object(stdClass)
     */
    public function execute($sectionParentId, $year, $period)
    {
        $sql = <<< SQL
            SELECT
              SUM(kpi1) AS kpi1,
              SUM(kpi2) AS kpi2,
              SUM(kpi3) AS kpi3,
              SUM(kpi4) AS kpi4,
              SUM(kpi5) AS kpi5,
              SUM(kpi6) AS kpi6,
              SUM(kpi7) AS kpi7,
              SUM(kpi8) AS kpi8,
              SUM(kpi9) AS kpi9,
              SUM(kpi10) AS kpi10,
              SUM(kpi11) AS kpi11,
              SUM(kpi12) AS kpi12
            FROM
              tb_section_kpi
            WHERE tb_section_kpi.section_id IN
            (
              SELECT DISTINCT
                tb_section.section_id
              FROM
                tb_section
                  JOIN
                tb_section_parent
                  ON tb_section.section_parent_id = ?
              WHERE
                tb_section.deleted = 0
              AND
                tb_section.year = ?
              AND
                tb_section.period = ?
            )
SQL;

        $result = DB::select($sql, [
                                    $sectionParentId,    // ON tb_section.section_parent_id = {$sectionParentId}
                                    $year,               // tb_section.year = '{$year}'
                                    $period,             // tb_section.period = '{$period}'
                                ]);

        return $result[self::MY_section_PARENT_INDEX];
    }
}

<?php

namespace App\Services;

use App\Services\Service;

use Illuminate\Support\Facades\DB;

class GetsectionParentGoalService extends Service

{

const MY_section_PARENT_INDEX = 0; // 集計部署取得クエリのINDEX

/**

* 集計対象部署の半期のKPIを返却

* @param int $sectionParentId

* @param int $year

* @param string $period

* @return object(stdClass)

public function execute($sectionParentId, $year, $period)

{

$sql = <<< SQL

SELECT

SUM(kpi1) AS kpi1,

SUM(kpi2) AS kpi2,

SUM(kpi3) AS kpi3,

SUM(kpi4) AS kpi4,

SUM(kpi5) AS kpi5,

SUM(kpi6) AS kpi6,

SUM(kpi7) AS kpi7,

SUM(kpi8) AS kpi8,

SUM(kpi9) AS kpi9,

SUM(kpi10) AS kpi10,

SUM(kpi11) AS kpi11,

SUM(kpi12) AS kpi12

FROM

tb_section_kpi

WHERE tb_section_kpi.section_id IN

(

SELECT DISTINCT

tb_section.section_id

FROM

tb_section

JOIN

tb_section_parent

ON tb_section.section_parent_id = ?

WHERE

tb_section.deleted = 0

AND

tb_section.year = ?

AND

tb_section.period = ?

)

SQL;

$result = DB::select($sql, [

$sectionParentId, // ON tb_section.section_parent_id = {$sectionParentId}

$year, // tb_section.year = '{$year}'

$period, // tb_section.period = '{$period}'

]);

return $result[self::MY_section_PARENT_INDEX];

}

終端文字の後に１行空行を入れることこの場合だとSQL; 終端文字の行の前後に文字が入らないようにする →左端にくっつきます。結果はarray+stdClassで取得されますヒアドキュメント内の変数・int型は{ …

の続きを読む

SQL

WordPressドメイン, HTTPSからHTTPに変更するSQL

2020年4月15日2020年4月15日 | by 優 | にコメントを残す

WordPressも春めいてきましたね！😊 暖かさにうきうきしてドメインを変えてみたくなったり ALBを利用したくなって HTTPSから薄手のHTTPに衣替えする季節になってきましたそんな春の訪れを感じるクエリ🐱🌸https://t.co/DYEWSw0Ufx#WordPress#progate#駆け出しエンジニアと繋がりたい — 優さん🌷わくわく開発YouTuber (@yuu1 …

の続きを読む

MySQL

MySQL xxx doesn’t have a default value

2020年4月14日2020年4月17日 | by 優 | にコメントを残す

MySQL5.6以降から規約が厳しくなった。 SQLを改善する MySQLの設定で緩める今回はMySQLの設定を緩めることで対応 /etc/my.cnf

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
↓
sql_mode=NO_ENGINE_SUBSTITUTION

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

↓

sql_mode=NO_ENGINE_SUBSTITUTION

対応

$ systemctl restart mysqld

1	$ systemctl restart mysqld

爆速レンタルサーバならCo …

の続きを読む

SQL

SQL 同じテーブルのレコードの列に別の列の値で更新する

2020年1月13日2021年1月13日 | by 優 | にコメントを残す

全件のカラム2の値をカラム1に代入して更新する例

UPDATE テーブル名
SET テーブル名.カラム1 = テーブル名.カラム2

1 2	UPDATE テーブル名 SET テーブル名.カラム1 = テーブル名.カラム2

具体例実際使う時は、条件を指定することが多い。

UPDATE administrators
SET administrators.login_info = administrators.remarks
WHERE administrators.login_info = ''
AND administrators.remarks != '';

UPDATE administrators

SET administrators.login_info = administrators.remarks

WHERE administrators.login_info = ''

AND administrators.remarks != '';

上書きされないように空の時にのみ入れています。爆速レンタルサーバならConoHa WI …

の続きを読む

SQL

SQL WHERE カラム名 IN (x, y, z, …)

2020年1月12日2021年1月12日 | by 優 | にコメントを残す

基本形

SELECT
  *
FROM 
  users
WHERE users.job_id IN (1, 6);

SELECT

FROM

users

WHERE users.job_id IN (1, 6);

実際はこういう形が多いはず

SELECT
  *
FROM 
  users
WHERE users.job_id IN (SELECT jobs.id FROM jobs WHERE job_code IN ('engineer', 'PM');

SELECT

FROM

users

WHERE users.job_id IN (SELECT jobs.id FROM jobs WHERE job_code IN ('engineer', 'PM');

ここが先に実行される

(SELECT jobs.id FROM jobs WHERE job_code IN ('engineer', 'PM')

1	(SELECT jobs.id FROM jobs WHERE job_code IN ('engineer', 'PM')

その結果でWHEREをする [crayon-60064401 …

の続きを読む

MySQL, RDB, SQL

MySQL5.7 いいね機能のSQL

2019年12月21日2019年12月27日 | by 優 | にコメントを残す

SQLの実行順序おさらい SELECT 列の抽出対象 FROM テーブル名 WHERE 行の絞り込み条件 GROUP BY hoge_colum HAVING 100 > amount Aテーブルの列と行を切り出しイメージをする Bテーブルの列と行を切り出しイメージをする ONで結合条件を指定 WHEREで行を絞りこむ GROUP BYでグループ化 …

の続きを読む