MariaDB 插入 BLOB 图像

2022-01-15 00:00:00 image blob null mariadb mysql

我想做什么?

  • 我想使用命令行，使用 LOAD_FILE 函数将图片插入 MariaDB 数据库.

怎么了?

  • 我总是得到一个 NULL 返回.
  • 我不想要这样的解决方案:这是一种糟糕的风格，到目前为止我还没有看到这个 - 尝试存储完整路径！我想要将此图片存储在此数据库中，而不是路径.
  • I always get a NULL return.
  • I don't want a solution like: This is bad style and I haven't seen this so far - try to store the full path! I want to store this picture in this database and not the path.

系统

  • mysql Ver 15.1 Distrib 10.1.17-MariaDB，适用于 Linux (x86_64)，使用 readline 5.1

  • mysql Ver 15.1 Distrib 10.1.17-MariaDB, for Linux (x86_64) using readline 5.1

ArchLinux 4.7.2-1-ARCH

ArchLinux 4.7.2-1-ARCH

/home/user/Blob/Test.jpg、/tmp/Test.jpg下的一张名为Test.jpg的图片(817KB) 甚至在 /var/lib(mysql/images/Test.jpg

A picture called Test.jpg (817KB) under /home/user/Blob/Test.jpg, /tmp/Test.jpg and even under /var/lib(mysql/images/Test.jpg

图片属于mysql用户和组，拥有我能想象到的所有权限

The picture belongs to the user and group mysql and has every permission I could imagine

-rwxrwxrwx  1 mysql mysql 836508 20. Feb 2016  Test.jpg

  • 我测试了几个用户，即 mysql 和 root

    • 数据库

    我创建了一个名为 Blobtest 的数据库，其中包含一个名为 Test 的表，其中包含一个 Blob 和一个 Longblob 变量.

    I have created a database called Blobtest with a table called Test with a Blob and a Longblob variable. 

    CREATE TABLE Test (id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,longblobimg LONGBLOB NOT NULL, blobimg BLOB NOT NULL, PRIMARY KEY(id));

+-------------+------------------+------+-----+---------+----------------+
| Field       | Type             | Null | Key | Default | Extra          |
+-------------+------------------+------+-----+---------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| longblobimg | longblob         | NO   |     | NULL    |                |
| blobimg     | blob             | NO   |     | NULL    |                |
+-------------+------------------+------+-----+---------+----------------+

    插入声明

    (在本例中为/home/user/directory)

    (in this case with the /home/user/directory)

    INSERT INTO Test VALUES (1, LOAD_FILE('/home/user/Blob/Test.jpg'), LOAD_FILE('/home/user/Blob/Test.jpg'));

    解决此问题的方法

    我按照此链接中的说明操作 MySQL LOAD_FILE() 加载空值

    I followed the instructions at this link MySQL LOAD_FILE() loads null values

    • 我对父目录有执行权限
    • 文件权限是明确授予的.(在 .TO mysql@localhost 上授予文件)
    • 我已刷新权限
    • 我已注销并重新登录
    • 我已经通过 chmod 和 chown 命令测试了几个属于 mysql 或 user 的目录
    • SHOW VARIABLES LIKE 'max_allowed_pa​​cket'; 设置为 16 MB 或 16777216，图片为 817KB 大
    • select HEX(LOAD_FILE('/home/user/Blob/Test.jpg'));返回NULL
    • I have execute permission on the parent directory
    • The FILE privilege is explicily granted. (GRANT FILE on . TO mysql@localhost)
    • I have flushed privileges
    • I have logged out and logged back in
    • I have tested several directories, belonging to mysql or user via chmod and chown command
    • SHOW VARIABLES LIKE 'max_allowed_packet'; is set to 16 MB or 16777216, picture is 817KB big
    • select HEX(LOAD_FILE('/home/user/Blob/Test.jpg')); returns NULL

    解决方案?

    我不知道这是否是 MariaDB 中的一个错误，或者我是否是唯一一个遇到此问题的人.再次指出这一点:我想要将图片存储在这个数据库中.我不想存储路径.这是一个实验，我要把图片存入数据库.

    I don't know if this is a bug in MariaDB or if I'm the only one who has this problem. To point this out again: I want to store the picture within this database. I don't want to store the path. This is an experiment, I have to store the picture in the database.

    如果有人能帮我解决这个问题，那就太棒了！

    It would be awesome if somebody can help me with this problem!

    推荐答案

    LOAD_FILE() 仅在文件位于数据库服务器上时才有效.如果服务器与文件在同一个系统上，那么加载它应该没有问题.如果是这样，我建议打开关于错误报告不足的错误报告.

    LOAD_FILE() only works if the file is located on the database server. If the server is on the same system as the file, then there should be no problems loading it. If so, I'd recommend opening a bug report about inadequate error reporting.

    通常连接器提供将二进制数据加载到数据库中的方法.例如，MariaDB C 连接器提供 mysql_stmt_send_long_data.

    Usually connectors provide methods for loading binary data into the database. For example the MariaDB C connector provides mysql_stmt_send_long_data.

    另一种选择是在客户端将数据转换为非二进制编码(例如 base64 或十六进制)并在服务器上对其进行解码(base64 与 FROM_BASE64 和十六进制与 UNHEX).

    Another option is to convert the data into a non-binary encoding (e.g. base64 or hex) on the client side and decode it on the server (base64 with FROM_BASE64 and hex with UNHEX).

    相关文章