順位付けを行うクエリ
例えば以下のテーブル「user」について。
| name | value |
| alex | 6 |
| brigid | 3 |
| cath | 9 |
| darlton | 6 |
| elenore | 4 |
これをvalueの高い順に並び替え、順位を付けたいとします。
| rank | name | value |
| 1 | cath | 9 |
| 2 | alex | 6 |
| 2 | darlton | 6 |
| 4 | elenore | 4 |
| 5 | brigid | 3 |
以下のようなクエリで実装できます。
SELECT ( SELECT COUNT(*) FROM `user` AS `tmp_user` WHERE `tmp_user`.`value` > `user`.`value` ) + 1 AS `rank` ,`user`.`name` ,`user`.`value` FROM `user` ORDER BY `rank` ASC
順位部分は「そのレコードのvalueより高いvalueをもつレコード数を求め、+1する」というロジックです。
もちろん昇順のランキングであれば不等号を逆にすればOK。
MySQLのバイナリログとフルバックアップから復旧させる方法
実現したいこと
- データベースがぶっ壊れても復旧できるようにしたい
- サーバーには4つのDBが入っているが、うち2つだけを対象にしたい
ざっくり調べて理解したこと
バイナリログを残す設定にする方法
- my.cnfに設定を追加
- ちなみに設定値はMySQLに「SHOW VARIABLES」クエリを打って確認することも可能です
vi /etc/my.cnf
[mysqld] # バイナリログの出力先設定 # （この設定なら、「/var/lib/mysql」ディレクトリに # 「bin.000000」という名前のバイナリファイルができる。数字部分は連番。増える。） log_bin = /var/lib/mysql/bin # バイナリログを出力する対象のDB # （この設定なら「test1」「test2」についてのみバイナリログが残る） binlog_do_db = test1 binlog_do_db = test2 ## 必要に応じて以下のような設定もできます ## バイナリログを保存する日数 ## （これより古いと自動削除） # expire_logs_days = 10 ## バイナリログの最大サイズ ## （最大というか、これを超えると次のファイルに移る感じ？） # max_binlog_size = 1024M
- mysqlを再起動
フルバックアップ保存する方法
ここでは2つのDBについて同時にバックアップを取りつつ、「--flush-logs」オプションを用いて現在のバイナリログをクローズして新しいバイナリログを生成してもらいます。
例えば最新のバイナリログが「bin.000001」であれば、バックアップ以降のログは「bin.000002」に記録されるようになります。
# ユーザーが「root」、バックアップするデータベースが「db1」「db2」 # 保存先が「backup.sql」 ※当然ですがフルパスなり相対パスなり適当に対応してください mysqldump -u root -p --databases db1 db2 --flush-logs > backup.sql
バイナリログを削除する方法
※今回のケースでは、バックアップの直後に過去のバイナリログをすべて削除するのを想定
まず、最新のバイナリログを確認します。
ls -l /var/lib/mysql
これで一番新しいやつをメモ。
MySQLからでも確認可能です。
SHOW MASTER STATUS
こっちの場合は一番新しいログの名前が出てくるのでそれをメモ。
MySQLにて下記クエリを流す。
-- 「bin.000011」の部分はさっきメモした最新のバイナリログ。 -- この場合は「bin.000010」以前がすべて消えます。 PURGE MASTER LOGS TO 'bin.000011';
バイナリログを流し込む方法
- まずはバイナリファイルを人間の読める形に展開する
# 「bin.000011」の中身を「recover.sql」に展開 mysqlbinlog --no-defaults --disable-log-bin bin.000011 > recover.sql # 複数のバイナリログが存在するときがネック。状況に応じて以下のような感じで # ワイルドカードで一つのファイルにまとめて突っ込むとか # （意図した順に記録されるかどうかは知りません） mysqlbinlog --no-defaults --disable-log-bin bin.0* > recover.sql # 並べてまとめて突っ込むとか # （意図した順に記録されるかどうかは知りません） mysqlbinlog --no-defaults --disable-log-bin bin.000011 bin.000012 > recover.sql # 素直に(？)並べるとか mysqlbinlog --no-defaults --disable-log-bin bin.000011 > recover_11.sql mysqlbinlog --no-defaults --disable-log-bin bin.000012 > recover_12.sql
- 展開したファイルを必要に応じて修正する
- 間違えてDELETEしてしまったところを削除するとか。viとかでOK
- 復元したDBに対してログを流し込む
mysql -u root -p < recover.sql
ざっくりSQL入門 #5：クエリを書く＜テーブル結合編＞
※MySQLを前提に書いています。一部関数などは方言差がある場合もあるのでご注意ください。
今回はテーブル結合のやりかたについて。
テーブル結合とは？
文字通り、二つのテーブルを結合します。
ここでは例えばツイッター的なサービスを想像してみましょう。
ものすごく単純化して、以下のようなテーブルがあるとします。
それぞれ、中身はこんな感じ。
| user_id | name |
| hoge | ほげほげ |
| fuga | ふがふが |
| tweet_id | user_id | text |
| 1 | hoge | そろそろ帰りたい |
| 2 | fuga | たのしいじんせい |
| 3 | hoge | 会社いきたくない |
| 4 | fuga | 圧倒的成長 |
| 5 | fuga | やっぱむり |
| 6 | piyo | ごはんおいしい |
これ、ツイッター的なサービスということで、タイムラインのようなものにしますよね。
そうすると、ツイートのようなものだけでなく、ユーザー名を併せて表示するのが普通ではないでしょうか。
しかしその場合、両方のテーブルのデータを使用していますから、
どうやってもひとつのテーブルを用いたSELECT構文では実現できません。
こういうときに使うのが「JOIN」句です。
JOINには「内部結合」と「外部結合」、そして「クロス結合」があるので、
それぞれについて記載していきます。
ざっくりSQL入門 #4：クエリを書く＜集計処理編＞
※MySQLを前提に書いています。一部関数などは方言差がある場合もあるのでご注意ください。
今回は集計系の処理について。
集計処理とは
簡単に言うと「○○ごとの○○」を集計することです。
例えば以下のようなケース。
- 商品テーブルについて、カテゴリごとの商品登録件数を知りたい
- 商品ごとの売り上げ金額合計を集計したい
- 1か月ごとの、会員登録件数の推移を見たい
基本の書き方
構文
SELECT 【件数や合計など】 FROM ... WHERE ... GROUP BY 【「○○ごと」のカラム名、複数ならカンマ区切り】
例文
-- 商品ID(product_id)ごとに、売上人数と売上金額の合計を集計する SELECT `product_id` ,COUNT(*) AS `count` -- 売上人数：レコード数のカウント ,SUM(`price`) AS `sum` -- 売上金額：priceカラムの値の合計 FROM `order_product` GROUP BY `product_id` -- 「商品IDごと」にまとめる
ざっくりSQL入門 #3：クエリを書く＜SELECT編＞
※MySQLを前提に書いています。一部関数などは方言差がある場合もあるのでご注意ください。
今回はSELECTクエリについて。
SELECTクエリとは
データベースに入っているレコードを抜き出すクエリです。ただそれだけ。
基本の書き方
構文
SELECT 【カラム名（複数ならカンマ区切り、「*」とだけ書けば全カラム）】 FROM 【テーブル名】 WHERE 【条件式（WHERE句自体を省略すると条件なし、つまりテーブル全件）】
例文
-- 「table_name」というテーブルから、「id」が1のレコードの、「id」「hoge」カラムを抜き出す SELECT `id`, `hoge` FROM `table_name` WHERE `id` = 1
MySQLで4バイトutf-8文字を扱う＆ファイルフォーマットを変更する
エントリをアップするの忘れてて今更感。
そもそも4バイトutf-8って何？
- 絵文字
- なんか特殊な漢字
が該当します。
4バイトutf-8文字の例
𣖔：𣖔木作（ほうのきざく、福島県の地名） 𣗄：𣗄代（たらのきだい、山形県の地名） 𩸽：ホッケ（おいしいさかな） 🍣：寿司（おいしい） 🍺：ビール（おいしい）
参考文献
JIS第3水準漢字一覧表【全1259字】(JIS X 0213:2004) - fragment.database.
JIS第4水準漢字一覧表【全2436字】(JIS X 0213:2004) - fragment.database.
「文字コード技術入門」こぼれ話: 「ほうの木作」を訪ねる - yanok.net
MySQLで扱えないの？
結論から言うと、文字セットを「utf8mb4_*」にすればOKです。
「utf8_*」では使えません。（INSERTしてみると、4バイト文字以降の部分が消えます）
接続方法、データベース、テーブル、カラム、すべてこの設定に統一しましょう。
何らかの理由で一部のみを変えたい場合、その動作はよく検証してください。
例えば、接続設定のみを変更した場合は4バイト文字が「?」に化けますが、それ以降の文字列は消えずに済みます。
接続設定とカラムのみの変更でも4バイト文字を保存することは可能ではあるようですが、
一時テーブル作成時、わざわざ文字セットを指定しない限りはデータベースの設定値が使用されるなど、
トラブルの原因になりがちです。
utf8mb4のデメリットはないの？
utf8_*系であれば1文字に割り当てられるサイズが3バイトですが、
utf8mb4_*系にすることで4バイトになります。
つまり文字列（特にCHAR型）のカラムやインデックスのサイズが大きくなったりします。
それは困る
困ると思う人はもうご存知な気もしますが、
ファイルフォーマットをAntelopeからBarracudaに変更することをお勧めします。
ちょっと何言ってるかわかんない
参考文献を貼っておきます。
InnoDBの制限とファイルフォーマットAntelopeとBarracudaの違い - かみぽわーる
MySQL InnoDBファイルフォーマットBarracudaへの設定方法 | Go-Nextブログ
Barracudaにすると何が良いのかというと、
- インデックスサイズ制限が拡張できる（innodb_large_prefix）
- 可変長カラムが外部ページに保存され、ローカルページにはそこへのポインタだけ保存される
- Antelopeでは先頭768バイトがローカルページに保存されていた。つまりBarracudaにすると軽くなる
まあ後者は4バイト文字とは関係ないんですが、前者が良いポイントですね。
元々767バイトという制限だったのが3072バイトまで拡張可能になるんです。
（そんな長い文字列にインデックス張るのがよくない、という話は置いといて）
utf8_*系では255文字のカラムに対してインデックスを張ろうとすると
「255文字×3バイト＝765バイト」で767バイト制限に引っかからなかったんですが、
これをutf8mb4_*系に変更するだけで1020バイトになり、エラーとなってしまうのです。
変更方法
データベースのデフォルト文字コードを変更
こちらは一発。
ALTER DATABASE 【データベース名】 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
テーブル・カラムの文字コードを変更
こちらはちょっと面倒。
（新規で作るときは、もちろんデータベースの設定さえしておけば無指定でOKなんですが）
後述のクエリやExcelを駆使して自動生成するのがたぶん一番楽。
ALTER TABLE 【テーブル名】 -- テーブルの文字コード DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci -- 以降はカラムの文字コード -- （型やコメントなども含める必要があるため注意） ,CHANGE 【カラム名1】 【カラム名1】 VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'カラム1' ,CHANGE 【カラム名2】 【カラム名2】 VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT 'カラム2' , ...
参考：作業時の確認用クエリ
データベースの文字コードを確認する
SELECT `SCHEMA_NAME` ,`DEFAULT_CHARACTER_SET_NAME` ,`DEFAULT_COLLATION_NAME` FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME` = '【データベース名】';
文字コードが「utf8」系になっているテーブルを抽出する
SELECT `TABLE_NAME` ,`TABLE_COLLATION` ,`TABLE_COMMENT` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` = '【データベース名】' AND `TABLE_COLLATION` LIKE 'utf8\_%';
文字コードが「utf8」系になっているカラムを抽出する
SELECT `TABLE_NAME` ,`COLUMN_NAME` ,`COLUMN_TYPE` ,`CHARACTER_SET_NAME` ,`COLLATION_NAME` ,`COLUMN_COMMENT` FROM `information_schema`.`COLUMNS` WHERE `TABLE_SCHEMA` = '【データベース名】' AND `CHARACTER_SET_NAME` = 'utf8';
SQLでビット演算
※MySQLを前提としています
// 結局実務に使うことはなく中途半端。気が向いたら加筆します
例えば「販売商品のカテゴリを複数設定可能にしたい」という場合。
- みかん（生鮮/果物）
- 冷凍ミカン（冷凍/果物）
- みかん缶詰（缶詰/果物）
- いわし缶詰（缶詰/魚）
- 冷凍マグロ（生鮮/冷凍/魚）
…みたいなね。
普段は以下のようにテーブル分けしています。
- 商品テーブル
- 商品ID(PK)
- 商品名
- カテゴリテーブル
- カテゴリID(PK)
- カテゴリ名
- 商品×カテゴリテーブル
- 商品ID(PK)
- カテゴリID(PK)
カテゴリがかなり重要な意味を持つ場合はこれでよいと思うのですが、
ちょっとした区分をくっつけたいだけの場合に大げさすぎる感じがしたので、
ビット演算で対応できないか試してみました。
結論としては、商品テーブルにカテゴリをくっつけるだけでいけます。
- 商品テーブル
- 商品ID
- 商品名
- カテゴリ
このカテゴリには、例えば2進数で「11000」のような値が入ります。
2進数で左の桁から順に「生鮮」「果物」「魚」「缶詰」「冷凍」のように意味を持たせ、
1の部分が有効としています。
つまり「11000」なら「生鮮」「果物」のカテゴリというわけです。
10進数に変換した値を入れてもよいのですが、
桁数などで問題がなければ、パッと見てわかりやすい2進数でよいのではないかと思います。
もう少し単純化して具体例を。
3タイプあり、右の桁からA・B・Cというカテゴリが割り当てられているものとします。
| type | 補足 |
| 000 | （いずれも該当しない） |
| 001 | A |
| 010 | B |
| 011 | AとB |
| 100 | C |
| 101 | AとC |
| 110 | BとC |
| 111 | AとBとC |
こういうtypeデータを持つテーブルに対して、
例えばカテゴリAを含むレコードを取得したい場合は「001」で論理積をとります。
どうせなら全パターン書きますか。
SELECT ... FROM ... WHERE `type` & 000; -- 何もヒットしない SELECT ... FROM ... WHERE `type` & 001; -- Aを含むレコード(001,011,101,111)がヒット SELECT ... FROM ... WHERE `type` & 010; -- Bを含むレコード(010,011,110,111)がヒット SELECT ... FROM ... WHERE `type` & 011; -- AかBを含むレコード(001,010,011,101,110,111)がヒット SELECT ... FROM ... WHERE `type` & 100; -- Cを含むレコード(100,101,110,111)がヒット SELECT ... FROM ... WHERE `type` & 101; -- AかCを含むレコード(001,011,100,101,110,111)がヒット SELECT ... FROM ... WHERE `type` & 110; -- BかCを含むレコード(010,011,100,101,110,111)がヒット SELECT ... FROM ... WHERE `type` & 111; -- AかBかCを含むレコード(000以外)がヒット
先述の通り分かりやすくするために2進数としていますが、10進数を使うこともできます。
SELECT ... FROM ... WHERE `type` & 0; -- 000と同じ SELECT ... FROM ... WHERE `type` & 1; -- 001と同じ SELECT ... FROM ... WHERE `type` & 2; -- 010と同じ ...
また、もちろん単純に一致を取ることもできます。
SELECT ... FROM ... WHERE `type` = 011; -- AとBのみを含むレコード(011)がヒット
mecabとPHPで形態素解析
そもそも形態素解析とは？
文章を「言語で意味を持つ最小単位」に分割し、それぞれの品詞等を判別する作業。
参考：形態素解析 - Wikipedia
形態素解析すると具体的にどうなるの？
元の文字列
吾輩は猫である。名前はまだ無い。 どこで生れたかとんと見当がつかぬ。何でも薄暗いじめじめした所でニャーニャー泣いていた事だけは記憶している。
形態素解析すると…
吾輩,は,猫,で,ある,。,名前,は,まだ,無い,。 どこ,で,生れ,た,か,とんと,見当,が,つかぬ,。,何,でも,薄暗い,じめじめ,し,た,所,で,ニャーニャー,泣い,て,いた事,だけ,は,記憶,し,て,いる,。
こうなります。
それはSQLのLIKEやPHPのstrposじゃだめなの？
機能としては、この文章に対して「はまだ」だとか「いじめ」だとかでヒットしても良ければOKです。
吾輩は猫である。名前はまだ無い。 どこで生れたかとんと見当がつかぬ。何でも薄暗いじめじめした所でニャーニャー泣いていた事だけは記憶している。
ただし、strposはともかく、LIKEについては速度面でも問題になる可能性があります。
（文章量やレコード数がそれほど多くない場合は問題にならない可能性もあります）
詳しくは過去に書きましたので参考にしてください。
MySQLでフリーワード検索 - アナログCPU：5108843109
どうやって使うの？
- mecabや辞書をインストールする
- 公式github MeCab: Yet Another Part-of-Speech and Morphological Analyzer
- その他詳細はググってください（気が向いたら書きます）
- 単語に分割するには
$keyword_list = mecab_split("形態素解析を行いたい文章");
// $keyword_list は以下のようになる
// array(
// "形態素",
// "解析",
// "を",
// "行い",
// "たい",
// "文章",
// )
- カタカナへの変換もできたりします（ひらがなに変換するならmb_convert_kanaを併用で）
$mecab = new MeCab_Tagger(array('-O' => 'yomi'));
$kana_string = $mecab->parse("カナ変換を行いたい文章")
// $kana_string は以下のようになる
// カナヘンカンヲオコナイタイブンショウ
問題点はないの？正確に動くの？
- 人名やアニメ作品名などの固有名詞に弱いです。辞書登録することで解決する場合もあります。
- 形態素解析とかな変換を併用する場合は精度が落ちがちになります。
例） 元の文章 ：東京都新宿区西新宿五丁目 形態素解析のみ：東京,都,新宿,区,西新宿,五,丁目 かな変換のみ ：とうきょうとしんじゅくくにししんじゅくごちょうめ 形態素解析したものをかな変換：とうきょう,と,しんじゅく,く,にししんじゅく,ご,ひのとめ かな変換したものを形態素解析：とうき,ょうとしんじゅくくにししんじゅくごちょうめ
