今回は以前紹介した第248回 知っておくと得するかもしれないMySQLの組み込み関数の
MD5() / SHA2()関数
MD5()とSHA2()関数は、文字列をハッシュ化するための関数です。どちらも
MD5()関数は引数文字列をMD5アルゴリズム
mysql> SELECT MD5('aaa');
+----------------------------------+
| MD5('aaa') |
+----------------------------------+
| 47bce5c74f589f4867dbd57e9ca9f808 |
+----------------------------------+
1 row in set (0.00 sec)
SHA2()関数はSHA-2アルゴリズムでハッシュ値を返し、引数にビット長を指定して異なる長さのハッシュ
mysql> SELECT SHA2('aaa',256);
+------------------------------------------------------------------+
| SHA2('aaa',256) |
+------------------------------------------------------------------+
| 9834876dcfb05cb167a5c24953eba58c4ac89b1adf57f28f2f9d09af107ee8f0 |
+------------------------------------------------------------------+
1 row in set (0.01 sec)
これらの関数は、データの整合性チェックに活用できます。たとえば、通常プライマリーとレプリカのデータは同一であるべきですが、誤操作などで不一致が起きる可能性があります。すべてのデータを目視で確認することは困難です。そこで、行のデータをCONCAT()でまとめてハッシュ化し、プライマリとレプリカで比較します。
mysql> SELECT md5(concat(user,host)) FROM user ORDER BY user LIMIT 5; +----------------------------------+ | md5(concat(user,host)) | +----------------------------------+ | 8a4fdc53ee19786fce19f765e0c06099 | | d4addca5b72e136f051c76fa64d67511 | | 4de58b5b61f86e2a8ce89ad0e542860e | | 599b40c9be4237ec4fe0ee5844939f3c | | b7f7f71494e47b0a09caa2e73a8b65ab | +----------------------------------+ 5 rows in set (0.00 sec)
これにより
また、現場では、MySQLの出力を直接OSコマンドに渡してテーブル全体の整合性を確認する方法もよく使われます。
以下はmysqlコマンドラインクライアントのpagerサブコマンドを使って、OSのmd5コマンドに渡してハッシュ値を取得する例です。pagerサブコマンドについては 第6回 mysqlコマンドラインクライアントにページャーを指定するをご確認ください。
mysql> pager md5sum mysql> SELECT * FROM user ORDER BY user; 2a1b2fa62aaca511bd1740f52ab15d08 - 16 rows in set (0.00 sec)
これでテーブル全体のハッシュ値を取得し、プライマリとレプリカの結果を比較できます。もし差異が見つかった場合は、前述の行単位のハッシュで差分を特定するのが実用的です。
MD5()とSHA2()関数のそれぞれの特徴としては以下が挙げられます。
- MD5() …軽量で速いが、セキュリティには弱い。チェックサムや一意性確認向いている
- SHA2() …セキュリティ強度が高く、パスワードやデータ保護にも利用可能
FIELD()関数
FIELD関数は特定の文字列がカンマ区切りで指定した文字列の中から何番目に一致したかを
FIELD(str, str1, str2, str3, ...)
strが str1, str2, …の中で何番目に一致しているかを返し、一致しなければ0を返します。
次の例は文字列bの一致する位置情報を確認しています。
mysql> SELECT FIELD('b', 'a', 'b', 'c');
+---------------------------+
| FIELD('b', 'a', 'b', 'c') |
+---------------------------+
| 2 |
+---------------------------+
1 row in set (0.00 sec)
FIELD関数の主な使いどころとして、ORDER BY句に用いるとことで任意の順番で並びを作ることができます。
mysql> SELECT task, priority FROM todo_list ORDER BY FIELD(priority, 'high', 'medium', 'low');
この例では、priorityが high → medium → low の順で並びます。通常のORDER BYだと辞書順となり
CONNECTION_ID()関数
CONNECTION_SHOW PROCESSLIST;で表示される
mysql> SELECT connection_id(); +-----------------+ | connection_id() | +-----------------+ | 8 | +-----------------+ 1 row in set (0.00 sec)
この関数は、特定のクライアントセッションを識別する際に役立ちます。長時間実行中のクエリや不要なセッションを終了させるKILL <id>）CONNECTION_を記録しておけば、performance_
他の利用方法としては、行へのアクセスが集中してINSERT INTO DUPLICATE UPDATE文が望ましいです）。
mysql> UPDATE article_counter
SET counter=counter+1
WHERE article_id=1;
注目度の高い記事にアクセスが集中すると、article_の行に更新が殺到し、ロックの競合によって処理が滞る可能性があります。この競合を分散させるための一案として、CONNECTION_をキーの一部として利用する方法があります。テーブルにconnection_カラムを追加し、次のように更新します。
mysql> UPDATE article_counter
SET counter=counter+1
WHERE article_id=1 AND connection_id=CONNECTION_ID();
こうすることで、セッションごとに別の行に分散され、同一行へのロック競合を回避できます。
注意点として以下があります。
- この方法は コネクションプーリングを利用している場合に限り有効 です。都度接続を張る方式だと接続ごとに
CONNECTION_の値が変わり、行が増えすぎてしまいます。ID() - アプリケーションや設計によっては
「記事ごとに単一カウンタを維持する必要がある」 ケースもあるため、その場合は別の手法 （非同期集計、シャーディングなど） を検討してください。 - あくまで
「アクセス集中を緩和するテクニックのひとつ」 として理解するのが安全です。
まとめ
今回は知っておくと得するかもしれないMySQLの組み込み関数として、MySQLの組み込み関数の中から4つの関数を紹介しました。
- MD5() / SHA2()：文字列をハッシュ化するための関数
- FIELD()：並び順制御や検索で便利
- CONNECTION_
ID()：一意の整数ID （コネクションID） を返す
この他にも、MySQLには多種多様な関数が用意されています。興味のある方は公式ドキュメント 14.