為什麼不能在 MariaDB shell 中粘貼或鍵入特殊字元？

我的作業系統資訊：

uname -a
Linux debian 5.10.0-8-amd64 #1 SMP Debian 5.10.46-4 (2021-08-03) x86_64 GNU/Linux

區域設置：

locale 
LANG=en_HK.UTF-8
LANGUAGE=en_HK:en
LC_CTYPE="en_HK.UTF-8"
LC_NUMERIC="en_HK.UTF-8"
LC_TIME="en_HK.UTF-8"
LC_COLLATE="en_HK.UTF-8"
LC_MONETARY="en_HK.UTF-8"
LC_MESSAGES="en_HK.UTF-8"
LC_PAPER="en_HK.UTF-8"
LC_NAME="en_HK.UTF-8"
LC_ADDRESS="en_HK.UTF-8"
LC_TELEPHONE="en_HK.UTF-8"
LC_MEASUREMENT="en_HK.UTF-8"
LC_IDENTIFICATION="en_HK.UTF-8"
LC_ALL=

從剪貼板中的網頁複製一個特殊字元å，我可以將它粘貼到滑鼠墊和shell終端，它可以正常顯示。

mysql版本資訊：

mysql --version
mysql  Ver 15.1 Distrib 10.5.11-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper

MariaDB 中的字元和排序規則設置：

MariaDB [(none)]> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.001 sec)
show variables like 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.001 sec)

我可以將它從剪貼板粘貼到 sqlite3 shell 中，並且可以將特殊字元輸入到 sqlite3 shell 中！

我可以在 bash shell 上執行 msyql select 命令並顯示特殊字元。

debian@debian:~$ mysql -u root -D test -p -e 'select "å"  ;'
+----+
| å  |
+----+
| å  |
+----+

Mariadb shell 可以正確顯示特殊字元：

select * from `character`;
+---------+
| special |
+---------+
| å       |
+---------+
1 row in set (0.000 sec)

進入MariaDB，滑鼠游標進入MariaDB shell，特殊字元å不能粘貼到MariaDB shell，我也不能輸入特殊字元å到MariaDB shell！

我無法在 Mariadb shell 中鍵入以下 sql 命令：

select * from `character` where special="å";

如何讓 MariaDB shell 接受它作為輸入並正常顯示？

我通過使用 MariaDB 的預設設置解決了這個問題。

sudo vim /etc/mysql/mariadb.cnf
# i delete all my customized setting.
[client]
[mysql]
[client-server]
socket = /run/mysqld/mysqld.sock
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

sudo systemctl restart mysqld
mysql -u root -p
MariaDB [(none)]> show variables like '%collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8_general_ci    |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.001 sec)

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

MariaDB [(none)]> select "å" ;
+----+
| å  |
+----+
| å  |
+----+
1 row in set (0.000 sec)

現在我可以在 MariaDB shell 中鍵入或粘貼並顯示特殊字元。

請解釋為什麼在使用預設設置時可以在 MariaDB shell 中鍵入和顯示特殊字元？

@roaima，這是我的自定義設置，導致粘貼和鍵入特殊字元失敗。

cat    /etc/mysql/mariadb.cnf
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]

character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
init_connect = 'SET collation_connection = utf8mb4_unicode_ci'

這是導致粘貼和鍵入特殊字元成功的預設設置。

cat /etc/mysql/mariadb.cnf
[client]
[mysql]
[client-server]
socket = /run/mysqld/mysqld.sock
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

‘mysql –version’ 的輸出表明您的 mysql 客戶端連結到 EditLine 而不是 readline。嘗試使用與 readline 連結的 mysql-client（來自社區包或自己編譯）。這似乎是一個已知的錯誤，至少對於 Ubuntu：

https://bugs.launchpad.net/ubuntu/+source/mysql-5.7/+bug/1853818

在 mysql shell 中，試試這個：

SET NAMES 'utf8';

嗯……我找到了這個。尤其

[myslqd]
skip-character-set-client-handshake
collation_server=utf8_unicode_ci
character_set_server=utf8 

引用自：https://unix.stackexchange.com/questions/667475