MySQL のストアドで urlencode/urldecode する

データベースにurlエンコードされたままの文字列が入ってしまって困っていたのですが、ストアドを使えば比較的容易にデコードすることが出来そうだったので試してみました。MySQL は10年くらい前はストアドが使えないデータベースだったのですが、いまやちゃんと使えるんですね。ちなみにこの手のストアドはググると沢山みつかるのですが、今回は UTF-8 のエンコード、デコードができるものについてメモしておきます。

ストアドの定義が完了すると、こんな感じでエンコードデコードができるようになります。

mysql> select 'テスト', urlencode('テスト'), urldecode(urlencode('テスト')), urldecode('%e3%83%86%e3%82%b9%e3%83%88');
+-----------+-----------------------------+-----------------------------------+------------------------------------------+
| テスト | urlencode('テスト')      | urldecode(urlencode('テスト')) | urldecode('%e3%83%86%e3%82%b9%e3%83%88') |
+-----------+-----------------------------+-----------------------------------+------------------------------------------+
| テスト | %E3%83%86%E3%82%B9%E3%83%88 | テスト                         | テスト                                |
+-----------+-----------------------------+-----------------------------------+------------------------------------------+
1 row in set (0.00 sec)

もちろんperlやphpでプログラムを書く手もあるのですが、ちょっとした処理をするときにはこれは便利ですね。
具体的なストアドは下記にメモしておきます。

† urldecode()

urlencode/urldecode as MySQL stored functions [sql] [mysql] [urlencode] [urldecode]

DROP FUNCTION IF EXISTS urldecode;

DELIMITER |

CREATE FUNCTION urldecode (s VARCHAR(4096)) RETURNS VARCHAR(4096)
DETERMINISTIC 
CONTAINS SQL 
BEGIN
       DECLARE c VARCHAR(4096) DEFAULT '';
       DECLARE pointer INT DEFAULT 1;
       DECLARE s2 VARCHAR(4096) DEFAULT '';
       DECLARE h3 VARCHAR(4096) DEFAULT '';

       IF ISNULL(s) THEN
          RETURN NULL;
       ELSE
       SET s2 = '';
       WHILE pointer <= LENGTH(s) DO
          SET c = SUBSTR(s,pointer,1);
          IF c = '+' THEN
             SET h3 = '20';
          ELSEIF c = '%' AND pointer + 2 <= LENGTH(s) THEN
                   SET h3 = UPPER(SUBSTR(s,pointer+1,2));
                   SET pointer = pointer + 2;
          ELSE
                   SET h3 = HEX(c);
          END IF;
          SET s2 = CONCAT(s2,h3);
          SET pointer = pointer + 1;
       END while;
       END IF;
       RETURN UNHEX(s2);
END;
 
|

DELIMITER ;

† urlencode()

Tools :: Code Snippet :: Url Encode for utf8^*1

DELIMITER $$

DROP FUNCTION IF EXISTS `urlencode` $$
CREATE FUNCTION `urlencode`(original_text text) RETURNS text CHARSET utf8
BEGIN
  declare new_text text DEFAULT NULL;
  declare current_char varchar(10) DEFAULT '';
  declare ascii_current_char int DEFAULT 0;
  declare pointer int DEFAULT 1;
  declare hex_pointer int DEFAULT 1;

  IF original_text IS NOT NULL then
    SET new_text = '';
    while pointer <= char_length(original_text) do
      SET current_char = mid(original_text,pointer,1);
      SET ascii_current_char = ascii(current_char);
      IF current_char = ' ' then
        SET current_char = '+';
      ELSEIF NOT (ascii_current_char BETWEEN 48 AND 57 || ascii_current_char BETWEEN 65 AND 90 || ascii_current_char BETWEEN 97 AND 122
        || ascii_current_char = 45 || ascii_current_char = 95 || ascii_current_char = 46 || ascii_current_char = 126
        || ascii_current_char = 34
      ) then
        SET current_char = hex(current_char);
        SET hex_pointer = char_length(current_char)-1;
        while hex_pointer > 0 do
          SET current_char = INSERT(current_char, hex_pointer, 0, "%");
          SET hex_pointer = hex_pointer - 2;
        end while;
      end IF;
      SET new_text = concat(new_text,current_char);
      SET pointer = pointer + 1;
    end while;
  end IF;

  RETURN new_text;
END $$

DELIMITER ;