More than 5 years have passed since last update.

MySQLとSQLのメモメモ

Last updated at 2018-05-18Posted at 2015-09-27

MySQLの備忘録。
GolangでMySQLのClientにGORMを使った場合についてメモ

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

初期設定

rootのPWを変更

> UPDATE mysql.user SET 
authentication_string=PASSWORD('rootchan') WHERE User='root';

新しくデータベースを作成する

> CREATE DATABASE dbo;
> CREATE DATABASE dbo_test;

新しくユーザーを作成する

> CREATE USER 'hoge'@'%' IDENTIFIED BY 'hogechang';
> UPDATE mysql.user SET authentication_string=PASSWORD('hogechan') WHERE User='hoge';
> SELECT user,host from mysql.user;

ユーザーの権限を付与する(hoge系のDBの全テーブルにGrant Option以外の権限付与)

see https://dev.mysql.com/doc/refman/5.7/ja/grant.html

> GRANT ALL PRIVILEGES ON dbo.* TO 'hoge'@'%';
> GRANT ALL PRIVILEGES ON dbo_test.* TO 'hoge'@'%';
> SHOW GRANTS for 'hoge'@'%'; # 権限確認

設定を反映する

> FLUSH PRIVILEGES;

SQL Modeの変更

GoのDate型は値型で初期値が'0000-00-00'であり、これがMySQLのError Code 1292: Incorrect datetime valueにあたりそれを許容するため変更する。
本当はDate型のポインタ型にし、NullをMySQLに入れたい。しかし、Datastoreではポインタ型のDate型は禁止のため、基底クラスの互換性を優先する

> SELECT @@GLOBAL.sql_mode;
> SET GLOBAL sql_mode = 'ALLOW_INVALID_DATES';
> SELECT @@GLOBAL.sql_mode;

再起動

$ mysql.server restart

コネクション

GORMでMySQLのコネクション

gorm.OpenメソッドでTCP接続を開始したら1コネクションとなる。
明示的にdb.Close()をした場合は、コネクションを切れる。
MySQLのコンソールから累積接続数を確認するとそれが分かる。
また、gorm.DBオブジェクトはスレッドセーフなので、グローバル変数において使用するのもOK。

db, err := gorm.Open("mysql", args)
defer db.Close()

GORMでMySQLのコネクションループ

コネクションループをする場合は、db.Close()をしなければ良い。
ただし、タイムアウトするので、go-sql-driver/mysqlなどのパッケージのオプション(SetConnMaxLifetimeなど)を設定する必要がある。

現在の接続数

> show global status like 'Threads_connected';

最大接続数設定(上限)

> show global variables like 'max_connections';

起動してからの累積接続数

> show global status like 'Connections';

起動してからこれまでの最大同時接続数

> show global status like 'Max_used_connections';

接続プロセスのタイムアウトの確認

> show global variables like 'wait_timeout%';

最大接続数を超えた場合のエラー(Too many connections)について

> show processlist;

テストデータ投入

> source ./hoge.sql

hoge.sqlƒ

INSERT INTO
  hoges
VALUES(
  2,
  "23t3223",
  "http://google.com",
  '2016-10-10',
  '2016-10-10'
);

ディレクトリ確認

> SHOW VARIABLES WHERE Variable_name LIKE "%dir"
+---------------------------+------------------------------------------------------+
| Variable_name             | Value                                                |
|---------------------------+------------------------------------------------------|
| basedir                   | /usr/local/Cellar/mysql/5.7.17/                      |
| character_sets_dir        | /usr/local/Cellar/mysql/5.7.17/share/mysql/charsets/ |
| datadir                   | /usr/local/var/mysql/                                |
| innodb_data_home_dir      |                                                      |
| innodb_log_group_home_dir | ./                                                   |
| innodb_tmpdir             |                                                      |
| lc_messages_dir           | /usr/local/Cellar/mysql/5.7.17/share/mysql/          |
| plugin_dir                | /usr/local/Cellar/mysql/5.7.17/lib/plugin/           |
| slave_load_tmpdir         | /var/folders/5z/ks1lxtfn1djddq6lvdx7vj1r0000gn/T/    |
| tmpdir                    | /var/folders/5z/ks1lxtfn1djddq6lvdx7vj1r0000gn/T/    |
+---------------------------+------------------------------------------------------+
10 rows in set
Time: 0.003s

