MySQL command line formatting using UTF8

Question

I have a database table containing Swedish / Norwegian rows.

When I request some data, I get the output as follows:

Exit with set names latin1;

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

Now, if I set names utf8; To see characters with their proper encoding, the formatting of the MySQL command line table output breaks.

Exit with set names utf8;

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

Question:

This is not a big problem, but it makes the conclusion a little harder to read. Does anyone know how to keep table formatting intact?

Answer 1

Short answer

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

 mysql --default-character-set=utf8 

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

 [mysql] default-character-set=utf8 

The short answer did not work, read below

The utf8 command above sets the configuration variables character_set_client , character_set_connection and character_set_results to utf8 .

To check the values ​​for all configuration variables associated with a character set, you can run:

 show variables like '%char%'; 

The character_set_database database provides you with the character set of the current database (schema) where you are located. Schema and tables are created by default with the character set specified in character_set_server , unless specified explicitly in the CREATE statement.

character_set_server can be changed in my.cnf file:

 [mysqld] character-set-server = utf8 

In addition, tables and columns may have their own encoding, which may differ from their parent table or schema. To specifically check the values ​​of each table and column in the database, review the following answer: How do I find out which character set is used in the MySQL database / table / column?

If you want to change the character set of existing tables and columns, see Answer: How to convert the entire character set and sorting options of a MySQL database to UTF-8?

More information about join character sets in the mysql documentation.

Everything is configured on utf8, but I still see strange characters

Even if utf8 is set for all charsets, tables, and columns, there may be times when strange characters appear on the screen. For example, someone could write Unicode characters in the utf8 column through a client with latin1 connection (for example, by running mysql --default-character-set=utf8 ). In this case, you need to connect to the database with the same encoding in which the values ​​were written. You can also get and rewrite them using the correct encoding.

NOTE As noted in the comments, myslq utf8 encoding is not a true and complete implementation of UTF-8. If a full implementation of UTF-8 is required, you can use utf8mb4 encoding:

 mysql --default-character-set=utf8mb4 

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

Answer 2

These words "ø ö ä" with utf8 occupy 2 bytes, so you forgot to use the wchar or utf line?

Here is 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 the same as for your program. 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,' '),'|' 

result

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

I have not tested in C ++, but I suggest you use wchar, std :: wcout.