Как хранить картинки в базе данных mysql

Как хранить картинку в БД?

Подскажите пожалуйста как хранить картинку в БД MySQL? И как ее отправлять в БД и вытаскивать оттуда. Может знаете какие туториалы по данной тематике. Создаю web-приложение для ресторана. Картинки это будут фотографии блюд. Back буду делать на Node.js.

Отслеживать

Domosed_007

задан 10 июл 2020 в 8:55

Domosed_007 Domosed_007

193 13 13 бронзовых знаков

А требование только такое — все картинки должны быть в дб? Не удобнее ли будет в таблице хранить ссылки на файлы, а файлы — в отдельной папке? Мне просто кажется, что сам принцип хранение картинок в таблице — неудобный ни для программиста, ни для клиента.

Как хранить картинки в БД MySQL?

Целесеобразность такого решения давайте не будем тут затрагивать.
1) Какой тип поля использовать и вообще как харнится там картинка, в виде набора байтов?
2) Вопрос, вытекающий из предыдущего, как сделать запрос на вставку и указать это jpg, png, gif и т.д.?
3) Как выводить на странице? Когда мы просто картинку средствами php тдаём там всё понятно header отдаём файловый, потом сам файл и всё ок, но здесь header уже передан и это обычная страница или там стоит типа src=»https://qna.habr.com/q/getimage.php?id=738″ где уже php скрипт формирует и отдаёт каждую картинку?

• Вопрос задан более трёх лет назад
• 58851 просмотр

Комментировать
Решения вопроса 0
Ответы на вопрос 4

1) Какой тип поля использовать и вообще как харнится там картинка, в виде набора байтов?

2) Вопрос, вытекающий из предыдущего, как сделать запрос на вставку и указать это jpg, png, gif и т.д.?

Отдельное поле с указанием типа.
Или делать магию узнавания типа файла по магии php.net/manual/en/function.mime-content-type.php
Или (правильное решение) после загрузки файла приводить его к одному типу (жпг, пнг, вебм)
Или даже так https://stackoverflow.com/a/21732109

3) Как выводить на странице? Когда мы просто картинку средствами php тдаём там всё понятно header отдаём файловый, потом сам файл и всё ок, но здесь header уже передан и это обычная страница или там стоит типа src=»https://qna.habr.com/q/getimage.php?id=738″ где уже php скрипт формирует и отдаёт каждую картинку?

а никак. Когда браузер будет по данному урлу запрашивать, пхп ему должен сформировать ответ с правильным контент-тайпом, так что перед началом отдачи — нужно будет полностью получить саму картинку из БД

Ответ написан более трёх лет назад
Комментировать
Нравится 4 Комментировать

Вопрос хранения графических файлов в MySQL можно решить другим образом: создать в файловой системе папку для графических файлов и после закачки их в эту папку записывать в базу MySQL имена этих файлов. Таким образом сервер базы данных не будет перегружаться. В общем-то, это еще и проще сделать. Могу, если есть необходимость, привести код.
Начинающий в области программирования с использованием MySQL

Ответ написан более трёх лет назад
Комментировать
Нравится 4 Комментировать
DevOps engineer в Containerum
Сначала делаете таблицу для хранения картинок:

create table testblob ( image_id tinyint(3) not null default '0', image_type varchar(25) not null default '', image blob not null, image_size varchar(25) not null default '', image_ctgy varchar(25) not null default '', image_name varchar(50) not null default '' );

$imgData = file_get_contents($filename); $size = getimagesize($filename); mysql_connect("localhost", "$username", "$password"); mysql_select_db ("$dbname"); // mysqli // $link = mysqli_connect("localhost", $username, $password,$dbname); $sql = sprintf("INSERT INTO testblob (image_type, image, image_size, image_name) VALUES ('%s', '%s', '%d', '%s')", mysql_real_escape_string($size['mime']), mysql_real_escape_string($imgData), $size[3], mysql_real_escape_string($_FILES['userfile']['name']) ); mysql_query($sql);

Для отображения картинки на веб странице:

$link = mysql_connect("localhost", "username", "password"); mysql_select_db("testblob"); $sql = "SELECT image FROM testblob WHERE image_id=0"; $result = mysql_query("$sql"); header("Content-type: image/jpeg"); echo mysql_result($result, 0); mysql_close($link);

