カテゴリー: SQL

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;

重複しない検索結果を検証する方法具体例

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

SELECT

id,

name

FROM

sample

GROUP BY

id,

name

HAVING COUNT(*) > 1;

の続きを読む

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 MySQLでロックを特定し、強制終了する – Qiita

の続きを読む

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日2020年5月17日 | 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

の続きを読む

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でグループ化 …

の続きを読む

RDB, SQL

SQLの書き方 SQL書き方ドリルを通して

2019年9月28日2019年10月3日 | by 優 | 2件のコメント

表紙だけ見るとかなりぬるそうなのだけれど本格的！秀逸な本？ソフトウェア。同梱されているソフトウェアで演習できるのですが、クオリティが高く問題数も多くて、教材としてすごく良くできてるのですよね。本屋でぱらぱら見ると『SQLを紙に書く本？』に見えてしまって魅力がわからないと思うのですが、メインはCDに収録されているソフトです。段階的に暗記せずにSQLが組めるように考 …

の続きを読む

MySQL, RDB, チューニング, Linux

MySQL SHOWによる解析とチューニング

2019年4月2日2019年5月29日 | by 優 | にコメントを残す

MySQLのチューニング調査依頼で時短で終わるようにバージョン CentOS7 MySQL5.6 DB, テーブルのDBエンジンの確認データベースの確認

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| exampledb       |
+--------------------+
4 rows in set (0.00 sec)

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| exampledb |

+--------------------+

4 rows in set (0.00 sec)

デフォルトのエンジンの確認

mysql> show engines;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql> show engines;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| MyISAM | YES | MyISAM storage engine | NO | NO | NO |

| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |

| CSV | YES | CSV storage engine | NO | NO | NO |

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| ARCHIVE | YES | Archive storage engine | NO | NO | NO |

| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

9 rows in set (0.00 sec)

…

の続きを読む

MySQL, AWS

AWSでのMySQLデータ生存戦略

2018年11月28日2019年11月28日 | by 優 | にコメントを残す

MySQL on EC2 EFSをマウント mysqldumpのスナップショットを定期で取ってEFSに出力バイナリログを–result-file=で指定してEFSに出力合わせて、 Cronを5秒ずつ程度走らせてバイナリログをS3に同期してバックアップ CloudWatchにもログを出力 MySQL on RDS 5分毎にSnapshotが走っている仕様 …

の続きを読む