MySQL

概要

• MySQL使ってて気づいたこととか思ったことハマりどころなどをメモして後世(未来の俺)に伝えるためのものです。

mysqldumpについて

• 普通にやるとテーブルロックかかって読み込めなくなったりするので、以下の様な感じでやる。

mysqldump -u root --all-databases --single-transaction

SELECT ... FOR UPDATE

• 指定の行を排他ロックして参照できる。
• 他のスレッドがその行を参照しようとするとwaitしなければならなくなる
• なので
  • SELECT ... FOR UPDATEして、UPDATE ... することによってもう1個のスレッドは最新の情報を受け取れる、というかデータの整合性がぶっ壊れることなくアクセス出来る。

欠点

• 行が一意に定まらないクエリのときはテーブル・ロックになっちゃうらしいので、わりと気づきにくいボトルネックになる可能性もある
  • そういうときはおそらくSQLの設計をよく考えて、たとえばINSERTで衝突させるなどしてアトミックな操作に変更することを検討したほうが良いのかもしれない。

INSERT IGNORE INTO ...

• INSERTするときに普通は重複してたらエラーになっちゃって残りのSQLが実行されない
• そのエラーを無視して残りのSQLを継続実行してくれるようになるキーワードがIGNORE
• Bulk INSERTするときなんかはこれつけとくとすごい楽ちん

INSERT IGNORE INTO (fields...) VALUES ((VALUE1) (VALUE2) (VALUE3));

こんな感じで。

INSERT INTO ... ON DUPLICATE KEY UPDATE ...

もし重複レコードがなかったらINSERTして、あったらUPDATEする、みたいなのが書ける。

INSERT INTO table (id, value) VALUES (1, 'string1')
ON DUPLICATE KEY UPDATE value = 'string2';

例えばこの場合、idが1のレコードがなかったら idが1の'string1'というデータがINSERTされる。そしてidが1のレコードがあった場合そのレコードのvalueが'string2'になる。

• 利点としてはINSERTとそれが無理だった時にUPDATEするという1つのアトミックな操作として扱えることが考えられる。
  • アプリケーション側で面倒な整合性の確認がいらない

COLLATEについて

• MySQLはデフォルトでcase insensitiveになっている。
• これはどういうことかというと、VARCHARのフィールドで大文字小文字区別しないで比較するということ
• これを何とかするにはいくつか種類がある
  • 1. 特定のカラムだけCase Sensitiveにする方法
    • VARCHAR(255) COLLATE utf8_bin として特定のフィールドだけ変更できる
  • 2. 特定のテーブルだけCase Sensitiveにする方法
    • CREATE TABLE players (name VARCHAR(255)) COLLATE utf8_bin;
  • 3. 特定のデータベースをCase Sensitiveにする方法
    • CREATE DATABASE db DEFAULT CHARSET utf8 COLLATE utf8_bin;
• ActiveRecordはテーブル単位でcollateを設定しているみたいだけど、DataMapperはテーブル単位でもcollateを設定してくれないし、フィールド単位でcollateを設定するための仕組みもない。なのでデータベースにcollate utf8_binと付ける必要がある。このへんちょっとダルい。
  • ダルいというか実際にハマって涙を流してた。