MySQL/データマッチング時に、半角全角とかをいい感じにするストアドファンクション #MySQL

概要

他のDBからデータを移管した際に、移管データが正しく登録されているかチェックする時のメモ。
元データをそのまま移行していれば苦労なくマッチングはできますが、元データをフォーマットなり何なりをしている時に面倒が起きます。

元データとインポート先で全角、半角が違う
全角英語、半角英語、全角数字、半角数字、半角カナ、全角カナなど
半角スペース、全角スペースやその位置など
全角ハイフン、半角ハイフン、全角ダッシュ、全角記号など

これらをマッチングさせるのはなかなか骨が折れるので、 差分になりそうな文字を置換するファンクションを作りました。
ベースはMySQL 半角カタカナを全角に変換するストアドファンクションを参考にさせてもらいました。

詳細

ストアドファンクション

multibyte2cv

CREATE DEFINER = CURRENT_USER FUNCTION `multibyte2cv`(`str` text)
 RETURNS text(0) CHARSET utf8
BEGIN
  -- 全角英語、半角英語
  DECLARE eng_len INT(2);
  DECLARE eng_z VARCHAR(52) DEFAULT 'ａｂｃｄｅｆｇｈｉｊｋｌｍｎｏｐｑｒｓｔｕｖｗｘｙｚＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ';
  DECLARE eng_h VARCHAR(52) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';

  -- 半角カナ、全角カナ
  DECLARE kana1_len, kana2_len INT(2);
  DECLARE kana1_h VARCHAR(61) DEFAULT 'ｱｲｳｴｵｶｷｸｹｺｻｼｽｾｿﾀﾁﾂﾃﾄﾅﾆﾇﾈﾉﾊﾋﾌﾍﾎﾏﾐﾑﾒﾓﾔﾕﾖﾗﾘﾙﾚﾛﾜｦﾝｯｬｭｮｧｨｩｪｫｰ｡｢｣､･';
  DECLARE kana1_z VARCHAR(61) DEFAULT 'アイウエオカキクケコサシスセソタチツテトナニヌネノハヒフヘホマミムメモヤユヨラリルレロワヲンッャュョァィゥェォー。「」、・';
  DECLARE kana2_h VARCHAR(52) DEFAULT 'ｶﾞｷﾞｸﾞｹﾞｺﾞｻﾞｼﾞｽﾞｾﾞｿﾞﾀﾞﾁﾞﾂﾞﾃﾞﾄﾞﾊﾞﾋﾞﾌﾞﾍﾞﾎﾞﾊﾟﾋﾟﾌﾟﾍﾟﾎﾟｳﾞ';
  DECLARE kana2_z VARCHAR(26) DEFAULT 'ガギグゲゴザジズゼゾダヂヅデドバビブベボパピプペポヴ';

  -- 全角数字、半角数字
  DECLARE int_len INT(2);
  DECLARE int_z VARCHAR(10) DEFAULT '１２３４５６７８９０';
  DECLARE int_h VARCHAR(10) DEFAULT '1234567890';

  -- 全角記号、半角記号（お好みで調整）
  DECLARE symbol_len INT (2);
  DECLARE symbol_z VARCHAR(10) DEFAULT 'ー―－，．：；（）＆';
  DECLARE symbol_h VARCHAR(10) DEFAULT '---,.:;()&';

  -- 全角英語から半角英語に変換
  SET eng_len = CHAR_LENGTH(eng_h);
  WHILE eng_len > 0 DO
    SET str = REPLACE(str, SUBSTRING(eng_z,eng_len,1), SUBSTRING(eng_h,eng_len,1));
    SET eng_len = eng_len - 1;
  END WHILE;

  -- 半角カナから全角カナに変換
  SET kana1_len = CHAR_LENGTH(kana1_z);
  SET kana2_len = CHAR_LENGTH(kana2_z);
  WHILE kana2_len > 0 DO
    SET str = REPLACE(str, SUBSTRING(kana2_h,kana2_len*2-1,2), SUBSTRING(kana2_z,kana2_len,1));
    SET kana2_len = kana2_len - 1;
  END WHILE;
  WHILE kana1_len > 0 DO
    SET str = REPLACE(str, SUBSTRING(kana1_h,kana1_len,1), SUBSTRING(kana1_z,kana1_len,1));
    SET kana1_len = kana1_len - 1;
  END WHILE;

  -- 全角数字から半角数字に変換
  SET int_len = CHAR_LENGTH(int_z);
  WHILE int_len > 0 DO
    SET str = REPLACE(str, SUBSTRING(int_z,int_len,1), SUBSTRING(int_h,int_len,1));
    SET int_len = int_len - 1;
  END WHILE;

  -- 全角ハイフン系を半角ハイフンに変換
  SET symbol_len = CHAR_LENGTH(symbol_z);
  WHILE symbol_len > 0 DO
    SET str = REPLACE(str, SUBSTRING(symbol_z,symbol_len,1), SUBSTRING(symbol_h,symbol_len,1));
    SET symbol_len = symbol_len - 1;
  END WHILE;

  -- 半角スペース、全角スペース、改行を削除
  SET str = REPLACE(REPLACE(REPLACE(REPLACE(str, '　', ''), ' ', ''), CHAR(13), ''), CHAR(10), '');

  RETURN str;
END;

やっていること

半角カナは全角に変換
全角英数は半角に変換
全角ハイフン系は全部半角ハイフンに変換お好みで記号追加で変換
半角スペース、全角スペース、改行は削除

使い方

ストアドファンクションを登録して、対象のカラムをSELECTする時に使用します。
例えば住所データとかはいろんなデータがあるので、以下のようにやるととても楽にマッチングできます。

SELECT
  multibyte2cv(`オリジナルの住所`) AS addr_org,
  multibyte2cv(`インポートした住所`) AS addr
FROM
  example_table
HAVING
  addr_org <> addr;

補足

パフォーマンスは非常に悪いです
ローカルDBで実行する用と思っていただければと。
記号系の置換はお好みでカスタマイズしてください
文字コードなどの調整は適宜してください