MySQL command line formatting with UTF8

问题:

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?

回答1:

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

mysql --default-character-set=utf8

This forces the character_set_client, character_set_connection and character_set_results variables to be utf8.

More info on connection character sets here.

NOTE: As the comments point out, the myslq 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?

You can also set this as a default option in your my.cnf file, in the [mysql] section to be included automatically each time you invoke the mysql client.

[mysql]
default-character-set=utf8

回答2:

These words "ø ö ä" with utf8 takes 2 bytes, so did you forget use wchar or utf string?

Here's my test code in python:

s = ["Kid Interiør","Bwg Homes","If Skadeförsäkring"]
for w in s:
    print '|',w.ljust(20,' '),'|' 

the result is as the same as your program print out. all I need to do is change the encoding of string s:

s = [u"Kid Interiør",u"Bwg Homes",u"If Skadeförsäkring"]
for w in s:
    print '|',w.ljust(20,' '),'|'

the result is

| Kid Interiør         |
| Bwg Homes            |
| If Skadeförsäkring   |

I haven't test in c++, but I suggest you can use wchar, std::wcout.