MySQLのbinaryタイプを操作する方法

2014年10月24日

1.テーブルのサンプル
CREATE TABLE startnews24_bin (
bin_id BINARY(16) NOT NULL
) Engine=InnoDB

2.データを挿入(32桁のUUID文字列)
INSERT INTO startnews24_bin(bin_id) VALUES(UNHEX('FA34E10293CB42848573A4E39937F479′));
INSERT INTO startnews24_bin(bin_id) VALUES(UNHEX(?));
OR
INSERT INTO startnews24_bin(bin_id) VALUES(x’FA34E10293CB42848573A4E39937F479’);

3.データをクエリ
SELECT HEX(bin_id) AS bin_id FROM startnews24_bin;

SELECT HEX(bin_id) AS bin_id FROM startnews24_bin WHERE bin_id = UNHEX('FA34E10293CB42848573A4E39937F479′);
SELECT HEX(bin_id) AS bin_id FROM startnews24_bin WHERE bin_id = UNHEX(?);
SELECT HEX(bin_id) AS bin_id FROM startnews24_bin WHERE bin_id = x’FA34E10293CB42848573A4E39937F479’;

結果：
bin_id
————————–
FA34E10293CB42848573A4E39937F479

4.BINARY（16）タイプのUDID値を戻すためにMySQLのUUID()関数を利用している
CREATE FUNCTION uu_id() RETURNS binary(16) RETURN UNHEX(REPLACE(UUID(),’-',"));
OR
CREATE FUNCTION uu_id() RETURNS binary(16) RETURN UNHEX(REVERSE(REPLACE(UUID(),’-',")));

使い方：
INSERT INTO test_bin(bin_id) VALUES(uu_id());