目录:
配置MySQL,包括path和my.cnf
新建库保证为utf8、已有的库修改其为utf8以及查看编码的方法
避免导入 数据库 中文乱码问题
解决网页中乱码问题
首先配置好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 ```