データベースで画像を保存するべき理由と方法

この記事では外部リソースに画像を保存する方法ではなく、データベースに画像を保存する方法のメリットとやり方を紹介する。

まず簡単に結論を述べると、データベースは外部リソースを管理することはできない。

であるため、バックアップなどのSQLで恩恵を受けれるはずの機能が受けれなくなってしまう。

サーバーラックが倒れたら...?

事例１:あなたはwebサイトの管理人で、webサイトでは画像を表示する機能がありその画像はデータベースには格納していません。

データベースには画像のパスだけを格納し、そのパスに画像を保存しています。

ある時事件が起きます。

データベースと画像を保存しているサーバーラックが倒れてしまい、データが消し飛んでしまいました。

しかしDBAはこのような事態も想定しています。

DBAはOracleに標準搭載されていたバックアップを使ってwebサイトのほとんどの機能を回復させることができました。

ですが、90%の機能が回復しましたが、webサイトの画像は表示されないままです。

DBAに確認すると、「データベースに格納されているデータについては保証できたが、外部リソースとなった画像については責任は取れない」とのこと。

あなたはwebサイトの画像を全て修復しなければならなくなりました。

この事例のように、外部ストレージで画像ファイルを管理した場合、SQLやDBAで管理しているバックアップなどの恩恵を受けることができないのです。

画像ファイルをディレクトリで管理するメリット

データベースには画像のパスだけを格納し、そのパスに画像を保存しています。

データベースにおけるこの画像の管理方法は賛否両論でこのような管理方法には「ファントムファイル」という名前がつけられています

メリットとしては以下の点が挙げられます。

データベース自体の容量を減らすことができる
データベースのバックアップ自体が短時間で完了する
ファイルが外部リソースとして管理されているため、編集が楽になる

画像ファイルをディレクトリで管理するデメリット

以下の全ての欠点を克服できる場合、ファントムファイル構造を使用して良い。

画像を読み込むときに、データベースだけでなく外部リソースを読み込む手順が発生する
行を削除したときに、画像ファイルは削除されず「孤児」になる
画像の変更のタイミングと、データベースのコミットのタイミングにズレが発生する
ロールバックが効かない(最大の欠点)
バックアップが効かない
SQLの権限によって、画像へのアクセスを制限できない

対応方法:画像のファイル管理かデータベース管理かを常に検討する

例えば、次のようなプロジェクトの場合は画像をファイル管理しても問題ないでしょう。

高度なトランザクション処理も、SQLアクセス権限も、複数クライアントからの同時アクセス対応も、ロールバックも必要ない。

また、エンジニアリング旧時代では、アプリケーションとデータベースの処理能力はとても低く、データベースに格納する際の技術的な制約が存在してました。

このような場合であれば、画像をファイル管理しても問題ないでしょう。

画像をデータベースで管理するメリット

画像データはデータベースに格納されるので、読み込みのための追加のステップは不要です。謝ったファイルパスを格納するというリスクもありません。
行を削除すると、自動的に画像も削除されます。（外部ファイル管理だとそうはいきません）
画像の変更は、コミットするまで誰も見えません。
トランザクションをロールバックすると、画像を以前の状態に復元できます。
行の更新時には、ロックが行われます。このため、他のクライアントは同時に同じ画像を更新できません。
データベースのバックアップが保証されます。
SQLの権限管理によって、行と画像へのアクセスを管理できます。

データベースで画像を管理する方法：BLOB型を使え

この記事で見た問題のいずれかに該当する場合は、画像を外部ファイル管理ではなく、データベースの内部に格納することを考えるべきです。

ほぼ全てのデータベース製品は、あらゆるバイナリデータを格納できるBLOBデータ型をサポートしています。

また、BLOBの最大サイズはデータベース製品によって異なりますが、いずれの場合もほとんどの画像を格納するための十分な容量があります。あるいは、BLOBに類似するデータ型をサポートしているかもしれません。

例えば、

MySQLは最大16MBのデータを格納できるMEDIUMBLOBという型を持ちます。
OracleはLONG RAWというデータ型で最大2GBも保管できます。

いずれの場合もほとんどの画像を格納するのに十分な容量でしょう。

実際のSQLの宣言は次の通りです。

CREATE TABLE screen_shots {

    bug_id  UNSINED INT NOT NULL,

    img_id  UNSINED INT NOT NULL,

    img     BLOB

    caption VARCHAR(100)

}

上記のようにデータベースに保存することで、これまで示した欠点を全て克服できます。

ポイントは、画像についての説明と画像のテーブルを別にすることで管理のしやすさを実現することである

画像ファイルをデータベースに格納する方法:LOAD_FILE関数を使う

多くの場合、データベースに格納する前の元画像はファイルとして存在しているでしょう。

データベースにはこれらの画像ファイルをBLOB列に読み込むための方法を用意してます。

例えば、MySQLのLOAD_FILE関数では、ファイルの読み込みと、内部のBLOB列への格納に使用できます。

UPDATE

    Screenshots

SET

    img = LOAD_FILE('image/screenshot1234-1.jpg')

WHERE 1=1

    and bug_id = 1234

    and image_id = 1

データベースの画像データから、画像ファイルを生成する:DUMPFILE関数を使う

BLOB列の内容をファイルに保存することもできます。これを行うのが、DUMPFILE関数です。

例えば、MySQLにはSELECTステートメントのオプション句が存在し、列名や行の終了などの装飾をつけずに、クエリ結果をそのまま格納できます。

SELECT

    screenshot_image

INTO 

    DUMPFILE 'images/screenshot1234-1.jpg'

FROM 

    Screenshots

WHERE 1=1

    and bug_id = 1234

    and image_id = 1

結論：画像ファイルは可能ならばデータベース保存するのが良い

画像ファイルをデータベース保存する際の恩恵は以下の通り！

画像データはデータベースに格納されるので、読み込みのための追加のステップは不要です。謝ったファイルパスを格納するというリスクもありません。
行を削除すると、自動的に画像も削除されます。（外部ファイル管理だとそうはいきません）
画像の変更は、コミットするまで誰も見えません。
トランザクションをロールバックすると、画像を以前の状態に復元できます。
行の更新時には、ロックが行われます。このため、他のクライアントは同時に同じ画像を更新できません。
データベースのバックアップが保証されます。
SQLの権限管理によって、行と画像へのアクセスを管理できます。

備考

title:データベースで画像を保存するべき理由と方法

description:データベースで画像を保存するべき理由と方法。この記事では外部リソースに画像を保存する方法ではなく、データベースに画像を保存する方法のメリットとやり方を紹介する。

img:https://www.oreilly.co.jp/books/images/picture_large978-4-87311-589-4.jpeg

category_script:page_name.startswith("30")

diff:タイトル、ディスクリプション、内容の拡充（役2倍）

title_before:画像ファイルのデータベースへの格納方法ファントムファイル【SQL アンチパターン】

description_before:データベースは外部リソースを管理することはできない。1. データベース自体の容量を減らすことができる。1. データベースのバックアップ自体は短時間で完了する。1. ファイルが外部リソースとして管理されているため、編集が楽になる

page:https://minegishirei.hatenablog.com/entry/2023/02/11/194744

プログラミング学習サイト

ファントムファイル【SQLアンチパターンまとめ】