カテゴリー別アーカイブ: MySQL

　プチMySQLメモ

　MySQLで、あるカラム値が別の問い合わせ結果の一覧に含まれているか問い合わせる場合に使うのが副問い合わせ（サブクエリ）

　例えば、商品一覧テーブル items があって、在庫一覧テーブル stock の在庫数量 stock_num が0より多い（在庫がある）商品を検索する場合、共通の商品ID item_id で関連を持っているとすれば

SELECT * FROM items WHERE item_id IN (SELECT item_id FROM stock WHERE stock_num > 0)

　行副問い合わせは、複数カラムを使った副問い合わせ。

　例えば、共通の item_id が無くて、商品名 item_name とメーカー名 item_maker によって管理されている場合には

SELECT * FROM items WHERE ROW(item_maker, item_name) IN (SELECT item_maker, item_name FROM stock WHERE stock_num > 0)

　のようにして、複数カラムによって問い合わせることが出来る。

　なお、副問い合わせは１カラムなので名前に関係ないが、行副問い合わせでは複数のカラムの対応を取るためにカラム名を使うので、主テーブルのカラム名と副問い合わせテーブルのカラム名が異なるときには、カラム AS カラム名（SELECT stock_item_maker AS item_maker, stock_item_name AS item_name FROM stock…の様に）でカラム名を書き換えて対応を取らせる。

※テストケースとして副問い合わせを使っているが、これらのパターンの場合、MySQLでは結合(JOIN)した方が高速である場合が多いのには注意

(1061)

Tweet

プチMySQLメモ

マスターテーブル user に shop_id(int) カラムがあって、ユーザの登録店舗を保存しているとする。

店舗再設置に伴い店舗をランダムに振り分ける場合

UPDATE user SET shop_id = ROUND(RAND()*10)

これで、０～１０のIDが振られる。　最低値を１にするなら　1+RAND()*9。
MySQLのRAND()関数は、0～1.0の範囲の小数乱数を発生するので、これに10を乗算して0～10.0の範囲にして、ROUND()関数で小数点以下を丸めてint型に適合させる。

(2441)

Tweet

プチMySQLメモ

マスターテーブル user に shop_id カラムがあって、ユーザの登録店舗を保存しているとする。

関連テーブルとして、問い合わせテーブル inquiry にも shop_id を保存して、問い合わせ先の店舗を保存しているとする。

ある時、問合せをユーザの登録店舗で集約する為に、inquiry.shop_id を user.shop_id で更新することにした。

なお、inquiry.user_id カラムによって、 user.id と関連づけられているとすると、この場合の更新文は

UPDATE inquiry LEFT JOIN user ON inquiry.user_id = user.id SET inquiry.shop_id = user.shop_id

でOK。

(138)

Tweet

　まぁ、動作的にこうなるのは仕方ないんだけど、テーブルのauto_increment値はrollbackしても戻らず増加する。
　一般的にテーブルのPKはidで、auto_incrementにすると思うんだけど、InnoDBでトランザクション制御をしているとき

BEGIN;
INSERT INTO main_table(id, code, num) VALUES(0, 1, 2);
UPDATE sub_table SET main_id=LAST_INSERT_ID() WHERE code=1;
ROLLBACK;

　とかすると、INSERTもUPDATEも無かったことにされるけど、main_tableのauto_incrementカウンタは増加しているんで、次にINSERTするとidが飛んでしまうのがMySQLの仕様。
　下手に巻き戻したりすると、平行して実行して先行処理がロールバックするとカウンタが前後しちゃったりするんでベターな処理ではある。
　基本事項だけど一応確認。

(2434)

Tweet

　MySQLでは、従来、MyISAMが標準ストレージエンジンだったけど、トランザクション機能が使えるInnoDBのニーズも高く、最近は最適化も進み、InnoDBを利用する場面も増えてきた。
　しかし、InnoDBでは注意しなければならない点がある。
　デフォルトのMySQLでは、InnoDBの1行はデータ量8KB以下にしなければならない(MyISAMでは64KBまで対応していた)
　例えば、VARCHAR(255)のカラムが40個あれば10KBになってしまうので、こういったデータのInsert/Updateを実行すると’Got error 139 from storage engine’と言うエラーメッセージで転ける。
　InnoDBはページサイズの１／２の大きさの行を許容するので、ページサイズをMaxの64KBにすれば32KBまで入る様になる。

　ちなみに、TEXTやBLOBカラムは通常、先頭の768バイトがテーブルに、残りが外部に保存されるので、このような型のカラムは10個までしか使えない。
　が、InnoDB1.1以降では、my.cnfで
[mysqld]
innodb_file_per_table
innodb_file_format=Barracuda
　の設定をしてやることで、TEXT/BLOBは20バイトのポインタが保存されて、実体は外部に保存されるので、このような型のカラムを多くする場合にはBarracudaファイルフォーマットを検討すると良いだろう。

(3158)

Tweet