mysql.blob_如何在Ubuntu 18.04上使用MySQL BLOB数据类型通过PHP存储图像

mysql.blob

The author selected Girls Who Code to receive a donation as part of the Write for DOnations program.

作者选择了《编码的女孩》作为Write for DOnations计划的一部分来接受捐赠。

介绍 (Introduction)

A Binary Large Object (BLOB) is a MySQL data type that can store binary data such as images, multimedia, and PDF files.

二进制大对象( BLOB )是一种MySQL数据类型，可以存储二进制数据，例如图像，多媒体和PDF文件。

When creating applications that require a tightly-coupled database where images should be in sync with related data (for example, an employee portal, a student database, or a financial application), you might find it convenient to store images such as students’ passport photos and signatures in a MySQL database alongside other related information.

当创建需要紧密耦合数据库的应用程序时，图像应与相关数据同步(例如，员工门户网站，学生数据库或财务应用程序)，您可能会发现方便存储图像(例如学生护照) MySQL数据库中的照片和签名以及其他相关信息。

This is where the MySQL BLOB data type comes in. This programming approach eliminates the need for creating a separate file system for storing images. The scheme also centralizes the database, making it more portable and secure because the data is isolated from the file system. Creating backups is also more seamless since you can create a single MySQL dump file that contains all your data.

这就是MySQL BLOB数据类型的来源。这种编程方法消除了创建用于存储图像的单独文件系统的需要。该方案还集中了数据库，因为它与文件系统隔离，因此使其更加可移植和安全。创建备份也更加无缝，因为您可以创建一个包含所有数据的MySQL转储文件。

Retrieving data is faster, and when creating records you can be sure that data validation rules and referential integrity are maintained especially when using MySQL transactions.

检索数据的速度更快，并且在创建记录时可以确保保持数据验证规则和参照完整性，尤其是在使用MySQL事务时。

In this tutorial, you will use the MySQL BLOB data type to store images with PHP on Ubuntu 18.04.

在本教程中，您将使用MySQL BLOB数据类型在Ubuntu 18.04上使用PHP存储图像。

先决条件 (Prerequisites)

To follow along with this guide, you will need the following:

要遵循本指南，您将需要以下内容：

An Ubuntu 18.04 server configured using the Initial Server Setup with Ubuntu 18.04 and a non-root user with sudo privileges.
使用初始服务器设置和Ubuntu 18.04配置的Ubuntu 18.04服务器，以及具有sudo特权的非root用户。
Apache, MySQL, and PHP set up by following the guide on How To Install Linux, Apache, MySQL, PHP (LAMP) stack on Ubuntu 18.04. For this tutorial, it isn’t necessary to create virtual hosts, so you can skip Step 4.
通过遵循有关如何在Ubuntu 18.04上安装Linux，Apache，MySQL，PHP(LAMP)堆栈的指南来设置Apache，MySQL和PHP。对于本教程，无需创建虚拟主机，因此可以跳过步骤4。

第1步-创建数据库 (Step 1 — Creating a Database)

You’ll start off by creating a sample database for your project. To do this, SSH in to your server and then run the following command to log in to your MySQL server as root:

首先，为您的项目创建一个示例数据库。为此，SSH登录到您的服务器，然后运行以下命令以root身份登录到您MySQL服务器：

sudo mysql -u root -p
须藤mysql -u root -p

Enter the root password of your MySQL database and hit ENTER to continue.

输入您MySQL数据库的root密码，然后按ENTER继续。

Then, run the following command to create a database. In this tutorial we’ll name it test_company:

然后，运行以下命令来创建数据库。在本教程中，我们将其命名为test_company ：

CREATE DATABASE test_company;
创建数据库test_company ;

Once the database is created, you will see the following output:

创建数据库后，您将看到以下输出：


   
    Output
   Query OK, 1 row affected (0.01 sec)

Next, create a test_user account on the MySQL server and remember to replace PASSWORD with a strong password:

接下来，在MySQL服务器上创建一个test_user帐户，并记住用一个强密码替换PASSWORD ：

CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'PASSWORD';
CREATE USER'test_user '@'localhost'由' PASSWORD '标识;

You’ll see the following output:

您将看到以下输出：


   
    Output
   Query OK, 0 rows affected (0.01 sec)

To grant test_user full privileges on the test_company database, run:

要授予test_user对test_company数据库的完全特权，请运行：

GRANT ALL PRIVILEGES ON test_company.* TO 'test_user'@'localhost';
GRANT ALL PRIVILEGES ON test_company * TO 'test_user '@' localhost'的。

