Binary Data in MySQL [closed]

2019-01-02 18:02发布

站内问答 / MySQL
1220 9 6

How do I store binary data in MySQL?

9条回答
荒废的爱情
2楼-- · 2019-01-02 18:21

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
    // 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>
查看更多
0人赞 添加讨论(0) 举报
美炸的是我
3楼-- · 2019-01-02 18:28

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

查看更多
0人赞 添加讨论(0) 举报
十年一品温如言
4楼-- · 2019-01-02 18:30

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.

查看更多
0人赞 添加讨论(0) 举报
刘海飞了
5楼-- · 2019-01-02 18:31

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.

查看更多
0人赞 添加讨论(0) 举报
余生请多指教
6楼-- · 2019-01-02 18:34

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

查看更多
0人赞 添加讨论(0) 举报
倾城一夜雪
7楼-- · 2019-01-02 18:41

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.

查看更多
0人赞 添加讨论(0) 举报
1 2 下一页
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(6)