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

複数カラムの値を別問い合わせの複数カラム結果に基づいて抽出する – 行副問い合わせ 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)

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

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

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

(641)


カテゴリー: MySQL | コメントをどうぞ

intレコードの値をランダムに更新してみる – MySQL UPDATE RAND ROUND

プチMySQLメモ

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

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

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

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

(1565)


カテゴリー: MySQL | コメントをどうぞ

マスターレコードの値で関連レコードを更新してみる – MySQL JOIN UPDATE VPS

プチMySQLメモ

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

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

ある時、問合せをユーザの登録店舗で集約する為に、inquiry.shop_iduser.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。

(86)


カテゴリー: MySQL | コメントをどうぞ

phpMyAdmin – VPS MySQL 管理 config.inc.php

 MySQLをお手軽に管理するためのツールとして定番のphpMyAdmin。
 remiリポジトリにも入っていて、yum –enablerepo=epel,remi install phpMyAdmin で簡単に導入できる。
 しかし、これの設定ファイルの場所が昔と変わっていてちょっと手間取ったのと、その他のメモ。
 /usr/share/phpMyAdmin にファイルが設置されて、/etc/httpd/conf.d/phpMyAdmin で、Aliasを使って /phpMyAdmin にマッピングされているんだけど、これは美味しくない。
 全てのバーチャルホストの /phpMyAdmin にマップされてしまうのでセキュリティ的に美味しくない。
 なので、/etc/httpd/conf.d/phpMyAdmin を削除して、中身を認証とSSLを入れたバーチャルホストのVirtualHostディレクティブ内に設置する。
 また、マップするアドレス自体も Alias /hogepiyo/phpMyAdmin /usr/share/phpMyAdmin 等のように、わかりにくい場所にマッピングするのも有効だろう。
 設定ファイルの config.inc.php は、/usr/share/phpMyAdmin 直下ではなく、/etc/phpMyAdmin 配下に設置されているので、こちらを編集する。

(402)


カテゴリー: MySQL, サーバ設定 | コメントをどうぞ

MySQL接続にSSLを使用する – PHP MySQL SSL WordPress MYSQL_CLIENT_SSL 暗号化

 MySQLの接続にSSLを使う話。

 このブログはWordpressとMySQLが別のサーバで動いている(WindowsServer2003R2化したさくらのVPS上にMySQLサーバを設置して、Apache+PHPを設置したLinux状態のさくらVPSからmysqlコネクタを経由して接続している)
mysql接続は通常は平文なので、ネットワーク上で色々な情報がだらだら流れてしまうのは嫌なので、このコネクションをSSLで暗号化する。

 まず、WindowsServer2003R2上に設置したMySQLサーバにSSL設定を行う。
 MySQL5.5サーバのWindowsバイナリにはSSL機能が含まれているから、特別なプログラムを追加する必要はないけど、Windows上のSSL管理は複雑だ。
 MySQLサーバでは秘密鍵は暗号化されていないファイルでなければならないのだけど、Windowsではそもそも証明書ストアの内部に格納されているので、これを取り出さなければならないんだけど、その操作が証明書の取得と前後する(普通は秘密鍵をベースとして証明書の管理をするんだけど、Windowsでは証明書を取得しないと秘密鍵か取り出せない)
 まず、WindowsのIISマネージャを起動して、ナビゲーションからサーバ>Webサイト>既定のWebサイトを選択して、プロパティを開いて、「ディレクトリセキュリティ」タブにある「セキュリティで保護された通信」の「サーバー証明書」を叩いて「IIS証明書ウィザード」から、「証明書の要求を作成して後で送信する」処理でCSRを作成する(WindowsServer2003R2では古い1024ビットがデフォルトになっているけど、現在は2048ビットが一般的なので、それを選択してCSRを作成する)
 CSRを生成したら、普通にSSL証明書を取得してきて(今回はSSLボックス
からRapidSSLを取得)、適当なcer(※α)としてファイルに保存して、再度「IIS証明書ウィザード」から証明書を登録する(証明書を取得したときに、認証局の証明書も来ると思うので、これも適当なcer(※β)としてファイルに保存しておく)
 証明書の登録処理が完了したら、「セキュリティで保護された通信」の「証明書の表示」を開いて、「詳細」タブの「ファイルにコピー」を実行して、「秘密キーのエクスポート」で「はい」を選んで進むと、PKCS#12形式に固定されているので、チェックボックスを全て解除して、適当なパスワードを設定してpfxファイルにエクスポートする。
 OpenSSLが導入された環境で・・・

openssl pkcs12 -in 出力したpfxファイル -out 中間.cer

で、PFXをデコードする。 この時、聞かれるパスワードは、エクスポート時に設定したパスワードで、パスフレーズはCERに設定するパスフレーズである。
 で、CERにすると、秘密鍵はRSA暗号化された状態なので、再度opensslで

openssl rsa -in 中間.cer -out 平文.pem

とすると、パスフレーズを聞かれるから、先ほど設定したパスフレーズを入れて平文の秘密鍵(※γ)を得る。

 この手順で、α:サーバ証明書、β:認証局証明書、γ:秘密鍵が揃うので、MySQLサーバのあるホストの適当な場所に配置する。
 後は、MySQL設定ファイル(Unixではmy.cnfだが、WindowsではMySQLインストールディレクトリのmy.ini)の[mysqld]セクションに設定を追記する。

ssl-cert=”αのフルパス”
ssl-ca=”βのフルパス”
ssl-key=”γのフルパス”

の、3設定を入れてMySQLサービスを起動する。
 MySQLクライアントで「SHOW VARIABLES LIKE ‘%ssl%’;」とか打つと、

have_openssl : YES
have_ssl : YES

になっていれば、サーバはSSL動作可能な状態になっている。

 PHPのMySQLクライアントでSSL接続する場合には、mysql_connectの第五引数にMYSQL_CLIENT_SSL定数を設定すればOK。
 Wordpressでは、/wp-include/wp-db.php で、@mysql_connect(…)としている部分で、第四引数までセットされているから、「, MYSQL_CLIENT_SSL」と第五引数を追記してやれば、WordpressのDB接続時にはSSL接続を使うようになる。
 正常にSSLに接続されているか確認するために、mysql_connectの下あたりに

$res = mysql_query(“SHOW STATUS LIKE ‘Ssl_cipher'”);
var_dump(mysql_fetch_assoc($res));

とか書いてWordpressサイトに接続してみると、DHE-RSA-AES256-SHAとかの値がべたっと入っていれば、接続はSSLで保護されている(空文字が戻っていたらSSL保護されていない)

 これで、ネット経由のmysql接続が保護されるので、安心して使うことが出来る。

(907)


カテゴリー: MySQL, PHP, Windows VPS, サーバ設定 | コメントをどうぞ

Rollbackしてもauto_incrementは戻らない – MySQL

 まぁ、動作的にこうなるのは仕方ないんだけど、テーブルの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の仕様。
 下手に巻き戻したりすると、平行して実行して先行処理がロールバックするとカウンタが前後しちゃったりするんでベターな処理ではある。
 基本事項だけど一応確認。

(1923)


カテゴリー: MySQL | コメントをどうぞ

InnoDBの一行は8KBまで – MySQL InnoDBの制限

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

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

(2702)


カテゴリー: MySQL | コメントをどうぞ