如何在 MySQL / MariaDB 中实现图片存储,BLOB 数据类型详解,BLOB 有哪些应用场景?

A kitten
蒋川
B 端产品经理,卡拉云联合创始人
阅读时长 2 分钟

如何在 MySQL / MariaDB 中实现图片存储,BLOB 数据类型详解,BLOB 有哪些应用场景?

大多数情况,并不推荐直接把文件存放在 MySQL 数据库中,但如果应用场景是文件与数据高度耦合,那么将文件与数据存放在一起,即安全快捷,又方便备份和迁移。

这种情况多见于 学生档案、员工信息、产品信息等。比如 学生信息系统,学生等数据、证件照、学术 pdf 文件等存放在同一张表里,非常方便调取查看和迁移备份

MySQL 提供 BLOB 数据类型用于应对这样的需求,这种格式无需单独创建文件系统来存储图片、pdf 或任何多媒体文件,非常实用。

本教程将交给大家如何使用 Ubuntu + PHP 或卡拉云(低代码开发工具)搭建一套「学生信息管理系统」,并将学生的证件照存放在 MySQL 数据库中的 BLOB 数据格式中。

先决条件

学习教程前,你将需要准备:

  • 安装有 LAMP 的 Ubuntu 20.04 系统、有 root 权限账号
  • 开通卡拉云账号(免费注册

创建数据库

首先连上服务器,登录 MySQL server:

$ sudo mysql -u root -p

输入你的 MySQL 数据库 root 账号的密码,然后 ENTER 登录;

我们创建一个演示数据库,my_school:

mysql> CREATE DATABASE my_school;

创建数据库后,命令行返回成功信息:

Query OK, 1 row affected (0.01 sec)

接下来,我们创建一个账号用于管理这个数据库,这里账号名为 teacher 并设置强密码 PASSWORD


CREATE USER 'teacher'@'localhost' IDENTIFIED BY 'PASSWORD';

执行命令后,返回信息:

Query OK, 0 rows affected (0.01 sec)

提示:ERROR 1819 (HY000): Your password does not satisfy the current policy requirement;

如果在创建账号时,返回以上这段错误提示,说明你的密码不符合规范,这里强密码的定义是 8 位、包含大小写字母、包含符号。三者缺一不可,比如 KaLaCloud777% 这种是符合标准的密码。

接着我们赋予 teacher 读写 my_school 数据库的所有权限:

GRANT ALL PRIVILEGES ON my_school.* TO 'teacher'@'localhost';

返回结果为:

Output
Query OK, 0 rows affected (0.01 sec)

最后,一定要记得刷新权限表,让 MySQL 重新加载我们刚刚的设置:

FLUSH PRIVILEGES;

确保您看到以下输出:

Query OK, 0 rows affected (0.01 sec)

现在 my_school 数据库和管理员teacher 都已经有了。接着我们来创建表 students 用于记录学生信息和存储学生证件照。在后文中,你将使用 students 表以 BLOB 格式插入学生照片。

退出 MySQL server:

QUIT;

然后我们使用刚刚创建的 teacher 账号来登录 MySQL

mysql -u teacher -p

按照提示输入 teacher 账号的密码,ENTER 登录 MySQL,然后选择使用 my_school 这个数据库:

USE my_school;

选择成功,命令行返回信息:

Output
Database changed

接着,我们来创建表 students

create table students ( 
student_id BIGINT PRIMARY KEY AUTO_INCREMENT,
student_name VARCHAR(50) NOT NULL, 
age DOUBLE,
student_photo BLOB NOT NULL
) Engine = InnoDB;

创建的这个表(students)包含四列:

  • student_id:此列使用 BIGINT 数据类型,最大容纳 2⁶³-1 的整数,我们将学生 ID 标记为主键,并且让 MySQL 自动顺序生成。

  • student_name :学生姓名,使用VARCHAR数据类型,该字段可最多处理 50 个字符(限制50个字符仅从本教程角度设置,你可以根据需求自行修改)

  • student_photo :使用 BLOB 格式存储,可存储图片的二进制数据。

    Output
    Query OK, 0 rows affected (0.03 sec)
    

之后,退出 MySQL Server:

QUIT;

命令行输出:

Output
Bye

学校学生档案数据库已经准备就绪,下面我们使用 PHP 或 卡拉云向数据库写入学生数据(包括学生照片)。

方案A:第一步 - 创建 PHP 连接并导入学生信息

此步骤我们一起写一个 PHP 脚本,使用这个脚本连接上文创建的 MySQL 数据库 my_school ,然后使用脚本向 students 表中插入三名学生的信息。

如果你的服务器上没有安装 LAMP,可跳过此方案,直接看「方案B:使用卡拉云快速搭建学生信息库」。

首先,我们创建一个 PHP 连接数据库的配置页

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

然后,在编辑器中输入以下信息,请替换 PASSWORD 为你给 teacher 账号设置的密码:

<?php

define('DB_NAME', 'my_school');
define('DB_USER', 'teacher');
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);

