使用 UTF8 格式化 MySQL 命令行 [英] 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:

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

现在，如果我 set names utf8; 以查看具有正确编码的字符，那么 MySQL 命令行的表格输出格式将中断.

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.

+-----------------------------------+
| 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?

推荐答案

简短回答

使用选项--default-character-set=utf8启动客户端:

mysql --default-character-set=utf8

您可以在 /etc/mysql/my.cnf 文件中将其设置为默认值.

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

[mysql]
default-character-set=utf8

简短的回答不起作用，请阅读以下

以上命令强制character_set_client、character_set_connection 和character_set_results 配置变量为utf8.

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

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

show variables like '%char%';

character_set_database 为您提供当前数据库(架构)的字符集.默认情况下，架构和表是使用 character_set_server，除非在 CREATE 语句中明确指定.

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.

character_set_server可以在my.cnf文件中修改:

[mysqld]
character-set-server = utf8

此外，表和列可以有自己的字符集，这可能与其父表或架构不同.要专门检查数据库中每个表和列的值，请参阅此答案:怎么做我看到 MySQL 数据库/表/列是什么字符集?

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?

如果要更改现有表和列的字符集，请参阅此答案:如何将整个 MySQL 数据库字符集和排序规则转换为 UTF-8?

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?

mysql 文档说明

More info on connection character sets in the mysql docsumentation.

即使所有的字符集变量、表和列都设置为 utf8，您也可能会在屏幕上看到奇怪的字符.例如，有人可能在 utf8 列中写入了 Unicode 字符，通过具有 latin1 连接的客户端(例如通过运行 mysql --default-character-set=latin1).在这种情况下，您需要使用与写入值相同的字符集连接到数据库.您还可以通过正确的编码检索和重写它们.

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.

注意:正如评论所指出的，mysql utf8 编码不是 UTF-8 的真正完整实现.如果需要完整的 UTF-8 实现，可以使用 utf8mb4 字符集:

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

更多信息在这里:什么是MySQL 中 utf8mb4 和 utf8 字符集的区别?

这篇关于使用 UTF8 格式化 MySQL 命令行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！