mysql 命令行展示中文乱码

mysql 命令行展示中文乱码

在bash上直连mysql发现乱码, 比如下面查询的operation字段返回?

 select * from security_audit_log limit 5 \G;
*************************** 1. row ***************************
             id: 1
      user_name: NULL
        user_id: 0
      operation: ??
operation_value: {""}
    create_user: NULL
    create_time: NULL
    update_user: NULL
    update_time: NULL

原因是mysql默认ascii连接, 返回的也是ascii格式, 需要在连接命令行上添加utf8参数. 如果mysql server默认配置了utf8, 那连接串也就不需要配置了.

mysql -hhost -uroot -p'password@passowrd' -P 3308 -Ddatabase --default-character-set=utf8

select * from security_audit_log limit 5 \G;
*************************** 1. row ***************************
             id: 1
      user_name: NULL
        user_id: 0
      operation: 授权
operation_value: {""}
    create_user: NULL
    create_time: NULL
    update_user: NULL
    update_time: NULL

参考

https://stackoverflow.com/questions/6787824/mysql-command-line-formatting-with-utf8

Start the client with option --default-character-set=utf8:

mysql --default-character-set=utf8

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

[mysql]
default-character-set=utf8

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