Truncate

foreign key constraintがあるときのTruncate

SET FOREIGN_KEY_CHECKS = 0; 
TRUNCATE table $table_name; 
SET FOREIGN_KEY_CHECKS = 1;

インデックス

例

hogesテーブルは次の2つのインデックスを持つ
- age_cdとline_noの複合index
- customer_codeのindex

mysql> show index from hoges \G;
*************************** 1. row ***************************
        Table: hoges
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: hoges
   Non_unique: 1
     Key_name: index_hoges_on_age_cd_and_line_no
 Seq_in_index: 1
  Column_name: age_cd
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 3. row ***************************
        Table: hoges
   Non_unique: 1
     Key_name: index_hoges_on_age_cd_and_line_no
 Seq_in_index: 2
  Column_name: line_no
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 4. row ***************************
        Table: hoges
   Non_unique: 1
     Key_name: index_hoges_on_customer_code
 Seq_in_index: 1
  Column_name: customer_code
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:

その他

接続

$ mysql -uroot -ppassword -h localhost hoge_development_db

SQLの一括投入

ターミナルから

$ ls -al data
hogehoge.sql  ageage.sql   import.sh   user.conf
$ cd data
$ chmod 600 user.cnf
$ sh import.sh ./user.conf

data/user.conf

[client]
user = root
password = root
host = localhost
database = app

data/import.sh

# !/bin/bash

sqlfiles=*.sql
 
for filepath in ${sqlfiles}
do
    mysql --defaults-extra-file=$1 < ${filepath}
done

Schema

Key

> DESCRIBE hoges; 
> DESC hoges; 
> SHOW COLUMNS FROM hoges;

Key	説明
PRI	主キー
UNI	ユニークキー
MUL	主キーでもユニークキーでもないインデックス

KeyがMUL場合、列は、列内で指定された値の複数の出現が許可される非一意索引の最初の列です。
- refs https://code.i-harness.com/ja/q/512501

Kind of Index

Key	説明
主キー	テーブルの中のデータを識別する（重複する値のない）１つまたは複数のフィールド（１つのみ設定、Nullは含まない）
ユニークキー	重複する値のない１つまたは複数のフィールド（複数設定可能でNullを許可）
インデックス	指定されたフィールドのみ検索対象として、検索速度を早くする（主キー、ユニークキー以外で複数設定可能、Nullを許可）
フルテキスト	SELECT文での検索可能（MyISAM型のデータベースでのみ使用可能）

設計

量の名前

量の名前	次元
スカラ	0次元、値
ベクトル	1次元、配列
テンソル	2次元、多次元配列

正規化

正規化	説明
第1	データをスカラ値にする
第2	PKで分ける
第3	FKで分ける

アソシエーション

1:多

芸能人と一般人のパターン
多が1を知る必要がある(FK)

多:多

タグ付けのテーブルなどで使われるパターン
2次元では表現できないので、中間テーブルを用意する

1:1

目的ごとに2つのテーブルを分けるパタン
意味論的と変更可能性を考慮して設計する
- 例えば、UserテーブルとそのUserのCredentialのテーブルの場合、
- UserがCredentialのIDを所有する
  - 意味論的にusers.credential_idの方がcredentials.user_idより良いから
  - もし、credentials.user_idにすると、credentailを作る時に、userが必要になるから
- ただし、今後の変更で1:多になるなら
  - CredentialがUserのIDを所有する
  - 正規化ではベクトルは許容されないから

