MySQL 中的二进制数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17/
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
Binary Data in MySQL
采纳答案by Mat
The answer by phpguy is correct but I think there is a lot of confusion in the additional details there.
phpguy 的回答是正确的，但我认为那里的附加细节有很多混乱。
The basic answer is in a BLOBdata type / attribute domain. BLOBis short for Binary Large Object and that column data type is specific for handling binary data.
基本答案是在BLOB数据类型/属性域中。BLOB是 Binary Large Object 的缩写，该列数据类型专用于处理二进制数据。
See the relevant manual page for MySQL.
请参阅MySQL 的相关手册页。
回答by Geoff Dalgas
For a table like this:
对于这样的表：
CREATE TABLE binary_data (
id INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,
description CHAR(50),
bin_data LONGBLOB,
filename CHAR(50),
filesize CHAR(50),
filetype CHAR(50)
);
Here is a PHP example:
这是一个 PHP 示例：
<?php
// store.php3 - by Florian Dittmer <[email protected]>
// Example php script to demonstrate the storing of binary files into
// an sql database. More information can be found at http://www.phpbuilder.com/
?>
<html>
<head><title>Store binary data into SQL Database</title></head>
<body>
<?php
// Code that will be executed if the form has been submitted:
if ($submit) {
// Connect to the database (you may have to adjust
// the hostname, username or password).
mysql_connect("localhost", "root", "password");
mysql_select_db("binary_data");
$data = mysql_real_escape_string(fread(fopen($form_data, "r"), filesize($form_data)));
$result = mysql_query("INSERT INTO binary_data (description, bin_data, filename, filesize, filetype) ".
"VALUES ('$form_description', '$data', '$form_data_name', '$form_data_size', '$form_data_type')");
$id= mysql_insert_id();
print "<p>This file has the following Database ID: <b>$id</b>";
mysql_close();
} else {
// else show the form to submit new data:
?>
<form method="post" action="<?php echo $PHP_SELF; ?>" enctype="multipart/form-data">
File Description:<br>
<input type="text" name="form_description" size="40">
<input type="hidden" name="MAX_FILE_SIZE" value="1000000">
<br>File to upload/store in database:<br>
<input type="file" name="form_data" size="40">
<p><input type="submit" name="submit" value="submit">
</form>
<?php
}
?>
</body>
</html>
回答by Issac Kelly
While you haven't said what you're storing, and you may have a great reason for doing so, often the answer is 'as a filesystem reference' and the actual data is on the filesystem somewhere.
虽然您没有说明要存储的内容，而且您可能有充分的理由这样做，但通常答案是“作为文件系统参考”，而实际数据位于文件系统的某个位置。
http://www.onlamp.com/pub/a/onlamp/2002/07/11/MySQLtips.html
http://www.onlamp.com/pub/a/onlamp/2002/07/11/MySQLtips.html
回答by Scott Noyes
The question also arises how to get the data into the BLOB. You can put the data in an INSERT statement, as the PHP example shows (although you should use mysql_real_escape_stringinstead of addslashes). If the file exists on the database server, you can also use MySQL's LOAD_FILE
问题还出现在如何将数据放入 BLOB 中。您可以将数据放入 INSERT 语句中，如 PHP 示例所示（尽管您应该使用mysql_real_escape_string而不是 addslashes）。如果该文件存在于数据库服务器上，也可以使用 MySQL 的LOAD_FILE
回答by user10117
While it shouldn't be necessary, you could try base64encoding data in and decoding it out. That means the db will just have ascii characters. It will take a bit more space and time, but any issue to do with the binary data will be eliminated.
虽然没有必要，但您可以尝试base64将数据编码并解码出来。这意味着数据库将只有 ascii 字符。这将需要更多的空间和时间，但与二进制数据有关的任何问题都将被消除。
回答by Alex Weinstein
I strongly recommend againststoring binary data in a relational database. Relational databases are designed to work with fixed-size data; that's where their performance strength is: remember Joel's old articleon why databases are so fast? because it takes exactly 1 pointer increment to move from a record to another record. If you add BLOB data of undefined and vastly varying size, you'll screw up performance.
我强烈建议不要将二进制数据存储在关系数据库中。关系数据库旨在处理固定大小的数据；这就是它们的性能优势所在：还记得Joel关于为什么数据库如此之快的旧文章吗？因为从一个记录移动到另一个记录只需要 1 个指针增量。如果您添加未定义且大小变化很大的 BLOB 数据，您会搞砸性能。
Instead, store files in the file system, and store file names in your database.
相反，将文件存储在文件系统中，并将文件名存储在数据库中。
回答by d0nut
It depends on the data you wish to store. The above example uses the LONGBLOBdata type, but you should be aware that there are other binary data formats:
这取决于您要存储的数据。上面的例子使用了LONGBLOB数据类型，但你应该知道还有其他二进制数据格式：
TINYBLOB/BLOB/MEDIUMBLOB/LONGBLOBVARBINARYBINARY
TINYBLOB/BLOB/MEDIUMBLOB/LONGBLOBVARBINARYBINARY
Each has their use cases. If it is a known (short) length (e.g. packed data) often times BINARYor VARBINARYwill work. They have the added benefit of being able ton index on them.
每个都有自己的用例。如果它是已知的（短的）长度（例如打包数据），则通常BINARY或VARBINARY将起作用。它们具有能够对它们进行索引的额外好处。
回答by d0nut
If the - not recommended- BLOB field exists, you can save data this way:
如果 -不推荐- BLOB 字段存在，您可以这样保存数据：
mysql_query("UPDATE table SET field=X'".bin2hex($bin_data)."' WHERE id=$id");
Idea taken from here.
想法取自这里。
回答by zeppaman
When I need to store binary data I always use VARBINARYformat, as introduced byd0nut.
当我需要存储二进制数据时，我总是使用VARBINARY格式，正如d0nut所介绍的那样。
You can find documentation at MySQL website under documented topic 12.4.2 The BINARY and VARBINARY Types
您可以在 MySQL 网站的文档主题12.4.2 The BINARY and VARBINARY Types下找到文档
If you are asking what are advantagese, please look at question why-varbinary-instead-of-varchar
如果你问有什么好处，请看问题why-varbinary-instead-of-varchar
