MySQL의 이진 데이터
바이너리 데이터를 MySQL에 어떻게 저장 합니까?
phpguy의 대답은 정확하지만 추가 세부 정보에는 많은 혼란이 있다고 생각합니다.
기본 답변은 BLOB데이터 유형 / 속성 도메인에 있습니다. BLOB 는 이진 큰 개체 (Binary Large Object)의 약자이며 해당 열 데이터 형식은 이진 데이터를 처리하기위한 것입니다.
MySQL 관련 매뉴얼 페이지를 참조하십시오 .
이와 같은 테이블의 경우 :
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)
);
다음은 PHP 예제입니다.
<?php
// store.php3 - by Florian Dittmer <dittmer@gmx.net>
// 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>
I strongly recommend against storing 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 article on 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.
Instead, store files in the file system, and store file names in your database.
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
It depends on the data you wish to store. The above example uses the LONGBLOB data type, but you should be aware that there are other binary data formats:
TINYBLOB/BLOB/MEDIUMBLOB/LONGBLOB
VARBINARY
BINARY
Each has their use cases. If it is a known (short) length (e.g. packed data) often times BINARY or VARBINARY will work. They have the added benefit of being able ton index on them.
While it shouldn't be necessary, you could try base64 encoding 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.
If the - not recommended - BLOB field exists, you can save data this way:
mysql_query("UPDATE table SET field=X'".bin2hex($bin_data)."' WHERE id=$id");
Idea taken from here.
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_string instead of addslashes). If the file exists on the database server, you can also use MySQL's LOAD_FILE
When I need to store binary data I always use VARBINARY format, as introduced byd0nut.
You can find documentation at MySQL website under documented topic 12.4.2 The BINARY and VARBINARY Types
If you are asking what are advantagese, please look at question why-varbinary-instead-of-varchar
참고URL : https://stackoverflow.com/questions/17/binary-data-in-mysql
'IT story' 카테고리의 다른 글
| 파일에서 임의의 줄을 선택하십시오 (0) | 2020.05.21 |
|---|---|
| UIScrollView에서 스크롤 방향을 찾으십니까? (0) | 2020.05.21 |
| 자식에서 분기를 삭제하면 기록에서 제거됩니까? (0) | 2020.05.20 |
| T-SQL 저장 프로 시저에서 선택적 매개 변수를 사용하려면 어떻게해야합니까? (0) | 2020.05.20 |
| PHP에서 문자열의 중괄호 (0) | 2020.05.20 |