構文

指定順序と実行順序

The conceptual order of query processing is:

指定順序	実行順序	句	内容
2	1	FROM(Including JOINs)	実行対象のテーブルを指定する
3	2	WHERE	テーブルに対してレコードの抽出条件を指定する
4	3	GROUP BY	レコードをグループ化する
5	4	HAVING	グループ化した結果に対して抽出条件を指定する
1	5	SELECT	取得（表示）する列を指定する
6	6	ORDER BY	取得した列を並び替える

CRUD

-- コメント
/*
   複数行
　 コメント
*/

条件分岐

CASE式

まとめ系

GROUP BY句

複数のレコードをグルーピングする
データ操作には、FROM句とWHERE句の結果が使用される
GROUP BYを使った場合、SELECT句に加工なしで使える項目は、GROUP BY句に使った項目に依存する
要はGROUP BY句で指定していない、重複するカラムをSELECT文に集計関数抜きなどでは指定できない

HAVING句

GROPU BY用のWHERE句
HAVING句の指定はグループ(複数行のレコード)に対して行われる
HAVING構文は、集計関数を含めることができる点を除けば、WHERE 構文と同じ
WHERE 検索条件がグループ化操作の前に適用されるのに対し、HAVING 検索条件はグループ化操作の発生後に適用される

エイリアス

fields AS name1
(
) AS name2

サブクエリ

主にSELECT, JOIN, FROM, WHERE, HAVINGなどで括弧と共に使う。
相関サブクエリはエイリアスなどを使って、外側の結果を参照したもの
基本的にサブクエリは一つの関数として読む

タイプ	読み方
(非相関)サブクエリ	サブクエリの引数はなく。1度だけ実行される
相関サブクエリ	サブクエリの引数があるため、引数が変わったタイミングで実行される

ANY, ALL, EXISTS演算子

ANY, ALL, EXISTS演算子はサブクエリの「TRUE」または「FALSE」を評価する

ANY(SOME)

比較演算子と共に使用する
結果セットの値のいずれかが比較演算子の関係を満たす場合にTRUEを返す

ALL

比較演算子と共に使用する
結果セットの値の全てが比較演算子の関係を満たす場合にTRUEを返す

EXISTS

サブクエリの生成した値が存在する場合はTRUEを返す
否定はNOT EXISTS

DISTINCT

結合系

UNION

和集合

重複を削除する場合

/* BLUE + RED */

SELECT * FROM BLUE
UNION
SELECT * FROM RED ;

重複を削除しない場合

/* BLUE + RED */

SELECT * FROM BLUE
UNION ALL
SELECT * FROM RED ;

EXPECT

差集合

/* BLUE - RED(BLUEにはあって、REDにはないもの) */

SELECT * FROM BLUE
EXCEPT
SELECT * FROM RED ;

INTERSECT

積集合

/* BLUE * RED */
 
SELECT * FROM BLUE
INTERSECT
SELECT * FROM RED ;

集計関数

関数名	機能
COUNT	集計対象の行数を出力
MAX	集計対象のなかで最大のものを出力
MIN	集計対象のなかで最小のものを出力
SUM	集計対象の合計値を出力
AVG	集計対象の平均を出力

例

GROPU BYしてその中の最大の値を含む行を取得する


-- table

id  home  datetime     player   resource
---|-----|------------|--------|---------
1  | 10  | 04/03/2009 | john   | 399 
2  | 11  | 04/03/2009 | juliet | 244
5  | 12  | 04/03/2009 | borat  | 555
3  | 10  | 03/03/2009 | john   | 300
4  | 11  | 03/03/2009 | juliet | 200
6  | 12  | 03/03/2009 | borat  | 500
7  | 13  | 24/12/2008 | borat  | 600
8  | 13  | 01/01/2009 | borat  | 700

-- wants result

