Ken的杂谈
  • Ken的杂谈 (current)
  • 关于
  • 杂谈
    Java Spring Spring Boot Spring Cloud MyBatis C# .NET Core .NET ASP.NET Core ASP.NET ClassLibrary Mono 数据库 MySQL SQL Server 网络 缓存 Web Linux CentOS Ubuntu macOS Nginx Windows ServiceStack JS/JQ 正则 工具 IDE Grafana Exceptions CI/CD Solution 微服务 Arch Docker 杂谈
  • 系列
    Java 快速入门系列教程 Spring Boot 入门教程 Spring Boot 2.x 入门教程 Spring Cloud 入门教程 .NET Core 快速入门教程 ASP.NET Core 2.1 入门教程 CentOS 7 快速上手教程 Ubuntu快速上手入门教程 Hyper-V基础教程 Docker入门教程
  • GitHub

【问题解决】MySQL修改字符编码解决中文乱码问题

MySQL Exceptions @ 2017-07-03 15:15:35 · 阅读:(5278)

一、前言

为什么MySQL中存储中文会乱码?

https://zh.wikipedia.org/wiki/%E5%AD%97%E7%AC%A6%E7%BC%96%E7%A0%81
mysql database字符编码默认是latin1,并不支持中文

本篇文章解决办法适用范围?

Linux下的mysql 5.6+版本
其他版本未尝试过,不敢保证可行

二、MySQL编码设置

1、查看MySQL字符编码

#登录mysql
mysql -u rrot -p
#在mysql中查询字符编码设置
mysql> show variables like '%char%';

+--------------------------------------+----------------------------+
| Variable_name                        | Value                      |
+--------------------------------------+----------------------------+
| character_set_client                 | utf8                       |
| character_set_connection             | utf8                       |
| character_set_database               | latin1                     |
| character_set_filesystem             | binary                     |
| character_set_results                | utf8                       |
| character_set_server                 | latin1                     |
| character_set_system                 | utf8                       |
| character_sets_dir                   | /usr/share/mysql/charsets/ |
| validate_password_special_char_count | 1                          |
+--------------------------------------+----------------------------+

如果character_set_database = latin1那说明确实是字符编码造成的乱码问题

2、查找配置文件位置

whereis my.cnf

3、修改配置文件

具体请看#修改1,#修改2

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

#修改1:增加client配置
[client]
default-character-set=utf8mb4

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

#修改2:增加mysqld配置
#charset
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci

4、重启mysql服务

#重启后配置即可生效
systemctl restart mysqld

三、备注

  • 相关阅读

MySQL中的utf8
http://www.infoq.com/cn/articles/in-mysql-never-use-utf8-use-utf8


Ken的杂谈

本文由 ken.io 创作,采用CC BY 3.0 CN协议 进行许可。 可自由转载、引用、甚至修改,但需署名作者且注明出处。

MySQL Exceptions

随笔目录


    © Copyright 2012-2023 Ken的杂谈

    豫ICP备10025012号-1

    ASP.NET Core(6.0) on Linux