如何仅使用 sql 语法（不使用 PHP）在 mysql 表中的 blob 中插入图像？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34328601/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert images in blob in mysql table using only sql syntax (without PHP)?
提问by xxx
- Hi,I am new to SQL and I wanted to store images in the database.I already created a column with blob data type and tried to execute the following statement as given here
- 嗨，我是 SQL 新手，我想将图像存储在数据库中。我已经创建了一个 blob 数据类型的列，并尝试执行此处给出的以下语句
INSERT INTO `abc`
(`img`)
SELECT
BulkColumn FROM OPENROWSET(
Bulk 'C:\Users\adity\Desktop\New folder\a.png', SINGLE_BLOB) AS BLOB
which gives error
这给出了错误
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( Bulk
C:\Users\name\Desktop\New folder\a.png, SINGLE_BLOB) AS BLOB' at line 4
您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册
C:\Users\name\Desktop\New folder\a.png，了解在第 4 行的“( Bulk , SINGLE_BLOB) AS BLOB”附近使用的正确语法
I also tried following code as given here
我也试过按照这里给出的代码
insert into table `abc`(`img`) values('C:\Users\name\Desktop\New folder\an.jpg') where id=1;
which gives the error
这给出了错误
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table
abc(img) values('C:\Users\adity\Desktop\New folder\an.jpg') where id=1' at line 1
#1064 - 你的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在第 1 行处使用近 'table
abc(img) values('C:\Users\adity\Desktop\New folder\an.jpg') where id=1'的正确语法
So please suggest me how to store images in a blob without using php,etc and simply using simple sql insert statement.I am using wamp server for my database.
所以请建议我如何在不使用 php 等的情况下将图像存储在 blob 中，而只需使用简单的 sql 插入语句。我正在为我的数据库使用 wamp 服务器。
- I know that I should use file system for images instead of using database.But what does a file system actually mean.Does it mean a file or image hosting site whose address will be stored in database.
- 我知道我应该对图像使用文件系统而不是使用数据库。但是文件系统实际上是什么意思。它是指地址将存储在数据库中的文件或图像托管站点。
回答by aceraven777
I think that command is a MSSQL syntax. Try this command:
我认为该命令是 MSSQL 语法。试试这个命令：
INSERT INTO `abc`
(`img`)
VALUES
(LOAD_FILE('C:/Users/adity/Desktop/New folder/a.png'))
This command stores image as a BLOB
此命令将图像存储为 BLOB
回答by Kona Suresh
Through Mysql workbench, its very easy to load images into database using the following steps.
通过 Mysql 工作台，使用以下步骤可以很容易地将图像加载到数据库中。
- Right click on the value of the (blob)column in the table and select "Load value from file".
- Then we can provide the image path in the system.
- Then it will converted into byte array and stored it automatically.
- finally save the changes of the table.
- 右键单击表中 (blob) 列的值，然后选择“从文件加载值”。
- 然后我们可以在系统中提供图像路径。
- 然后它将转换为字节数组并自动存储。
- 最后保存表的更改。
回答by tk_
Below works for me,
下面对我有用，
However, I was able to get it done by moving the image(fileName.jpg) file first in to below folder(in my case) C:\ProgramData\MySQL\MySQL Server 5.7\Uploadsand then I executed below command and it works for me,
但是，我能够通过首先将图像（fileName.jpg）文件移动到下面的文件夹（在我的情况下）C:\ProgramData\MySQL\MySQL Server 5.7\Uploads然后我执行下面的命令来完成它，它对我有用，
INSERT INTO `abc`
(`img`)
VALUES
(LOAD_FILE('C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/an.jpg'));
Hope this helps.
希望这可以帮助。