使用 CTRL+X 保存并关闭文件。

在此文件中,您使用了四个 PHP 常量连接到您在上文中创建的 MySQL 数据库:

  • DB_NAME:这个常量保存 my_school 数据库的名称。
  • DB_USER:这个变量保存 teacher 用户名。
  • DB_PASSWORD:这个常量存储 PASSWORDteacher账户的MySQL 。
  • DB_HOST:这表示数据库所在的服务器。在这种情况下,您使用的是localhost服务器。

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

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

接着,我们再创建两个 PHP 脚本,分别用于「写入学生信息数据」和「展示学生信息数据」,这两个页面都读取 config.php

首先,创建/var/www/html/insert_students.php用于将记录插入到 students 表的PHP 脚本:

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

然后,将以下信息添加到 /var/www/html/insert_students.php 文件中:

<?php

require_once 'config.php';

$students = [];

$students[] = [
              'student_name' => 'Jack Ma',
              'age' => 17,
              'student_photo' => file_get_contents("https://pic.imgdb.cn/item/614201662ab3f51d91e492ac.jpg")
              ];

$students[] = [
              'student_name' => 'Jun Lei',
              'age' => 16,
              'student_photo' => file_get_contents("https://pic.imgdb.cn/item/614201662ab3f51d91e492b3.jpg")
              ];

$students[] = [
              'student_name' => 'Pony Ma',
              'age' => 17,
              'student_photo' => file_get_contents("https://pic.imgdb.cn/item/614201662ab3f51d91e492c4.jpg" )
              ];

$sql = "INSERT INTO students(student_name, age, student_photo) VALUES (:student_name, :age, :student_photo)";

foreach ($students as $student) {
    $stmt = $pdo->prepare($sql);
    $stmt->execute($student);
}

echo "所有学生信息已导入学生数据库,状态:成功";

使用 CTRL+X 保存并关闭文件。

在文件中,我们引用了 config.php 文件。这使得此页可以拿到数据库的权限写入数据。

接着,我们把已经写好的数据写入数据库中。使用一条 SQL 语句并使用 PHPforeach{...}语句将每个学生信息插入到数据库中。

在浏览器中打开这个 insert_students.php 看效果

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

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

01-insert_students.php.jpg

您已成功将包含产品图像的三个记录插入到students表中。

在下一步中,您将创建一个 PHP 脚本来检索这些记录并将它们显示在您的浏览器中。

方案A:第二步 - 从 MySQL 数据库显示产品信息

上一步,我们已经将学生信息及照片写入到数据库中,接下来我们再写一个 PHP 脚本,调取数据库信息并展示在页面上。

我们创建一个 display_students.php 作为学生信息系统的展示页

sudu nano /var/www/html/display_students.php

然后,在编辑器中输入以下信息:

<html>
  <title>卡拉云大学 - 学生信息库 - 使用 BLOB 存储学生照片</title>
  <body>

  <?php

  require_once 'config.php';

  $sql = "SELECT * FROM students";
  $stmt = $pdo->prepare($sql);
  $stmt->execute();
  ?>

  <table border = '1' align = 'center'> <caption> 卡拉云大学「学生信息库」 </caption>
    <tr>
      <th> 学生 ID </th>
      <th> 学生姓名 </th>
      <th> 年龄 </th>
      <th> 证件照 </th>
    </tr>

  <?php
  while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
      echo '<tr>';
      echo '<td>' . $row['student_id'] . '</td>';
      echo '<td>' . $row['student_name'] . '</td>';
      echo '<td>' . $row['age'] . '</td>';
      echo '<td>' .
      '<img src = "data:image/png;base64,' . base64_encode($row['student_photo']) . '" width = "60px" height = "60px"/>'
      . '</td>';
      echo '</tr>';
  }
  ?>

  </table>
  </body>
