Why Wikipedia Uses BLOB Type Instead of TEXT Type in MySQL

database-designmariadbMySQL

As I experienced, Blob type is good choice when you want to save binary data such as images or videos etc and Text type is good choice when you want to save large string.

Today I saw Wikipedia database. The interesting thing was that wiki use medium blob type for storing wikitext of the page.

So I want to know why Wiki prefer to use Blob type instead of Text?

Thanks

Best Answer

The 'right' way to store text is to use TEXT with CHARACTER SET ut8fmb4. However, that requires the you know what encoding exists in the client, so that it can be converted (by MySQL) as it is stored.

By storing into BLOB, the above issue is eliminated. However, that leads to another issue -- how to display the text.

Another possible excuse for BLOB is that Wikipedia was probably started before utf8mb4 was added to MySQL.

(We can only guess.)

Related Solutions

Mysql – Are Blob Sizes Fixed

No, as defined, a Blob is "a binary large object that can hold a variable amount of data." It can hold between 0 and 2^16 - 1 bytes (65535). A LONGBLOB can hold up to 4,294,967,295 bytes (or 4GB).

In most engines, blobs are implemented dynamically, meaning that it will only take its actual size plus some extra bytes for lenght/addressing, etc. In fact, fixed-row size formats are normally not compatible with blobs (MyISAM fixed, Memory storage engine).

However, there are some reasons to avoid storing "files" on a relational database (and MySQL and InnoDB in particular, mainly due to the overhead on the transaction and binary logs, plus multiversioning and its incompatibilities with temporary tables on memory). The actual decision varies depending on the needs -the most common alternative is to store those on the filesystem and just add a URL on the database.

If you store those objects inside the database, the size will not be your biggest problem (unless they are over 4GB), but the performance on complex queries and writes. However, a pure key-value store may work well with MySQL model. There is also specialised document-based databases.

Mysql – unusual relation, binary aspect

WHERE (1 << CVbits) & ConcVal
  AND CVbits != 0    -- (to "exclude the zero bit")

Best Answer

Related Solutions

Mysql – Are Blob Sizes Fixed

Mysql – unusual relation, binary aspect

Related Question