linux mysql字符编码_Linux下修改MySQL字符编码

ゝ一纸荒年。 2022-10-24 13:15 217阅读 0赞

环境：CentOS + MySQL5.5.25a

1. 首先登陆MySQL查看默认编码，应该存在Latin编码，不支持中文

mysql> show variables like '%character%';

2. 关闭MySQL

\# /etc/init.d/mysql stop

3. 接下来创建并修改配置文件

把/usr/share/doc/mysql-server-5.1.52/my-large.cnf 复制到 /etc/my.cnf

用命令：cp /usr/share/doc/mysql-server-5.1.52/my-large.cnf  /etc/my.cnf

打开my.cnf后添加配置信息

在\[client\]节点下面加入　　default-character-set=utf8

在\[mysqld\]节点下面加　　character\_set\_server=utf8　　　init\_connect='SET NAMES utf8'

在\[mysql\]节点下面加入　default-character-set=utf8

修改后的配置文件my.cnf如下：

\# Example MySQL config file for large systems.

\# This is for a large system with memory = 512M where the system runs mainly

\# MySQL.

\# MySQL programs look for option files in a set of

\# locations which depend on the deployment platform.

\# You can copy this option file to one of those

\# locations. For information about these locations, see:

\# http://dev.mysql.com/doc/mysql/en/option-files.html

\# In this file, you can use all long options that a program supports.

\# If you want to know which options a program supports, run the program

\# with the "--help" option.

\# The following options will be passed to all MySQL clients

\[client\]

\#password = your\_password

port  = 3306

socket  = /var/lib/mysql/mysql.sock

default-character-set=utf8

\# Here follows entries for some specific programs

\# The MySQL server

\[mysqld\]

port  = 3306

socket  = /var/lib/mysql/mysql.sock

skip-external-locking

key\_buffer\_size = 256M

max\_allowed\_packet = 1M

table\_open\_cache = 256

sort\_buffer\_size = 1M

read\_buffer\_size = 1M

read\_rnd\_buffer\_size = 4M

myisam\_sort\_buffer\_size = 64M

thread\_cache\_size = 8

query\_cache\_size= 16M

\# Try number of CPU's\*2 for thread\_concurrency

thread\_concurrency = 8

character\_set\_server=utf8

init\_connect='SET NAMES utf8'

\# Don't listen on a TCP/IP port at all. This can be a security enhancement,

\# if all processes that need to connect to mysqld run on the same host.

\# All interaction with mysqld must be made via Unix sockets or named pipes.

\# Note that using this option without enabling named pipes on Windows

\# (via the "enable-named-pipe" option) will render mysqld useless!

\#skip-networking

\# Replication Master Server (default)

\# binary logging is required for replication

log-bin=mysql-bin

\# binary logging format - mixed recommended

binlog\_format=mixed

\# required unique id between 1 and 2^32 - 1

\# defaults to 1 if master-host is not set

\# but will not function as a master if omitted

server-id = 1

\# Replication Slave (comment out master section to use this)

\# To configure this host as a replication slave, you can choose between

\# two methods :

\# 1) Use the CHANGE MASTER TO command (fully described in our manual) -

\#    the syntax is:

\#    CHANGE MASTER TO MASTER\_HOST=, MASTER\_PORT=,

\#    MASTER\_USER=, MASTER\_PASSWORD= ;

\#    where you replace , , by quoted strings and

\#    by the master's port number (3306 by default).

\#    Example:

\#    CHANGE MASTER TO MASTER\_HOST='125.564.12.1', MASTER\_PORT=3306,

\#    MASTER\_USER='joe', MASTER\_PASSWORD='secret';

\# OR

\# 2) Set the variables below. However, in case you choose this method, then

\#    start replication for the first time (even unsuccessfully, for example

\#    if you mistyped the password in master-password and the slave fails to

\#    connect), the slave will create a master.info file, and any later

\#    change in this file to the variables' values below will be ignored and

\#    overridden by the content of the master.info file, unless you shutdown

\#    the slave server, delete master.info and restart the slaver server.

\#    For that reason, you may want to leave the lines below untouched

\#    (commented) and instead use CHANGE MASTER TO (see above)

\# required unique id between 2 and 2^32 - 1

\# (and different from the master)

\# defaults to 2 if master-host is set

\# but will not function as a slave if omitted

\#server-id      = 2

\# The replication master for this slave - required

\#master-host    =

\# The username the slave will use for authentication when connecting

\# to the master - required

\#master-user    =

\# The password the slave will authenticate with when connecting to

\# the master - required

\#master-password =

\# The port the master is listening on.

\# optional - defaults to 3306

\#master-port    =

\# binary logging - not required for slaves, but recommended

\#log-bin=mysql-bin

\# Uncomment the following if you are using InnoDB tables

\#innodb\_data\_home\_dir = /var/lib/mysql

\#innodb\_data\_file\_path = ibdata1:10M:autoextend

\#innodb\_log\_group\_home\_dir = /var/lib/mysql

\# You can set ..\_buffer\_pool\_size up to 50 - 80 %

\# of RAM but beware of setting memory usage too high

\#innodb\_buffer\_pool\_size = 256M

\#innodb\_additional\_mem\_pool\_size = 20M

\# Set ..\_log\_file\_size to 25 % of buffer pool size

\#innodb\_log\_file\_size = 64M

\#innodb\_log\_buffer\_size = 8M

\#innodb\_flush\_log\_at\_trx\_commit = 1

\#innodb\_lock\_wait\_timeout = 50

\[mysqldump\]

quick

max\_allowed\_packet = 16M

\[mysql\]

no-auto-rehash

\# Remove the next comment character if you are not familiar with SQL

\#safe-updates

default-character-set=utf8

\[myisamchk\]

key\_buffer\_size = 128M

sort\_buffer\_size = 128M

read\_buffer = 2M

write\_buffer = 2M

\[mysqlhotcopy\]

interactive-timeout

4. 启动MySQL

\# /etc/init.d/mysql start

5. 再次查看编码

mysql> show variables like '%character%';

注：即使做了以上修改如果直接数据库再创建表，然后存入中文，取出来的还会是问号。

解决的办法是：创建数据库的时候指明默认字符集为utf8，如：

create database test default character set utf8;

![0b1331709591d260c1c78e86d0c51c18.png][]

[0b1331709591d260c1c78e86d0c51c18.png]: /images/20221024/4fcc5a4d94f3423db0ad2ac73053b157.png

linux mysql字符编码_Linux下修改MySQL字符编码

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MySQL字符集、字符编码、排序规则、MySQL设置字符编码、设置表字段编码

相关修改MySQL的默认字符编码

相关 linux mysql字符编码_Linux下修改MySQL字符编码

相关 MySQL 字符编码

相关 Linux下修改Mysql默认编码

相关 mysql编码字符相关

相关 linux下查看Mysql默认编码、修改默认编码

相关 mysql下修改字符编码

相关 mysql之修改字符编码

相关 mysql命令行修改字符编码