Make sure you get the following output:

确保获得以下输出：


   
    Output
   Query OK, 0 rows affected (0.01 sec)

Finally, flush the privileges table in order for MySQL to reload the permissions:

最后，刷新特权表以使MySQL重新加载许可：

FLUSH PRIVILEGES;
冲洗特权;

Ensure you see the following output:

确保您看到以下输出：


   
    Output
   Query OK, 0 rows affected (0.01 sec)

Now that the test_company database and test_user are ready, you’ll move on to creating a products table for storing sample products. You’ll use this table later to insert and retrieve records to demonstrate how MySQL BLOB works.

现在已经准备好test_company数据库和test_user ，接下来将继续创建一个用于存储示例产品的products表。稍后将使用此表插入和检索记录，以演示MySQL BLOB工作方式。

Log out from the MySQL server:

从MySQL服务器注销：

QUIT;
退出;

Then, log back in again with the credentials of the test_user that you created:

然后，使用您创建的test_user的凭据再次登录：

mysql -u test_user -p
mysql -u test_user -p

When prompted, enter the password for the test_user and hit ENTER to continue. Next, switch to the test_company database by typing the following:

出现提示时，输入test_user的密码，然后按ENTER继续。接下来，通过键入以下test_company切换到test_company数据库：

USE test_company;
USE test_company ;

Once the test_company database is selected, MySQL will display:

一旦选择了test_company数据库，MySQL将显示：


   
    Output
   Database changed

Next, create a products table by running:

接下来，运行以下命令来创建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主键AUTO_INCREMENT，product_name VARCHAR(50)，价格DOUBLE，product_image BLOB)ENGINE = InnoDB;

This command creates a table named products. The table has four columns:

此命令创建一个名为products的表。该表有四列：

product_id: This column uses a BIGINT data type in order to accommodate a large list of products up to a maximum of 2⁶³-1 items. You’ve marked the column as PRIMARY KEY to uniquely identify products. In order for MySQL to handle the generation of new identifiers for inserted columns, you have used the keyword AUTO_INCREMENT.
product_id ：此列使用BIGINT数据类型，以便容纳最多2⁶³-1项目的大型产品列表。您已将该列标记为PRIMARY KEY以唯一标识产品。为了使MySQL能够为插入的列生成新的标识符，您使用了关键字AUTO_INCREMENT 。
product_name: This column holds the names of the products. You’ve used the VARCHAR data type since this field will generally handle alphanumerics up to a maximum of 50 characters—the limit of 50 is just a hypothetical value used for the purpose of this tutorial.
product_name ：此列保存产品名称。您已使用VARCHAR数据类型，因为此字段通常最多可处理最多50字符的字母数字-限制为50只是用于本教程目的的假设值。
price: For demonstration purposes, your products table contains the price column to store the retail price of products. Since some products may have floating values (for example, 23.69, 45.36, 102.99), you’ve used the DOUBLE data type.
price ：出于演示目的，您的products表包含price列，用于存储产品的零售价格。由于某些产品可能具有浮动值(例如23.69、45.36、102.99)，因此您使用了DOUBLE数据类型。
product_image: This column uses a BLOB data type to store the actual binary data of the products’ images.
product_image ：此列使用BLOB数据类型存储产品图像的实际二进制数据。

You’ve used the InnoDB storage ENGINE for the table to support a wide range of features including MySQL transactions. After executing this for creating the products table, you’ll see the following output:

您已经在表格中使用了InnoDB存储ENGINE来支持包括MySQL事务在内的各种功能。执行完此操作以创建products表后，您将看到以下输出：


   
    Output
   Query OK, 0 rows affected (0.03 sec)

Log out from your MySQL server:

从您MySQL服务器注销：

QUIT;
退出;

You will get the following output

您将获得以下输出


   
    Output
   Bye

The products table is now ready to store some records including products’ images and you’ll populate it with some products in the next step.

现在， products表准备就绪，可以存储一些记录，包括产品的图像，然后在下一步中向其中填充一些产品。

第2步-创建用于连接和填充数据库PHP脚本 (Step 2 — Creating PHP Scripts for Connecting and Populating the Database)

In this step, you’ll create a PHP script that will connect to the MySQL database that you created in Step 1. The script will prepare three sample products and insert them into the products table.

在此步骤中，您将创建一个PHP脚本，该脚本将连接到您在步骤1中创建MySQL数据库。该脚本将准备三个样本产品并将其插入products表中。

To create the PHP code, open a new file with your text editor:

要创建PHP代码，请使用文本编辑器打开一个新文件：

