Mac下修改MySQL编码格式（Linux基本一致）

目录：

1. 配置MySQL，包括path和my.cnf
2. 新建库保证为utf8、已有的库修改其为utf8以及查看编码的方法
3. 避免导入数据库中文乱码问题
4. 解决网页中乱码问题

首先配置好MySQL.

1 先配置环境变量path

首先cd到用户目录，然后ls -a查看所有文件，去.bash_profile中配置JAVA_HOME

vi .bash_profile

将下面这段复制进去：

export PATH=${PATH}:/usr/local/mysql/bin

:wq!保存退出。

关闭MySQL

sudo /usr/local/mysql/support-files/mysql.server stop

2 配置my.cnf

查看一下/usr/local/mysql/support-files文件夹，里面有没有my-default.cnf或my.cnf文件。

如果有：

(5.5以前系统)在[client]下面加入

default-character-set=utf8

在[mysqld]下面加入

default-character-set=utf8

Notice：注意 如果修改后不能启动、报错,试试把

#### (5.5以后系统)如下修改：

[client] default-character-set=utf8 [mysqld] default-storage-engine=INNODB character-set-server=utf8 collation-server=utf8_general_ci

如果没有：/etc下新建my.cnf

$ cd /etc $ sudo vim my.cnf

复制文后附带的文本内容进去。



重启MySQL



	sudo /usr/local/mysql/support-files/mysql.server start

 

> show variables like '%char%'; 

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

## 如果是已有的库，修改方法



### 查看默认的编码格式：

mysql> show variables like “%char%”;

### 查看数据库的编码格式：

mysql> show create database test;

### 建数据库时：

CREATE DATABASE test CHARACTER SET ‘utf8’ COLLATE ‘utf8_general_ci’;

### 建表时：

CREATE TABLE database_user (  ID varchar(40) NOT NULL default ‘’,  UserID varchar(40) NOT NULL default ‘’, ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

### 修改数据库为utf8的方法：

mysql>ALTER DATABASE mydatabase DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

mydatabase就是已有的你要修改的库。

### 修改表为utf8的方法：

ALTER TABLE tb_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

## 避免导入数据中文乱码问题



### 将数据编码格式保存为utf-8

设置默认编码为utf8：

set names utf8;

执行SET NAMES utf8的效果等同于同时设定如下：

SET character_set_client=’utf8’; SET character_set_connection=’utf8’; SET character_set_results=’utf8’;

设置数据库db_name默认为utf8:

设置表tb_name默认编码为utf8:

导入：

LOAD DATA LOCAL INFILE ‘C:\utf8.txt’ INTO TABLE yjdb;

### 将数据编码格式保存为ansi(即GBK或GB2312)



设置默认编码为gbk：

set names gbk;

设置数据库db_name默认编码为gbk:

ALTER DATABASE db_name DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

设置表tb_name默认编码为gbk:

ALTER TABLE tb_name DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

导入：

LOAD DATA LOCAL INFILE ‘C:\gbk.txt’ INTO TABLE yjdb;

注意：



* UTF8不要导入gbk，gbk不要导入UTF8;

* dos下不支持UTF8的显示;



## 解决网页中乱码的问题



将网站编码设为 utf-8,这样可以兼容世界上所有字符。



如果网站已经运作了好久,已有很多旧数据,不能再更改简体中文的设定,那么建议将页面的编码设为 GBK, GBK与GB2312的区别就在于:GBK能比GB2312显示更多的字符,要显示简体码的繁体字,就只能用GBK。



1. 编辑/etc/my.cnf　,在[mysql]段加入```default_character_set=utf8;```

2. 在编写Connection URL时，加上```?useUnicode=true&characterEncoding=utf-8参;```

3. 在网页代码中加上一个```set names utf8```或者```set names gbk```的指令，告诉 MySQL 连线内容都要使用utf8或者gbk;



附my.cnf

Example MySQL config file for medium systems.

#

This is for a system with little memory (32M - 64M) where MySQL plays

an important part, or systems up to 128M where MySQL is used together with

other programs (such as a web server)

#

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] default-character-set=utf8 #password = your_password port = 3306 socket = /tmp/mysql.sock

Here follows entries for some specific programs

The MySQL server

[mysqld] default-storage-engine=INNODB character-set-server=utf8 collation-server=utf8_general_ci init_connect=’SET NAMES utf8 port = 3306 socket = /tmp/mysql.sock skip-external-locking key_buffer_size = 16M max_allowed_packet = 1M table_open_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M 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 = /usr/local/mysql/data #innodb_data_file_path = ibdata1:10M:autoextend #innodb_log_group_home_dir = /usr/local/mysql/data

You can set .._buffer_pool_size up to 50 - 80 %

of RAM but beware of setting memory usage too high

#innodb_buffer_pool_size = 16M #innodb_additional_mem_pool_size = 2M

Set .._log_file_size to 25 % of buffer pool size

#innodb_log_file_size = 5M #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 = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M

[mysqlhotcopy] interactive-timeout ```

本文由天善智能Live 创作，采用 知识共享署名-相同方式共享 3.0 中国大陆许可协议 进行许可。

转载、引用前需联系作者，并署名作者且注明文章出处。

本站文章版权归原作者及原出处所有 。内容为作者个人观点， 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台，并不用于任何商业目的，如果有任何问题，请及时联系我们，我们将根据著作权人的要求，立即更正或者删除有关内容。本站拥有对此声明的最终解释权。