MySQL command line formatting with UTF8
MysqlUtf 8Mysql Problem Overview
I have a database table that contains Swedish/Norwegian strings.
When I query some data, I get output like this:
set names latin1;
Output with +-----------------------------------+
| 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.
set names utf8;
Output with +-----------------------------------+
| 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?
Mysql Solutions
Solution 1 - Mysql
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: https://stackoverflow.com/questions/1049728/how-do-i-see-what-character-set-a-mysql-database-table-column-is/1049958#1049958
If you want to change the character set of existing tables and columns, see this answer: https://stackoverflow.com/questions/6115612/how-to-convert-an-entire-mysql-database-characterset-and-collation-to-utf-8/6115705#6115705
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: https://stackoverflow.com/questions/30074492/what-is-the-difference-between-utf8mb4-and-utf8-charsets-in-mysql
Solution 2 - Mysql
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.