id  home  datetime     player   resource 
---|-----|------------|--------|---------
1  | 10  | 04/03/2009 | john   | 399
2  | 11  | 04/03/2009 | juliet | 244
5  | 12  | 04/03/2009 | borat  | 555
8  | 13  | 01/01/2009 | borat  | 700

SELECT tt.*
FROM topten tt
INNER JOIN
    (SELECT home, MAX(datetime) AS MaxDateTime
    FROM topten
    GROUP BY home) groupedtt 
ON tt.home = groupedtt.home 
AND tt.datetime = groupedtt.MaxDateTime

別テーブルに存在するIDの合計値を取得したい

- 全社員の売上の合計を表示したい。

社員マスタ
社員コード	社員名	部署コード	年齢
1	テスト社員1	1	22
2	テスト社員2	2	30
3	テスト社員3	null	20

売上明細
売上NO	社員コード	売上日	売上金額
1	1	20150401	200
2	1	20150402	300
3	2	20150408	100
4	2	20150501	150
5	3	20150505	550

SELECT
   MAX(T1.社員名) AS 社員名
   ,SUM(T2.売上金額) AS 売上金額
FROM
  社員マスタ AS T1
LEFT JOIN
  売上明細 AS T2
ON
  T1.社員コード = T2.社員コード
GROUP BY
  T1.社員コード


--実行結果

社員名	売上金額
テスト社員1	500
テスト社員2	250
テスト社員3	550

別テーブルにIDが存在していたら、元のレコードを取得する

- 売上のある社員のみ表示したい。

社員マスタ
社員コード	社員名	部署コード	年齢
1	テスト社員1	1	22
2	テスト社員2	2	30
3	テスト社員3	null	20
4	テスト社員4	1	30

売上明細
売上NO	社員コード	売上日	売上金額
1	1	20150401	200
2	1	20150402	300
3	2	20150408	100
4	2	20150501	150
5	3	20150505	550

SELECT
     社員名
FROM
     社員マスタ AS T1
WHERE
     EXISTS(
          SELECT
               1
          FROM
               売上明細 AS S1
          WHERE
               T1.社員コード = S1.社員コード
     )


--↓EXISTSを使わない場合
SELECT
     MAX(T1.社員名) AS 社員名
FROM
     社員マスタ AS T1
JOIN
     売上明細 AS T2
ON
     T1.社員コード = T2.社員コード
GROUP BY
     T1.社員コード

自己結合

テーブルが再帰的(同一テーブルへのparent_idを持っている)時などに有効

従業員コード	従業員名	上司コード
00001	チャーリー・ブラウン
00002	ペパーミント・パティ
00003	ルシール・ヴァン・ぺルト	00001
00004	ライナス・ヴァン・ぺルト	00003
00005	シュローダー	00001
00006	マーシー	00002

SELECT
     部下.従業員コード
     ,部下.従業員名
     ,上司.従業員名 AS 上司の名前
FROM
     従業員 AS 部下
JOIN 
     従業員 AS 上司
ON
     部下.上司コード = 上司.従業員コード
ORDER BY
    部下.従業員コード

従業員コード	従業員名	上司の名前名
00003	ルシール・ヴァン・ぺルト	チャーリー・ブラウン
00004	ライナス・ヴァン・ぺルト	ルシール・ヴァン・ぺルト
00005	シュローダー	チャーリー・ブラウン
00006	マーシー	ペパーミント・パティ

Gist

外部テーブルとJOINしたい時の例


/*
  　パターン1
*/


SELECT
        i.*,
        c.name AS rank
FROM
        inquiries AS i
INNER JOIN
        constants AS c
ON
        c.key = i.rank
WHERE
        i.id = 1
        AND
        c.field = 'RANK'
\G

/*
  　パターン2
*/

SELECT
        i.*,
        c2.name AS rank
FROM
        inquiries AS i
INNER JOIN (
        SELECT
                *
        FROM
                constants  AS c
        WHERE
                c.field = 'RANK'
) AS c2
ON
        c2.key = i.rank
