MySQL 直接通过mySQL命令行上传图片
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8439202/
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
Upload image directly through mySQL Command Line
提问by BurninatorDor
I have a certain table in mySQL which has a field called "image" with a datatype of "BLOB". I was wondering if it is possible to upload an image in that field directly from the Command Line Client rather than doing it through php...If it is possible, then where exactly should I place my image files?
我在 mySQL 中有一个特定的表，它有一个名为“image”的字段，其数据类型为“BLOB”。我想知道是否可以直接从命令行客户端上传该字段中的图像，而不是通过 php 上传...如果可能，那么我应该将图像文件放在哪里？
回答by Bill Karwin
Try using the LOAD_FILE()function.
尝试使用LOAD_FILE()函数。
UPDATE `certain_table`
SET image = LOAD_FILE('/full/path/to/new/image.jpg')
WHERE id = 1234;
See the manual for requirements about the path to the filename, privileges, etc.
有关文件名路径、权限等的要求，请参见手册。
回答by Teudimundo
LOAD_FILEworks only with certain privileges and if the file is on the server. I've found out a way to make it work completely client side:
LOAD_FILE仅适用于某些特权并且文件在服务器上。我找到了一种让它在客户端完全工作的方法：
mysql -e "update mytable set image=FROM_BASE64('`base64 -i image.png`')" DBNAME
The idea is to encode the image to base64 on the fly and let then MySql to decode it.
这个想法是将图像动态编码为 base64，然后让 MySql 对其进行解码。
回答by Tobia
This is a variation on Teudimundo's answer that works with older MySQL versions, where Base64 functions are not available:
这是Teudimundo答案的变体，适用于旧版 MySQL，其中 Base64 函数不可用：
mysql -e "update mytable set col = x'$(xxd -p image.png | tr -d \n)' where ..."
The trick is to use xxd -pto convert a binary file to a plain hexdump:
诀窍是使用xxd -p将二进制文件转换为普通的十六进制转储：
$ xxd -p /usr/share/font-manager/data/blank.png
89504e470d0a1a0a0000000d4948445200000040000000400806000000aa
6971de000000274944415478daedc1010d000000c220fba77e0e37600000
00000000000000000000000000c0bd0040400001ee1dbb2f000000004945
4e44ae426082
then using tr -d \\nto remove the newlines, and finally embedding the result into a MySQL-specific hexdump string literal: x'...'
然后使用tr -d \\n删除换行符，最后将结果嵌入到特定于 MySQL 的 hexdump 字符串文字中：x'...'
回答by mugetsu
I recommend you to never upload images directly in a database, it's quite inefficient. It's better to simply store the location and name of the image and store those images in a folder somewhere.
我建议你永远不要直接在数据库中上传图片，这样效率很低。最好简单地存储图像的位置和名称，并将这些图像存储在某个文件夹中。
and if you want to "upload" via the commandline, you can just do an:
如果您想通过命令行“上传”，您可以执行以下操作：
insert into table(image_loc) values('/images/random/cool.jpg') where id=1;
and depending on your environment you can use shell access to move images around. I'm not quite sure what you are trying to do with these images or how your system is setup. You'll probably need to clarify on that.
根据您的环境，您可以使用 shell 访问来移动图像。我不太确定你想用这些图像做什么，或者你的系统是如何设置的。你可能需要澄清这一点。
回答by BurninatorDor
It is more preferable to build a sample application and then insert the values in the database. For instance this method could be used to enter a BLOB datatype into the database...
更可取的是构建一个示例应用程序，然后将值插入到数据库中。例如，此方法可用于将 BLOB 数据类型输入数据库...
[WebMethod]
public string sendDataToMySql(string get_name, byte[] buffer)
{
string MyConString = "SERVER=localhost;" +
"DATABASE=test;" +
"UID=root;" +
"PASSWORD=admin;";
MySqlConnection connection = new MySqlConnection(MyConString);
connection.Open();
MySqlCommand command = new MySqlCommand("", connection);
command.CommandText = "insert into testImage(name, image) values(@name, @image);";
MySqlParameter oParam1 = command.Parameters.Add("@name", MySqlDbType.VarChar, 50);
oParam1.Value = get_name;
MySqlParameter oParam2 = command.Parameters.Add("@image", MySqlDbType.Blob);
oParam2.Value = buffer;
command.ExecuteNonQuery();
connection.Close();
return "Data was inserted successfully!";
}
回答by sushil surana
Sometimes we try to upload file using loadfile but file is not loaded or file path in formatted text is stored in BLOB field. This is because of access issues. If you are facing such condition, instead of loading file from any location, try to load it from data path of mysql preferably like :
有时我们尝试使用 loadfile 上传文件，但未加载文件或格式化文本中的文件路径存储在 BLOB 字段中。这是因为访问问题。如果您遇到这种情况，不要从任何位置加载文件，而是尝试从 mysql 的数据路径加载它，最好像：
INSERT INTO `srms`.`images` (`ID`, `Image`) VALUES ('5', load_file('C:\ProgramData\MySQL\MySQL Server 5.7\Uploads\test.jpg'));
