Storing and Retrieving Binary DataOne of the more common questions people ask regarding MySQL is how to store and retrieve binary data in a database. Binary data includes items such as an image, a PDF file, or a video clip. These items have to be handled differently than you would the things you normally file in a database: simple text strings, dates, and numbers. Show For this example, I'll use PHP as the programming language. This is a logical choice, as Web content often includes binary data (specifically images). If you're using JSP or even CGI scripts written in Perl, you should still be able to follow along. I'll start by creating the database. Then I'll write one page that stores the binary data and lists all of the currently stored records. The third step will be to create a script that retrieves and displays the data. In this chapter I teach how to store binary data, but there is some debate as to whether or not you should store binary data in your database. The alternative would be to store the file on the server in a convenient location, and then store the filename in the database. There are pros and cons to both methods. On the one hand, storing binary data in a database allows you to back it up at the same time as you back up the rest of the data. It also makes those files accessible to anyone with access to the database. This means that the binary files can be accessible to multiple computers, as well. On the other hand, you'll need to write extra SQL and code in order to store and retrieve this information. Your application may have decreased performance, too. In the end, it's really up to the developer and the needs of the application as to which method you use, but it's great that MySQL offers different options. You should experiment with both approaches to see which you like the best. Creating the databaseTo store binary data in your database, you should first make a column of type BLOB. CREATE TABLE binary ( binary_item BLOB ) MySQL supports different sizes of BLOBsTINYBLOB, MEDIUMBLOB, BLOB, and LONGBLOBeven though the SQL standard does not. There's also BINARY and VARBINARY, which store smaller amounts of binary data, too small to use in this example. For this example I will store imagesJPEGs, GIFs, and PNGsin a database. Along with the actual image, I will store the image's original name, its MIME type, its size in pixel dimensions, and its size in bytes. Of this metadata, the type is the most important, as PHP needs to know that information in order to properly show the image in a Web browser. To create the database:
Storing binary dataOnce you've established a table field that can take binary data, you could use the LOAD_FILE() function to store data in it. Using the SET notation, an example INSERT would be: INSERT INTO tablename SET image_col= LOAD_FILE('/path/to/file.ext'), SET other_col='value'... The LOAD_FILE() function takes as its lone argument the full path and name of the file on the server (e.g., C:/data/myfile.xls). There are two requirements for using this method:
This last requirement may be an issue, as, for security purposes, only administrators should have FILE permission. An alternative to using LOAD_FILE() is to have your programming language read the file into a variable and then use that variable in a query, just as you would any other piece of data. Using this method, the first of the two preceding conditions still applies but the second does not. To demonstrate this, I'll create a PHP script that uploads an image from a form and stores it in the database. I will use the Improved MySQL extension functions, discussed in Chapter 7, "MySQL and PHP." This assumes that you are using at least version 5 of PHP, with support for this extension (the version of MySQL will not matter in this example). To store binary data:
Retrieving binary dataNow that I've written a script for storing images in a database, I'll create another that will retrieve and display the image in a Web browser. The retrieval part is easy: it's just a SELECT query. Getting the image to display from that point is a matter of telling the Web browser what kind of data to expectan image of a certain typeand then sending the image to the browser. PHP's header() function can let the browser know what type of data is forthcoming, as you'll see in this script. To retrieve and display binary data:
How do I query binary in MySQL?The MySQL BINARY function is used for converting a value to a binary string. The BINARY function can also be implemented using CAST function as CAST(value AS BINARY). The BINARY function accepts one parameter which is the value to be converted and returns a binary string.
How do I query BLOB data in MySQL?It is a kind of data type in MySQL that can store files or images in the database in binary format. It has four types i.e TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB.. Alter the table and add a column having data type TEXT.. Add content to that column after converting BLOB data to TEXT date.. Drop BLOB column.. What is binary data in MySQL?The BINARY attribute is a nonstandard MySQL extension that is shorthand for specifying the binary ( _bin ) collation of the column character set (or of the table default character set if no column character set is specified). In this case, comparison and sorting are based on numeric character code values.
How do I select data in MySQL?Introduction to MySQL SELECT statement
First, specify one or more columns from which you want to select data after the SELECT keyword. If the select_list has multiple columns, you need to separate them by a comma ( , ). Second, specify the name of the table from which you want to select data after the FROM keyword.
|