sudo nano /var/www/html/config.php
须藤纳米/var/www/html/config.php

Then, enter the following information into the file and replace PASSWORD with the test_user password that you created in Step 1:

然后，在文件中输入以下信息，并将PASSWORD替换为您在步骤1中创建的test_user密码：

/var/www/html/config.php

<?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);

Save and close the file.

保存并关闭文件。

In this file, you’ve used four PHP constants to connect to the MySQL database that you created in Step 1:

在此文件中，您已使用四个PHP常量连接到在步骤1中创建MySQL数据库：

DB_NAME : This constant holds the name of the test_company database.
DB_NAME ：此常量保存test_company数据库的名称。
DB_USER : This variable holds the test_user username.
DB_USER ：此变量保存test_user用户名。
DB_PASSWORD : This constant stores the MySQL PASSWORD of the test_user account.
DB_PASSWORD ：此常量存储test_user帐户MySQL PASSWORD 。
DB_HOST: This represents the server where the database resides. In this case, you are using the localhost server.
DB_HOST ：这表示数据库所在的服务器。在这种情况下，您正在使用localhost服务器。

The following line in your file initiates a PHP Data Object (PDO) and connects to the MySQL database:

文件中的以下行将启动PHP 数据对象 (PDO)并连接到MySQL数据库：

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

Toward the end of the file, you’ve set a couple of PDO attributes:

在文件末尾，您已经设置了两个PDO属性：

ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION: This attribute instructs PDO to throw an exception that can be logged for debugging purposes.
ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION ：此属性指示PDO抛出可记录为调试目的的异常。
ATTR_EMULATE_PREPARES, false: This option increases security by telling the MySQL database engine to do the prepare instead of PDO.
ATTR_EMULATE_PREPARES, false ：此选项通过告诉MySQL数据库引擎执行prepare而不是PDO来提高安全性。

You’ll include the /var/www/html/config.php file in two PHP scripts that you will create next for inserting and retrieving records respectively.

您将在两个PHP脚本中包含/var/www/html/config.php文件，接下来将分别创建这两个脚本来分别插入和检索记录。

First, create the /var/www/html/insert_products.php PHP script for inserting records to the products table:

首先，创建/var/www/html/insert_products.php PHP脚本，用于将记录插入到products表中：

sudo nano /var/www/html/insert_products.php
须藤纳米/var/www/html/insert_products.php

Then, add the following information into the /var/www/html/insert_products.php file:

然后，将以下信息添加到/var/www/html/insert_products.php文件中：

/var/www/html/insert_products.php

<?php

require_once 'config.php';

$products = [];