</html>

使用 CTRL+X 保存并关闭文件。

在这里,再次调用了 config.php 连接到数据库。然后,使用 PDO 准备并执行了一条 SQL 语句,SELECT * FROM studentsstudent 信息查询出来。

接下来,创建一个 HTML 表并使用 PHP while() {...} 语句填写学生信息。

$row = $stmt->fetch(PDO::FETCH_ASSOC) 查询数据库,然后使用$row['column_name']语法把它显示在页面上。

student_photo 列中的图像包含在<img src = "">标签内。你可以使用widthheight属性将图像调整为更小的尺寸。

为了将BLOB数据类型保存的数据转换回图像,我们使用 PHP 内置的 base64_encode函数和数据 URI 方案的以下语法:

data:media_type;base64, base_64_encoded_data

接下来,我们在浏览器里打开 display_students.php 这个页面

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

display_students.php浏览器中运行该文件后,你能看到一个 HTML 表格上有学生信息与对应的照片。

display_students

方案B:使用卡拉云快速搭建学生信息库

卡拉云是新一代低代码开发工具,内置常见的前端组件,拖拽生成。只需几行代码即可快速搭建「学生信息系统」

接下来我来演示一下,如何使用卡拉云,在 5 分钟内搭建「学生信息系统」,并使用上传组件上传「学生证件照」到数据库,使用 BLOB 格式存储,不用写复杂的base64_encode 转换,卡拉云全都帮你解决好了。

首先,你可以免费注册卡拉云账号,登录卡拉云后,我们先连接数据库。卡拉云支持市面上常见的多数数据库,仅需简单配置即可快速连接。

卡拉云-数据源

卡拉云支持接入常见的数据库、API

04-kalacloud-mysql.jpg

仅需简单的配置,即可接入远程 MySQL 数据库(上图为 host 在腾讯云的 MySQL)

05-set-table.gif

拖拽表格组件到画布上,「新建查询」命名为 get_list

select * from students;

我们先把学生列表拉下来。

然后拖拽一个「表格」组件放在画布上,调整好大小,然后在表格数据中填写 {{get_list.data}} 将表格组件与 get_list 我们可以看到数据库中的数据已经投射到表格中。

06-kalacloud-students.jpg

同样的方法,把我们需要的组件拖拽到合适的地方。

再新建一个查询 insert_student_info 在这个查询的 SQL 语句编辑框中填写

INSERT INTO students (student_name,age,student_photo) 
VALUES ("{{input2.value}}",{{input3.value}},"{{filepicker1.file.data}}");

student_nameagestudent_photo 三个列的值,对应三个组件即「学生姓名」{{input2.value}} 、「学生年龄」{{input3.value}} 、「学生证件照」{{filepicker1.file.data}} 。如上图所示,卡拉云「上传文件」组件,已经自动把图片处理转化成 BLOB 可存储的 base 64

07-kalacloud-complete.jpg

点击右上角的「预览」锁住编辑。至此一套「学生信息系统」就搭建完成了,我们可以新建和编辑学生信息,上传的学生照片,直接转换成 base64 存入 BLOB 格式的数据库中,简单方便,所见即所得,你还可以把开发号的系统一键分享给身边的同学共享使用。

总结

本教程详细讲解了 BLOB 作用和意义,在 Ubuntu 20.04 上与 PHP 的搭建系统的应用,在卡拉云上构建学生信息系统时,存储学生照片的应用。图片与其对应的数据信息一起存储在数据库中,简单快捷又安全,数据与图片同在一个数据库中,也方便迁移与备份。特别适合学生、员工、产品等少量图片对应数据的应用场景。

更多有关 MySQL 教程,欢迎访问卡拉云

如果你还有什么疑问,欢迎一起讨论。我的微信 HiJiangChuan。

卡拉云创始人蒋川的微信

有关 MySQL 教程,可继续拓展学习:

© 2021 卡拉云 Built with ❤️ in Beijing

京ICP备15049164号-9