MySQLでシリアル化データを保存する：BLOB、JSON、VARBINARYの比較

2024-06-19

MySQLデータベースでシリアル化されたデータを格納する列型

適切な列型を選択しないと、以下の問題が発生する可能性があります。

データ破損
データの喪失
パフォーマンスの低下

シリアル化されたデータを格納するために一般的に使用されるMySQL列型は以下の3つです。

BLOB型

BLOB型は、バイナリデータを格納するために使用される汎用的な列型です。
シリアル化されたデータはバイナリデータであるため、BLOB型は適切な選択肢となります。
BLOB型には4つのサブタイプがあります。
- TINYBLOB：最大255バイトのデータを格納できます。
シリアル化されたデータのサイズがわからない場合は、MEDIUMBLOBまたはLONGBLOBを使用することをお勧めします。

JSON型

JSON型は、JSON（JavaScript Object Notation）データを格納するために使用される列型です。
JSONは、構造化データを記述するための軽量なデータ交換形式です。
シリアル化されたデータがJSON形式である場合は、JSON型を使用すると便利です。
JSON型は、MySQL 5.7.7以降で使用できます。

VARBINARY型

BLOB型と同様に、シリアル化されたデータを格納するために使用できます。
VARBINARY型は、BLOB型よりも古い列型です。

シリアル化されたデータのサイズがわかっている場合は、そのサイズに合ったBLOBサブタイプを選択してください。
データのサイズがわからない場合、またはJSON形式ではない場合は、MEDIUMBLOBを選択してください。

その他の考慮事項

データベースのスキーマを設計する際には、将来のデータ成長の可能性を考慮する必要があります。
シリアル化されたデータは、圧縮して格納することができます。これにより、ストレージスペースを節約できます。
シリアル化されたデータのセキュリティを考慮する必要があります。暗号化を使用して、データを保護することができます。

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  data MEDIUMBLOB
);

INSERT INTO users (name, email, data)
VALUES ('John Doe', '[email protected]',
  SERIALIZE(
    STRUCT(
      'address' => '123 Main Street',
      'phone' => '555-1234',
      'hobbies' => ['reading', 'writing', 'coding']
    )
  )
);

SELECT * FROM users;

UPDATE users
SET data = SERIALIZE(
  STRUCT(
    'address' => '456 Elm Street',
    'phone' => '555-5678',
    'hobbies' => ['running', 'biking', 'swimming']
  )
)
WHERE id = 1;

DELETE FROM users WHERE id = 1;

このコードは、次のことを行います。

usersという名前のテーブルを作成します。
このテーブルには、id、name、email、dataという4つの列があります。
id列はプライマリキーであり、自動的にインクリメントされます。
nameとemail列は必須です。
data列は、MEDIUMBLOB型です。
data列には、シリアル化されたデータが格納されます。
INSERTステートメントを使用して、新しいレコードをusersテーブルに挿入します。
このレコードには、John Doeという名前、[email protected]という電子メールアドレス、およびシリアル化されたデータが含まれています。
シリアル化されたデータには、address、phone、hobbiesという3つのキーを持つ構造体が含まれています。
SELECTステートメントを使用して、usersテーブルからすべてのレコードを選択します。