Database Reference
In-Depth Information
Compressing Strings
Some columndata types allow large amounts of data. For instance,the
BLOB
column can
store plenty. To reduce the size of tables that use this column data type, you can compress
the data it contains when inserting the data. The
COMPRESS()
functioncompresses a
string and the
UNCOMPRESS()
functiondecompresses a compressed string. If you want to
use them, MySQL has to have been compiled with a compression library (i.e.,
zlib
). If it
wasn't, a NULL value will be returned when using
COMPRESS()
. Let's look at some ex-
amples of their use.
The
humans
table has a column for
birding_background
which is a
BLOB
. Mem-
bers can write as much as they like about themselves, which could result in pages of in-
formation on their experiences and education as bird-watchers. This could potentially slow
down queries and updates if many members do this. So we decide to use
COMPRESS()
to
compress the member's background when inserting it into the
humans
table. Here's how
that might look:
INSERT INTO
humans
(
formal_title
,
name_first
,
name_last
,
join_date
,
birding_background
)
VALUES
(
'Ms'
,
'Melissa'
,
'Lee'
,
CURDATE
(),
COMPRESS
(
"lengthy
background..."
));
This SQL statement inserts a new member's information into the
humans
table — it has
more columns than shown here, but we're trying to keep this example simple. The state-
ment uses the
COMPRESS()
function to compress the background information given
(which isn't much for this simple example). You would normally get such data from an API
variable using something like PHP to store text entered by the user through a web page. So
instead of the text shown here, you would use a variable (e.g.,
$bird-
ing_background
).
To see how the data looks in the compressed form, we could do this:
SELECT birding_background AS Background
FROM humans
WHERE name_first = 'Melissa' AND name_last = 'Lee' \G
*************************** 1. row ***************************
Background: x#####/ɨTHJL##/######## Z######
Notice that the results are not normal text. The
mysql
client substitutes a hash sign (
#
) for
binary values. In order to see the text contained in this compressed format, we would use
