MySQL命令行表列宽与utf8 [英] MySQL command-line table column width with utf8

问题描述

为什么 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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！