使用 UTF8格式化 MySQL 命令行

我有一个包含瑞典/挪威字符串的数据库表。

当我查询一些数据时，我得到如下输出:

输出与 set names latin1;

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

现在，如果我为了查看字符的正确编码而使用 set names utf8;，那么 MySQL 命令行的表格输出的格式就会中断。

输出与 set names utf8;

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

问题:

这不是一个大问题，但它使得输出有点难以阅读。有人知道如何保持表格格式的完整性吗？

73214 次浏览
小开

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.

小开
最佳答案

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.

为了检查所有与字符集相关的配置变量的值，您可以运行:

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?


提交答案