作成者別アーカイブ: 鮎

まじめも：collationでUTF8日本語を使う場合、候補にutf8_general_ciとutf8_unicode_ciのどちらを選ぶか迷う事がある

これについては、
MySQL5.5ユニコードキャラクタセットの説明
の中程に

For any Unicode character set, operations performed using the xxx_general_ci collation are faster than those for the xxx_unicode_ci collation.
～
A difference between the collations is that this is true for utf8_general_ci:
ß = s
Whereas this is true for utf8_unicode_ci, which supports the German DIN-1 ordering (also known as dictionary order):
ß = ss

generalはunicodeに比べて高速だと書かれている。
挙動の違いとして、上記エスツェット（ドイツ語拡張文字）の違いが挙げられているが、現在の書法ではss(unicodeの挙動)が正しい。
しかし、日本語の扱いの違いが上げられていないが、実際に評価すると以下の様な挙動を示す。

utf8_unicode_ciにすると、なんとカタカナとひらがな表記が同じであると判定されてしまうのだ。
これだと、実運用上問題が生じることが多いだろうから、一般的には、utf8_unicode_ciではなくutf8_general_ciを使う様にした方がいい。

(335)

このWordPressを動かしていてページ送り(古い記事へ・新しい記事へ)が動作しない問題が発生した。
MySQLで全SQLログを取得してみると、SELECT文の前に必ず’EXPLAIN 実行予定SELECT文’が実行されていた。
WordPressでは、ページ数を取得するために、MySQLのFOUND_ROWS()を実行して取得している。　これは

SELECT SQL_CALC_FOUND_ROWS * FROM stock LIMIT 0,10;
SELECT FOUND_ROWS();

このように実行すると、SQL_CALC_FOUND_ROWS キーワードをつけたSELECT文のLIMITをつけなかった場合の件数（SELECT count(*) FROM stockの結果に同じ）を得られるのだが、このFOUND_ROWS()は直近の文を読むので、今回の様にEXPLAINが挿入されると↓のようになってしまうので、FOUND_ROWSの戻り値が0になってしまう。

SELECT SQL_CALC_FOUND_ROWS * FROM stock LIMIT 0,10;
EXPLAIN SELECT FOUND_ROWS();
SELECT FOUND_ROWS();

で、このEXPLAINがどこから来ているのか。　WordPressの全ソース検索をしても出てこない。
色々調べて、PHPに行き着いた。
PHP5.3.5ソース
php_mysql.c:1431-1432

int newql = spprintf (&newquery, 0, “EXPLAIN %s”, query);
mysql_real_query(mysql->conn, newquery, newql);

これで実行されていた。　これが実行される条件は、
php_mysql.c:1426

if (MySG(trace_mode))

の条件が指定されていて、このtrace_modeは
php_mysql_structs.h:125

long trace_mode;

で定義されていて、php.iniの

mysql.trace_mode

で設定することが出来て、On(=1)の場合にEXPLAINが自動実行されて、Off(=0)なら実行されないため、WordPressを実行するサーバではmysql.trace_mode=Offとする(php.iniでグローバル設定する以外に、wpの設置ディレクトリに.htaccessを配置してphp_flag mysql.trace_mode Offの記述を入れてもOK)か、WordPressのquery.phpでカウントするSQLをcount()を使う様に修正しなければいけない。

(458)

テーブルstockでレコードが削除されるとき自動でバックアップテーブルに挿入するトリガ

DELIMITER ..
CREATE TRIGGER stock_auto_backup
BEFORE DELETE ON stock FOR EACH ROW
BEGIN
INSERT INTO stock_backup(code, name, num, deleted_on) VALUES (OLD.code, OLD.name, OLD.num, unix_timestamp());
END..
DELIMITER ;

MySQLのunix_timestamp()関数により削除時間(now)を追加して記録することが出来る。
全てのUPDATE操作をログしたいとき、DELETEをUPDATEに書き換えれば、トリガでログしておけるので、操作側の修正の手間が省ける。

更に、一定以上古いバックアップを削除する機能もつける場合、END..の前に

DELIMITER ..
CREATE TRIGGER stock_auto_backup
BEFORE DELETE ON stock FOR EACH ROW
BEGIN
INSERT INTO stock_backup(code, name, num, deleted_on) VALUES (OLD.code, OLD.name, OLD.num, unix_timestamp());
DELETE FROM stock_backup WHERE unix_timestamp(deleted_on)<(unix_timestamp()-60*60*24*7); END.. DELIMITER ;

の様にすれば、トリガ実行タイミングで7日より前のレコードをバックアップテーブルから自動削除できる。

なお、MySQLでは同じテーブルの同じ条件に複数のトリガを設定できないので、同じ条件に機能追加する場合は

DROP TRIGGER stock_auto_backup

のようにしてトリガを一度削除する。

とあるテーブルのAUTO_INCREMENT値を変更する。

ALTER TABLE stock AUTO_INCREMENT = 1;

この場合はstockテーブルを1にしている。

(166)