MySQL command line formatting with UTF8

Question

I have a database table that contains Swedish/Norwegian strings.

When I query some data, I get output like this:

Output with set names latin1;

+-----------------------------------+ | name                              | +-----------------------------------+ | Kid Interi#####                   |  | Bwg Homes                         |  | If Skadef####kring                |  | Jangaard Export                   |  | Nordisk Film                      |  +-----------------------------------+ 

Now if I set names utf8; in order to see the characters with their proper encoding, then the formatting of the tabular output of the MySQL command line breaks.

Output with set names utf8;

+-----------------------------------+ | name                              | +-----------------------------------+ | Kid Interiør                     |  | Bwg Homes                         |  | If Skadeförsäkring              |  | Jangaard Export                   |  | Nordisk Film                      |  +-----------------------------------+ 

Question:

This is not a big issue but it makes the output a bit harder to read. Does anybody know how to keep the tabular formatting intact?

Answer 1

Short answer

Start the client with option --default-character-set=utf8:

mysql --default-character-set=utf8 

You can set this as a default in the /etc/mysql/my.cnf file.

[mysql] default-character-set=utf8 

The short answer did not work, read below

The command above forces the character_set_client, character_set_connection and character_set_results config variables to be utf8.

In order to check the values for all the charset related config variables you can run:

show variables like '%char%'; 

The character_set_database gives you the character set of the current database (schema) that you are in. The schema and tables are created by default with the charset specified in the character_set_server, unless it is specified explicitly in the CREATE statement.

The character_set_server can be changed in the my.cnf file:

[mysqld] character-set-server = utf8 

Additionally, tables and columns can have their own charset which might be different from their parent table or schema. To specifically check the values of each table and column in a database see this answer: How do I see what character set a MySQL database / table / column is?

If you want to change the character set of existing tables and columns, see this answer: How to convert an entire MySQL database characterset and collation to UTF-8?

More info on connection character sets in the mysql docsumentation.

Everything is set to utf8, but I still see weird characters

Even if all the charsets variables, tables and columns are set to utf8, there might be cases where you see weird characters on your screen. For example, somebody might have written Unicode characters in a utf8 column, through a client with latin1 connection (for example by running mysql --default-character-set=latin1). In this case you need to connect to the database with the same charset as the values were written. You can also retrieve and rewrite them through the correct encoding.

NOTE: As the comments point out, the mysql utf8 encoding is not a true and full implementation of UTF-8. If a full implementation of UTF-8 is needed, one can use the utf8mb4 charset:

mysql --default-character-set=utf8mb4 

More info here: What is the difference between utf8mb4 and utf8 charsets in MySQL?