Setting up Unicode defaults for MariaDB (or MySQL)

It is common knowledge that MySQL's defaults were chosen under strong influence of drugs, this is basically why they're so random and bad. MariaDB mostly inherits those, too, because backward compatibility. Or maybe, you know, because doing drugs while coding is just plain fun.

Anyway, let's fix the retarded MySQL's three-byte utf8 encoding. Fun fact: the abomination that MySQL calls utf8 is NOT the well-known UTF-8 encoding; it's a broken three-byte variation thereof, not capable of storing many commonly used Unicode characters.

The fix is trivial. In the config file, typically /etc/my.cnf (or /usr/local/etc/my.cnf if installed on OS X with Homebrew):

[client]
default-character-set = utf8mb4

[mysqld]
character-set-server = utf8mb4

(At this point you might have noticed that Coderwall's syntax coloring sucks, but you get the idea.)

Then reload the server by issuing a command along the lines of:

mysql.server restart

Now, in order to test the new settings, you can do the following:

mysql -u root -p

MariaDB> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

The end result should read:

+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_general_ci |
| collation_database       | utf8mb4_general_ci |
| collation_server         | utf8mb4_general_ci |
+--------------------------+--------------------+

Coderwall, please fix your text editor / Markdown thingy! It's bad, and you should feel bad.