MySQLでBINARY操作！ SQLでVARBINARY型を検索する方法

こんにちは。池島です。
 
以前MySQLでVARBINARY型のデータを操作する機会があったのですが、少し癖がありました。そこで今回はVARBINARY型についてご紹介しながら、検索する方法についても紹介していきたいと思います。

検証環境

今回検証を行ったMySQLのバージョンは以下の通りです。

MySQL

8.0.32

VARBINARY型とは

VARBINARY型とは、可変長のバイナリデータを格納することが可能なデータ型になります。指定できるデータ長は0バイトから65,535バイトまでとなっています。類似の型として固定長のバイナリデータを格納するBINARY型があります。

VARBINARY型の特徴

最大の特徴はやはりバイナリデータ自体を格納することになります。例えば、以下の通りテーブルを作成します。

CREATE TABLE varbinary_test
(str_val VARCHAR(255), varbinary_val VARBINARY(255))

INSERT INTO spstest.varbinary_test(str_val, varbinary_val) 
VALUES ('TEST', 'TEST')

SELECT
    str_val
    , varbinary_val
FROM
    varbinary_test 
; 

str_valからは入力したTESTが取れますが、varbinary_valからは数字が取れました。この値はデータベースの管理ソフトがバイナリデータを解釈し、16進数で出力したものになります。
 
この値をUTF-8でデコードするとTESTになります。入力した文字が文字として格納されるのではなく、バイナリデータとして格納されていることが分かります。
 
気を付けないといけないのが、varbinary_test に格納されている値はバイナリデータであるため、文字や数字とは合致しません。そのため、次の通りSQLを実行しても、意図したデータは取得されません。

SELECT
    str_val
    , varbinary_val
FROM
    varbinary_test 
WHERE
    varbinary_val = '54455354'
; 

VARBINARY型の検索方法

ではどのようにすればVARBINARY型を検索できるかというと、CAST関数を利用しVARBINARY型を検索したい文字列に変換して検索を行います。例えば下記のようなテーブルから検索するとします。

次のように記載すれば指定した文字列で検索が可能です。

SELECT
    str_val
    , varbinary_val
FROM
    varbinary_test 
WHERE
    CAST(varbinary_val AS CHAR) = 'TEST'
; 

まとめ

いかがでしたでしょうか。
 
頻繁には利用しないデータ型ですが、時々利用する機会があります。実際にVARBINARY型を操作する際に、このブログを思い出して参考にしていただけると幸いです。
 
ではまた！
 
 
 
 
《関連記事》

技術

2019.2.13(Wed)

MariaDB Connector/J + Amazon Aurora でタイムアウト発生

#プログラム #Java #データベース

技術

2019.8.16(Fri)

【エラー解決方法】MySQLでコネクションタイムアウト発生！？ を解決する方法

#プログラム #Java #データベース

技術

2023.11.15(Wed)

MySQLであいまい検索！ LIKE検索で濁点、半濁点を区別する方法

#データベース