Compressed Data Values with EMySQL API
Using the EMySQL API – Part 11
Foreword: In this part of the series, I explain how you can use MySQL to compress a long value (kilo and mega-bytes) and insert into a table; I also explain the data types to use for the table.
By: Date Published: 24 Oct 2017
Introduction
Data Types
In this scheme, compression takes place at the server (not at the client API). The table data type to use for a compressed column is BINARY or VARBINARY or BLOB. A Western European Character (e.g. A) occupies a space in memory of one byte. Each of these data types stores bytes (not really characters) in a table cell.
BINARY
With the binary data type, the number of bytes in each cell for a column are the same. Spaces are padded to make the length of the byte strings, the same, in all the cells of the column.
VARBINARY
VARBINARY is similar to BINARY; however, with VARBINARY a string of bytes in a cell can be of any length up to the maximum, indicated; there is no padding with spaces. So, in a column of VARBINARY, the string of bytes in the different cells are of different lengths.
BLOB
The BLOB type is similar to VARBINARY, but it is for large value, such as a large image or a video file. The BLOB type is further divided into, TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB.
COMPRESS() and UNCOMPRESS()
The function for MySQL server to compress value, before saving, is COMPRESS(); the reverse function is, UNCOMPRESS(). The following SQL statement will compress 'any string' before inserting into the table.
INSERT INTO Pet (Comp) VALUES (COMPRESS('any string'));
The following SQL statement will select all the cell values from the column, Comp and uncompress them (before outputting).
SELECT UNCOMPRESS(Comp) from Pet;
Alter the pet table in the PetStore database with the following code:
const mysql = require('./server/Mysql.js');
con = new mysql.Connection("root", "secret", "localhost", 3306, function(err)
{
if (err)
console.log(err);
});
var db = `PetStore`;
con.selectDB(db, function(err)
{
if (err)
console.log(err);
else
console.log('database selected');
});
con.query("ALTER TABLE Pet ADD Comp VARBINARY(100)", function(err)
{
if (err)
console.log(err);
else
console.log('table altered');
});
con.close();
The following code will compress 'any string' before inserting into the table, in a cell in the column, Comp:
const mysql = require('./server/Mysql.js');
con = new mysql.Connection("root", "secret", "localhost", 3306, function(err)
{
if (err)
console.log(err);
});
var db = `PetStore`;
con.selectDB(db, function(err)
{
if (err)
console.log(err);
else
console.log('database selected');
});
con.query("INSERT INTO Pet (Comp) VALUES (COMPRESS('any string'))", function(err)
{
if (err)
console.log(err);
else
console.log('row inserted');
});
con.close();
The following code will select all the cell values from the column, Comp and uncompress them.
const mysql = require('./server/Mysql.js');
con = new mysql.Connection("root", "secret", "localhost", 3306, function(err)
{
if (err)
console.log(err);
});
var db = `PetStore`;
con.selectDB(db, function(err)
{
if (err)
console.log(err);
else
console.log('database selected');
});
con.query(`SELECT UNCOMPRESS(Comp) As Str from Pet`, function(err, result)
{
if (err)
console.log(err);
else
{
for (i=0; i<result.length; ++i)
{
tempStr = `${result[i].get('Str')}`;
console.log(tempStr);
}
}
});
con.close();
That is it for this part of the series. We stop here and continue in the next part.
Chrys