SQL に関する投稿を表示しています

CREATE TABLE 文から Markdown のテーブル表記をつくる

ドキュメントにテーブルの説明を書くのがちょっと苦しかったので CREATE TABLE 文から Markdown のテーブル表記を作れるようにした。

GitHub - sters/createtable2Markdown

CREATE TABLE 文をパースできれば勝てるので別に MySQL 限定とかではない。特殊すぎる記載じゃなければ動くと思う。

GitHub - xwb1989/sqlparser: SQL Parser implemented in Go

これがすごくて、若干の挙動が怪しい不要な要素をスルーするようにしてるけれども、あとはパース結果を組み立てる以外なにもしてない。

標準入力で SQL な文字列を受け取って、標準出力で Markdown が出せるようにしているので、ダンプしたものをそのまま流したり、クリップボードを活用したり、コマンドラインでサクサク使うのにめちゃいい。
Readme にも書いたけどこういうことができる。

mysqldump -u database_user -p -d -n --compact database_name | createtable2Markdown

MySQL の文字コードについてチョット調べた

この記事は公開されてから1年以上経過しており、情報が古い可能性があります。

MySQL 使ってて utf8_unicode_ci や utf8_general_ci とか mb4 …? とプチ事故が起きたので解消するべく調べた。

照合順序とは

「utf8_general_ci」といった文字コードの指定してるっぽいやつは、文字列カラムにおけるコレーション(=照合順序)と呼ばれる。ここで指定された値によって、MySQL が文字列を解釈したり比較を行うようになっている。

表記は 文字コード_文字セット名_比較方法(※特殊なものとして *_bin もある)

  • 文字コード
    • utf8mb4 とか utf8 とか cp932 とか
  • 言文字セット名
    • japanese とか german とか
    • Unicodeに限り、japanese はないので general か unicode か bin を選ぶ。
      • (言語によって文字の特殊性があるらしく、その言語別の定義があるが日本語はない)
  • 比較方法
    • 3種類あある
    • ci
      • 大文字小文字を比較しない
    • cs
      • 大文字小文字を比較する
    • bin
      • バイナリとして判断する
    • よく使う utf8... では ci と bin のみになる…?(何でー?)

どんな照合順序が使えるかを確認する

show collation というクエリを実行することで確認できる。

show collation;
Collation Charset Id Default Compiled Sortlen
big5_chinese_ci big5 1 Yes Yes 1
big5_bin big5 84 Yes 1
dec8_swedish_ci 8-Dec 3 Yes Yes 1
dec8_bin 8-Dec 69 Yes 1
cp850_general_ci cp850 4 Yes Yes 1
cp850_bin cp850 80 Yes 1
hp8_english_ci hp8 6 Yes Yes 1
hp8_bin hp8 72 Yes 1
koi8r_general_ci koi8r 7 Yes Yes 1
koi8r_bin koi8r 74 Yes 1
latin1_german1_ci latin1 5 Yes 1
latin1_swedish_ci latin1 8 Yes Yes 1
latin1_danish_ci latin1 15 Yes 1
...

※ like など SQL を書くときに使うような演算子を利用して絞り込みができたり、 ORDER BY 並び順を設定できる。

show collation where Collation like 'utf8%';
Collation Charset Id Default Compiled Sortlen
utf8_general_ci utf8 33 Yes Yes 1
utf8_bin utf8 83 Yes 1
utf8_unicode_ci utf8 192 Yes 8
utf8_icelandic_ci utf8 193 Yes 8

MySQL サーバーのバージョンによって使える照合順序が違うので、環境によって結果が変わることがある。(古いと utf8mb4 がないとかそういう) 5.6, 5.7 あたりならこの記事の話は問題ないはず。詳しい一覧はここで確認ができる。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 10.1.14 MySQL でサポートされる文字セットと照合順序

*_unicode_ci と *_general_ci の違い

照合順序の違いによってどんなことが起こるか、実際にクエリを発行しつつ確認する。

# バージョン確認
select version();
# 5.7.17


# アルファベットの大文字小文字、半角全角
#============================================================
SET NAMES 'utf8' COLLATE 'utf8_unicode_ci';
select 'A' = 'a', 'A' = 'A', 'A' = 'a';
# 1, 1, 1
 
SET NAMES 'utf8' COLLATE 'utf8_general_ci';
select 'A' = 'a', 'A' = 'A', 'A' = 'a';
# 1, 0, 0
 
SET NAMES 'utf8' COLLATE 'utf8_bin';
select 'A' = 'a', 'A' = 'A', 'A' = 'a';
# 0, 0, 0
 
 
# ひらがな, 半角カタカナ + 半濁音
#============================================================
SET NAMES 'utf8' COLLATE 'utf8_unicode_ci';
select 'は' = 'パ';
# 1
 
SET NAMES 'utf8' COLLATE 'utf8_general_ci';
select 'は' = 'パ';
# 0
 
SET NAMES 'utf8' COLLATE 'utf8_bin';
select 'は' = 'パ';
# 0

この結果をまとめると。

半角全角の比較 大文字小文字の比較 ひらがなカタカナの比較
unicode_ci できない できない できない
general_ci できる できない できる
bin できる できる できる

というわけで general_ci か bin を使うようにするとこのあたりの比較がちゃんと出来てよさそう。

utf8 と utf8mb4 の違い

MySQL の UTF-8 は 3 バイトの幅しかなく、絵文字などを表現するためには 4 バイトが必要。きっと歴史的な背景から utf8 と utf8mb4 が分裂している。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 10.1.10.7 utf8mb4 文字セット (4 バイトの UTF-8 Unicode エンコーディング)

先程と同じように文字コードを指定して比較していく。

SET NAMES 'utf8' COLLATE 'utf8_unicode_ci';
select 'あ' = '?', '?' = '?';
# 0, 1

SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';
select 'あ' = '?', '?' = '?';
# 0, 1

SET NAMES 'utf8' COLLATE 'utf8_general_ci';
select 'あ' = '?', '?' = '?';
# 0, 0

SET NAMES 'utf8mb4' COLLATE 'utf8mb4_general_ci';
select 'あ' = '?', '?' = '?';
# 0, 1

SET NAMES 'utf8' COLLATE 'utf8_bin';
select 'あ' = '?', '?' = '?';
# 0, 0

SET NAMES 'utf8mb4' COLLATE 'utf8mb4_bin';
select 'あ' = '?', '?' = '?';
# 0, 0

SET NAMES 'utf8' COLLATE 'utf8_unicode_520_ci';
select 'あ' = '?', '?' = '?';
# 0, 1

SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_520_ci';
select 'あ' = '?', '?' = '?';
# 0, 0

// あれ、このあたり文字化けしてるかも…? 
// あ=絵文字1, 絵文字1=絵文字2; と絵文字を比較してます。。

むむ、結構厄介。絵文字が入力される可能性があり、ちゃんと識別したい場合は utfmb4_bin を使うのがよさそう。

参考リンク

utf8_unicode_ci に対する日本の開発者の見解 - かみぽわーる
MySQL と寿司ビール問題 - かみぽわーる