linux mysql字符编码_Linux下修改MySQL字符编码 ゝ一纸荒年。 2022-10-24 13:15 147阅读 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
还没有评论,来说两句吧...