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

23 марта, 2021 12:36 пп 9 083 views | Комментариев нет

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 перезагрузил права:

FLUSH PRIVILEGES;

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

Query OK, 0 rows affected (0.01 sec)

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

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

QUIT;

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

mysql -u test_user -p

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

USE test_company;

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

Database changed

Затем создайте таблицу 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:

QUIT;

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

Bye

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

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

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

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

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

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

<?php

define('DB_NAME', 'test_company');

define('DB_USER', 'test_user');

define('DB_PASSWORD', 'PASSWORD');

define('DB_HOST', 'localhost');

$pdo = new PDO("mysql:host=" . DB_HOST . "; dbname=" . DB_NAME, DB_USER, DB_PASSWORD);

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

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

В этом файле для подключения к базе данных MySQL мы использовали четыре константы PHP:

DB_NAME: эта константа содержит имя базы данных, test_company.
DB_USER: содержит имя пользователя, test_user.
DB_PASSWORD: хранит пароль учетной записи test_user.
DB_HOST: определяет сервер, на котором находится база данных. В этом случае это сервер localhost.

Следующая строка в файле инициирует PHP Data Object (PDO) и подключается к базе данных MySQL:

...

$pdo = new PDO("mysql:host=" . DB_HOST . "; dbname=" . DB_NAME, DB_USER, DB_PASSWORD);

...

Ближе к концу файла мы установили пару атрибутов PDO:

ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION: этот атрибут генерирует исключение, которое может быть зарегистрировано и использовано в целях отладки.
ATTR_EMULATE_PREPARES, false: повышает безопасность, так как перепоручает подготовку ядру базы данных MySQL вместо PDO.

Файл /var/www/html/config.php нужно включить в два сценария PHP, которые мы создадим далее. Они будут отвечать за вставку и извлечение записей соответственно.

Сначала создайте PHP-скрипт /var/www/html/insert_products.php для вставки записей в таблицу products:

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

Затем добавьте следующие конфигурации в файл /var/www/html/insert_products.php:

<?php

require_once 'config.php';

$products = [];

'product_name' => 'VIRTUAL SERVERS',

$products[] = [

'price' => 5,

'product_image' => file_get_contents("https://i.imgur.com/VEIKbp0.png")

];

$products[] = [

'product_name' => 'MANAGED KUBERNETES',

'price' => 30,

'product_image' => file_get_contents("https://i.imgur.com/cCc9Gw9.png")

];

$products[] = [

'product_name' => 'MySQL DATABASES',

'price' => 15,

'product_image' => file_get_contents("https://i.imgur.com/UYcHkKD.png" )

];

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

foreach ($products as $product) {

$stmt = $pdo->prepare($sql);

$stmt->execute($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-адресом сервера):

http://your-server-IP/insert_products.php

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

Records inserted successfully

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

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

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

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

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

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

<html>

<title>Using BLOB and MySQL</title>

<body>

<?php

require_once 'config.php';

$sql = "SELECT * FROM products";

$stmt = $pdo->prepare($sql);

$stmt->execute();

?>

<table border = '1' align = 'center'> <caption>Products Database</caption>

<tr>

<th>Product Id</th>

<th>Product Name</th>

<th>Price</th>

<th>Product Image</th>

</tr>

<?php

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

echo '<tr>';

echo '<td>' . $row['product_id'] . '</td>';

echo '<td>' . $row['product_name'] . '</td>';

echo '<td>' . $row['price'] . '</td>';

echo '<td>' .

'<img src = "data:image/png;base64,' . base64_encode($row['product_image']) . '" width = "50px" height = "50px"/>'

. '</td>';

echo '</tr>';

}

?>

</table>

</body>

</html>

Сохраните изменения в файле и закройте его.

Мы снова сослались на файл config.php для подключения к базе данных. Затем мы с помощью PDO создали и выполнили оператор SQL для извлечения всех элементов из таблицы; это делается при помощи команды SELECT * FROM products.

Затем мы создали таблицу HTML и заполнили ее данными о товарах с помощью PHP-оператора while() {…}. Строка $row = $stmt->fetch(PDO::FETCH_ASSOC) запрашивает БД и сохраняет результат в переменной $row в виде многомерного массива, который затем отображается в столбце таблицы HTML с помощью $row[‘column_name’].

Изображения из столбца product_image заключены в теги <img src = “”>. Мы задали атрибуты ширины и высоты, чтобы уменьшить размер изображений (иначе бы они не поместились в столбце таблицы HTML).

Чтобы преобразовать данные, содержащиеся в типе BLOB, обратно в изображения, мы использовали встроенную PHP функцию base64_encode и следующий синтаксис для схемы Data URI:

data:media_type;base64, base_64_encoded_data

В этом случае image/png – это media_type, а строка в кодировке Base64 из столбца product_image – это base_64_encoded_data.

Запустите файл display_products.php в веб-браузере:

http://your-server-IP/display_products.php

После запуска файла display_products.php вы увидите в браузере HTML-таблицу со списком товаров и их изображениями.

Это значит, что наш сценарий PHP правильно извлекает и визуализирует изображения и другие данные MySQL.

Заключение

В этом руководстве вы научились использовать тип данных BLOB для хранения и отображения картинок с помощью PHP. Вы также на практике увидели основные преимущества хранения изображений в базе данных (по сравнению с их хранением в файловой системе). Среди главных преимуществ: портативность, безопасность и простота резервного копирования. Если вы создаете приложение, которое требует, чтобы данные и связанные с ними изображения хранились вместе, то эта технология может быть очень вам полезна.

За дополнительной информацией о поддерживаемых типах данных в MySQL обращайтесь сюда. Если вам нужны дополнительные инструкции о связке MySQL и PHP, ознакомьтесь со следующими руководствами:

Tags: BLOB, LAMP, LAMP stack, MySQL, PHP, Ubuntu 18.04

Добавить комментарий Отменить ответ

Для отправки комментария вам необходимо авторизоваться.