WHERE
        i.id = 1
\G

cd値でGroup byし、最小の行(no)の値をもつレコードを取得し、その中に既読がないもののカウント

SELECT
        COUNT(*) AS unread_count
FROM
        letters AS i
INNER JOIN (
        SELECT
                cd,
                MIN(no) AS first_letter_no
        FROM
                letters
        GROUP BY
                cd
) AS i2
ON
        i.cd = i2.cd AND
        i.no = i2.first_letter_no
WHERE 0 = ANY(
        SELECT
                opend
        FROM
                letters AS i3
        WHERE
                i.cd = i3.cd
)
\G;

Datatypes

charとvarcharの違い

type	違い
char	固定長。最大255byte。CD値なのに
varchar	可変長。0~65535文字まで。可変長なのでメモリ効率がいい

NOTE

文字数を記録するために1バイト消費する点に注意が必要。
例えばvarchar(4)に"abcd"と格納した場合，5バイト分メモリを消費する．

varcharとtextの違い

type	初期値	最大文字長	最大sizeの数の意味
varchar	指定可	指定可	文字数で指定(日本語でも、最大65535文字まで入る)
text	指定不可	指定不可	byteで指定(1文字3byteのutf8で日本語入れたら65535 の 1/3文字入る)

InnoDBの8KBの壁に当たることもあるので、varcharで良い

制約

FK制約
UNIQUE
PRIMARY
NOT NULL

確認方法

> SHOW CREATE TABLE something\G

Key

MUL 重複可能インデックス
PRI primary key
UNI unique

JOINの使い分け

INNER JOIN
- 所謂絞り込みで使用する
OUTER JOIN
- 全件取得など

Loading

Eager loading
Lazy Loading

参考文献

http://tamata78.hatenablog.com/entry/2015/10/30/140000
https://github.com/jinzhu/gorm/issues/1053#issuecomment-265344516
https://stackoverflow.com/questions/5452760/how-to-truncate-a-foreign-key-constrained-table
http://omokichi.sakura.ne.jp/blog/mysql%E3%81%ABsql%E3%83%95%E3%82%A1%E3%82%A4%E3%83%AB%E3%82%92%E4%B8%80%E6%8B%AC%E3%82%A4%E3%83%B3%E3%83%9D%E3%83%BC%E3%83%88%E3%81%99%E3%82%8B%E6%96%B9%E6%B3%95/
http://blog.doli.jp/blog/2012/post490/
http://hrt0kmt.hatenablog.com/entry/2014/07/09/000000
http://write-remember.com/archives/4805/
https://dev.classmethod.jp/server-side/db/difference-where-and-having/
https://stackoverflow.com/questions/24127932/mysql-query-clause-execution-order
https://qiita.com/suzukito/items/edcd00e680186f2930a8
http://d.hatena.ne.jp/CAMUS/20060626/1151334328
https://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql
https://stackoverflow.com/questions/30749913/which-performs-first-where-clause-or-join-clause
http://omachizura.com/sql/GROUP%20BY%E5%8F%A5%E3%81%A7%E3%83%87%E3%83%BC%E3%82%BF%E3%81%AE%E9%9B%86%E8%A8%88%E3%83%BB%E9%9B%86%E7%B4%84%E3%82%92%E8%A1%8C%E3%81%86.html
http://www.techscore.com/tech/sql/index.html/
https://doruby.jp/users/takeshita/entries/%E6%84%8F%E8%AD%98%E3%81%97%E3%81%AA%E3%81%84%E3%81%A8%E3%81%BE%E3%81%9A%E3%81%84%EF%BC%9F-ActiveRecord-SQL%E3%82%AD%E3%83%A3%E3%83%83%E3%82%B7%E3%83%A5%E3%81%AE%E7%BD%A0
https://stackoverflow.com/questions/1299374/what-is-eager-loading

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up