MySQL 如何在mysql数据库（表）中插入图像？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14704559/
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 image in mysql database(table)?
提问by Viru
I want to insert image into a table like
我想将图像插入到表格中
CREATE TABLE XX_SAMPLE(ID INT
,IMAGE BLOB);
So can you help out form how to insert image into the above table.
那么你能帮助形成如何在上表中插入图像吗？
回答by Madhav
Please try below code
请尝试以下代码
INSERT INTO xx_BLOB(ID,IMAGE) VALUES(1,LOAD_FILE('E:/Images/Hyman.jpg'));
回答by Ivaylo Strandjev
回答by x'tian
You can try something like this..
你可以试试这样的..
CREATE TABLE 'sample'.'picture' (
'idpicture' INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
'caption' VARCHAR(45) NOT NULL,
'img' LONGBLOB NOT NULL,
PRIMARY KEY('idpicture')) TYPE = InnoDB;
or refer to the following links for tutorials and sample, that might help you.
或参考以下链接以获取教程和示例，这可能对您有所帮助。
http://forums.mysql.com/read.php?20,17671,27914
http://forums.mysql.com/read.php?20,17671,27914
http://mrarrowhead.com/index.php?page=store_images_mysql_php.php
http://mrarrowhead.com/index.php?page=store_images_mysql_php.php
http://www.hockinson.com/programmer-web-designer-denver-co-usa.php?s=47
http://www.hockinson.com/programmer-web-designer-denver-co-usa.php?s=47
回答by samuel owino
I have three answers to this question:
对于这个问题，我有三个答案：
It is against user experienceUX best practice to use BLOB and CLOB data types in string and retrieving binary data from an SQL database thus it is advised that you use the technique that involves storing the URL for the image( or any Binary file in the database). This URL will help the user application to retrieve and use this binary file.
Second the BLOB and CLOB data types are only available to a number of SQL versions thus functions such as LOAD_FILE or the datatypes themselves could miss in some versions.
Third DON'T USE BLOB OR CLOB. Store the URL; let the user application access the binary file from a folder in the project directory.
在字符串中使用 BLOB 和 CLOB 数据类型并从 SQL 数据库中检索二进制数据违反用户体验UX 最佳实践，因此建议您使用涉及存储图像 URL（或数据库中的任何二进制文件）的技术）。此 URL 将帮助用户应用程序检索和使用此二进制文件。
其次，BLOB 和 CLOB 数据类型仅适用于许多 SQL 版本，因此 LOAD_FILE 等函数或数据类型本身在某些版本中可能会丢失。
第三，不要使用 BLOB 或 CLOB。存储网址；让用户应用程序从项目目录中的文件夹访问二进制文件。
回答by Ganesan J
Step 1: open your mysql workbench application select table. choose image cell right click select "Open value in Editor"
步骤 1：打开您的 mysql 工作台应用程序选择表。选择图像单元格右键单击选择“在编辑器中打开值”
Step 2: click on the load button and choose image file
第 2 步：单击加载按钮并选择图像文件
Step 3:then click apply button
第三步：然后点击应用按钮
Step 4: Then apply the query to save the image .Don't forgot image data type is "BLOB".
Step 5: You can can check uploaded image
第 4 步：然后应用查询来保存图像。不要忘记图像数据类型是“BLOB”。第 5 步：您可以查看上传的图片
回答by tk_
I tried all above solution and fail, it just added a null file to the DB.
我尝试了上述所有解决方案但失败了，它只是向数据库添加了一个空文件。
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 xx_BLOB(ID,IMAGE) VALUES(1,LOAD_FILE('C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/fileName.jpg'));
Hope this helps.
希望这可以帮助。
回答by karthik surya
If I use the following query,
如果我使用以下查询，
INSERT INTO xx_BLOB(ID,IMAGE)
VALUES(1,LOAD_FILE('E:/Images/xxx.png'));
Error: no such function: LOAD_FILE
错误：没有这样的功能：LOAD_FILE
回答by Ganesan J
This is on mysql workbench -- give the image file path:
这是在mysql工作台上——给出图像文件路径：
INSERT INTO XX_SAMPLE(id,image) VALUES(3,'/home/ganesan-pc/Documents/aios_database/confe.jpg');
