Getting the size of BLOB in MySql

If you want to store binary in database, you can use BLOB as the data type of that column. In Mysql you can use TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB depending on your space requirement. Here is an example of database table using BLOB as a column type.

CREATE TABLE BloBTest (
    id INT NOT NULL AUTO_INCREMENT,
    filename VARCHAR( 32 ) NOT NULL,
    content BLOB NOT NULL,
    PRIMARY KEY ( id )
)

Storing Data

PHP:

$filename = "myimage.png";
$filecontent = file_get_contents($filename);
$filecontent_escaped = mysql_real_escape_string($filecontent);

$sql = "INSERT INTO BloBTest(filename, content) " +
       "VALUES('$filename','$filecontent_escaped')";
mysql_query($sql, $link);

Java:

String filename = "myimage.png";
InputStream filecontent = new FileInputStream(filename);

String sql = "INSERT INTO BloBTest(filename, content) VALUES(?, ?)";

int size = filecontent.available();
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, filename);
ps.setBinaryStream(2, filecontent, size);
ps.executeUpdate();

Retrieving Data

PHP

$sql = "SELECT filename, content FROM BloBTest";
$result = mysql_query($sql, $link);
while ($row = mysql_fetch_assoc($result)) {

    $filename = $row["filename"];
    $content = $row["content"];
    $new_filename = "new_" . $filename;
    file_put_contents($new_filename, $content);
}

Java:

String sql = "SELECT filename, content FROM BloBTest";

PrepareStatement ps  = conn.prepareStatement(resourceContentSQL);
ResultSet result = ps.executeQuery();

if (result.next()){
    String filename = result.getString("filename");
    InputStream contentStream = result.getBinaryStream("content");
    String newFilename = "new_" + filename;
    // storing the input stream in the file

    OutputStream out=new FileOutputStream(newFilename);
    byte buf[]=new byte[1024];
    int len;
    while((len=contentStream.read(buf))>0)

    out.write(buf,0,len);
    out.close();
}

Retrieving the Size of the Blob

After you store your data as a blob, you can manipulate or query the data with some of the in-built String functions in mysql. For an example if you want to query the size of the blob you just stored, you can use OCTET_LENGTH function. Here is an example,  (this will give you the size in bytes.)

SELECT OCTET_LENGTH(content) FROM BloBTest WHERE filename='myimage.png'

.