問題描述

為什么 mysql 命令行輸出 utf8 列的寬度是非 utf 列的兩倍?示例:

Why mysql command-line outputs utf8 columns twice as wide compared to non-utf columns? Example:

$ mysql -u user --default-character-set=utf8
mysql> select "αβγαβγαβγαβγαβγαβγαβγ";
+--------------------------------------------+
| αβγαβγαβγαβγαβγαβγαβγ                      |
+--------------------------------------------+
| αβγαβγαβγαβγαβγαβγαβγ                      |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select "abcabcabcabcabcabcabc";
+-----------------------+
| abcabcabcabcabcabcabc |
+-----------------------+
| abcabcabcabcabcabcabc |
+-----------------------+
1 row in set (0.00 sec)

如您所見，第一個表格的列寬是第二個表格的兩倍，當行開始超過半個屏幕時，這通常會破壞格式.

As you can see, first table has column twice as wide compared to second table, and this often breaks formatting when lines start to get more than half-screen wide.

我在 MySQL 14.14 和 MariaDB 15.1 上試過這個.

I tried this on MySQL 14.14 and MariaDB 15.1.

有沒有辦法輸出與非utf寬度相同的utf8列?

Is there a way to output utf8 columns with the same width as non-utf?

MariaDB [(none)]> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

推薦答案

mysql.cc 的源代碼(mysql 客戶端的源代碼)的注釋塊中有對函數的解釋get_field_disp_length() 用于結果集輸出的格式化.

In the source code for mysql.cc (the source for the mysql client) there is an explanation in the comment block for function get_field_disp_length() which is used in the formatting of result set output.

返回一個字段被渲染后的長度文本.

Return the length of a field after it would be rendered into text.

這不知道也不關心多字節字符.假設我們是使用這樣的字符集.我們無法知道所有即將到來的行對于此列將有字節，每個字節都呈現為某個分數的一個字符.至少有可能一行的字節數全部渲染為一個字符，因此最大長度為仍然是字節數.(假設 1:這再好不過了因為我們永遠無法知道 DB 的字符數將要發送——只有字節數.2:字符 <= 字節.)

This doesn't know or care about multibyte characters. Assume we're using such a charset. We can't know that all of the upcoming rows for this column will have bytes that each render into some fraction of a character. It's at least possible that a row has bytes that all render into one character each, and so the maximum length is still the number of bytes. (Assumption 1: This can't be better because we can never know the number of characters that the DB is going to send -- only the number of bytes. 2: Chars <= Bytes.)

換句話說，由于UTF8可以存儲每個字符1個字節的字符(如拉丁字符)，結果在獲取數據之前無法知道數據是什么，它必須假設任何或所有字符可能是每個字符一個字節.

In other words, since UTF8 can store characters that are 1 byte per character (like Latin characters), and the result can't know what the data is before it fetches it to display, it must assume any or all characters may be one byte per character.

如果您使用的字符集每個字符使用常量 2 個字節，則情況可能會有所不同，例如 UCS-2.但我從未聽說有人使用 UCS-2，因為 MySQL 支持可變長度的 Unicode 編碼.

The story might be different if you used a character set that uses a constant 2 bytes per character, like UCS-2. But I have never heard of anyone using UCS-2, since MySQL supports variable-length Unicode encodings.

這篇關于MySQL命令行表列寬與utf8的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！