マルチバイト文字列関数の罠
ある時、CSVのデータをインポートする処理で不具合が発生。
「2件のデータをまとめてインポートすると1件のみ失敗して、
成功したデータを除いて再度その1件のみをインポートすると成功した」
いやいやそんなアホな。
色々調べた結果、この結論に至りました。
「マルチバイト文字列関数を使用するときは文字コードを指定した方が安全かもしれない」
…うーん…。
今でも微妙に納得いかないのですが。
ちなみに、当該箇所の処理はこうです。
- CSVを取り込む
- 改行区切りで1セットのデータに分割
- カンマ区切りでデータ内の各項目に分割して文字配列にする
- ある項目を全角数字から半角数字に変換（mb_convert_kanaを使用）
- 他項目も含めいろいろ調整したデータをDBに入れたりいろいろする
…という感じなのですが、どうやら全角→半角にする過程で誤変換されたようです。
有効な半角数字にならず、データ不備としてはじかれた、と。
何故か、その2件のデータを一緒に取り込んだ時のみ、片方だけ。
なので特定の文字列が誤変換されるというわけではないですし、問題なく複数レコードが取り込めることだってありました。
（開発時にも、もちろんテストしましたし…）
ともかく、
＞全角数字から半角数字に変換（mb_convert_kanaを使用）
これを
$hoge = mb_convert_kana($hoge, "a")
と書いていたのを
$hoge = mb_convert_kana($hoge, "a", "UTF-8")
こうすると改善されました。
誤変換しない場合の方が圧倒的に多いというのがネックですね…。
テストで気付きにくいですし。
他のマルチバイト系関数を使用する際も、文字コードが指定できるものはした方が安全かもしれません。
関係ありそうでしかもちょくちょく使う関数一覧
（リンクは公式マニュアル）
- mb_convert_kana
- 半角・全角間の変換を行う
- mb_strstr / mb_stristr / mb_strpos / mb_strrpos / mb_stripos / mb_strripos
- 文字列Aの中に文字列Bが最初(または最後)に現れる位置を探す
- mb_strlen
- 文字列の文字数を返す
- mb_substr
- 文字列の一部を返す
- mb_strtolower / mb_strtoupper
- 文字列をすべて小文字(または大文字)にする
文字列のトリム（左端・右端にある特定の文字列を削除 ）
以下MySQLで確認していますが、Oracle・PostgreSQLでも同じ書き方である模様。
例えば都道府県欄に「○○県」と入っており「県」は除いて表示したい場合など、は以下のようにします。
SELECT TRIM(オプション 削除する文字列 FROM フィールド名) FROM テーブル名
オプションは、
両端から削除：BOTH （※省略可）
左端から削除：LEADING
右端から削除：TRAILING
となります。
-- 「○○県」から右端の「県」を取り除いて表示 SELECT TRIM(TRAILING '県' FROM `pref`) FROM `pref_master`
【ネタ】手作りソート
何やら「A1:A10に入力された数値データを昇順に並び変えてB1:B10に格納せよ」という学校の課題的なお題を頂いたので作ってみました。
もちろんソート機能は使用禁止です。
（つまり実務で使う意味は皆無のネタコードです）
Option Explicit 'ソート範囲 Const SORT_RANGE As String = "A1:A10" Const RESULT_RANGE As String = "B1:B10" 'ソート個数 Const SORT_COUNT As Long = 10 '難しいことを考えるのは面倒なので、 '一番小さい数字を見つけたら一番上に入れて、 '残りの数字からさらに一番小さい数字を見つけたら…を繰り返すソートにします。 Public Sub Main() 'ワークシートを定義 Dim wsSheet As Worksheet Set wsSheet = ThisWorkbook.Sheets("Sheet1") 'ソート範囲内の数字を配列にぶちこむ 'この方法だと1列しかなくても二次元配列になる。 '※(1,1)～(10,1) Dim lNumList As Variant lNumList = wsSheet.Range(SORT_RANGE) '一番小さい数字を見つけて並べ替えるのを繰り返す Dim i As Long Dim j As Long Dim lTmpNum As Long Dim lTmpAdd As Long For i = 1 To SORT_COUNT - 1 '一番小さい数字を探す lTmpNum = lNumList(i, 1) '仮数字に先頭の数字をセット lTmpAdd = i '仮数字の座標をセット For j = i + 1 To SORT_COUNT If lNumList(j, 1) < lTmpNum Then '仮数字よりも小さいものを見つけたらそっちを仮数字に lTmpNum = lNumList(j, 1) '元の一番小さい数字と新しい一番小さい数字を入れ替える lNumList(j, 1) = lNumList(lTmpAdd, 1) lNumList(lTmpAdd, 1) = lTmpNum End If Next 'この時点でlTmpNumに入っているものが一番小さい 'それ以外でまた並べ替える Next ' 結果をワークシートに書き込む wsSheet.Range(RESULT_RANGE) = lNumList End Sub
ワークシートの最大行数、最大列数を取得
最大行数は Rows.Count
最大列数は Columns.Count
で取得できます。
' 使用例(1)：「Sheet1」シートの全セルのデータや書式設定を削除 With ThisWorkbook.Sheets("Sheet1") Range(Cells(1, 1), Cells(Rows.Count, Columns.Count)).Clear End With ' 使用例(2)：可変値が範囲外にならないようなエラーチェック Dim lRow As Long Dim lCol As Long lRow = ～～ lCol = ～～ If (0 < lRow And lRow <= Rows.Count) And (0 < lCol And lCol <= Columns.Count) Then ThisWorkbook.Sheets("Sheet1").Cells(lRow, lCol).Value = "hoge" Else MsgBox ("範囲外やで") End If
ボタンひとつで簡易grep
何度もgrepをかけるとき、いちいちウインドウ出てくることや
検索フォルダが「今開いているファイルの位置」になるのが非常に鬱陶しいので、
「あらかじめ指定されたフォルダ以下にて今選択している文字列をgrep」するマクロを作りました。
ショートカットキー割り当てておくと非常に楽です。
私は折り返し位置を81文字に設定しているので、
grep後に調整しています（コメントアウトしている部分）。
//grepフォルダ $curdir = "C:\\hoge\\hage\\"; //grep対象ファイル(拡張子とか) $filter = "*.c;*.cpp;*.h"; //選択範囲の文字列を取得 $word = gettext(seltopx,seltopy,selendx,selendy); //grep grep $word,$filter,$curdir,icon,subdir; ////折り返し位置変更 //config "w2000";
…という記事を旧ブログから移行してきたのですが、今読むと「ねぇよそんな作業」って感じです…。
かつては何故かすんげえ頻度ですんげえ大量にgrepする現場にいたので…。
VBAでセルの結合
セルを結合するには、MeageCellsプロパティを用います。
' A1～C3のセルを結合 Range("A1:C3").MergeCells = True '現在選択している範囲を結合 Selection.MergeCells = True
ここで、結合したいセルのうち2つ以上にデータが入っていると、警告が表示されます。
これを無視して(表示させずに)結合したい場合は、
あらかじめ「Application.DisplayAlerts」をFalseに設定しておけばOKです。
Application.DisplayAlerts = False Selection.MergeCells = True Application.DisplayAlerts = True
また、逆にセルの結合を解除したい場合は、もちろんMergeCellsプロパティをFalseに設定すればOKです。
頭文字検索
※MySQLを前提として書いています
用語集みたいなやつを作っていて、「あ行」「か行」…「英数字」という頭文字検索があったので、
調べてみたところLIKEかREGEXPが使えそうかなと思ったのですが…
LIKE
→複雑な正規表現は使用不可
→日本語に対応
でした。
日本語対応版REGEXPであるMREGEXPというものもあるのですが、
会社の環境なのでインストールはちょっと…。
参考
mregexp - MySQLで日本語の正規表現を扱う
LIKEを使うとしたら
-- あ行 SELECT * FROM `table` WHERE `yomi` LIKE 'あ%' OR `yomi` LIKE 'い%' OR `yomi` LIKE 'う%' OR `yomi` LIKE 'え%' OR `yomi` LIKE 'お%' -- 英数字 SELECT * FROM `table` WHERE `yomi` LIKE '0%' OR `yomi` LIKE '1%' -- (中略) OR `yomi` LIKE 'a%' OR `yomi` LIKE 'b%' -- (中略) OR `yomi` LIKE 'z%'
…これはひどい。
REGEXPを使うなら
-- あ行 SELECT * FROM `table` WHERE `hoge` REGEXP '^(あ|い|う|え|お)' -- 英数字 SELECT * FROM `table` WHERE `hoge` REGEXP '^([0-9]|[a-z])'
という形になるはず。
ダメもとで試してみたところ、
例えば「あ行」の検索だと、確かに「あ～おのいずれかで始まる単語」はすべてヒットするのですが、
他の一部の単語もヒットしてしまいました。ほんとにダメかー。
ということで、日本語頭文字はLIKE検索・英数字はREGEXP検索、とすることにしました。
MREGEXPが使用可能ならこちらを使ってもよいと思いますが、LIKEの方が速度は速いようです。
データ量や正規表現の有無を考えて、MREGEXPにしろREGEXPにしろ、ご利用は計画的に。
あとは、根本的な対策として、インデックス用フィールドみたいなのを作っても良いかもしれませんね。
「あ行」「か行」みたいなのを読みから判断するのではなく、それ自体を持たせる形で。
フィールドの順序を変更する
※MySQLを前提として書いています
あるテーブルで、フィールドが「field_a」「field_b」がこの順に存在し、これを入れ替えたいときは以下のようにします。
ALTER TABLE テーブル名 MODIFY COLUMN `field_a` 型名 AFTER `field_b`;
テーブルの再構築
※MySQLを前提として書いています
いつもは速いクエリが突然やたらと遅くなったので調べてみると、何故か適切なインデックスが使用されなくなっていた模様。
とりあえずインデックスを一旦削除して張り直すと戻りました。
（FORCE INDEX なんかも知ってはいますが、リリース済みのコードを直すのは(社内的に)面倒なので）
アホみたいにレコード数の多いテーブルなので定期メンテナンスできないか調べたところ、どうも空の ALTER TABLE を発行すると良さそう。
ALTER TABLE `テーブル名` ENGINE エンジン;
とりあえず開発環境の同一構成テーブル（InnoDB、インデックスは11個）で試してみたところ、
約4万件に対して約2.8秒。
ちなみに本番環境は約500万件なので、同一構成のテーブルをもう一つ作成し
テストレコードをそのくらい入れて試してみたところ、15分。
結構きますね。
さらに全く別のテーブルでも試してみました。
約56万件、MyISAM、フルテキストインデックスあり。
1レコードあたりのサイズが大きい上に、フルテキストインデックスということでやはりかなりの時間がかかるようで
2時間近くかかりました。
…ということで定期メンテナンスが導入されたのですが、
空ALTERを発行することで逆に適切なインデックスが使われなくなってしまう箇所があり、
そこだけ一旦削除→再作成というステップを踏む羽目になっているのでした。なんでや。