Ответ написан более трёх лет назад
Нравится 3 2 комментария

Оптимус Пьян @marrk2 Автор вопроса

Спасибо, стало всё понятнее. Последний вопрос: если это простая html-страница с разметкой и на ней надо вывести 5 таких картинок из БД, то код, который вы привели для отображения картинок как и где должен отрабатывать?

$link = mysql_connect("localhost", "username", "password"); mysql_select_db("testblob"); $sql = "SELECT image FROM testblob WHERE image_id=0"; $result = mysql_query("$sql"); header("Content-type: image/jpeg"); echo mysql_result($result, 0); mysql_close($link);

кладут в отдельный файл image.php и обращаются по GET

но на всякий случай сообщу, что код, который вам написали выше использовать в таком виде нельзя. Подключение должно быть через PDO с bindParam, GET переменные должны экранироваться, нужно предусмотреть картинку-заглушку если айди не найден и т.д.

Как хранить изображения в MySQL с помощью BLOB

BLOB (или Binary Large Object, большой двоичный объект) – это тип данных MySQL, который позволяет хранить двоичные данные: изображения, мультимедиа и файлы PDF.

Хранить изображения (такие как фотографии и подписи) в базе данных MySQL вместе с другой информацией удобно в том случае, если вы разрабатываете приложения с сильной привязкой к БД (например, портал поиска работы, база данных студентов или финансовое приложение), и в этой БД изображения должны быть синхронизированы с другими данными.

И тогда на помощь приходит тип данных BLOB. Этот подход устраняет необходимость в создании отдельной файловой системы для хранения изображений, а также централизует базу данных, делая ее более портативной и надежной, поскольку данные изолированы от файловой системы. А еще это упрощает создание резервных копий, поскольку вы можете создать один дамп MySQL, содержащий все ваши данные.

Извлечение данных обрабатывается быстрее, а при создании новых записей вы можете быть уверены, что правила проверки данных и ссылочная целостность четко соблюдены (особенно при использовании транзакций MySQL).

В этом мануале мы расскажем о том, как использовать тип данных BLOB для хранения изображений с помощью PHP.

Требования

• Сервер Ubuntu 18.04, предварительно настроенный согласно этим инструкциям.
• Стек LAMP, установленный на вашем сервере. С установкой вам поможет этот мануал (раздел о виртуальных хостах можно пропустить, здесь мы не будем их использовать).

1: Создание базы данных

Давайте начнем с создания тестовой базы данных для этого проекта. Подключитесь к серверу по SSH, а затем выполните следующую команду, чтобы войти на сервер MySQL как пользователь root:

sudo mysql -u root -p

Введите root-пароль базы данных MySQL и нажмите Enter, чтобы продолжить.

После этого выполните следующую команду, чтобы создать базу данных. В этом руководстве мы назовем ее test_company:

CREATE DATABASE test_company;

После создания БД вы увидите следующее:

Query OK, 1 row affected (0.01 sec)

Теперь нам нужно создать на сервере MySQL учетную запись test_user; не забудьте заменить PASSWORD сложным паролем:

CREATE USER ‘test_user’@’localhost’ IDENTIFIED BY ‘PASSWORD’;

Вы получите следующий результат:

Query OK, 0 rows affected (0.01 sec)

Чтобы предоставить пользователю test_user полные права доступа к базе данных test_company, запустите команду:

GRANT ALL PRIVILEGES ON test_company.* TO ‘test_user’@’localhost’;

Вы должны получить следующий результат:

Query OK, 0 rows affected (0.01 sec)

В завершение нужно сбросить таблицы привилегий, чтобы MySQL перезагрузил права:

На экране должно появиться:

Query OK, 0 rows affected (0.01 sec)

Теперь, когда база данных test_company и пользователь test_user готовы, мы можем перейти к созданию таблицы. Предположим, нам нужна таблица products для хранения списка товаров. Позже мы попробуем вставить и извлечь данные из этой таблицы, чтобы понять, как работает BLOB в MySQL.

Выйдите из оболочки MySQL:

