使用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.
The short answer did not work, read bellow
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%';
character_set_database为您提供当前所在数据库(模式)的字符集.默认情况下，模式和表是使用character_set_server中指定的字符集创建的，除非在character_set_server中明确指定了该字符集. c11>声明.
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.
可以在my.cnf文件中更改character_set_server:
[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?
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.
即使所有字符集变量，表和列都设置为utf8，在某些情况下，您仍可能在屏幕上看到奇怪的字符.例如，有人可能通过具有latin1连接的客户端(例如，通过运行mysql --default-character-set=utf8)在utf8列中编写了Unicode字符.在这种情况下，您需要使用与写入值相同的字符集连接到数据库.您还可以通过正确的编码来检索和重写它们.
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=utf8). 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.
注意:正如评论所指出的那样，myslq utf8编码不是UTF-8的真实且完整的实现.如果需要完整实现UTF-8，则可以使用utf8mb4字符集:
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
更多信息在这里:什么是utf8mb4和utf8字符集在MySQL中有什么区别?
这篇关于使用UTF8进行MySQL命令行格式化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
