mysql编码转换问题

翔子

按照下面做法，终于成功了！庆祝，纪念......


背景：某个系统的mysql数据库dnname采用默认的gbk字符集，系统升级需要将所有数据转换成utf-8格式，目标数据库为newdbname(建库时使用utf8)

方法一：

步骤一 命令行执行：mysqldump --opt -hlocalhost -uroot -p*** --default-character-set=lantin1 dbname > /usr/local/dbname.sql

步骤二 将 dbname.sql文件中的create table语句的CHARSET=gbk改为CHARSET=utf8

步骤三 在dbname.sql文件中的insert语句之前加一条'set names utf8;'

步骤四 将dbname.sql转码为utf-8格式，建议使用UltraEditor，可以直接使用该编辑器的'转换->ASCII到UTF-8(Unicode编辑)'，或者将文件另存为UTF-8(无BOM)格式

步骤五 命令行执行：mysql -hlocalhost -uroot -p*** --default-character-set=utf8 new_dbname < /usr/local/dbname.sql

总结：这种方法有个致命之处就是当数据中有大量中文字符和其他特殊符号字符时，很有可能导致在[步骤五]时报错导致无法正常导入数据，如果数据库比较大可以分别对每张表执行上述步骤

方法二（推荐大家使用）：

为了解决第一种方法中总结时说到的问题，在网上苦苦查找了一天资料才东拼西凑的搞出一个比较稳妥的解决方法

步骤一 将待导出的数据表的表结构导出（可以用Phpmyadmin、mysqldump等，很简单就不说了），然后将导出的create table语句的CHARSET=gbk改为CHARSET=utf8，在目标库newdbname中执行该create table语句把表结构建好，接下来开始导出-导入数据。命令：

./mysqldump -d DB_Dig > /usr/local/tmp/tables.sql


步骤二 命令行：进入mysql命令行下，mysql -hlocalhost -uroot -p*** dbname

步骤三 执行SQL select * from tbname into outfile '/usr/local/tbname.sql';

步骤四 将tbname.sql转码为utf-8格式，建议使用UltraEditor，可以直接使用该编辑器的'转换->ASCII到UTF-8(Unicode编辑)'，或者将文件另存为UTF-8(无BOM)格式

步骤五 在mysql命令行下执行语句 set character_set_database=utf8;  注：设置mysql的环境变量，这样mysql在下一步读取sql文件时将以utf8的形式去解释该文件内容

步骤六 在mysql命令行下执行语句 load data infile 'tbname.sql' into table newdbname.tbname;

注意：千万不要忘了第四步

采用第二种方法，所有数据均正常导入，且格式转换成功没有乱码。


参考：http://blog.csdn.net/guoguo1980/archive/2008/01/28/2070701.aspx

翔子

mysql中的数据编码问题

  mysql 4.1版本后，对编码的支持大大加强。有人曾建议,数据库字符集尽量使用utf8(utf-8),以使你的数据能很顺利的实现迁移,因为utf8字符集是目前最适合于实现多种不同字符集之间的转换的字符集,尽管你在命令行工具上无法正确查看数据库中的内容,依然强烈建议使用utf8作为默认字符集.
    MYSQL的字符集缺省是latin1，现在将编码转为真正的utf8。
  在mysql 命令行下或用 GUI工具执行sql 语句,这里以命令行为例:
    mysql> show variables like ‘%character%’;应该会发现如下全部都是latin1，如果不是就不用改了。
  

1. 
   
2.     | character_set_client | latin1 |
   
3.     | character_set_connection | latin1 |
   
4.     | character_set_database | latin1 |
   
5.     | character_set_results | latin1 |
   
6.     | character_set_server | latin1 |
   
7.     | character_set_system | utf8 |

复制代码

然后修改/etc/my.cnf(windows 下请修改mysql.ini)
   加入:

1.    [mysqld]
   
2.    default-character-set=utf8
   
3.    [mysql]
   
4.    default-character-set=utf8

复制代码

重新启动mysql
  再show variables like "%char%"如下所示：

1. | character_set_client | latin1 |
   
2. | character_set_connection | latin1 |
   
3. | character_set_database | utf8 |
   
4. | character_set_results | latin1 |
   
5. | character_set_server | utf8 |
   
6. | character_set_system | utf8 |
   
