mysql编码转换工具 php_mysql字符集编码转换

mysql库编码修改，库默认的编码为拉丁文，我创建的agilefant库编码也就是拉丁文的 不支持中文。

整个操作过程切记务必先备份库再操作。

mysql> show variables like 'collation_%';

+----------------------+-------------------+

| Variable_name | Value |

+----------------------+-------------------+

| collation_connection | latin1_swedish_ci |

| collation_database | latin1_swedish_ci |

| collation_server | latin1_swedish_ci |

+----------------------+-------------------+

首先vi /etc/my.cnf

[client]

#password = your_password

port = 3306

socket = /tmp/mysql.sock

default-character-set=utf8

# Here follows entries for some specific programs

# The MySQL server

[mysqld]

port = 3306

socket = /tmp/mysql.sock

datadir = /wwwroot/mysql

default-character-set=utf8

skip-name-resolve

default-character-set=utf8(这句)

再次：

mysql> show variables like 'collation_%';

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 18

Current database: agilefant

+----------------------+-----------------+

| Variable_name | Value |

+----------------------+-----------------+

| collation_connection | utf8_general_ci |

| collation_database | utf8_general_ci |

| collation_server | utf8_general_ci |

+----------------------+-----------------+

再次：

mysqldump -u -p --default-character-set=utf8 -d agilefant > createtables.sql

再次：

mysqldump -u -p --quick --no-create-info --extended-insert --default-character-set=latin1 agilefant > data.sql (只导出了结构)

再把 sed -i 's/latin1/utf8/g' createtables.sql

sed -i 's/latin1/utf8/g' data.sql

再导入结构再导入数据即可。

网上还看到另外种做法：

mysqldump 的时候加上–compatible=mysql40 参数，就可以指定编码了

mysqldump -uroot -proot --compatible=mysql40 --default-character-set=utf8 d5s_data > d5s.sql