Затем снова войдите в систему, на этот раз – как пользователь test_user:

mysql -u test_user -p

При появлении запроса введите пароль test_user и нажмите Enter, чтобы продолжить. Затем откройте базу данных test_company, набрав команду:

Перейдя в базу данных test_company, MySQL отобразит такой результат:

Затем создайте таблицу products:

CREATE TABLE `products` (product_id BIGINT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(50), price DOUBLE, product_image BLOB) ENGINE = InnoDB;

Эта команда создаст таблицу по имени products. В таблице будет четыре столбца:

• product_id: в этом столбце используется тип данных BIGINT, он позволяет вместить большой список продуктов, содержащий 2⁶³-1 элементов. Мы пометили столбец как PRIMARY KEY, чтобы присвоить товарам уникальные идентификаторы. Чтобы MySQL мог обрабатывать создание новых идентификаторов, мы использовали ключевое слово AUTO_INCREMENT.
• product_name: этот столбец содержит названия товаров. Здесь мы использовали тип данных VARCHAR, так как это поле обычно обрабатывает буквенно-цифровые значения длиной до 50 символов; ограничение в 50 символов – это всего лишь гипотетическое значение, используемое для целей этого руководства.
• price: этот столбец содержит розничные цены наших товаров. Поскольку цена на некоторые товары может выражаться числом с плавающей точкой (например, 23.69, 45.36, 102.99), мы указали здесь тип данных DOUBLE.
• product_image: в этом столбце мы указали тип данных BLOB, поскольку он предназначен для хранения двоичных данных – изображений товаров.

Для поддержки широкого спектра функций, включая транзакции MySQL, мы использовали механизм InnoDB. Выполнив вышеприведенную команду для создания таблицы, вы увидите следующий результат:

Query OK, 0 rows affected (0.03 sec)

Выйдите из сервера MySQL:

Вы получите сообщение:

Таблица products готова. Вы можете использовать ее для хранения некоторых записей, включая изображения продуктов. Скоро мы заполним ее данными.

2: Создание PHP-скрипта для заполнения базы данных

На этом этапе мы создадим сценарий PHP, который будет подключаться к базе данных MySQL, созданной в первом разделе руководства. Сценарий подготовит три записи о товарах и вставит их в таблицу products.

Чтобы создать PHP-скрипт, откройте новый файл в текстовом редакторе:

sudo nano /var/www/html/config.php

Затем вставьте в него следующую информацию (замените PASSWORD паролем test_user, который вы создали в разделе 1):

