[MySQL]文字数のカウントはchar_length()、バイト数の長さはlength()

Read More



MySQLで文字数のカウントを調べる場合はchar_length()という関数を使う。
バイト数の長さを調べる場合はlength()という関数を使う。


使い方は引数にカラム名を指定する、あるいは特定の文字列を直接指定するなどの使い方がある。

■例:char_length()とlength()の使い方
SELECT 
  CHAR_LENGTH('あ') AS char_lengh, 
  LENGTH('あ') AS length 
FROM 
  (SELECT 1) AS dummy 
;


■結果
+----------------------+
| char_length | length |
+----------------------+
| 1           | 3      |
+----------------------+


自分の環境はcharsetがUTF8なのでlength('あ')が3バイトになっている。


またJIS第3・第4水準(JIS X 0213:2004)の漢字の一部は4バイト文字となる場合があるので、
そういったものを調べるのにもlength()は使えたりする。


参考までに4バイト文字を以下に書いておく。
𠀋 𡈽 𡌛 𡑮 𡢽 𠮟 𡚴 𡸴 𣇄 𣗄 𣜿 𣝣 𣳾 𤟱 𥒎 𥔎 𥝱 𥧄 𥶡 𦫿 𦹀 𧃴 𧚄 𨉷 𨏍 𪆐 𠂉 𠂢 𠂤 
𠆢 𠈓 𠌫 𠎁 𠍱 𠏹 𠑊 𠔉 𠗖 𠘨 𠝏 𠠇 𠠺 𠢹 𠥼 𠦝 𠫓 𠬝 𠵅 𠷡 𠺕 𠹭 𠹤 𠽟 𡈁 𡉕 𡉻 𡉴 𡋤 
𡋗 𡋽 𡌶 𡍄 𡏄 𡑭 𡗗 𦰩 𡙇 𡜆 𡝂 𡧃 𡱖 𡴭 𡵅 𡵸 𡵢 𡶡 𡶜 𡶒 𡶷 𡷠 𡸳 𡼞 𡽶 𡿺 𢅻 𢌞 𢎭 
𢛳 𢡛 𢢫 𢦏 𢪸 𢭏 𢭐 𢭆 𢰝 𢮦 𢰤 𢷡 𣇃 𣇵 𣆶 𣍲 𣏓 𣏒 𣏐 𣏤 𣏕 𣏚 𣏟 𣑊 𣑑 𣑋 𣑥 𣓤 𣕚 
𣖔 𣘹 𣙇 𣘸 𣘺 𣜜 𣜌 𣝤 𣟿 𣟧 𣠤 𣠽 𣪘 𣱿 𣴀 𣵀 𣷺 𣷹 𣷓 𣽾 𤂖 𤄃 𤇆 𤇾 𤎼 𤘩 𤚥 𤢖 𤩍 
𤭖 𤭯 𤰖 𤴔 𤸎 𤸷 𤹪 𤺋 𥁊 𥁕 𥄢 𥆩 𥇥 𥇍 𥈞 𥉌 𥐮 𥓙 𥖧 𥞩 𥞴 𥧔 𥫤 𥫣 𥫱 𥮲 𥱋 𥱤 𥸮 
𥹖 𥹥 𥹢 𥻘 𥻂 𥻨 𥼣 𥽜 𥿠 𥿔 𦀌 𥿻 𦀗 𦁠 𦃭 𦉰 𦊆 𦍌 𣴎 𦐂 𦙾 𦚰 𦜝 𦣝 𦣪 𦥑 𦥯 𦧝 𦨞 
𦩘 𦪌 𦪷 𦱳 𦳝 𦹥 𦾔 𦿸 𦿶 𦿷 𧄍 𧄹 𧏛 𧏚 𧏾 𧐐 𧑉 𧘕 𧘔 𧘱 𧚓 𧜎 𧜣 𧝒 𧦅 𧪄 𧮳 𧮾 𧯇 
𧲸 𧶠 𧸐 𧾷 𨂊 𨂻 𨊂 𨋳 𨐌 𨑕 𨕫 𨗈 𨗉 𨛗 𨛺 𨥉 𨥆 𨥫 𨦇 𨦈 𨦺 𨦻 𨨞 𨨩 𨩱 𨩃 𨪙 𨫍 𨫤 
𨫝 𨯁 𨯯 𨴐 𨵱 𨷻 𨸟 𨸶 𨺉 𨻫 𨼲 𨿸 𩊠 𩊱 𩒐 𩗏 𩙿 𩛰 𩜙 𩝐 𩣆 𩩲 𩷛 𩸽 𩸕 𩺊 𩹉 𩻄 𩻩 
𩻛 𩿎 𪀯 𪀚 𪃹 𪂂 𢈘 𪎌 𪐷 𪗱 𪘂 𪘚 𪚲
※Windows Vista以降の環境なら表示される。まさか今時XPもいまい


