MySQL shell: Enable UTF-8

In a nutshell: to avoid your shell character set from messing with imports, use -r to export and SOURCE when importing.

Dumping safely

# Do not do this, since it might screw up encoding
mysqldump -uroot -p database...

First do

ALTER DATABASE database_name CHARACTER SET "utf8";
ALTER DATABASE database_name COLLATE "utf8_unicode_ci";

After that, for each table:

ALTER TABLE table_name DEFAULT CHARACTER SET "utf8" COLLATE "utf8_unicode_ci";

This jus...

For reasons that completely escape me, MySQL 5.x limits UTF-8 strings to U+FFFF and smaller.

When you have a string containing umlauts which don't behave as expected (are not matched with a regexp, can't be found with an SQL query, do not print correctly on LaTeX documents, etc), you may be encountering umlauts which are not actually umla...

Unless all MySQL server defaults are set to UTF-8, mysqldump encodes UTF-8 characters incorrectly and only outputs correct UTF-8 when you switch to Latin 1 (!). Also you need to setup charset and collation manually for each new database.

To preve...

When talking to your MySQL server via a mysql shell, you can terminate queries by ; or \G -- the latter gives you a vertical output.

You know this:

mysql> SELECT * FROM users;
+----+---------+---------------------+-----------------...

This will make MySQL log all received queries so you can see for yourself what happens on the database level.

Don't switch this on for production machines!

1. Edit your my.cnf:
   sudo vim /etc/mysql/my.cnf
2. In the [mysqld] section, a...

Sometimes you need to remove high Unicode characters from a string, so all characters have a code point between 0 and 127. The remaining 7-bit-encoded characters ("Low-ASCII") can be ...

When using GROUP BY, MySQL now complains if the SELECT includes columns which are not part of the GROUP BY.

Reason:

There could be multiple values for those columns per group but only one value can be picked for the results.

The de...

In order to open a MySQL shell without the need to enter user and password, you can say the following in any Rails 2 project:

script/dbconsole -p

In Rails 3 you can say:

rails dbconsole -p

If you'd like to enter a database for an envir...