$products[] = [
              'product_name' => 'VIRTUAL SERVERS',
              '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";

Save and close the file.

保存并关闭文件。

In the file, you’ve included the config.php file at the top. This is the first file you created for defining the database variables and connecting to the database. The file also initiates a PDO object and stores it in a $pdo variable.

在该文件中，您已在顶部包含config.php文件。这是您创建的第一个文件，用于定义数据库变量并连接到数据库。该文件还启动一个PDO对象，并将其存储在$pdo变量中。

Next, you’ve created an array of the products’ data to be inserted into the database. Apart from the product_name and price, which are prepared as strings and numeric values respectively, the script uses PHP’s in-built file_get_contents function to read images from an external source and pass them as strings to the product_image column.

接下来，您已经创建了要插入数据库的产品数据数组。除了分别准备为字符串和数字值的product_name和price之外，该脚本还使用PHP的内置file_get_contents函数从外部源读取图像，并将其作为字符串传递给product_image列。

Next, you have prepared an SQL statement and used the PHP foreach{...} statement to insert each product into the database.

接下来，您准备了一个SQL语句，并使用PHP foreach{...}语句将每个产品插入数据库。

To execute the /var/www/html/insert_products.php file, run it in your browser window using the following URL. Remember to replace your-server-IP with the public IP address of your server:

要执行/var/www/html/insert_products.php文件，请使用以下URL在浏览器窗口中运行它。请记住用your-server-IP的公共IP地址替换your-server-IP ：

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

After executing the file, you’ll see a success message in your browser confirming records were inserted into the database.

执行完文件后，您将在浏览器中看到一条成功消息，确认记录已插入数据库中。

You have successfully inserted three records containing product images into the products table. In the next step, you’ll create a PHP script for retrieving these records and displaying them in your browser.

您已成功将三个包含产品图像的记录插入到products表中。在下一步中，您将创建一个PHP脚本来检索这些记录并将其显示在浏览器中。

第3步-从MySQL数据库显示产品的信息 (Step 3 — Displaying Products’ Information From the MySQL Database)

With the products’ information and images in the database, you’re now going to code another PHP script that queries and displays the products’ information in an HTML table on your browser.

在数据库中包含产品信息和图像之后，您现在将编写另一个PHP脚本，该脚本可在浏览器HTML表中查询并显示产品信息。

To create the file, type the following:

要创建文件，请键入以下内容：

sudo nano /var/www/html/display_products.php
须藤纳米/var/www/html/display_products.php

Then, enter the following information into the file:

然后，在文件中输入以下信息：

/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>

Save the changes to the file and close it.

将更改保存到文件并关闭。

Here you’ve again included the config.php file in order to connect to the database. Then, you have prepared and executed an SQL statement using PDO to retrieve all items from the products table using the SELECT * FROM products command.

在这里，您再次包含config.php文件以连接到数据库。然后，您已经使用PDO准备并执行了一条SQL语句，以使用SELECT * FROM products命令从products表中检索所有项目。

Next, you have created an HTML table and populated it with the products’ data using the PHP while() {...} statement. The line $row = $stmt->fetch(PDO::FETCH_ASSOC) queries the database and stores the result in the $row variable as a multi-dimensional array, which you have then displayed in an HTML table column using the $row['column_name'] syntax.

接下来，您创建了一个HTML表，并使用PHP while() {...}语句用产品数据填充了该表。 $row = $stmt->fetch(PDO::FETCH_ASSOC)查询数据库并将结果存储在$row变量中作为多维数组，然后使用$row['column_name']显示在HTML表格列中$row['column_name']语法。

The images from the product_image column are enclosed inside the <img src = ""> tags. You’ve used the width and height attributes to resize the images to a smaller size that can fit in the HTML table column.

product_image列中的图像包含在<img src = "">标记内。您已使用width和height属性将图像调整为可以容纳在HTML表格列中的较小尺寸。

In order to convert the data held by the BLOB data type back to images, you’ve used the in-built PHP base64_encode function and the following syntax for the Data URI scheme:

为了将BLOB数据类型保存的数据转换回图像，您已使用内置PHP base64_encode函数和Data URI方案的以下语法：

data:media_type;base64, base_64_encoded_data

In this case, the image/png is the media_type and the Base64 encoded string from the product_image column is the base_64_encoded_data.

在这种情况下， image/png是media_type和Base64编码从字符串product_image列是base_64_encoded_data 。

Next, execute the display_products.php file in a web browser by typing the following address:

接下来，通过输入以下地址在Web浏览器中执行display_products.php文件：

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

After running the display_products.php file in your browser, you will see an HTML table with a list of products and associated images.

在浏览器中运行display_products.php文件后，您将看到一个HTML表，其中包含产品和相关图像的列表。

This confirms that the PHP script for retrieving images from MySQL is working as expected.

这确认了用于从MySQL检索图像PHP脚本正在按预期工作。

结论 (Conclusion)

In this guide, you used the MySQL BLOB data type to store and display images with PHP on Ubuntu 18.04. You’ve also seen the basic advantages of storing images in a database as opposed to storing them in a file system. These include portability, security, and ease of backup. If you are building an application such as a students’ portal or employees’ database that requires information and related images to be stored together, then this technology can be of great use to you.

在本指南中，您使用MySQL BLOB数据类型在Ubuntu 18.04上使用PHP存储和显示图像。您还已经看到了将图像存储在数据库中而不是将它们存储在文件系统中的基本优点。这些包括可移植性，安全性和易于备份。如果您要构建一个需要将信息和相关图像存储在一起的应用程序(例如学生门户或员工数据库)，那么该技术可能对您很有用。

For more information about the supported data types in MySQL follow the MySQL Data Types guide. If you’re interested in further content relating to MySQL and PHP, check out the following tutorials:

有关MySQL支持的数据类型的更多信息，请遵循MySQL数据类型指南。如果您对与MySQL和PHP有关的其他内容感兴趣，请查看以下教程：

How To Implement Pagination in MySQL with PHP on Ubuntu 18.04
如何在Ubuntu 18.04上使用PHP在MySQL中实现分页
How To Use the PDO PHP Extension to Perform MySQL Transactions in PHP on Ubuntu 18.04
如何使用PDO PHP扩展在Ubuntu 18.04上PHP中执行MySQL事务

翻译自: https://www.digitalocean.com/community/tutorials/how-to-use-the-mysql-blob-data-type-to-store-images-with-php-on-ubuntu-18-04

mysql.blob