なおMySQLで4バイト文字を格納する場合はutf8mb4という文字コードを使う必要がある。

テーブルやcharsetにこれを指定すると4バイト文字が扱える。
この状態だとSQLエディタのMySQL Workbenchでは特に改めて設定などせずともその文字を扱えた。
しかしCSEなんかだと文字化けを起こしていた。
ここでは4バイトについてこれ以上触れないが参考リンクのみ残しておく。
■PHP+MySQL5.5.24 utf8mb4奮闘記。 - erio_nk://memo
http://d.hatena.ne.jp/erio_nk/20120523/1337757900



あと自分の場合はlength()はあまり利用しないがchar_length()はちょいちょい使う。
例えば特定カラムの文字列が30文字以上のレコードだけを抽出したいときなど。

■例:特定カラムの文字列が30文字以上のレコードのみを抽出するSQL
SELECT 
  {column_name} AS clm, 
  CHAR_LENGTH(AAA) AS mblen 
FROM 
  {table_name} 
WHERE 
  CHAR_LENGTH(AAA) >= 30 
;
※{table_name}は任意のテーブル名、{column_name}は任意のカラム名


■結果
+--------------+
| clm  | mblen |
+--------------+
| {...}| 30    |
| {...}| 40    |
| {...}| 45    |
| {...}| 32    |
+--------------+
※{...}は表示上省略



あと文字列の長さ繋がりでいくと、
30文字以上の文字列の長さをもったレコードに対して文字列を一定の長さに揃える(はみ出た文字列を切り捨てる)ことがある。
これもついでにメモとして残しておく。

■例: 文字列を20文字に揃えるSQL
UPDATE 
  {table_name} 
SET 
  {column_name} = LEFT({column_name}, 30) 
;


心配な人はwhere句でchar_length()を使って30文字以上、と条件を付けてもよい。
付けても付けなくても結果は変わらないが。
あとleft()やsubstring()の長さ指定はカラムの型に依存するので注意すること。
カラムの型がchar型やvarchar型の場合は長さ指定が文字単位となるがbinary型の場合はバイト単位となる。


カラムの型に依存せずにバイト単位で切り捨てを行いたい場合はcast()binary()を使う。

■例: バイト単位で文字を切り捨て
SELECT 
  CAST(LEFT(CAST({column_name} AS BINARY), 90) AS CHAR), 
  CAST(LEFT(BINARY {column_name}, 90) AS CHAR), 
FROM 
  {table_name} 
;


上記はどちらも同じ結果を返す。

最初にcast()でバイナリ型にキャストし、
その状態でleft()で90バイトまでを取得、
その後改めてcast()で文字列に戻している。

これだとバイト単位で切り捨てることができる。

ただし日本語を扱う場合は2バイト文字などがあるので文字化けに注意が必要となる。
そして英語などの1バイト文字だけの場合はバイト単位で切り捨てる必要がない。
ということでバイト単位で切り捨てるというのは使いどころがあまりないかもしれない。




--
元々PostgreSQL使いだった自分も気が付けばMySQLでの開発の方が長くなっている。

昔のMySQL(Ver.3.0頃)はホントに使えないという代物だったが今は割とちゃんと使えるようになったなぁという印象。


まあ個人でやる分にはSQLiteで十分なんだけど。
先述したMySQLのようにutf8mb4なんて指定せずとも普通に4バイト文字が扱えるわけで。