7. | character_sets_dir | /usr/share/mysql/charsets/ |

复制代码

如果希望character-set-client, character-set-results,character-set-connection也是utf8, 不知道在my.cnf中如何设置才能做到？

我目前都是先执行set names ‘utf8′，再作进一步查询。

执行set names ‘utf8′后，show variables "%char%", show variables "%collation%"显示所有的变量设置都是utf8的了。
另外：修改数据库编码可用如下命令

mysql> Alter DATABASE DEFAULT CHARACTER SET utf8;

接下来是完整的一个例子：

　　1.创建数据库表

　　mysql> CREATE DATABASE IF NOT EXISTS my_db default charset utf8 COLLATE utf8_general_ci;
　　#注意后面这句话 "COLLATE utf8_general_ci",大致意思是在排序时根据utf8变码格式来排序
　　#那么在这个数据库下创建的所有数据表的默认字符集都会是utf8了
　　
　　mysql> create table my_table (name varchar(20) not null default ‘’)type=myisam default charset utf8;
　　#这句话就是创建一个表了,制定默认字符集为utf8
　　
　　2.写数据

　　例子1是通过php直接插入数据:
　　a.php

1. 　　<?php
   
2. 　　mysql_connect(’localhost’, 'user’, 'password’);
   
3. 　　mysql_select_db(’my_db’);
   
4. 　　
   
5. 　　//请注意,这步很关键,如果没有这步,所有的数据读写都会不正确的
   
6. 　　//它的作用是设置本次数据库联接过程中,数据传输的默认字符集
   
7. 　　mysql_query("set names utf8;");
   
8. 　　
   
9. 　　//必须将gb2312(本地编码)转换成utf-8,也可以使用iconv()函数
   
10. 　　mysql_query(mb_convet_encoding("insert into my_table values(’测试’);", "utf-8", "gb2312"));
    
11. 　　?>

复制代码

　　

　　例子是通过页面提交插入数据2:
　　b.php

1. 　　<?php
   
2. 　　//输出本页编码为utf-8
   
3. 　　header("content-type:text/html; charset=utf-8");
   
4. 　　
   
5. 　　mysql_connect(’localhost’, 'user’, 'password’);
   
6. 　　mysql_select_db(’my_db’);
   
7. 　　
   
8. 　　if(isset($_REQUEST[’name’))
   
9. 　　{
   
10. 　　   //由于上面已经指定本页字符集为utf-8了,因此无需转换编码
    
11. 　　   mysql_query(sprintf("insert into my_table values(’%s’);", $_REQUEST[’name’]));
    
12. 　　}
    
13. 　　
    
14. 　　$q = mysql_query("select * from my_table");
    
15. 　　while($r = mysql_fetch_row($q))
    
16. 　　{
    
17. 　　   print_r($r);
    
18. 　　}
    
19. 　　?>
    
20. 　　
    
21. 　　<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    
22. 　　<form action="" method="post">
    
23. 　　<input type="text" name="name" value="">
    
24. 　　<input type="submit" value=’submit’>
    
25. 　　</form>

复制代码

　　

　　自此,使用utf8字符集的完整的例子结束了.
　　如果你想使用gb2312编码,那么建议你使用latin1作为数据表的默认字符集,这样就能直接用中文在命令行工具中插入数据,并且可以直接显示出来.而不要使用gb2312或者gbk等字符集,如果担心查询排序等问题,可以使用binary属性约束,例如:

1. create table my_table ( name varchar(20) binary not null default ‘’)type=myisam default charset latin1;

复制代码

附：旧数据升级办法
　　以原来的字符集为latin1为例，升级成为utf8的字符集。原来的表: old_table (default charset=latin1)，新表：new_table(default charset=utf8)。
　　第一步：导出旧数据
　　

1. mysqldump –default-character-set=latin1 -hlocalhost -uroot -B my_db –tables old_table > old.sql
   

复制代码

　　第二步：转换编码
　　

1. iconv -t utf-8 -f gb2312 -c old.sql > new.sql

复制代码

　　在这里，假定原来的数据默认是gb2312编码。
　　第三步：导入
　　修改old.sql，增加一条sql语句： "SET NAMES utf8;"，保存。
　　

1. mysql -hlocalhost -uroot my_db > new.sql

复制代码

     大功告成！！