$pdo = new PDO(«mysql:host=» . DB_HOST . «; dbname https://i.imgur.com/VEIKbp0.png»)

‘product_name’ => ‘MANAGED KUBERNETES’,

‘product_name’ => ‘MySQL DATABASES’,

$sql = «INSERT INTO products(product_name, price, product_image) VALUES (:product_name, :price, :product_image)»;

foreach ($products as $product)

echo «Records inserted successfully»;

Сохраните и закройте файл.

Этот файл нужен для определения переменных базы данных и подключения к ней. Файл также инициирует объект PDO и сохраняет его в переменной $pdo. Мы ссылаемся на файл config.php вверху.

Затем мы создали массив данных о товарах, которые нужно вставить в БД. Помимо product_name и price, которые заданы в виде строк и числовых значений соответственно, сценарий использует встроенную функцию PHP file_get_contents для чтения изображений из внешнего источника и передачи их в виде строк в столбец product_image.

После этого мы подготовили оператор SQL и использовали оператор PHP foreach для вставки каждого продукта в базу данных.

Запустите /var/www/html/insert_products.php в окне браузера, используя следующий URL-адрес (не забудьте заменить your-server-IP внешним IP-адресом сервера):

На экране вы увидите сообщение об успешном выполнении файла – следовательно, записи были вставлены в базу данных.

Records inserted successfully

Итак, мы успешно вставили три записи, содержащие изображения товаров, в таблицу. На следующем этапе мы напишем сценарий PHP для извлечения этих записей и отображения их в браузере.

3: Извлечение и визуализация данных из БД MySQL

Имея ​в базе данных информацию и изображения товаров, вы можете написать второй сценарий PHP, который будет запрашивать и отображать данные в таблице HTML в браузере.

Чтобы создать файл, введите:

sudo nano /var/www/html/display_products.php

Затем вставьте в файл следующее:

$sql = «SELECT * FROM products»;

Хранение изображений сайта в БД

Автор должен признаться: статья родилась и выросла из довольно небольшой и не претендующей на откровение оптимизации сайта, описанного в другом материале. Сайт этот связан с музыкальной тематикой и, соответственно, активно отображает обложки альбомов, хранящиеся (до поры до времени) на сервере не в ФС , как обычно бывает, а в БД SQL Server (в BLOB-поле, о чём несложно догадаться); в старом варианте пора извлечения изображений из базы и сохранения их в виде файлов возникает в момент запроса альбомов пользователем, после чего (по истечении сессии) они удаляются.

Чтобы почти окончательно развеять куцую интригу и помочь возможному читателю определиться с тем, имеет ли смысл продолжить чтение, опишу конечный результат оптимизации: обложки по-прежнему останутся в базе данных, причём в поле того же типа, но вот веб-сервер, отдающий в итоге изображения клиенту, будет «обманут» и станет обращаться за файлом знать не зная, что его на самом деле нет в ФС, а вызов идёт сразу и непосредственно к СУБД. Такой «обман» конечно же не является самоцелью – всё в основном затевалось ради уменьшения нагрузки на дисковую подсистему.

Старая реализация

Прежде всего необходимо напомнить структуру HTML-тэга img , обязательным у которого является лишь атрибут src :

У несложных сайтов изображение, находящееся по URL в этом атрибуте, веб-сервер обычно напрямую извлекает из ФС, т. к. относительный путь к нему очень часто совпадает со структурой каталогов на сервере, а имя файла тоже берётся непосредственно из ФС; однако такое распространённое поведение, если брать конкретно IIS (именно он применяется в данном проекте), может быть заменено на совершенно другое за счёт модулей (расширений), когда изображения станут, к примеру, генерироваться на лету (т. е. физически они нигде не хранятся). Так вот статья затрагивает лишь первый вариант.

На этом сайте обложки располагаются не в файловой системе из-за желания «всё своё носить с собой» – чтобы резервная копия БД содержала полный набор информации, была самодостаточной; соответственно, дабы веб-сервер мог отдать изображение клиенту, оно, как уже говорилось, должно быть извлечено из базы данных и сохранено в виде файла, что добавляет лишнюю операцию: сначала читаем из БД, после чего записываем копию на диск, бездарно и теряя дисковое пространство, и нагружая эту подсистему. Ко всему прочему, если несколько разных пользователей относительно одновременно запросят одну и ту же обложку, то из-за того, что в uniGUI каждый из них представлен отдельной сессией, файл дополнительно продублируется ещё и для каждой из них.

Схематически и упрощённо описанное непотребство можно представить следующим образом:

Если проиллюстрировать прошлую реализацию кодом, то его логика будет иметь довольно классический вид:

1. Запрос на выборку данных возвращает, среди прочих, и BLOB-поле с обложкой (в данном случае на примере компонента spAlbums типа TFDStoredProc из состава FireDAC).
2. Изображение из поля, в зависимости от формата (png, jpg…), загружается в соответствующего наследника TGraphic.
3. Далее используется предоставляемая uniGUI функция uniImageToURL , сохраняющая изображение в нужную папку и возвращающая готовый URL для атрибута src .

uses System.SysUtils, System.Classes, Data.DB, Vcl.Graphics, Vcl.Imaging.jpeg, Vcl.Imaging.pngimage, Vcl.Imaging.GIFImg, uniGUIUtils; function TMainForm.AlbumsToHTML: string; function CoverURL: string; var CoverFormat: string; Image: TGraphic; ImageStream: TStream; begin CoverFormat := spAlbums['CoverFormat']; if CoverFormat = 'jpg' then Image := TJPEGImage.Create; if CoverFormat = 'png' then Image := TPngImage.Create; if CoverFormat = 'gif' then Image := TGIFImage.Create; if CoverFormat = 'bmp' then Image := TBitmap.Create; try ImageStream := spAlbums.CreateBlobStream ( spAlbums.FieldByName('Cover'), bmRead ); try Image.LoadFromStream(ImageStream); finally ImageStream.Free; end; Result := uniImageToURL(Image); finally Image.Free; end; end; const AlbumHTMLTemplate = '
' + '
' + '' + . '
' + '
'; begin spAlbums.First; while not spAlbums.Eof do begin Result := Result + Format( AlbumHTMLTemplate, [CoverURL, . ] ); spAlbums.Next; end; end;

Идеальный вариант

Если предаться мечтаниям и представить, что обложки по-прежнему находятся в БД в BLOB-поле, а IIS каким-то образом, почти без усилий и программирования с нашей стороны, сам извлекает их, то окажется, что SQL Server способен такую мечту исполнить – за счёт функционала FileTable, представляющего собой, если очень кратко, создаваемую разработчиком специального вида таблицу, каждая запись которой отображается в файл в NTFS; обращение к таким файлам ничем не отличается от стандартного (однако некоторые возможности не поддерживаются), т. е. их можно просматривать, изменять и удалять хоть через Проводник.

Если снова графически показать такую схему, то мы избавимся от копирования обложки в папку сайта:

Данный вариант полностью совместим и с отложенной загрузкой изображений, настраиваемой скажем через атрибут loading , т. е. если пользователь не прокрутит страницу сайта до некоторых обложек, то они и не будут прочитаны из базы (написанное, разумеется, справедливо и для случая, когда в браузере вообще отключен показ изображений):

Чтобы воспользоваться этой манной небесной, ниспосланной SQL Server, потребуется, само-собой, выполнить некоторые действия как на стороне СУБД, так и на стороне IIS, чему собственно и посвящена оставшаяся практическая часть статьи. Но предварительно хотелось бы сделать небольшую ремарку и отметить, что конкуренты Microsoft тоже могут предложить тонкую работу с BLOB, но функционал уровня FileTable автору удалось найти лишь у Oracle – речь о Database File System (DBFS). Остальные же игроки предоставляют лишь условно традиционный (но неизбежно СУБД-специфичный) API, несколько примеров:

1. PostgreSQL
2. Interbase
3. MySQL

Настройка SQL Server

По умолчанию FileTable-возможности отключены в SQL Server. Официальная документация содержит весьма подробную и внятную инструкцию по параметрам, которые необходимо задействовать как на уровне всего экземпляра, так и в конкретной базе данных, отчего не представляется разумным пересказывать здесь подобные тонкости – вместо этого автор сосредоточится на небольшом SQL-скрипте, выполняющем перенос обложек из существующей таблицы с альбомами в новую FileTable-таблицу (если же в Вашем случае файлы изначально в БД не хранятся, в отличие от данного проекта, то с их адаптацией к FileTable поможет документация).

Таблицы

Далее в скрипте предполагается следующая структура двух только что упомянутых таблиц (источника – с данными об альбомах, и приёмника – новой таблицы с обложками):

Поле	Тип данных	Описание
ID	integer	Первичный ключ.
Cover	varbinary(max)	BLOB-поле с собственно изображением, которое нужно перенести в новую таблицу.
CoverFormat	nvarchar(10)	Тип изображения (jpg, png и т. д.).
Прочие поля (название альбома, год издания и т. п.).

Поле	Тип данных	Описание
path_locator	hierarchyid	Первичный ключ.
file_stream	varbinary(max)	Содержимое файла (в нашем случае это изображение).
name	nvarchar(255)	Название файла (с расширением), отображаемое в ФС.
Прочие поля.

Поле	Тип данных	Описание
CoverID	hierarchyid	Внешний ключ на приёмник.
Прочие поля.

Скрипт

Все действия с базой логически делятся на несколько этапов:

Банальнейшее по реализации добавление поля CoverID в источник:

ALTER TABLE dbo.Album ADD CoverID hierarchyid;

DECLARE @Covers TABLE ( AlbumID integer NOT NULL, path_locator hierarchyid NOT NULL ); MERGE dbo.AlbumCover AS cover USING ( SELECT ID, CoverID, Cover, CAST( NEWID() as nvarchar(max) ) + '.' + CoverFormat AS FileName FROM dbo.Album ) AS album ON album.CoverID = cover.path_locator WHEN NOT MATCHED THEN INSERT (file_stream, name) VALUES (album.Cover, album.FileName) OUTPUT album.ID, INSERTED.path_locator INTO @Covers; UPDATE dbo.Album SET CoverID = path_locator FROM @Covers WHERE > Использование MERGE вместо конструкции INSERT продиктовано невозможностью в последней обратиться в блоке OUTPUT к полям любых задействованных таблиц (допустимы только поля той, в которую и происходит вставка). Другими словами, было бы нечем заполнить поле AlbumID табличной переменной @Covers , т. к. обращение ниже выполняется к таблице-источнику, а вставка идёт в dbo.AlbumCover :

. OUTPUT album.ID, . 

ALTER TABLE dbo.Album DROP COLUMN Cover, CoverFormat;

SELECT FileTableRootPath('dbo.AlbumCover');

Оговорка про «почти» связана с тем, что читатель может столкнуться с отсутствием доступа при попытке обратиться к означенным файлам – в этом случае следует ознакомиться с той частью следующего раздела, где описывается нюанс с правами. Также для определённости примем, что наш путь выглядит как \\ServerName\MSSQLSERVER\DatabaseName\AlbumCover (далее в другом примере будет отсылка к нему, где для наглядности потребуется конкретное значение).

Настройка веб-сервера

Хотя в текущем разделе демонстрация идёт на примере IIS, но большинство вещей применимы и к любому другому веб-серверу, ибо являются скорее ОС- и СУБД-специфичными.

Права на чтение

Если снова вернуться к атрибуту src тэга img , то становится очевидной небольшая проблема: путь в атрибуте должен быть относительным, а нужные изображения доступны лишь по UNC-пути. Решение довольно просто́ и заключается в создании символической ссылки в папке сайта; если условиться, что физически сайт располагается в каталоге c:\IIS\SiteName, а ссылка будет называться Covers, то её добавление выполняется следующей командой в консоли:

mklink /d "c:\IIS\SiteName\Covers" "\\ServerName\MSSQLSERVER\DatabaseName\AlbumCover"

Теперь атрибут src станет возможно заполнять подобным образом:

Доработки Delphi-кода, дающие показанное значение для src , будут приведены в конце статьи, а пока же остался важный нюанс с правами (в нашем случае достаточно лишь на чтение), причём делящийся на две части:

Предоставление доступа (на уровне ФС) пользователю IUSR и группе IIS_IUSRS для созданной символической ссылки:

Логин создаётся на основе Windows-пользователя вида IIS APPPOOL\ .

CREATE LOGIN [IIS APPPOOL\YourPoolName] FROM WINDOWS;

CREATE USER FileTableReader FOR LOGIN [IIS APPPOOL\YourPoolName];

GRANT SELECT ON dbo.AlbumCover TO FileTableReader;

Проверка подлинности

Последнее, что требуется настроить, относится непосредственно к IIS, а именно к проверке подлинности на сайте – чтобы она выполнялась от имени пула приложений, чуть выше только что получившего права на чтение обложек (если в сценарии читателя проверка обязана проходить от иного пользователя, то данный раздел можно пропустить, но при этом необходимо добавить ещё одну пару «логин-пользователь» как описано в предыдущем разделе, связав имя входа со значением из параметров проверки подлинности).

Новая реализация

В заключение осталось привести новый, значительно облегчённый по сравнению с предыдущим вариантом, код, ибо из БД уже не нужно извлекать само изображение – достаточно лишь получить имя файла с ним (из поля dbo.AlbumCover.name , имеющего в примере псевдоним CoverFileName ) и в неизменном виде подставить в атрибут src :

uses System.SysUtils; function TMainForm.AlbumsToHTML: string; const AlbumHTMLTemplate = '
' + '
' + '' + . '
' + '
'; SymbolicLinkName = 'Covers'; begin spAlbums.First; while not spAlbums.Eof do begin Result := Result + Format( AlbumHTMLTemplate, [SymbolicLinkName, spAlbums['CoverFileName'], . ] ); spAlbums.Next; end; end;

• Веб-разработка
• Delphi
• Microsoft SQL Server
• IIS
• Разработка под Windows