mac修改mysql编码_Mac下修改MySQL编码格式(Linux基本一致)

本文详细介绍了在Mac系统中配置MySQL,包括设置环境变量和my.cnf,确保MySQL以utf8编码运行。内容涵盖如何查看与修改数据库和表的编码,避免导入数据时出现中文乱码,以及解决网页显示乱码问题。提供了具体的操作步骤和配置示例。
摘要由CSDN通过智能技术生成

目录:

配置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 ```

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值