refirio.org

Memo

メモ > 技術 > データベース: MySQL

■設計
SQLアンチパターン勉強会 - Qiita https://qiita.com/iwata@github/items/9a47ddababd8e0a82662 ユーザ情報を保存する時のテーブル設計 - そーだいなるらくがき帳 https://soudai.hatenablog.com/entry/2018/05/01/204442 ソーシャルゲームのためのMySQL入門 - Technology of DeNA https://engineer.dena.jp/2010/11/mysql-for-socialgame.html MySQLテーブル設計入門 https://www.slideshare.net/yoku0825/mysql-47591576 ER図とは？書き方やテクニックをわかりやすく解説 https://products.sint.co.jp/ober/blog/create-er-diagram CRUD図 (クラッド図)とは｜「分かりそう」で「分からない」でも「分かった」気になれるIT用語辞典 https://wa3.i-3-i.info/word13565.html 【入門】データベース設計まとめ - Qiita https://qiita.com/KNR109/items/5d4a1954f3e8fd8eaae7 Postgres と MySQL における id, created_at, updated_at に関するベストプラクティス https://zenn.dev/mpyw/articles/rdb-ids-and-timestamps-best-practices
■コーディングスタイル
分析SQLのコーディングスタイル - クックパッド開発者ブログ http://techlife.cookpad.com/entry/2016/11/09/000033 「分析SQLスタイルガイド」をかなり真面目に考えた - Qiita https://qiita.com/kai_data/items/6c119c43ad3626226dfc ワンランク上のSQLを書くためのポイント3つ - Qiita https://qiita.com/ken1041/items/49417edd50536a397318 あくまでも参考程度に
■データベースの文字コード
utf8_unicode_ciではなく、utf8_general_ciにしておくのが無難 なお今はutf8だけでなく、utf8mb4についても検討する MySQLのutf8_general_ciとutf8_unicode_ciの違い http://program.sagasite.info/wiki/index.php?MySQL%E3%81%AEutf8_general_ci%E3%81%A8utf8_unicode_ci%E3... utf8_unicode_ci に対する日本の開発者の見解 http://blog.kamipo.net/entry/2015/03/08/145045 MySQLの照合順序 http://qiita.com/Vit-Symty/items/159c27d7d62c78ee9ce7 ■文字コードの変更 utf8からutf8mb4に変更する場合の調査メモ ・基本的にはコマンドを叩くことで変更できそう ・767byte問題のために、データ型の変更が発生する可能性がありそう ・特定テーブルの文字コードのみ変更して事足りるなら、それが影響範囲が少なくていいか ・特定テーブルの特定列の文字コードのみ変更できるかは要調査。以下のページによると可能そうだが、照合順を変更するだけかどうか MySQLのテーブル作成後に、文字コードをutf8mb4に変更する - karakaram-blog https://www.karakaram.com/changing-the-character-set-to-utf8mb4-after-creating-mysql-table/ MySQLの文字コードをutf8mb4に変更 - Qiita https://qiita.com/decoch/items/bfa125ae45c16811536a MySQLのencodingをutf8からutf8mb4に変更して寿司ビール問題に対応する｜TechRacho by BPS株式会社 https://techracho.bpsinc.jp/hachi8833/2020_11_26/25044 MySQL utf8をutf8mb4に変更する - PukiWiki https://yassu.jp/pukiwiki/index.php?MySQL+utf8%A4%F2utf8mb4%A4%CB%CA%D1%B9%B9%A4%B9%A4%EB MySQL の文字エンコーディングを utf8mb4 にあとから変更する - ハトネコエ Web がくしゅうちょう https://nekonenene.hatenablog.com/entry/2016/08/02/125424
■データベースのサイズ
サーバメモ「Command.txt」内の「MySQLのデータ容量を確認」を参照
■データ操作
データベース作成
CREATE DATABASE test DEFAULT CHARACTER SET utf8mb4;
データベース削除
DROP DATABASE test;
データベース仕様確認
SHOW CREATE DATABASE test;
テーブル作成
CREATE TABLE address( no INT, name VARCHAR(80), tel VARCHAR(80) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT 'アドレス';
テーブル名変更
ALTER TABLE address RENAME TO address2; RENAME TABLE address TO address2;
MySQLの「ALTER TABLE RENAME」と「RENAME TABLE」 - なからなLife https://atsuizo.hatenadiary.jp/entry/2016/06/16/100000 テーブル削除
DROP TABLE address;
テーブル仕様確認
SHOW COLUMNS FROM address; SHOW FULL COLUMNS FROM address; SHOW CREATE TABLE address;
登録
INSERT INTO address(no, name, tel) VALUES(1, '山田太郎', '090-1234-5678'); INSERT INTO address VALUES(1, '山田太郎', '090-1234-5678');
表示
SELECT name, tel FROM address; SELECT token, COUNT(*) FROM devices GROUP BY token;
更新
UPDATE address SET name = '山田健二' WHERE name = '山田太郎'; UPDATE address SET name = '山田健二', tel = '090-2345-6789' WHERE name = '山田太郎';
削除
DELETE FROM address WHERE name = '山田太郎'; DELETE FROM products WHERE id >= 5001;
複製
INSERT INTO address_test SELECT * FROM address WHERE no >= 3;
オートインクリメントをリセット
ALTER TABLE products AUTO_INCREMENT = 0;
日時をフォーマットして表示
SELECT DATE_FORMAT(created, '%Y-%m-%d %H:%i:%s') AS created FROM histories GROUP BY created ORDER BY id DESC LIMIT 100;
日付をフォーマットして表示
SELECT DATE_FORMAT(created, '%Y-%m-%d') AS created FROM histories GROUP BY created ORDER BY id DESC LIMIT 100;
日付ごとにランキング
SELECT DATE_FORMAT(created, '%Y-%m-%d') AS created, COUNT(*) AS count FROM histories GROUP BY DATE_FORMAT(created, '%Y-%m-%d') ORDER BY count DESC LIMIT 100;
連結して表示
SELECT histories.created_at, histories.user_id, histories.device_id, devices.token FROM histories LEFT JOIN devices ON histories.device_id = devices.id WHERE histories.detail IS NULL ORDER BY histories.id LIMIT 100 ; SELECT devices.token, COUNT(devices.token) FROM histories LEFT JOIN devices ON histories.device_id = devices.id WHERE histories.detail IS NULL AND histories.created_at = '2019-02-08 15:02:29' GROUP BY devices.token ;
データ件数を一括確認
SELECT table_name, table_rows from information_schema.TABLES WHERE table_schema = '対象データベース名' ;
MySQLで全テーブルのレコード数を一覧表示する - Qiita https://qiita.com/isobecky74/items/6efff247484f8e4fccae インデックスを確認
SHOW INDEX FROM histories;
インデックスを一括確認
SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, COLUMN_NAME, SEQ_IN_INDEX FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = '対象データベース名' ORDER BY TABLE_SCHEMA, TABLE_NAME,INDEX_NAME, SEQ_IN_INDEX ;
MySQLでDB内の全てのインデックスの一覧をCSV出力する｜エンジニア初心者の備忘録 http://rigil.iku4.com/mysql_command/index
■データ取得
■ソート MySQLでORDER BYをつけないときの並び順 - かみぽわーる https://blog.kamipo.net/entry/2016/12/24/234944 「MySQLでORDER BYがついていないときに返す結果セットの並び順は定められていない。」とされている 並び順を指定しないと、稀に意図しない順でデータが取得されるので注意 【MySQL】昇順ソートで、NULLを最後に表示する - MySQL < 技術ブログ < 株式会社プロネット | WEBシステム開発20年以上の実績 https://www.p-nt.com/technicblog/archives/123 ■グループ MySQLでgroup byの結果を逆順（最大順、最新順）で取得する方法 https://xov.jp/e/176/ 【MySQL】グループ毎に連番を振る - Qiita https://qiita.com/toyottoyo/items/813338f4756dee41e49b ■縦に並べて表示 MySQLのselectの表示結果を\Gで縦に見やすく表示したい - Qiita https://qiita.com/HorikawaTokiya/items/80196e7e33695fc594ab
> SELECT * FROM table_test; +----+---------------------------+ | id | text | +----+---------------------------+ | 1 | テストメッセージ1 | | 2 | テストメッセージ2 | | 3 | テストメッセージ3 | +----+---------------------------+ 3 rows in set (0.00 sec) > SELECT * FROM table_test \G *************************** 1. row *************************** id: 1 text: テストメッセージ1 *************************** 2. row *************************** id: 2 text: テストメッセージ2 *************************** 3. row *************************** id: 3 text: テストメッセージ3 3 rows in set (0.00 sec)
■ファイル出力 MySQLのSELECT結果をファイル出力する方法とよくあるエラー | pixelbeat sandbox http://pixelbeat.jp/mysql-select-into-outfile/ ■その他 MySQLで全文検索 - FULLTEXTインデックスの基礎知識｜blog｜たたみラボ http://www.tatamilab.jp/rnd/archives/000389.html MySQL FULLTEXT + Ngram : LIKE検索より数十倍高速な、お手軽 日本語全文検索 について｜blog｜たたみラボ http://www.tatamilab.jp/rnd/archives/000390.html MySQLでの近似値順での値の取得について質問させてください。 【OKWAVE】 https://okwave.jp/qa/q6056886.html SQL関数coalesceの使い方と読み方 | データベース | DoRuby https://doruby.jp/users/akio0911_on_rails/entries/SQL_coalesce_ MYSQLの関数 COALESCE - Qiita https://qiita.com/mikakane/items/1e45c2a798d0c7edffda 【MySQL】二つのカラムの組み合わせでユニーク設定するには. - My... - Yahoo!知恵袋 https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1252536032 そろそろSQLのウィンドウ関数を理解したい - 連載1/3話 - Qiita https://qiita.com/w-sato-ist/items/63600a3ab84aad38e879 SQL記述者全員が理解すべきSELECT文の実行順序のお話 - Qiita https://qiita.com/k_0120/items/a27ea1fc3b9bddc77fa1
■データ登録
InnoDBの8KBの壁にぶち当たったら。 - sawara.me http://sawara.me/mysql/2219/ ダミーデータを作成する。 insert_generate_series - イノベートな非日常 http://d.hatena.ne.jp/IT7C/20140520/1400522465 ■「0000-00-00 00:00:00」のデータを登録する データ登録時に以下のエラーになった場合、
ERROR 1292 (22007) at line 2419: Incorrect datetime value: '0000-00-00 00:00:00' for column 'created' at row 10
SQLの設定を変更することで登録できるようになる （「NO_ZERO_IN_DATE」と「NO_ZERO_DATE」の項目を削除している） とは言え、そもそも「0000-00-00 00:00:00」というのはおかしなデータなので、可能ならデータを「1970-01-01 00:00:00」やNULLにしておく方が好ましい
# mysql -u root -p > SELECT @@GLOBAL.sql_mode; +-------------------------------------------------------------------------------------------------------------------------------------------+ | @@GLOBAL.sql_mode | +-------------------------------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +-------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) > SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; Query OK, 0 rows affected, 1 warning (0.00 sec) > SELECT @@GLOBAL.sql_mode; +--------------------------------------------------------------------------------------------------------------+ | @@GLOBAL.sql_mode | +--------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +--------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) > exit Bye
Docker環境の場合、mysqlコンテナを停止＆起動すると設定がリセットされた この場合、my.confで以下のように指定することで恒久的に対処できた
[mysqld] character-set-server=utf8 sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION [client] default-character-set=utf8
MySQLのsql_modeのせいで'0000-00-00'登録時にエラーが出た昔話。 - Qiita https://qiita.com/tentatsu/items/fd2177777412ebc2f6b9 【MySQL】DATE/DATETIME型の'0000-00-00'について整理してみる - Qiita https://qiita.com/rhap/items/26b3e796c68a2adaccf5 Data truncation: Incorrect datetime value: ‘0000-00-00 00:00:00’ | DriftwoodJP https://www.d-wood.com/blog/2020/02/20_11802.html ■代理キー管理のテーブルにINSERTする codeが「test」のデータの代理キーをもとに挿入したい…という場合、あくまでも一例だが以下のような方法は使えるかもしれない （いったん仮の値で登録し、後から正しい代理キーに変更する）
INSERT INTO examinations(school_id, name, sort) VALUES(99999, 'AOエントリー', 1); INSERT INTO examinations(school_id, name, sort) VALUES(99999, 'A入試', 2); INSERT INTO examinations(school_id, name, sort) VALUES(99999, 'B入試', 3); INSERT INTO examinations(school_id, name, sort) VALUES(99999, '二次入試', 4); UPDATE examinations SET school_id = (SELECT id FROM schools WHERE code='test') WHERE school_id = 99999;
■グループごとに連番を作成
カテゴリごとにAUTO_INCREMENTな値を登録するメモ ■InnoDBで対応する場合 「最大値+1」で連番を作成する（AUTO_INCREMENTを使わない） | PHPプログラミングの教科書 [php1st.com] https://php1st.com/806 MySQLメモ： INSERT時に最大値＋1を使いたいけど、auto_incrementは使いたくない - Qiita https://qiita.com/EmikoKishi/items/7f05ef6289a85b525c55
CREATE TABLE test ( category VARCHAR(5), id INT, text VARCHAR(10), PRIMARY KEY (category, id) ) ENGINE=InnoDB; INSERT INTO test SELECT 'A', LAST_INSERT_ID(IFNULL(MAX(id), 0) + 1), 'TEST' FROM test WHERE category = 'A'; INSERT INTO test SELECT 'A', LAST_INSERT_ID(IFNULL(MAX(id), 0) + 1), 'TEST' FROM test WHERE category = 'A'; INSERT INTO test SELECT 'B', LAST_INSERT_ID(IFNULL(MAX(id), 0) + 1), 'TEST' FROM test WHERE category = 'B'; INSERT INTO test SELECT 'C', LAST_INSERT_ID(IFNULL(MAX(id), 0) + 1), 'TEST' FROM test WHERE category = 'C'; INSERT INTO test SELECT 'B', LAST_INSERT_ID(IFNULL(MAX(id), 0) + 1), 'TEST' FROM test WHERE category = 'B'; INSERT INTO test SELECT 'B', LAST_INSERT_ID(IFNULL(MAX(id), 0) + 1), 'TEST' FROM test WHERE category = 'B'; SELECT * FROM test; +----------+----+------+ | category | id | text | +----------+----+------+ | A | 1 | TEST | | A | 2 | TEST | | B | 1 | TEST | | B | 2 | TEST | | B | 3 | TEST | | C | 1 | TEST | +----------+----+------+
ただし MAX(id) で値を取得した直後にデータが挿入された場合に正しく連番が作られない …のような問題が無いかどうかは要検証 ロック、トランザクション、トランザクション分離レベルなどを気にしながら処理する必要があるかもしれない なおデータの挿入は、以下のように記述してもいい
INSERT INTO test (category, id, text) SELECT 'A', LAST_INSERT_ID(IFNULL(MAX(id), 0) + 1), 'TEST' FROM test WHERE category = 'A';
以下だとうまく行かなかった（0から開始されてしまう）
INSERT INTO test (category, id, text) SELECT 'A', MAX(id) + 1, 'TEST' FROM test WHERE category = 'A';
■MyISAMで対応する場合 [MySQL]親ごとに枝番をAUTO_INCREMENT @餅。 http://doshiroutonike.com/web/other-web/3740 以下のようにすると、categoryごとにidの連番を作ることができる ただしInnoDBではなくMyISAMにする必要があり、トランザクションが使えない問題があるので注意
CREATE TABLE test ( category VARCHAR(5), id INT AUTO_INCREMENT, text VARCHAR(10), PRIMARY KEY (category, id) ) ENGINE=MyISAM; INSERT INTO test VALUES('A', NULL, 'TEST'); INSERT INTO test VALUES('A', NULL, 'TEST'); INSERT INTO test VALUES('B', NULL, 'TEST'); INSERT INTO test VALUES('C', NULL, 'TEST'); INSERT INTO test VALUES('B', NULL, 'TEST'); INSERT INTO test VALUES('B', NULL, 'TEST'); SELECT * FROM test; +----------+----+------+ | category | id | text | +----------+----+------+ | A | 1 | TEST | | A | 2 | TEST | | B | 1 | TEST | | C | 1 | TEST | | B | 2 | TEST | | B | 3 | TEST | +----------+----+------+
■MyISAMで対応する場合: 検証メモ 以下は実際に検証した内容 前提として、MySQL5.5移行はデフォルトでInnoDBとなっている よって「ENGINE=InnoDB」を省略してもInnoDB扱いになる 以下のコードでは、そもそもテーブルを作成できない
CREATE TABLE `test1` ( category VARCHAR(5), id INT AUTO_INCREMENT, text VARCHAR(10), PRIMARY KEY (category, id) ) ENGINE=InnoDB; ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
以下のコードなら作成できるが、idが項目ごとの連番にならない
CREATE TABLE `test2` ( category VARCHAR(5), id INT AUTO_INCREMENT, text VARCHAR(10), PRIMARY KEY (id, category) ) ENGINE=InnoDB; INSERT INTO test2 VALUES('A', NULL, 'TEST'); INSERT INTO test2 VALUES('A', NULL, 'TEST'); INSERT INTO test2 VALUES('B', NULL, 'TEST'); INSERT INTO test2 VALUES('C', NULL, 'TEST'); INSERT INTO test2 VALUES('B', NULL, 'TEST'); INSERT INTO test2 VALUES('B', NULL, 'TEST'); SELECT * FROM test2; +----------+----+------+ | category | id | text | +----------+----+------+ | A | 1 | TEST | | A | 2 | TEST | | B | 3 | TEST | | C | 4 | TEST | | B | 5 | TEST | | B | 6 | TEST | +----------+----+------+
PRIMARY KEYの指定順が違っているので、やはりidが項目ごとの連番にならない
CREATE TABLE `test3` ( category VARCHAR(5), id INT AUTO_INCREMENT, text VARCHAR(10), PRIMARY KEY (id, category) ) ENGINE=MyISAM; INSERT INTO test3 VALUES('A', NULL, 'TEST'); INSERT INTO test3 VALUES('A', NULL, 'TEST'); INSERT INTO test3 VALUES('B', NULL, 'TEST'); INSERT INTO test3 VALUES('C', NULL, 'TEST'); INSERT INTO test3 VALUES('B', NULL, 'TEST'); INSERT INTO test3 VALUES('B', NULL, 'TEST'); SELECT * FROM test3; +----------+----+------+ | category | id | text | +----------+----+------+ | A | 1 | TEST | | A | 2 | TEST | | B | 3 | TEST | | C | 4 | TEST | | B | 5 | TEST | | B | 6 | TEST | +----------+----+------+
以下ならOK
CREATE TABLE `test4` ( category VARCHAR(5), id INT AUTO_INCREMENT, text VARCHAR(10), PRIMARY KEY (category, id) ) ENGINE=MyISAM; INSERT INTO test4 VALUES('A', NULL, 'TEST'); INSERT INTO test4 VALUES('A', NULL, 'TEST'); INSERT INTO test4 VALUES('B', NULL, 'TEST'); INSERT INTO test4 VALUES('C', NULL, 'TEST'); INSERT INTO test4 VALUES('B', NULL, 'TEST'); INSERT INTO test4 VALUES('B', NULL, 'TEST'); SELECT * FROM test4; +----------+----+------+ | category | id | text | +----------+----+------+ | A | 1 | TEST | | A | 2 | TEST | | B | 1 | TEST | | C | 1 | TEST | | B | 2 | TEST | | B | 3 | TEST | +----------+----+------+
■表示での対応 このファイル内の「データ取得 > グループ」を参照
■データ削除
■TRUNCATEで削除 一例だが以下のようにすると、データをすべて削除できる DELETEと違い、オートインクリメントもリセットされる
TRUNCATE TABLE users;
全データの削除(TRUNCATE文) - データの追加と削除 - MySQLの使い方 https://www.dbonline.jp/mysql/insert/index12.html ■一定期間経過したデータを削除 一例だが以下のようにすると、一定期間経過したデータをそれぞれ削除できる
# 30秒 DELETE FROM histories WHERE (created < DATE_SUB(CURDATE(), INTERVAL 30 SECOND)); # 30分 DELETE FROM histories WHERE (created < DATE_SUB(CURDATE(), INTERVAL 30 MINUTE)); # 12時間 DELETE FROM histories WHERE (created < DATE_SUB(CURDATE(), INTERVAL 12 HOUR)); # 1日 DELETE FROM histories WHERE (created < DATE_SUB(CURDATE(), INTERVAL 1 DAY)); # 1週間 DELETE FROM histories WHERE (created < DATE_SUB(CURDATE(), INTERVAL 1 WEEK)); # 10日 DELETE FROM histories WHERE (created < DATE_SUB(CURDATE(), INTERVAL 10 DAY)); # 1ヶ月 DELETE FROM histories WHERE (created < DATE_SUB(CURDATE(), INTERVAL 1 MONTH)); # 半年 DELETE FROM histories WHERE (created < DATE_SUB(CURDATE(), INTERVAL 6 MONTH));
以下のページで、Cronと合わせて自動削除する例が紹介されている 【MySQL】一定期間、特定の日付より以前のレコードを削除する cron設定有 https://web-creators-hub.com/%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9/mysql_date_del/
■変数を扱う
MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.7.6.1 変数代入の SET 構文 https://dev.mysql.com/doc/refman/8.0/ja/set-variable.html MySQL | SET文でシステム変数を変更(セッション変数, グロバール変数) - わくわくBank https://www.wakuwakubank.com/posts/413-mysql-set/ 以下のようにして、変数に値を代入＆使用できる
SET @name = 'yamada'; SELECT id FROM users WHERE name = @name;
変数は接続を終了するまで有効なので、以下のように連続して実行する際にも使用できる
SET @target = 5; UPDATE logs SET message = 'テスト' WHERE id = @target; UPDATE logs SET message = 'テスト' WHERE id = (@target + 1); UPDATE logs SET message = 'テスト' WHERE id = (@target + 2);
以下のようにして変数の内容を確認できる なおQUIT（EXIT）で接続を終了し、再度変数の内容を確認するとNULLに戻る
mysql> SELECT @target; +---------+ | @target | +---------+ | NULL | +---------+ 1 row in set (0.00 sec) mysql> SET @target = 5; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @target; +---------+ | @target | +---------+ | 5 | +---------+ 1 row in set (0.00 sec)
■少数を管理する
0.00〜100.00 までの値を管理する場合、合計5桁で小数部分が2桁なので、以下のように指定する -100.00〜100.00 までの値を管理する場合も、同様の定義で格納できる（マイナスがついていても格納できる）
rate DECIMAL(5, 2) NOT NULL COMMENT '受注率',
※MySQL バージョン 3.23 より前のバージョンでは引数に、符号と小数点に必要なスペースを含める必要があったらしい 【MySQL】データ型一覧 - ysklog http://ysklog.net/mysql/1797.html 数値型(データ型)のまとめ - [MySQL/SQL] ぺんたん info http://pentan.info/sql/mysql/mysql_type_num.html
■真偽値を管理する
MySQLに boolean の型は無いので、以下のようにして対応する
verified TINYINT(1) UNSIGNED NOT NULL COMMENT 'データ確認',
■日時を扱う
CURRENT_DATE() で日付を、CURRENT_TIME() で時間を、NOW() で日時をそれぞれ取得できる 時間の計算を行う際、CURRENT_DATE() を使用すると0時からの計算になるので注意
MySQL [recole]> SELECT CURRENT_DATE(); +----------------+ | CURRENT_DATE() | +----------------+ | 2022-11-08 | +----------------+ 1 row in set (0.00 sec) MySQL [recole]> SELECT CURRENT_TIME(); +----------------+ | CURRENT_TIME() | +----------------+ | 12:59:14 | +----------------+ 1 row in set (0.00 sec) MySQL [recole]> SELECT NOW(); +---------------------+ | NOW() | +---------------------+ | 2022-11-08 12:59:14 | +---------------------+ 1 row in set (0.00 sec) MySQL [recole]> SELECT CURRENT_DATE(), DATE_ADD(CURRENT_DATE(), INTERVAL 13 HOUR); +----------------+--------------------------------------------+ | CURRENT_DATE() | DATE_ADD(CURRENT_DATE(), INTERVAL 13 HOUR) | +----------------+--------------------------------------------+ | 2022-11-08 | 2022-11-08 13:00:00 | +----------------+--------------------------------------------+ 1 row in set (0.00 sec) MySQL [recole]> SELECT NOW(), DATE_ADD(NOW(), INTERVAL 13 HOUR); +---------------------+-----------------------------------+ | NOW() | DATE_ADD(NOW(), INTERVAL 13 HOUR) | +---------------------+-----------------------------------+ | 2022-11-08 12:59:14 | 2022-11-09 01:59:14 | +---------------------+-----------------------------------+ 1 row in set (0.00 sec)
■暗黙の型変換
code が 528 と 528A のデータがあった場合、以下のように検索すると両方ヒットしてしまうので注意
SELECT * FROM products WHERE code = 528;
以下のように型を明示的にして検索する必要がある
SELECT * FROM products WHERE code = '528';
PHP+PDOの場合、以下のようにして型を明示できる
PDO::PARAM_STR PDO::PARAM_INT PDO::PARAM_BOOL
MySQLが文字列と数値の比較の際に自動的に変換してしまう件 | 創作メモ帳 https://sousaku-memo.net/php-system/123 SQLの暗黙の型変換はワナがいっぱい | 徳丸浩の日記 https://blog.tokumaru.org/2009/09/implicit-type-conversion-of-SQL-is-trap-full.html
■ウインドウ関数
MySQL の Window 関数を完全に理解する - Qiita https://qiita.com/ngyuki/items/c35dd314815939ffbeb6 以下、MariaDB 10.4.8 で検証 ■簡単な例
CREATE TABLE users( uid INT NOT NULL PRIMARY KEY AUTO_INCREMENT, gid INT NOT NULL, value INT NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT 'ユーザ'; INSERT INTO users VALUES( 1, 100, 1); INSERT INTO users VALUES( 2, 200, 2); INSERT INTO users VALUES( 3, 100, 10); INSERT INTO users VALUES( 4, 200, 20); INSERT INTO users VALUES( 5, 100, 100); INSERT INTO users VALUES( 6, 200, 200); INSERT INTO users VALUES( 7, 100, 1000); INSERT INTO users VALUES( 8, 200, 2000); INSERT INTO users VALUES( 9, 100, 10000); INSERT INTO users VALUES(10, 200, 20000);
GROUP BY で gid ごとの合計を表示した場合
SELECT gid, SUM(value) AS total FROM users GROUP BY gid ; /* +-----+-------+ | gid | total | +-----+-------+ | 100 | 11111 | | 200 | 22222 | +-----+-------+ */
以下はWindow関数を使用した例 「PARTITION BY gid」によって、行の範囲を指定している
SELECT uid, gid, value, SUM(value) OVER (PARTITION BY gid) AS total FROM users ; /* +-----+-----+-------+-------+ | uid | gid | value | total | +-----+-----+-------+-------+ | 7 | 100 | 1000 | 11111 | | 1 | 100 | 1 | 11111 | | 9 | 100 | 10000 | 11111 | | 3 | 100 | 10 | 11111 | | 5 | 100 | 100 | 11111 | | 8 | 200 | 2000 | 22222 | | 2 | 200 | 2 | 22222 | | 10 | 200 | 20000 | 22222 | | 4 | 200 | 20 | 22222 | | 6 | 200 | 200 | 22222 | +-----+-----+-------+-------+ */
行の範囲をしていなければ、全体を対象に処理される
SELECT uid, gid, value, SUM(value) OVER () AS total FROM users ; /* +-----+-----+-------+-------+ | uid | gid | value | total | +-----+-----+-------+-------+ | 1 | 100 | 1 | 33333 | | 2 | 200 | 2 | 33333 | | 3 | 100 | 10 | 33333 | | 4 | 200 | 20 | 33333 | | 5 | 100 | 100 | 33333 | | 6 | 200 | 200 | 33333 | | 7 | 100 | 1000 | 33333 | | 8 | 200 | 2000 | 33333 | | 9 | 100 | 10000 | 33333 | | 10 | 200 | 20000 | 33333 | +-----+-----+-------+-------+ */
以下はウインドウを明示的に定義して使う例（定義しなければ暗黙的に定義されたことになる） Oracleでは明示的に定義することができないので、暗黙的に定義される方法の方が汎用性が高い
SELECT uid, gid, value, SUM(value) OVER w AS total FROM users WINDOW w AS (PARTITION BY gid) ; /* +-----+-----+-------+-------+ | uid | gid | value | total | +-----+-----+-------+-------+ | 7 | 100 | 1000 | 11111 | | 1 | 100 | 1 | 11111 | | 9 | 100 | 10000 | 11111 | | 3 | 100 | 10 | 11111 | | 5 | 100 | 100 | 11111 | | 8 | 200 | 2000 | 22222 | | 2 | 200 | 2 | 22222 | | 10 | 200 | 20000 | 22222 | | 4 | 200 | 20 | 22222 | | 6 | 200 | 200 | 22222 | +-----+-----+-------+-------+ */
OVER 句には ORDER BY も指定できる ORDER BY は Window 関数がパーティションの中で行を処理する順番を指定できる
SELECT uid, gid, value, ROW_NUMBER() OVER (PARTITION BY gid ORDER BY value) AS num FROM users ; /* +-----+-----+-------+-----+ | uid | gid | value | num | +-----+-----+-------+-----+ | 1 | 100 | 1 | 1 | | 3 | 100 | 10 | 2 | | 5 | 100 | 100 | 3 | | 7 | 100 | 1000 | 4 | | 9 | 100 | 10000 | 5 | | 2 | 200 | 2 | 1 | | 4 | 200 | 20 | 2 | | 6 | 200 | 200 | 3 | | 8 | 200 | 2000 | 4 | | 10 | 200 | 20000 | 5 | +-----+-----+-------+-----+ */
■フレームの概念 前述の ORDER BY を SUM で使用すると次のような結果になる gid で分割されたパーティションの中を value の昇順に並べ、パーティションの先頭から現在行までが順に集計される
SELECT uid, gid, value, SUM(value) OVER (PARTITION BY gid ORDER BY value) AS total FROM users ; /* +-----+-----+-------+-------+ | uid | gid | value | total | +-----+-----+-------+-------+ | 1 | 100 | 1 | 1 | | 3 | 100 | 10 | 11 | | 5 | 100 | 100 | 111 | | 7 | 100 | 1000 | 1111 | | 9 | 100 | 10000 | 11111 | | 2 | 200 | 2 | 2 | | 4 | 200 | 20 | 22 | | 6 | 200 | 200 | 222 | | 8 | 200 | 2000 | 2222 | | 10 | 200 | 20000 | 22222 | +-----+-----+-------+-------+ */
この「パーティションの先頭から現在行まで」のような範囲のことを「フレーム」と呼ぶ フレームの範囲も OVER 句で指定できる。次の「ROWS 〜」の部分がフレームの指定 フレームの終了位置はデフォルトで現在行なので、ROWS CURRENT ROW だけ指定すると「現在行〜現在行」となる
SELECT uid, gid, value, SUM(value) OVER (PARTITION BY gid ORDER BY value) AS total, SUM(value) OVER (PARTITION BY gid ORDER BY value ROWS CURRENT ROW) AS 'CURRENT ROW', SUM(value) OVER (PARTITION BY gid ORDER BY value ROWS UNBOUNDED PRECEDING) AS 'UNBOUNDED PRECEDING', SUM(value) OVER (PARTITION BY gid ORDER BY value ROWS 1 PRECEDING) AS '1 PRECEDING' FROM users ; /* +-----+-----+-------+-------+-------------+---------------------+-------------+ | uid | gid | value | total | CURRENT ROW | UNBOUNDED PRECEDING | 1 PRECEDING | +-----+-----+-------+-------+-------------+---------------------+-------------+ | 1 | 100 | 1 | 1 | 1 | 1 | 1 | | 3 | 100 | 10 | 11 | 10 | 11 | 11 | | 5 | 100 | 100 | 111 | 100 | 111 | 110 | | 7 | 100 | 1000 | 1111 | 1000 | 1111 | 1100 | | 9 | 100 | 10000 | 11111 | 10000 | 11111 | 11000 | | 2 | 200 | 2 | 2 | 2 | 2 | 2 | | 4 | 200 | 20 | 22 | 20 | 22 | 22 | | 6 | 200 | 200 | 222 | 200 | 222 | 220 | | 8 | 200 | 2000 | 2222 | 2000 | 2222 | 2200 | | 10 | 200 | 20000 | 22222 | 20000 | 22222 | 22000 | +-----+-----+-------+-------+-------------+---------------------+-------------+ */
■ウインドウ関数の利用例（移動平均） ウインドウ関数の典型的な利用ケースである、移動平均を求めてみる 移動平均を使えば、長期的なデータの変化を可視化することができる 算術平均 ・集合のすべての値を足して、その集合の要素数で割った値 ・その集合が、どのようなものかを表すことができる（例：テストの平均点） 移動平均 ・直近で取得したデータポイント群で平均を計算した値 ・デコボコの多いグラフをなめらかにして、傾向を把握しやすくすることができる MySQL の Window 関数を完全に理解する - Qiita https://qiita.com/ngyuki/items/c35dd314815939ffbeb6 SQLのWindow関数で移動平均を求める - Qiita https://qiita.com/omokawa_yasu/items/abb8624fc57a9f4c427c 【小ネタ】Window関数で移動平均を算出する【SQL】 - Qiita https://qiita.com/tmiki/items/739457f5df27a306a36b 32-3. 時系列データと移動平均 | 統計学の時間 | 統計WEB https://bellcurve.jp/statistics/course/12933.html 移動平均とは?活用するメリットやエクセルでの計算方法を紹介!｜いちばんやさしい、医療統計 https://best-biostatistics.com/summary/idou-heikinn.html [SQL] 移動平均を算出 〜Window関数を使って変動傾向を掴む〜 | DevelopersIO https://dev.classmethod.jp/articles/sql-moving-average/
CREATE TABLE profits( day INT NOT NULL PRIMARY KEY AUTO_INCREMENT, value INT NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '日ごとの売上'; INSERT INTO profits VALUES( 1, 57); INSERT INTO profits VALUES( 2, 47); INSERT INTO profits VALUES( 3, 122); INSERT INTO profits VALUES( 4, 104); INSERT INTO profits VALUES( 5, 34); INSERT INTO profits VALUES( 6, 65); INSERT INTO profits VALUES( 7, 38); INSERT INTO profits VALUES( 8, 39); INSERT INTO profits VALUES( 9, 43); INSERT INTO profits VALUES(10, 102); INSERT INTO profits VALUES(11, 87); INSERT INTO profits VALUES(12, 58); INSERT INTO profits VALUES(13, 43); INSERT INTO profits VALUES(14, 71); INSERT INTO profits VALUES(15, 45); INSERT INTO profits VALUES(16, 43); INSERT INTO profits VALUES(17, 142); INSERT INTO profits VALUES(18, 101); INSERT INTO profits VALUES(19, 58); INSERT INTO profits VALUES(20, 67); INSERT INTO profits VALUES(21, 48); INSERT INTO profits VALUES(22, 65); INSERT INTO profits VALUES(23, 75); INSERT INTO profits VALUES(24, 123); INSERT INTO profits VALUES(25, 134); INSERT INTO profits VALUES(26, 67); INSERT INTO profits VALUES(27, 63); INSERT INTO profits VALUES(28, 63); INSERT INTO profits VALUES(29, 76); INSERT INTO profits VALUES(30, 48); SELECT day, value, AVG(value) OVER ( ORDER BY day ROWS BETWEEN 4 PRECEDING AND CURRENT ROW ) AS `Moving AVG` FROM profits ; /* +-----+-------+------------+ | day | value | Moving AVG | +-----+-------+------------+ | 1 | 57 | 57.0000 | | 2 | 47 | 52.0000 | | 3 | 122 | 75.3333 | | 4 | 104 | 82.5000 | | 5 | 34 | 72.8000 | | 6 | 65 | 74.4000 | | 7 | 38 | 72.6000 | | 8 | 39 | 56.0000 | | 9 | 43 | 43.8000 | | 10 | 102 | 57.4000 | | 11 | 87 | 61.8000 | | 12 | 58 | 65.8000 | | 13 | 43 | 66.6000 | | 14 | 71 | 72.2000 | | 15 | 45 | 60.8000 | | 16 | 43 | 52.0000 | | 17 | 142 | 68.8000 | | 18 | 101 | 80.4000 | | 19 | 58 | 77.8000 | | 20 | 67 | 82.2000 | | 21 | 48 | 83.2000 | | 22 | 65 | 67.8000 | | 23 | 75 | 62.6000 | | 24 | 123 | 75.6000 | | 25 | 134 | 89.0000 | | 26 | 67 | 92.8000 | | 27 | 63 | 92.4000 | | 28 | 63 | 90.0000 | | 29 | 76 | 80.6000 | | 30 | 48 | 63.4000 | +-----+-------+------------+ */
例えば7dayの移動平均である「72.6000」は、以下のように求められている つまり、現在の行（CURRENT ROW）とその前の4行（4 PRECEDING）の計5行から平均化した値を算出し、傾向を把握しやすくしている (122 + 104 + 34 + 65 + 38) / 5 = 72.6
■トランザクション
■トランザクション ・BEGIN または START TRANSACTION でトランザクションを開始する（意味はどちらも同じ） ・COMMIT でトランザクションを終了し、変更は永続化される ・ROLLBACK でトランザクションを終了し、変更は取り消される ■トランザクション分離レベル MySQLのInnoDBのトランザクション分離レベルは、デフォルトで REPEATABLE READ となっている REPEATABLE READ はトランザクション開始後にテーブルの値を変更しても、SELECT で参照できるのは変更前の値 これにより、例えば予約システムなどで 1. トランザクションを開始する 2. 現在の予約数を取得する 3. 取得した予約数が一定件数以下なら予約データを登録する。一定件数以上ならエラーにする という処理を書いたとき、2で取得できるデータは1時点のもの つまり3に到達した時点で予約数はさらに増えている可能性がある つまり「定員が100人のイベントに105人予約されてしまった」という現象が起こりうる トランザクション分離レベルを「READ COMMITTED」にしておけば、操作もとでコミットされた時点で他端末からも変更後の値を参照できるようになり、Oracle、PostgreSQL、SQL Server などではこれがデフォルト設定となっている そちらの方が直感的なので、変更しておくと余計なトラブルを防ぐことができる（常に変更して良いものかは要検討） 以下のSQLを発行することで、その接続だけトランザクション分離レベルを変更できる
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
MySQLでトランザクションの4つの分離レベルを試す - FAT47の底辺インフラ議事録 http://d.hatena.ne.jp/fat47/20140212/1392171784 MySQLのデフォルトのトランザクション分離レベルは SELECT がスナップショットを参照する - ngyukiの日記 http://ngyuki.hatenablog.com/entry/2013/02/02/202558 [RDBMS][SQL]トランザクション分離レベルについて極力分かりやすく解説 - Qiita https://qiita.com/PruneMazui/items/4135fcf7621869726b4b InnoDBにおけるトランザクション分離レベルについて - Qiita https://qiita.com/taisho6339/items/1097c67a54cedf9504d6 現在のトランザクション分離レベルは、以下のSQLで確認できる
SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
第47回　トランザクション分離レベルを変更する：MySQL道普請便り｜gihyo.jp … 技術評論社 https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0047 MySQLのautocommitとトランザクション分離レベルのメモ - Qiita https://qiita.com/rubytomato@github/items/562a1638191aacaeb333 MySQLがこのような挙動になっているのは、バイナリログの整合性を担保するための名残らしい MySQL InnoDBのネクストキーロック おさらい - SH2の日記 http://d.hatena.ne.jp/sh2/20090112 以下、トランザクション分離レベルの挙動を検証したときのメモ 2つの端末からアクセスするので、それぞれ「mysql1>」「mysql2>」と表記する ■REPEATABLE READ のまま（デフォルト）
mysql1> BEGIN; mysql2> BEGIN; mysql1> SELECT * FROM test; mysql1> UPDATE test SET text = 'TEST2 UPDATED!' WHERE id = 2; mysql1> SELECT * FROM test; … 更新を確認できる mysql2> SELECT * FROM test; … 他端末からは更新を確認できない mysql1> COMMIT; mysql2> SELECT * FROM test; … 他端末からは更新を確認できない mysql2> COMMIT; mysql2> SELECT * FROM test; … 他端末からも更新を確認できる
■READ COMMITTED に変更
mysql1> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; mysql1> BEGIN; mysql2> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; mysql2> BEGIN; mysql1> SELECT * FROM test; mysql1> UPDATE test SET text = 'TEST2 UPDATED!' WHERE id = 2; mysql1> SELECT * FROM test; … 更新を確認できる mysql2> SELECT * FROM test; … 他端末からは更新を確認できない mysql1> COMMIT; mysql2> SELECT * FROM test; … 操作もとでコミットされた時点で、他端末からも更新を確認できる
■ロールバック 【忘備録】InnoDBでもロールバックが効かない文 http://mementomori.info/%E3%80%90%E5%BF%98%E5%82%99%E9%8C%B2%E3%80%91innodb%E3%81%A7%E3%82%82%E3%83%... MySQLでTruncateはRollback出来るのか？ | SRIA BLOG - 宮城県仙台市のWEBシステム開発・スマホアプリ開発 https://www.sria.co.jp/blog/2014/08/mysql-can-do-rollback-truncate/ MySQLの「暗黙のトランザクションコミット」対策：トランザクション中でも安全にCREATE TABLEなどをする方法 - Qiita https://qiita.com/suin/items/3527297a22632f3db31d MySQL :: MySQL 5.6 リファレンスマニュアル :: 13.3.3 暗黙的なコミットを発生させるステートメント https://dev.mysql.com/doc/refman/5.6/ja/implicit-commit.html
■ロックによる排他制御
MySQLのプロセス確認と強制終了（デッドロック解除）の方法は、サーバメモの Command.txt の「MySQLでプロセスを確認＆強制終了」を参照 ロックについては、このファイル内の「トランザクション」についても参照（トランザクション分離レベルについては注意が必要） 以下の記事は以前に試したときのメモ FOR UPDATE による行ロックの具体例がある （この内容以外にも、トランザクション分離レベルについては注意が必要。またボタンを連打されなように非活性化するなどの処理も入れておくべき） Webアプリケーションへの同時アクセス対策メモ | refirio.org http://refirio.org/view/367 CREATE TABLE や TRUNCATE TABLE を実行した場合は、暗黙的にコミットされてロックが外れる（テンポラリテーブルの場合は外れない） …など例外的な挙動もあるようなので注意する。要勉強 デッドロックが発生した場合、「SHOW ENGINE INNODB STATUS;」で詳細を確認できるみたい なぜあなたは SHOW ENGINE INNODB STATUS を読まないのか - そーだいなるらくがき帳 https://soudai.hatenablog.com/entry/2017/12/20/030013 以下、ロックの挙動を検証したときのメモ 主に以下のページを参考にしている DBのロックについてあまり意識したことがない人に向けた実は覚えておきたいロックについての知識 - CARTA TECH BLOG https://techblog.cartaholdings.co.jp/entry/2022/12/14/113000 デッドロックについては、後述の「デッドロックの具体例」も参照 ■前提 以下のテーブルを作成し、データを登録しているものとする （トランザクション分離レベルは、MySQLデフォルト設定の REPEATABLE READ としている）
CREATE TABLE test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, text VARCHAR(80), PRIMARY KEY(id) ) ENGINE=InnoDB; INSERT INTO test VALUES(1, 'TEST1'); INSERT INTO test VALUES(2, 'TEST2'); INSERT INTO test VALUES(3, 'TEST3'); INSERT INTO test VALUES(4, 'TEST4'); INSERT INTO test VALUES(5, 'TEST5'); 以下のとおり、データが登録されていることを確認できる > SELECT * FROM test; +----+-------+ | id | text | +----+-------+ | 1 | TEST1 | | 2 | TEST2 | | 3 | TEST3 | | 4 | TEST4 | | 5 | TEST5 | +----+-------+
以下で値の編集を確認できる 複数端末から実行しても編集できる
> SELECT * FROM test; > UPDATE test SET text = 'TEST2 UPDATED!!' WHERE id = 2; > SELECT * FROM test;
この前提で、トランザクション・共有ロック・排他ロックについて確認する 2つの端末からアクセスするので、それぞれ「mysql1>」「mysql2>」と表記する ■トランザクションの確認
mysql1> BEGIN; mysql2> BEGIN; mysql1> SELECT * FROM test; mysql1> UPDATE test SET text = 'TEST2 UPDATED!' WHERE id = 2; mysql1> SELECT * FROM test; … 更新を確認できる mysql2> SELECT * FROM test; … 他端末からは更新を確認できない mysql1> COMMIT; mysql2> SELECT * FROM test; … 他端末からは更新を確認できない mysql2> COMMIT; mysql2> SELECT * FROM test; … 他端末からも更新を確認できる
■共有ロックの確認（LOCK IN SHARE MODE） トランザクションが終了されるまで共有ロックを継続して取得し続ける 別のトランザクションからの読み取りを許可するが、書き込みは許可しない状態になる
mysql1> BEGIN; mysql2> BEGIN; mysql1> SELECT * FROM test WHERE id = 2 LOCK IN SHARE MODE; mysql1> UPDATE test SET text = 'TEST2 UPDATED!' WHERE id = 2; mysql2> SELECT * FROM test WHERE id = 2; … 他端末からは更新を確認できない mysql2> SELECT * FROM test WHERE id = 2 LOCK IN SHARE MODE; … 他端末からは共有ロックがブロックされる mysql2> UPDATE test SET text = 'TEST2 UPDATED!' WHERE id = 2; … 他端末からは更新もブロックされる mysql1> COMMIT; mysql2> COMMIT; mysql2> SELECT * FROM test WHERE id = 2; … コミットされたので、他端末からも更新を確認できる
■排他ロックの確認（FOR UPDATE） トランザクションが終了されるまで排他ロックを継続して取得し続ける 別のトランザクションからの読み取り＆書き込みの両方を許可しない状態になる ※ただしInnoDBでトランザクション分離レベルが REPEATABLE READ の場合、操作もとでコミットされると読み取りできる REPEATABLE READ はトランザクション開始後にテーブルの値を変更しても、SELECT で参照できるのは変更前の値なので、実質変更途中のデータを取得されることが無いためだと思われる
mysql1> BEGIN; mysql2> BEGIN; mysql1> SELECT * FROM test WHERE id = 2 FOR UPDATE; mysql1> UPDATE test SET text = 'TEST2 UPDATED!' WHERE id = 2; mysql2> SELECT * FROM test WHERE id = 2; … 他端末からは更新を確認できない（参照自体はできる） mysql2> SELECT * FROM test WHERE id = 2 FOR UPDATE; … 他端末からは排他ロックがブロックされる mysql2> UPDATE test SET text = 'TEST2 UPDATED!' WHERE id = 2; … 他端末からは更新できない mysql1> COMMIT; mysql2> COMMIT; mysql2> UPDATE test SET text = 'TEST2 UPDATED!!' WHERE id = 2; … コミットされたので、他端末からも更新できる
■排他ロックの確認（READ COMMITTED + FOR UPDATE） トランザクション分離レベルを READ COMMITTED にし、更新の前には FOR UPDATE で排他ロックをかける方法 予約処理の定員チェックなど、厳密性を求められる場面では基本的にこの処理が良さそう
mysql1> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; mysql1> BEGIN; mysql2> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; mysql2> BEGIN; mysql1> SELECT * FROM test WHERE id = 2 FOR UPDATE; mysql1> UPDATE test SET text = 'TEST2 UPDATED!' WHERE id = 2; mysql2> SELECT * FROM test WHERE id = 2; … 他端末からは更新を確認できない（参照自体はできる） mysql2> SELECT * FROM test WHERE id = 2 FOR UPDATE; … 他端末からはロックできない mysql2> UPDATE test SET text = 'TEST2 UPDATED!' WHERE id = 2; … 他端末からは更新できない mysql1> COMMIT; mysql2> SELECT * FROM test WHERE id = 2; … 操作もとでコミットされた時点で、他端末からも更新を確認できる mysql2> UPDATE test SET text = 'TEST2 UPDATED!!' WHERE id = 2; … 操作もとでコミットされた時点で、他端末からも更新できる
■引き続きの勉強中メモ ・トランザクション内でロックしたら、コミットしてから次の処理が進む ロックは、定員オーバーチェックの最初だけ行うといい。同じ処理の中にfor updateが2回あると問題。1回だとデッドロックは理論的に起こらない。はず 予約者をINSERTしていくような行が増えるテーブルはロックできない ・ダミーテーブルをロック、という手法は無いか 「予約者をINSERTしていくような行が増えるテーブルはロックできない」があるので、適当なテーブルで行うべきでは無いか データが1行で「ロックしているか否か」の値を持つだけのテーブルをロックするのなら有効か ・「ダミーテーブルをロック」が有効なら、ダミーファイルを配置しておいてファイルロックをかけ、それによって排他制御を行うのは有効か サーバが複数台構成の場合は使えないので、かえってややこしくなるだけか ・ミドルウェアの設定で「デッドロックを検知した瞬間にエラーで落ちる」とかできないか 以下などでは「スレッドを特定して強制終了」となっているので、そのような設定は無さそうだが MySQLでロックを特定し、強制終了する - Qiita https://qiita.com/RyutaKojima/items/a76f4cd4c94d1989a4a5 【障害対応】MySQLでデットロックが発生した時の対応方法 - 気ままに https://unot13.hatenablog.com/entry/2018/08/07/174951 MySQLでロックを特定、強制終了してみた｜SHIFT Group 技術ブログ｜note https://note.com/shift_tech/n/n808984951f92 以下は参考になりそうなサイト 嵐のコンサートがあるとダブルブッキングしてしまうホテル予約システムを作ってみた | 徳丸浩の日記 https://blog.tokumaru.org/2015/05/blog-post.html MySQLのINSERT/UPDATE時におこる不整合対策 - Slow Dance http://d.hatena.ne.jp/LukeSilvia/20110123/p1 MySQL - InnoDBのロック関連まとめ - Qiita https://qiita.com/mizzwithliam/items/31fb68217899bd0559e8 MySQL テーブルのロック - とみぞーノート http://wiki.bit-hive.com/tomizoo/pg/MySQL%20%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E3%81%AE%E3%83%AD%E... デッドロックを回避するために - 技術ブログ | 株式会社クラウディア https://cloudear.jp/blog/?p=1335 MySQLでINSERTのデッドロックに嵌る人を1人でも減らすために - ichirin2501's diary https://ichirin2501.hatenablog.com/entry/2015/12/24/164916 doc/innodb.md at master - ichirin2501/doc https://github.com/ichirin2501/doc/blob/master/innodb.md MySQLでSELECT FOR UPDATEと行ロックの挙動を検証してみた - JUST FOR FUN http://taiga.hatenadiary.com/entry/2018/02/12/170109 MySQLのトランザクション処理中にALTERするとコミットされる - ペチパーノート http://butterbull.hatenablog.com/entry/2014/06/12/150756 ソシャゲエンジニアの自分が開発に必須だなと思った知識（MySQL編） - Qiita https://qiita.com/west-hiroaki/items/ea6ee53765282a9c86cb 第75回　MySQLのさまざまなタイムアウトオプションについて：MySQL道普請便り｜gihyo.jp … 技術評論社 https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0075 InnoDB の行レベルロックについて解説してみる - あらびき日記 https://abicky.net/2016/11/30/082130/ MySQL のデッドロックを調査した - エムティーアイ エンジニアリングブログ https://tech.mti.co.jp/entry/2017/12/27/190733 MySQL(InnoDB)の行ロック - フリエン生活 https://free-engineer.life/mysql-innodb-record-locks/ MySQL １つのテーブルでデッドロックさせる│システムガーディアン株式会社 https://sys-guard.com/post-15568/ データベースのロックの基礎からデッドロックまで https://zenn.dev/gibjapan/articles/1d8dfb7520dabc
■デッドロックの具体例
■共有ロックでの例 DBのロックについてあまり意識したことがない人に向けた実は覚えておきたいロックについての知識 - CARTA TECH BLOG https://techblog.cartaholdings.co.jp/entry/2022/12/14/113000
mysql1> BEGIN; mysql2> BEGIN; mysql1> SELECT * FROM test WHERE id = 2 LOCK IN SHARE MODE; mysql2> SELECT * FROM test WHERE id = 2 LOCK IN SHARE MODE; mysql1> DELETE FROM test WHERE id = 2; … 処理待ちが発生するが完了はできる Query OK, 1 row affected (7.33 sec) mysql2> DELETE FROM test WHERE id = 2; … デッドロック発生 ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction mysql2> DELETE FROM test WHERE id = 2; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql2> DELETE FROM test WHERE id = 2; ^CCtrl-C -- query killed. Continuing normally. ERROR 1317 (70100): Query execution was interrupted
何度か試していると、mysql1の方でデッドロックになることもあった ■排他ロックでの例 MySQL のデッドロックを調査した - エムティーアイ エンジニアリングブログ https://tech.mti.co.jp/entry/2017/12/27/190733
CREATE TABLE test2 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT UNSIGNED, value INT UNSIGNED, PRIMARY KEY(id, user_id) ) ENGINE=InnoDB; INSERT INTO test2 VALUES(1, 100, 50); INSERT INTO test2 VALUES(2, 100, 100); INSERT INTO test2 VALUES(3, 200, 10); INSERT INTO test2 VALUES(4, 100, 90); INSERT INTO test2 VALUES(5, 300, 70); INSERT INTO test2 VALUES(6, 200, 120); SELECT * FROM test2; +----+---------+-------+ | id | user_id | value | +----+---------+-------+ | 1 | 100 | 50 | | 2 | 100 | 100 | | 3 | 200 | 10 | | 4 | 100 | 90 | | 5 | 300 | 70 | | 6 | 200 | 120 | +----+---------+-------+
ここまで準備 引き続き以下を実行
mysql1> BEGIN; mysql2> BEGIN; mysql1> UPDATE test2 SET value = (value + 10) WHERE user_id = 500; … 条件に一致するものが無いので更新は発生しないが、トランザクション内で「一致するものが無い」ことを保証するために（他のトランザクションの影響を受けないように）ロックがかけられる mysql2> UPDATE test2 SET value = (value + 10) WHERE user_id = 600; … 処理待ちが発生。この時点でも条件に一致するものが無いのでロックがかけられる mysql1> INSERT INTO test2 (user_id, value) values (500, 50); mysql2> INSERT INTO test2 (user_id, value) values (600, 60); … デッドロックが発生する 以下のように強制終了させることで、改めてINSERTを実行することはできた # mysql -u root -p mysql> SHOW PROCESSLIST; +----+-----------+-----------+------+---------+------+--------+-----------------------------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +----+-----------+-----------+------+---------+------+--------+-----------------------------------------------------+----------+ | 51 | webmaster | localhost | test | Sleep | 196 | | NULL | 0.000 | | 52 | webmaster | localhost | test | Query | 2 | update | INSERT INTO test2 (user_id, value) values (600, 60) | 0.000 | | 53 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST | 0.000 | +----+-----------+-----------+------+---------+------+--------+-----------------------------------------------------+----------+ mysql> KILL 51; mysql> KILL 52;
解説ページも参考に、引き続き勉強中 前述の「ロックによる排他制御 > 引き続きの勉強中メモ」も参照
■一斉アクセスを再現してテストする
abツールやec2-benchを使うことで、一斉アクセスを再現してテストできる 詳細は、Tuning.txt の「Apache チューニング」「ec2-bench」を参照 また、例えばPHPならプログラム内に sleep(5) などを入れることで処理待ちをさせることができる そのうえで複数ブラウザから同時にアクセスさせるなどして、一斉アクセスを再現するという手もある abツールやec2-benchは「特定のページへの1リクエスト」によるテストになりがちなので、 後者の方がより運用中環境に近い状況を再現できる可能性はある
■監視
MySQLの監視 ~ mackerel-plugin-mysqlを読み解く - そーだいなるらくがき帳 https://soudai.hatenablog.com/entry/mackerel-plugin-mysql
■チューニング
漢(オトコ)のコンピュータ道: MySQLのEXPLAINを徹底解説!! http://nippondanji.blogspot.com/2009/03/mysqlexplain.html MySQL EXPLAINの見方 - Qiita https://qiita.com/Stuffy86/items/809540b73cacde951997 例えば以下のようなSQLがあったとして、
SELECT id, created, name FROM students LIMIT 100;
このSQLの直前に「EXPLAIN」を付けると実行計画を表示でき、この情報をもとにボトルネックを探してチューニングすることができる 一例だが、以下のように表示される
> EXPLAIN SELECT id, created, name FROM students LIMIT 100; +------+-------------+----------+------+---------------+------+---------+------+-------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------+------+---------------+------+---------+------+-------+-------+ | 1 | SIMPLE | students | ALL | NULL | NULL | NULL | NULL | 70288 | | +------+-------------+----------+------+---------------+------+---------+------+-------+-------+ > EXPLAIN SELECT id, created, name FROM students WHERE name = '山田太郎' LIMIT 100; +------+-------------+----------+------+----------------+----------------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------+------+----------------+----------------+---------+-------+------+-----------------------+ | 1 | SIMPLE | students | ref | index_name_sei | index_name_sei | 767 | const | 398 | Using index condition | +------+-------------+----------+------+----------------+----------------+---------+-------+------+-----------------------+
結果の意味は以下のとおり ひとまずは「select_type」と「type」を確認すると良さそう ■id 実行順序 select_typeとセットで確認する ■select_type SIMPLE … サブクエリやユニオンが含まれていない、単純なSELECT文 SUBQUERY … サブクエリに指定されているSELECT文 PRIMARY … ユニオンの1つ目のSELECT文 UNION … ユニオンの2つ目以降のSELECT文 UNION_RESULT … ユニオンの無名一時テーブルから結果を取得するSELECT文 ■table 対象のテーブル名 ■type テーブルへのアクセス方法 const … インデックスによる検索。最速 eq_ref … JOINで用いられる、インデックスによる検索 ref … ユニーク（PRIMARY or UNIQUE）ではないインデックスを使って等価検索（WHERE key = value）を行なった検索 range … インデックスを用いた範囲検索 index … フルインデックススキャン。インデックスがはられていること以外はALLと同じなので遅い ALL … フルテーブルスキャン。インデックスがまったく利用されていないので改善が必要。遅い ■possible_keys 検索に使用できるインデックス 「NULL」の場合は参照するインデックスが無い ■key 検索に使用されたインデックス ■key_len 検索に使用されたインデックスの長さ ■ref 検索条件でkeyと比較されている値やカラムの種類 定数が指定されている場合は「const」と表示される ■rows テーブルから取得される行数の見積もり 大まかな見積もりなので正確では無い ■Extra クエリを実行するために、どのような戦略を選択したかを示す 以下は一例 Using where … 頻繁に出力される追加情報。WHERE句に検索条件が指定されており、なおかつインデックスを見ただけではWHERE句の条件を全て適用することができない場合 Using index … クエリがインデックスだけを用いて解決できることを示す Using filesort … filesort（クイックソート）でソートを行っていることを示す Using temporary … JOINの結果をソートしたり、DISTINCTによる重複の排除を行う場合など、クエリの実行にテンポラリテーブルが必要なことを示す。 ■メモ 引き続き確認したい あなたの遅延はどこから？ SQLから！ 〜患部に止まってすぐ効くSQLレビューチェックリスト 年初め特大サービス号〜 - ANDPAD Tech Blog https://tech.andpad.co.jp/entry/2023/01/12/100000 MySQLのExplainを確認する - woshidan's blog https://woshidan.hatenablog.com/entry/2015/06/20/165817 MySQLのexplainとかについてしらべたときのメモ - Qiita https://qiita.com/lastcat_/items/de7b530a94fbcf9ba646 漢(オトコ)のコンピュータ道: MySQLのEXPLAINを徹底解説!! http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html 漢(オトコ)のコンピュータ道: MySQLを高速化する10の方法 http://nippondanji.blogspot.jp/2009/02/mysql10.html ヤフー社内でやってるMySQLチューニングセミナー大公開 https://www.slideshare.net/techblogyahoo/mysql-58540246 Cygamesを支えるPHPと、その高速化の取り組み // Speaker Deck https://speakerdeck.com/cygames/cygameswozhi-eruphpto-sofalsegao-su-hua-falsequ-rizu-mi 秒間100万クエリを受け付ける大規模ソーシャルゲームのバックエンドDBシステムの設計・運用ノウハウ // Speaker Deck https://speakerdeck.com/cygames/miao-jian-100mo-kueriwoshou-kefu-keruda-gui-mo-sosiyarugemufalsebatu... MySQL with InnoDB のインデックスの基礎知識とありがちな間違い - クックパッド開発者ブログ http://techlife.cookpad.com/entry/2017/04/18/092524 MySQLでWhere句内でOR演算子を使うと「インデックスが効かなくなる」は本当か https://zenn.dev/nakasone/articles/ce989fa4cbe4c2 これだけ見れば大丈夫！ーMySQLパフォーマンス監視のツボ（クエリ編） | 株式会社インフィニットループ技術ブログ http://www.infiniteloop.co.jp/blog/2012/03/mysql-tuning-cacti-query/ これだけ見れば大丈夫！ーMySQLパフォーマンス監視のツボ（システム編） | 株式会社インフィニットループ技術ブログ https://www.infiniteloop.co.jp/blog/2013/08/mysql-tuning-cacti-system/ MySQL クエリーキャッシュ 【チューニング方法とかも】 - Qiita https://qiita.com/ryurock/items/9f561e486bfba4221747 MySQL クエリーキャッシュ 【チューニング方法とかも】 - Qiita https://qiita.com/ryurock/items/9f561e486bfba4221747 DSAS開発者の部屋:クエリキャッシュは切ったほうがいいんじゃなイカ？ http://dsas.blog.klab.org/archives/52021866.html MySQLでプライマリキーをUUIDにする前に知っておいて欲しいこと | Raccoon Tech Blog [株式会社ラクーンホールディングス 技術戦略部ブログ] https://techblog.raccoon.ne.jp/archives/1627262796.html SQLを速くするぞ―お手軽パフォーマンス・チューニング https://mickindex.sakura.ne.jp/database/db_optimize.html データベースの仕組み(アーキテクチャ)をざっくり理解する https://zenn.dev/revenuehack/articles/dc5a901fbe90e6 MySQLで全文検索機能を使う際のパフォーマンスについて調査してみた - iimon tech blog https://tech.iimon.co.jp/entry/2023/12/01/113000
■mysqldump
mysqldumpまとめ - Qiita https://qiita.com/PlanetMeron/items/3a41e14607a65bc9b60c
■達人に学ぶSQL徹底指南書 第2版
達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ（ミック）｜翔泳社の本 https://www.shoeisha.co.jp/book/detail/9784798157825 ■CASEでラベルを読み替え 単純CASE式、検索CASE式とも、結果は同じ 単純CASE式の方が簡潔に書くことができるが、できることも限られている
CREATE TABLE members( name VARCHAR(32) PRIMARY KEY, sex INTEGER NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '名簿'; INSERT INTO members VALUES('山田太郎', 1); INSERT INTO members VALUES('山田花子', 2); INSERT INTO members VALUES('鈴木一郎', 1); INSERT INTO members VALUES('佐藤次郎', 1); INSERT INTO members VALUES('田中三郎', 1); INSERT INTO members VALUES('岡田智子', 2); -- 単純CASE式で読み替え SELECT name, CASE sex WHEN '1' THEN '男' WHEN '2' THEN '女' ELSE 'その他' END AS sex FROM members ; -- 検索CASE式で読み替え SELECT name, CASE WHEN sex = '1' THEN '男' WHEN sex = '2' THEN '女' ELSE 'その他' END AS sex FROM members ;
■既存のコード体系を新しい体系に変換して集計
CREATE TABLE prefs( pref_name VARCHAR(32) PRIMARY KEY, population INTEGER NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '都道府県'; INSERT INTO prefs VALUES('徳島', 100); INSERT INTO prefs VALUES('香川', 200); INSERT INTO prefs VALUES('愛媛', 150); INSERT INTO prefs VALUES('高知', 200); INSERT INTO prefs VALUES('福岡', 300); INSERT INTO prefs VALUES('佐賀', 100); INSERT INTO prefs VALUES('長崎', 200); INSERT INTO prefs VALUES('東京', 400); INSERT INTO prefs VALUES('群馬', 50); -- 県名を地方名に再分類する SELECT CASE pref_name WHEN '徳島' THEN '四国' WHEN '香川' THEN '四国' WHEN '愛媛' THEN '四国' WHEN '高知' THEN '四国' WHEN '福岡' THEN '九州' WHEN '佐賀' THEN '九州' WHEN '長崎' THEN '九州' ELSE 'その他' END AS district, SUM(population) FROM prefs GROUP BY district ; -- 人口階級ごとに都道府県を分類する SELECT CASE WHEN population < 100 THEN 'A' WHEN population >= 100 AND population < 200 THEN 'B' WHEN population >= 200 AND population < 300 THEN 'C' WHEN population >= 300 THEN 'D' ELSE NULL END AS population_class, COUNT(*) AS cnt FROM prefs GROUP BY population_class ;
■異なる条件の集計を1つのSQLで行う
CREATE TABLE prefs( pref_name VARCHAR(32), sex CHAR(1) NOT NULL, population INTEGER NOT NULL, PRIMARY KEY(pref_name, sex) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '都道府県'; INSERT INTO prefs VALUES('徳島', '1', 60); INSERT INTO prefs VALUES('徳島', '2', 40); INSERT INTO prefs VALUES('香川', '1', 100); INSERT INTO prefs VALUES('香川', '2', 100); INSERT INTO prefs VALUES('愛媛', '1', 100); INSERT INTO prefs VALUES('愛媛', '2', 50); INSERT INTO prefs VALUES('高知', '1', 100); INSERT INTO prefs VALUES('高知', '2', 100); INSERT INTO prefs VALUES('福岡', '1', 100); INSERT INTO prefs VALUES('福岡', '2', 200); INSERT INTO prefs VALUES('佐賀', '1', 20); INSERT INTO prefs VALUES('佐賀', '2', 80); INSERT INTO prefs VALUES('長崎', '1', 125); INSERT INTO prefs VALUES('長崎', '2', 125); INSERT INTO prefs VALUES('東京', '1', 250); INSERT INTO prefs VALUES('東京', '2', 150); -- 男性の人口 SELECT pref_name, population FROM prefs WHERE sex = '1' ; -- 女性の人口 SELECT pref_name, population FROM prefs WHERE sex = '2' ;
以下のようにすれば、1つのSQLで処理できる
-- 男性の人口と女性の人口 SELECT pref_name, -- 男性の人口 SUM(CASE WHEN sex = '1' THEN population ELSE 0 END) AS cnt_male, -- 女性の人口 SUM(CASE WHEN sex = '2' THEN population ELSE 0 END) AS cnt_female FROM prefs GROUP BY pref_name ;
■条件を分岐させたUPDATE 以下の条件で更新をかけるとする 1. 現在の給料が30万円以上の社員は、10%の減給とする 2. 現在の給料が25万円以上28万円未満の社員は、20%の昇給とする UPDATE文を2回実行すると、1回目のSQLによって値が変わるため、意図した更新ができない CASEを使って同時に実行することで、意図した結果を得られるうえに、1回の実行で済むのでパフォーマンスもいい
CREATE TABLE personnel( name VARCHAR(32) PRIMARY KEY, salary INTEGER NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '従業員'; INSERT INTO personnel VALUES('相田', 300000); INSERT INTO personnel VALUES('神崎', 270000); INSERT INTO personnel VALUES('木村', 220000); INSERT INTO personnel VALUES('斎藤', 290000); -- 条件1 UPDATE personnel SET salary = salary * 0.9 WHERE salary >= 300000 ; -- 条件2 UPDATE personnel SET salary = salary * 1.2 WHERE salary >= 250000 AND salary < 280000 ;
以下のようにすれば、1つのSQLで処理できる
-- 条件1と条件2を同時に実行する UPDATE personnel SET salary = CASE WHEN salary >= 300000 THEN salary * 0.9 WHEN salary >= 250000 AND salary < 280000 THEN salary * 1.2 ELSE salary END ;
■テーブル同士のマッチング CASE式の大きな利点は、式を評価できること つまりCASE式の中でBETWEEN、LIKE、<、>といった便利な述語群を利用できる 中でもINとEXISTSはサブクエリを式に取れるため、非常に強力な表現力を持つ
CREATE TABLE course_master( course_id INTEGER PRIMARY KEY, course_name VARCHAR(32) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT 'コース内容'; INSERT INTO course_master VALUES(1, '経理入門'); INSERT INTO course_master VALUES(2, '財務知識'); INSERT INTO course_master VALUES(3, '簿記検定'); INSERT INTO course_master VALUES(4, '税理士'); CREATE TABLE course_schedule( month INTEGER, course_id INTEGER, PRIMARY KEY(month, course_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT 'コース予定'; INSERT INTO course_schedule VALUES(201806, 1); INSERT INTO course_schedule VALUES(201806, 3); INSERT INTO course_schedule VALUES(201806, 4); INSERT INTO course_schedule VALUES(201807, 4); INSERT INTO course_schedule VALUES(201808, 2); INSERT INTO course_schedule VALUES(201808, 4);
以下のように処理できる 結果は同じだが、パフォーマンスはEXISTS方式の方がいい
-- INでテーブル同士のマッチング SELECT course_name, CASE WHEN course_id IN ( SELECT course_id FROM course_schedule WHERE month = 201806 ) THEN '○' ELSE '×' END AS "6月", CASE WHEN course_id IN ( SELECT course_id FROM course_schedule WHERE month = 201807 ) THEN '○' ELSE '×' END AS "7月", CASE WHEN course_id IN ( SELECT course_id FROM course_schedule WHERE month = 201808 ) THEN '○' ELSE '×' END AS "8月" FROM course_master ; -- EXISTSでテーブル同士のマッチング SELECT cm.course_name, CASE WHEN EXISTS ( SELECT course_id FROM course_schedule cs WHERE month = 201806 AND cs.course_id = cm.course_id ) THEN '○' ELSE '×' END AS "6月", CASE WHEN EXISTS ( SELECT course_id FROM course_schedule cs WHERE month = 201807 AND cs.course_id = cm.course_id ) THEN '○' ELSE '×' END AS "7月", CASE WHEN EXISTS ( SELECT course_id FROM course_schedule cs WHERE month = 201808 AND cs.course_id = cm.course_id ) THEN '○' ELSE '×' END AS "8月" FROM course_master cm ;
■CASE式の中で集約関数を使う 学生番号と、その学生の所属クラブを管理するテーブルがあるとする 学生は複数のクラブに所属している（100, 200）場合もあれば、1つのクラブにしか所属していない場合もある 複数のクラブに所属している学生については、主なクラブを表すフラグ列に「Y」が入り、それ以外には「N」が入る 1つのクラブに専念している学生の場合は「N」が入る このテーブルから、以下の条件でデータを取得する 1. 1つだけのクラブに所属している学生については、そのクラブIDを取得する 2. 複数のクラブに所属している学生については、主なクラブのIDを取得する
CREATE TABLE student_club( std_id INTEGER, club_id INTEGER, club_name VARCHAR(32), main_club_flg CHAR(1), PRIMARY KEY (std_id, club_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '学生の所属クラブ'; INSERT INTO student_club VALUES(100, 1, '野球', 'Y'); INSERT INTO student_club VALUES(100, 2, '吹奏楽', 'N'); INSERT INTO student_club VALUES(200, 2, '吹奏楽', 'N'); INSERT INTO student_club VALUES(200, 3, 'バドミントン','Y'); INSERT INTO student_club VALUES(200, 4, 'サッカー', 'N'); INSERT INTO student_club VALUES(300, 4, 'サッカー', 'N'); INSERT INTO student_club VALUES(400, 5, '水泳', 'N'); INSERT INTO student_club VALUES(500, 6, '囲碁', 'N'); -- 条件1 SELECT std_id, MAX(club_id) AS main_club FROM student_club GROUP BY std_id HAVING COUNT(*) = 1 ; -- 条件2 SELECT std_id, club_id AS main_club FROM student_club WHERE main_club_flg = 'Y' ;
以下のようにすれば、1つのSQLで処理できる
SELECT std_id, CASE WHEN COUNT(*) = 1 -- 1つだけのクラブに所属している学生の場合 THEN MAX(club_id) ELSE MAX( -- 複数のクラブに所属している学生の場合 CASE WHEN main_club_flg = 'Y' THEN club_id ELSE NULL END ) END AS main_club FROM student_club GROUP BY std_id ;
■自己結合で検索 同じテーブルを結合して、高度な検索を行うことができる
CREATE TABLE address( name VARCHAR(32), family_id INTEGER, address VARCHAR(32), PRIMARY KEY(name, family_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '住所録'; INSERT INTO address VALUES('前田 義明', '100', '東京都港区虎ノ門3-2-29'); INSERT INTO address VALUES('前田 由美', '100', '東京都港区虎ノ門3-2-92'); INSERT INTO address VALUES('加藤 茶', '200', '東京都新宿区西新宿2-8-1'); INSERT INTO address VALUES('加藤 勝', '200', '東京都新宿区西新宿2-8-1'); INSERT INTO address VALUES('ホームズ', '300', 'ベーカー街221B'); INSERT INTO address VALUES('ワトソン', '400', 'ベーカー街221B'); -- 同じ家族で、住所が違うレコードを検索する（間違って登録されたデータを探す） SELECT DISTINCT A1.name, A1.address FROM address A1 INNER JOIN address A2 ON A1.family_id = A2.family_id AND A1.address <> A2.address ;
CREATE TABLE products( name VARCHAR(16) NOT NULL, price INTEGER NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '商品'; INSERT INTO products VALUES('りんご', 50); INSERT INTO products VALUES('みかん', 100); INSERT INTO products VALUES('ぶどう', 50); INSERT INTO products VALUES('スイカ', 80); INSERT INTO products VALUES('レモン', 30); INSERT INTO products VALUES('いちご', 100); INSERT INTO products VALUES('バナナ', 100); -- 同じ値段で、商品名が違うレコードを検索する SELECT DISTINCT P1.name, P1.price FROM products P1 INNER JOIN products P2 ON P1.price = P2.price AND P1.name <> P2.name ORDER BY P1.price;
■データをJSONで扱う
MySQLでJSONカラムを利用する（備忘録) https://www.usagi1975.com/202101220843/
CREATE TABLE json_test( id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID', text JSON NOT NULL COMMENT 'JSONデータ', PRIMARY KEY(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT 'JSONデータ操作テスト'; INSERT INTO json_test VALUES(NULL, '{"number": 1,"code": "AAA","title": "テストメッセージ1","completed": true}'); INSERT INTO json_test VALUES(NULL, '{"number": 2,"code": "BBB","title": "テストメッセージ2","completed": false}'); SELECT * FROM json_test;
このとき、以下のように JSON_EXTRACT を使用するとJSONの各値を参照できる 検索条件としても使用できる
SELECT id, text, JSON_EXTRACT(text, '$.number') AS number, JSON_EXTRACT(text, '$.code') AS code, JSON_EXTRACT(text, '$.title') AS title, JSON_EXTRACT(text, '$.completed') AS completed FROM json_test ;
■「->」で指定する MySQLでJSONを扱う - Qiita https://qiita.com/abcb2/items/8affae03caa3e94068b5 MySQLでJSON型を使う(JSON関数編) | スマートスタイル TECH BLOG｜データベース＆クラウドの最新技術情報を配信 https://www.s-style.co.jp/blog/2019/03/3614/ もう知ってた？ MySQL 5.7でNoSQLっぽくJSONデータを扱う方法 - WPJ https://www.webprofessional.jp/use-json-data-fields-mysql-databases/ MySQLでJSON型を使う（パフォーマンス編） | スマートスタイル TECH BLOG｜データベース＆クラウドの最新技術情報を配信 https://www.s-style.co.jp/blog/2017/06/420/ 「->」は JSON_EXTRACT のエイリアスとなっているらしいが、XAMPP環境（10.4.13-MariaDB）で試してもエラーになった バージョンに依存するのかも？要調査
SELECT id, text, text->"$.number" AS number, text->"$.code" AS code, text->"$.title" AS title, text->"$.completed" AS completed FROM json_test ;
■データの複製
別名で同じ仕様のテーブルを作成し、 そこに「INSERT INTO ○○ SELECT * FROM △△ WHERE no >= 3;」で必要な分だけデータを複製し、 その後「ALTER TABLE」もしくは「RENAME TABLE」でテーブル名を変更して新テーブルに入れ替えるのが良さそう MySQLの「ALTER TABLE RENAME」と「RENAME TABLE」 - なからなLife https://atsuizo.hatenadiary.jp/entry/2016/06/16/100000 MySQLで3億レコード物理削除した話 - Qiita https://qiita.com/ningenMe/items/57bd66359d89b691ba0d
■データのマスキング
MySQLで個人情報をマスキング - Qiita https://qiita.com/murak/items/7add80e9624b1abf103a
■ユーザと権限
■ユーザの作成と権限の設定 MySQL5.7からは、ユーザを作成してからGRANTする必要がある 以下は webmaster ユーザの作成例
CREATE USER webmaster@localhost IDENTIFIED BY '1234';
testデータベースに対しての全権限を与える例
GRANT ALL PRIVILEGES ON test.* TO webmaster@localhost;
環境によっては「ALL PRIVILEGES」で一括指定するとエラーになるので、必要なものを個別に指定する
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, EVENT ON test.* TO webmaster@localhost;
■権限の確認 ユーザを確認
SELECT host, user FROM mysql.user;
権限を確認
SHOW GRANTS FOR webmaster;
■データベース名の変更
※未検証 MySQL5.1.23以降は RENAME DATABASE が廃止されたらしい 一応 RENAME TABLE を使えば擬似的に対応できるらしい MySQLでdumpせずに "RENAME DATABASE" を実現する - Qiita https://qiita.com/whisper0077/items/06add22b692a25bd19b5 【MySQL】データベースの名前変更（リネーム、RENAME）の方法のひとつ at softelメモ https://www.softel.co.jp/blogs/tech/archives/5319
■外部キー制約
親テーブルの更新/削除の時の挙動(ON DELETE句,ON UPDATE句) - テーブルの作成 - MySQLの使い方 https://www.dbonline.jp/mysql/table/index12.html MySQLの外部キー制約RESTRICT,CASCADE,SET NULL,NO ACTIONの違いは？ - Qiita https://qiita.com/suin/items/21fe6c5a78c1505b19cb 外部キー制約を使うと、テーブル間のデータ整合性を厳密に保つことができる 制約は「FOREIGN KEY(外部キー名) REFERENCES 親テーブル名(カラム名) ON オプション」のように指定でき、オプションには以下のようなものがある RESTRICT: 親テーブルに対して削除または更新を行うとエラーとなる 設定を省略した場合は RESTRICT を設定したのと同じ NO ACTION: 親テーブルに対して削除または更新を行うとエラーとなる RESTRICT を設定した場合と同じ CASCADE: 親テーブルに対して削除または更新を行うと、子テーブルで同じ値を持つカラムのデータに対して削除または更新を行う 上記のように、MySQLでは「NO ACTION」と「RESTRICT」は同じ挙動となる 以下、実際にテーブルへの関連データを登録して、外部キー制約（FOREIGN KEY 制約）などの挙動を試してみる
CREATE TABLE goods( id INT, name VARCHAR(10), INDEX(name) ) ENGINE=InnoDB; INSERT INTO goods VALUES(1, '消しゴム'); INSERT INTO goods VALUES(2, '鉛筆'); INSERT INTO goods VALUES(3, 'ボールペン');
RESTRICT
CREATE TABLE sales1( id INT, name VARCHAR(10), date DATE, INDEX(name), FOREIGN KEY(name) REFERENCES goods(name) ON UPDATE RESTRICT ) ENGINE=InnoDB; INSERT INTO sales1 VALUES(1, '鉛筆', '2017-01-20'); INSERT INTO sales1 VALUES(2, '消しゴム', '2017-02-05'); UPDATE goods SET name = 'ケシゴム' WHERE id = 1; #1451 - Cannot delete or update a parent row: a foreign key constraint fails
NO ACTION
CREATE TABLE sales2( id INT, name VARCHAR(10), date DATE, INDEX(name), FOREIGN KEY(name) REFERENCES goods(name) ON DELETE NO ACTION ) engine=InnoDB; INSERT INTO sales2 VALUES(1, '鉛筆', '2017-01-20'); INSERT INTO sales2 VALUES(2, '消しゴム', '2017-02-05'); DELETE FROM goods WHERE id = 2; #1451 - Cannot delete or update a parent row: a foreign key constraint fails
CASCADE（MySQLにおいては、「NO ACTION」と「RESTRICT」は同じ挙動）
CREATE TABLE sales3( id INT, name VARCHAR(10), date DATE, INDEX(name), FOREIGN KEY(name) REFERENCES goods(name) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE=InnoDB; INSERT INTO sales3 VALUES(1, '鉛筆', '2017-01-20'); INSERT INTO sales3 VALUES(2, '消しゴム', '2017-02-05'); INSERT INTO sales3 VALUES(3, '消しゴム', '2017-03-02'); INSERT INTO sales3 VALUES(4, '鉛筆', '2017-04-26'); UPDATE goods SET name = 'ケシゴム' WHERE id = 1; # sales3 テーブルの name も更新される DELETE FROM goods WHERE id = 1; # sales3 テーブルの name が等しいデータも削除される
上記ではテーブル作成時に制約を追加している 後から追加する場合、以下のようにALTER TABLEで追加する 制約名は必須では無いようだが、削除のときに使用するので付けておくのが無難か 以下の制約名はLaravelが自動作成した制約名。制約名はランダムな文字列を付けることが多い？
ALTER TABLE sales1 ADD CONSTRAINT FK_23A0E6661220EA6 FOREIGN KEY(name) REFERENCES goods(name)
以下のようにすれば削除できる
ALTER TABLE sales1 DROP CONSTRAINT FK_23A0E6661220EA6;
■外部キー制約を無視して登録（インポート）・削除
データの移植時、外部キー制約があるとSQLを丸ごと登録（インポート）・削除できないことがある SQLファイルの最初に以下を追加し、
SET FOREIGN_KEY_CHECKS = 0;
最後に以下を追加すれば、一時的に外部キー制約を無視してインポートできる
SET FOREIGN_KEY_CHECKS = 1;
以下は削除コードの一例
TRUNCATE TABLE dtb_mail_history; TRUNCATE TABLE dtb_order_pdf; TRUNCATE TABLE dtb_url_token; SET FOREIGN_KEY_CHECKS = 0; TRUNCATE TABLE dtb_order_item; TRUNCATE TABLE dtb_shipping; TRUNCATE TABLE dtb_order; SET FOREIGN_KEY_CHECKS = 1;
■レプリケーション
複数サーバでデータの同期を行なうことで、負荷分散や障害対策ができる レプリケーションの設定については、サーバメモの「Etcetera.txt」を参照 また、AWSで設定したときのメモは、サーバメモの「AWS.txt」を参照 MySQLレプリケーション再入門 - Qiita https://qiita.com/snoguchi/items/43247a433973d09f7c08 MySQL/MariaDBレプリケーション - とほほのWWW入門 https://www.tohoho-web.com/ex/mysql-replication.html MySQL のレプリケーションから10年間逃げてきた我々が学んだこと8選 - Cybozu Inside Out | サイボウズエンジニアのブログ https://blog.cybozu.io/entry/2020/10/26/173000 ログの肥大化に注意 | logw-ログウ個人的な記録用- https://www.logw.jp/website/site_make/2719.html MySQLのバイナリログが原因でディスクが枯渇した場合の対処方法 - Qiita https://qiita.com/Brutus/items/5244685a2b5e6b7d2a54
■チューニングテスト
■テーブル 以下のテーブルに、TwitterのAPIから取得したデータを記録し続けていく ツイートテーブルとユーザテーブルを連結して一覧表示 キーワード検索やフォロワー数ソートなどを実装して、速度や負荷の様子を見る。スロークエリの記録も試したい
CREATE TABLE `tweets` ( `id` varchar(80) NOT NULL COMMENT 'ID', `created` datetime NOT NULL COMMENT '登録日時', `user_id` varchar(80) NOT NULL COMMENT 'ユーザID', `text` text NOT NULL COMMENT 'ツイート内容', `retweeted_id` varchar(80) DEFAULT NULL COMMENT 'リツイートID', `retweeted_user_id` varchar(80) DEFAULT NULL COMMENT 'リツイートユーザID', PRIMARY KEY (`id`), KEY `id` (`id`), KEY `created` (`created`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ツイート'; CREATE TABLE `users` ( `id` varchar(80) NOT NULL COMMENT 'ID', `created` datetime NOT NULL COMMENT '登録日時', `name` varchar(255) NOT NULL COMMENT '名前', `screen_name` varchar(80) NOT NULL COMMENT 'スクリーンネーム', `location` varchar(80) DEFAULT NULL COMMENT '位置情報', `description` text COMMENT '説明', `url` varchar(255) DEFAULT NULL COMMENT 'URL', `image_url` varchar(255) NOT NULL COMMENT '画像URL', `followers_count` int(11) NOT NULL COMMENT 'フォロワー数', `friends_count` int(11) NOT NULL COMMENT 'フォロー数', `listed_count` int(11) NOT NULL COMMENT 'リスト数', `statuses_count` int(11) NOT NULL COMMENT 'リツイート数', PRIMARY KEY (`id`), KEY `id` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ユーザ';
■環境 CentOS 7 + nginx 1.12.2 + MySQL 5.7.21 https://refirio.net/twitter/ ■検証内容 30万件くらいまでは、何も考えずに作れる 30万件くらいになると、「データ比較」「データ推移」が少し重い。最初の一覧やキーワード検索もほんの少し重いがまだ問題ない 50万件くらいでも十分に早い。Apache→nginxで、PHPもMySQLも新しくしているからか 60万件くらいになると、CPU使用率でuserの使用が確認できるようになってきた。ただし0.6%程度なので大したものではない 80万件くらいになると、最初の一覧でもはっきり遅いと感じる。「データ比較」はかなり重い 120万件 トップページの表示が3秒ほどかかる データ取得時に記事数をキャッシュするようにし、それをもとにページ数などを求めるように変更 トップページが2秒ほどで表示されるようになった levis標準機能のエクスポートではエラーになるが、execコマンドでmysqldumpを実行すればダンプできる 200万件 トップページの表示が3〜4秒ほどかかる キーワード検索すると8〜10秒ほどかかる データ比較は20秒ほどかかる データ推移は3秒ほどかかる CPU使用率は2〜3%くらい 実用的ではない程度には重い インデックスを張る（3つ目だけで良かったかもしれないが要検証）
ALTER TABLE users ADD INDEX (id); ALTER TABLE tweets ADD INDEX (id); ALTER TABLE tweets ADD INDEX (created);
トップページの表示が一瞬になった キーワード検索も一瞬になった データ比較とデータ推移は1〜2秒で表示されるようになった CPU使用率も下がった（2〜3%あった使用率が0.7%くらいになった） SWAPの発生も20〜40KB/sあったものが0.01KB/sくらいになった 全体的に明確な改善が見られる 300万件 トップページの表示は一瞬 キーワード検索も一瞬 データ比較とデータ推移は1〜2秒で表示される ユーザごとのページは3秒くらいかかる 400万件 （300万件のときと大差なし） 600万件 （300万件のときと大差なし） 1200万件 （300万件のときと大差なし） 1900万件 トップページの表示は一瞬 キーワード検索とデータ比較とデータ推移は1〜2秒で表示される ユーザごとのページは5秒くらいかかる 2700万件 トップページの表示は一瞬 キーワード検索とデータ比較とデータ推移は1〜2秒で表示される ユーザごとのページも1〜2秒で表示される 何故か早くなっているが、さくら側でハードウェアのメンテナンスなどがあったのかもしれないが不明
■セキュリティ
【SQLインジェクション対策】徳丸先生に怒られない、動的SQLの安全な組み立て方 https://www.slideshare.net/kwatch/sql-53624630 プレースホルダよりもSQLテンプレートの方が安全？要勉強
■トラブル
誰も教えてくれなかったMySQLの障害解析方法 - Qiita https://qiita.com/muran001/items/14f19959d4723ffc29cc MySQLがおかしい！あなたならどうしますか？ - MySQL Casual Advent Calendar 2011 - As a Futurist... https://blog.riywo.com/2011/12/02/002204/ MySQLのレプリケーション遅延をローカルで再現させる手順 その1 - 株式会社CFlatの明後日スタイルのブログ http://cflat-inc.hatenablog.com/entry/20130812/1376265269 RDBのトラブルの現場を追え！ / rdb-Troubleshooting - Speaker Deck https://speakerdeck.com/soudai/rdb-troubleshooting RDBのトラブルの現場を追え！ ~ 様々な現場を見る ~ / rdb-troubleshooting2 - Speaker Deck https://speakerdeck.com/soudai/rdb-troubleshooting2 MySQLで3億レコード物理削除した話 - Qiita https://qiita.com/ningenMe/items/57bd66359d89b691ba0d
■MySQL8へのバージョンアップ
MySQL 8.0 への移行が完了しました 〜さようなら全ての MySQL 5.7〜 - Cybozu Inside Out | サイボウズエンジニアのブログ https://blog.cybozu.io/entry/2021/05/24/175000
■MariaDBについて
MariaDBはMySQLの作者によって、MySQLのソースコードをベースに作成されたもの 詳細はサーバメモの Web.txt を参照
■Adminer
※phpMyAdminよりも軽量なデータベース管理ツール 1つのPHPファイルを設置するだけで動作する Adminer - Database management in a single PHP file https://www.adminer.org/ Adminerを設置する - Qiita https://qiita.com/nissuk/items/2b1aee7f81f351c7ab05
■A5:SQL Mk-2
※Windows用のデータベース管理ツール また、テーブル定義をもとにER図の雛形を作成することもできる A5:SQL Mk-2 - フリーの汎用SQL開発ツール/ER図ツール .. 松原正和 https://a5m2.mmatsubara.com/ A5:SQL Mk-2に回帰した話 | フューチャー技術ブログ https://future-architect.github.io/articles/20220225a/ ■接続先の追加 初回起動時に接続先を確認されるので設定できる その後はメニューの「データベース → データベースの追加と削除」から追加できる ■接続先の追加（トンネル接続の場合） 接続設定画面の「SSH2トンネル」タブで、サーバの接続情報を入力する 「基本」タブでは、そのサーバ内からデータベースへ接続する際の情報を入力する ■接続先の追加（Docker環境の場合） ・XAMPPをインストールしている ・WSL2に手動でDockerをインストールしている ・DockerのMySQLコンテナを起動させている という環境で確認 通常どおりA5:SQLのメニューから データベース → データベースの追加と削除 → 追加 → MySQL/MariaDB直接接続 として接続設定を行う 「基本」タブで以下のとおり設定 ホスト名: localhost ポート番号: 3306 ユーザ名: root パスワード: （docker-compose.yml の MYSQL_ROOT_PASSWORD で設定した値） データベース: （docker-compose.yml の MYSQL_DATABASE で設定した値） これで接続ができた なおXAMPPとコンテナの両方を起動させている場合、 「コンテナが起動されていると、コンテナのMySQLに接続できるがXAMPPのMySQLには接続できない」 「コンテナが起動されていなければ、コンテナのMySQLに接続できないがXAMPPのMySQLには接続できる」 となった 【Docker】DockerコンテナのDBに外部のA5:SQL Mk-2から接続する方法 | とんプログラミング https://programming.sincoston.com/docker-db-connect-pg-external/ 環境によっては接続先は localhost（127.0.0.1）で無いことがあるらしい その場合、以下などが参考になるかもしれない（未検証） Windows 10 Home（WSL2）のDockerでMySQL立ち上げてWindows側からA5:SQL Mk2で接続してみる | Snow System https://snowsystem.net/container/docker/docker-mysql-wsl-a5sql/ ■ER図を書く 以下でツールの基本操作が紹介されている ERエディタ - ER図ツール https://a5m2.mmatsubara.com/help/ER/ ER図を作成したいデータベースを選択し、メニューから「データベース → ER図 → ER図のリバース生成」を実行 テーブル一覧が表示されるので、すべてのテーブルを選択して「リバースER生成」ボタンを押す しばらく待つとER図が表示される。ただしテーブル間の関連は反映されていない 以下を参考にする限り、関連は手動での設定が必要みたい 描ける！ER図 https://www.slideshare.net/nekoruri/er-14127685 メニューの「ER図 → 表示レベル」は「属性」がデフォルトとなっているが、 これを例えば「属性とデータ型」にすると、列名も一緒に表示される この設定は「属性（論理名と物理名）」もしくは「属性（論理名と物理名）とデータ型（位置揃え）」にしておくのが判りやすいかもしれない メニューの「ER図 → ER図プロパティ」から、プロジェクト名や著作者などを登録できる 何に使われているかは不明だが、単純にドキュメントの情報として持っているだけかもしれない 「プロジェクト名」と「RDBMS種類」は、適切なものにしておくと後々混乱しなくて良さそう メニューの「設定 → オプション → ER図」から、ER図に関するオプションを調整できる 基本的にデフォルトのままで問題なさそうだが、「オブジェクトをスナップする」の間隔など、必要に応じて調整する メニューの「設定 → オブジェクトの追加 → リレーションシップ」から、テーブル間の関連を設定できる 「親エンティティをクリック（主キーがある方） → 子エンティティをクリック（xxx_id がある方）」 とクリックすると線が引かれる この線をダブルクリックするとリレーションの詳細を設定できる 基本的に ・「カーディナリティ」は「1」対「0以上」 ・「依存」にチェックしない ・「リレーション」は「代理キー」と設定したい「xxx_id」の列 として「適用」をクリックする …でいいはずだが、先の引かれる位置がおかしい？また調査する メニューの「設定 → オブジェクトの追加 → エンティティ」から、テーブルを追加できる 追加されたエンティティをダブルクリックすると、列の追加ができる 作成済みのエンティティをダブルクリックすると、列の追加編集削除などができる メニューの「ER図 → DDLを作成する」から、ER図をもとにSQLを作成できる 必要に応じて設定を変更してから「DDL生成」ボタンを押す ER図に修正を加える場合、基本的にはデータベース定義を見ながらA5:SQL上で作業することになる…はず ただし新規にテーブルを追加する場合、いったん対象テーブルをリバース生成し、それをコピー＆ペーストで持ってくると早い…はず メニューの「ファイル → 名前をつけて保存」から、拡張子「a5er」のファイルとしてER図を保存できる 次回このファイルを開けば、ER図が再現される その他、以下などを参考にすると良さそう 若手プログラマー必読！５分で理解できるER図の書き方５ステップ https://it-koala.com/entity-relationship-diagram-1897 ひととおり基本が紹介されている ER図とは？書き方や用語・テクニックを徹底解説 https://products.sint.co.jp/ober/blog/create-er-diagram 「ER図を見やすくするテクニック」部分などを参考にする 「親エンティティは左上から書く」は、常に意識しておくと良さそう 若手プログラマー必読！５分で理解できるER図の書き方５ステップ https://it-koala.com/entity-relationship-diagram-1897
■HeidiSQL
※Windows用のデータベース管理ツール Navicatは有料だし、MySQL Workbenchは日本語化に難があるし …だが、HeidiSQLなら問題なく使えそう 「HeidiSQL」オープンソースのデータベース管理ソフト - 窓の杜 https://forest.watch.impress.co.jp/library/software/heidisql/ 以下、SSHトンネルで接続するためのメモ 「plinkの取得」「puttygenの取得」「pemをppkに変換」 という作業が必要なので、それぞれ記載する HeidiSQL SSH tunnel経由でMySQL(MariaDB)に接続する - Symfoware https://symfoware.blog.fc2.com/blog-entry-1942.html ■plinkの取得 以下から plink.exe の64bit版をダウンロード Download PuTTY: latest release (0.74) https://www.chiark.greenend.org.uk/~sgtatham/putty/latest.html 手動で以下に配置した C:\Program Files\plink\plink.exe ■puttygenの取得 以下から puttygen.exe の64bit版をダウンロード Download PuTTY: latest release (0.74) https://www.chiark.greenend.org.uk/~sgtatham/putty/latest.html 手動で以下に配置した C:\Program Files\puttygen\puttygen.exe ■pemをppkに変換 PuTTYgenを使って変換できる 「Load」ボタンからpemファイルを読み込み、その後「SavePrivateKey」ボタンからppkファイルを出力する （「SavePublicKey」では無いので注意） pem ⇒ ppk 変換 - Qiita https://qiita.com/naka46/items/3297242f3386b5f9e3dc id_rsa⇒pem⇔ppk 相互変換 | Punio Kid Works http://www.puniokid.com/tips/linux/69/ ■HeidiSQL上での設定 ※plink.exeなどを設定した後の汎用的な手順 ※ユーザ名などは例 「設定」タブで以下を設定 ネットワーク種別: MySQL(SSH tunnel) ホスト名: localhost ユーザー: webmaster パスワード: abcd1234 ポート: 3306 データベース: test 「SSHトンネル」タブで以下を設定 SSHホスト＋ポート: 203.0.113.1 + 10022 ユーザー名: ssh-user パスワード: （空欄） 秘密鍵ファイル: Example.ppk ■トラブル（未解決） 新規に接続しようとしたとき、以下のエラーで接続できないことがあった
PLINKは、予期せず終了しました。コマンドライン： C:\Program Files\plink\plink.exe -ssh ec2-user@203.0.113.1 -P 10022 -i "C:\Users\refirio\SSH\test\test.ppk" -N -L 3307:production.xxxxx.ap-northeast-1.rds.amazonaws.com:3306
コマンドプロンプトから直接実行してみると、以下のエラーが表示された
C:\Users\refirio>C:\"Program Files"\plink\plink.exe -ssh ec2-user@203.0.113.1 -P 10022 -i "C:\Users\refirio\SSH\test\test.ppk" -N -L 3307:production.xxxxx.ap-northeast-1.rds.amazonaws.com:3306 Using username "ec2-user". FATAL ERROR: No supported authentication methods available (server sent: publickey,gssapi-keyex,gssapi-with-mic)
エラーメッセージをもとに調べてみると、authorized_keys のパーミッション問題の可能性はありそう 問題の解決方法 SSHの鍵認証接続を題材に - Qiita https://qiita.com/szly/items/ef057727e8b6d227ed29 …だが、調べてみるとパーミッションに問題は無かった またそもそも、このパーミッションが不正な場合はSSH接続自体できないはずだが、Poderosaで接続はできた 試しにHeidiSQLではなくMySQL Workbenchなら、すんなり接続できた 原因不明だが、HeidiSQLの問題で特定環境での接続に対応できないなどあるのかもしれない
■メモ
MySQLに初めてINSERTするとアクセスが発生するファイルは何かという質問をどう調べるのか - oranie's blog https://oranie.hatenablog.com/entry/2021/08/26/233701 straceコマンドの使い方 - Qiita https://qiita.com/hana_shin/items/a03150552e2f70994248

Advertisement