起因：
朋友的数据库，用的版本是5.5.19；服务端和客户端字符集都是utf8，因为某些原因，系统经过好多人的开发和处理，同一个表存在多种字符集写入；so乱码问题，时有发生。为了彻底解决这个问题。
我这边的操作如下：
1.核查工程中转码的地方。
2.将数据库每个表都转出来；转成utf8。
3.调试：新的工程和新的库。
--------------------------------------------------
1.核查工程中转码的地方，既然都是web工程，页面上用的也是utf8，那么为什么还要转过来转过去。
2.通过php（php操作数据库感觉很方便，以前没有用过）把数据库转成文本（两种格式文件：insert语句、loaddata能用的标准文本；文件字符集都用utf8）
注意事项：
1.把 max_allowed_packet 搞大点
2.load前 记得把sql_mode置空（看你自己业务要求）
3.为什么不用insert语句，而用loaddata？不仅仅是因为，load快，而且它可以让你不用去操心字符串中的单引号，这种特殊字符的转译。
4.所有字段都当做字符串处理。因为默认值的问题，可能会影响你的业务逻辑。
SSL: Not in useUsing delimiter: ;Server version: 5.5.19 MySQL Community Server (GPL)Protocol version: 10Connection: *** via TCP/IPServer characterset: utf8Db characterset: utf8Client characterset: utf8Conn. characterset: utf8TCP port: 3306
部分拼接代码，其实就是拼字符串。
$sql="SELECT Address,RealName,TtlGold,GoldOrder,SF,JG,TJR,Pic,subsTime,unsubsTime,Cases,Tel,silver,ID,WCID,SignStatus,UperID,NickName,City,Sex,IfDream,Role,HeadImgUrl,gold FROM usr001 where ID<=31176 order by ID ";
$result = $conn_old->query($sql);
if ($result->num_rows > 0) {
// 输出数据
$filename =$filepath.$tablename.'.dat';
$filename1 =$filepath.$tablename.'1.dat';
if(file_exists($filename)){
unlink($filename); //删除文件
}
if(file_exists($filename1)){
unlink($filename1); //删除文件
}
while($row = $result->fetch_assoc()) {
$Address=$row["Address"];
$RealName=$row["RealName"];
$TtlGold=$row["TtlGold"];
$GoldOrder=$row["GoldOrder"];
$SF=$row["SF"];
$JG=$row["JG"];
$TJR=$row["TJR"];
$Pic=$row["Pic"];
$Price=$row["Price"];
$subsTime=$row["subsTime"];
$unsubsTime=$row["unsubsTime"];
$Cases=$row["Cases"];
$Tel=$row["Tel"];
$silver=$row["silver"];
$ID=$row["ID"];
$WCID=$row["WCID"];
$SignStatus=$row["SignStatus"];
$UperID=$row["UperID"];
//$NickName=str_replace($row["NickName"],"'","\\'");$NickName=iconv("GBK","UTF-8",$row["NickName"]);
$NickName=$row["NickName"];
$City=$row["City"];
$Sex=$row["Sex"];
$IfDream=$row["IfDream"];
$Role=$row["Role"];
$HeadImgUrl=$row["HeadImgUrl"];
$gold=$row["gold"];
$flag=1;
$insertsql = "INSERT INTO usr0001(Address,RealName,TtlGold,GoldOrder,SF,JG,TJR,Pic,subsTime,unsubsTime,Cases,Tel,silver,ID,WCID,SignStatus,UperID,NickName,City,Sex,IfDream,Role,HeadImgUrl,gold)VALUES('".$Address."','".$RealName."','".$TtlGold."','".$GoldOrder."','".$SF."','".$JG."','".$TJR."','".$Pic."','".$subsTime."','".$unsubsTime."','".$Cases."','".$Tel."','".$silver."','".$ID."','".$WCID."','".$SignStatus."','".$UperID."','".$NickName."','".$City."','".$Sex."','".$IfDream."','".$Role."','".$HeadImgUrl."','".$gold."');\r\n";
//写文件
//file_put_contents($filename, $insertsql, FILE_APPEND);
$filep="\"".$Address."\"|\"".$RealName."\"|\"".$TtlGold."\"|\"".$GoldOrder."\"|\"".$SF."\"|\"".$JG."\"|\"".$TJR."\"|\"".$Pic."\"|\"".$subsTime."\"|\"".$unsubsTime."\"|\"".$Cases."\"|\"".$Tel."\"|\"".$silver."\"|\"".$ID."\"|\"".$WCID."\"|\"".$SignStatus."\"|\"".$UperID."\"|\"".$NickName."\"|\"".$City."\"|\"".$Sex."\"|\"".$IfDream."\"|\"".$Role."\"|\"".$HeadImgUrl."\"|\"".$gold."\"\r\n";
file_put_contents($filename1, $filep, FILE_APPEND);
//$result4new=$conn_new->query($insertsql);
//if ($result4new){
// echo "1 添加成功";
//}else{
// echo "0 添加失败";
//}
}
} else {
echo "0 结果";
}
基本语法：
load data [low_priority]
[local] --默认是服务端的文件，加上local就可以导客户端文件
infile'file_name txt'
[replace | ignore]
into table tbl_name
[fields
[terminated by't'] --列分割
[OPTIONALLY] enclosed by ''] --列的包括符
[escaped by'\' ]]
[lines terminated by'n'] --行分割
[ignore number lines]--忽略某行，比如标题
[(col_name, )]--导入的列与文件列的顺序一致
LOW_PRIORITY | CONCURRENT：若指定LOW_PRIORITY，则该LOAD DATA 语句的真正执行将推迟到没有客户端在读取所设计的表时（只对只支持表锁的引擎有效）；
LOCAL：若未指定该关键字，则说明文件在MySQL所在机子上，文件由MySQL服务器去读取，此时如果后面指定为文件路径为相对路径，1.如果路径以./开头，那么路径是相对于MySQL的data目录的，2.如果路径不是以./开头，那么路径是相对于默认数据库的目录的；若指定了该关键字，则说明文件在客户端机子上，文件由客户端去读取并通过网络发送给MySQL服务器
REPLACE | IGNORE ：当插入的行遇到UNIQUE字段重复时，若指定为REPLACE，则用该行替换原来的行；若指定为IGNORE，则忽略改行
PARTITION (partition_name,...)：将数据插入指定分区
CHARACTER SET：若不指定字符集，MySQL默认使用character_set_database变量指定的字符集去读取文件，若文件字符集不同，则应指定该关键字
FIELDS TERMINATED BY：字段值的分隔符，若不指定则默认为 '\t'
FIELDS ENCLOSED BY：字段值的包括符，若不指定则默认为 ''
FIELDS ESCAPED BY：字段值的转义字符，若不指定则默认为'\\'
LINES TERMINATED BY：指定行分隔符，若不指定则默认为为系统的默认行分隔符（‘\r\n‘ on windows，'\n' on linux）
LINES STARTING BY：若指定该值为xxx，则MySQL会自动去掉xxx及其前面的字符，若某行不包含xxx，则改行将被忽略，若不指定默认为''
load data local infile 'F:/cmsphp/phpStudy1/WWW/charset/usr_let311761.dat' replace into table usr character set utf8 fields terminated by '|' enclosed by '"' lines terminated by '\r\n' (Address,RealName,TtlGold,GoldOrder,SF,JG,TJR,Pic,subsTime,unsubsTime,Cases,Tel,silver,ID,WCID,SignStatus,UperID,NickName,City,Sex,IfDream,Role,HeadImgUrl,gold);
load data local infile 'F:/cmsphp/phpStudy1/WWW/charset/usr_gt311761.dat' replace into table usr character set utf8 fields terminated by '|' enclosed by '"' lines terminated by '\r\n' (Address,RealName,TtlGold,GoldOrder,SF,JG,TJR,Pic,subsTime,unsubsTime,Cases,Tel,silver,ID,WCID,SignStatus,UperID,NickName,City,Sex,IfDream,Role,HeadImgUrl,gold);
mysql> load data local infile 'F:/cmsphp/phpStudy1/WWW/charset/usr_let311761.dat' replace into table usr001 charac
Query OK, 31113 rows affected, 35 warnings (8.76 sec)
Records: 31113 Deleted: 0 Skipped: 0 Warnings: 35
mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------------------
| Level | Code | Message
+---------+------+---------------------------------------------------------------------------------------------
| Warning | 1366 | Incorrect integer value: '' for column 'Sex' at row 1239
| Warning | 1366 | Incorrect integer value: '' for column 'Sex' at row 1418
| Warning | 1366 | Incorrect integer value: '' for column 'Sex' at row 6457
| Warning | 1366 | Incorrect integer value: '' for column 'Sex' at row 9301
| Warning | 1366 | Incorrect integer value: 'hBibN
| Warning | 1261 | Row 10035 doesn't contain data for all columns
| Warning | 1261 | Row 10035 doesn't contain data for all columns
| Warning | 1366 | Incorrect integer value: '' for column 'Sex' at row 12102
| Warning | 1366 | Incorrect integer value: '' for column 'Sex' at row 13476
| Warning | 1366 | Incorrect integer value: '' for column 'Sex' at row 14445
| Warning | 1366 | Incorrect integer value: '' for column 'Sex' at row 14667
| Warning | 1366 | Incorrect integer value: 'hQsE4P6
| Warning | 1261 | Row 15215 doesn't contain data for all columns
| Warning | 1366 | Incorrect integer value: 'ht5Td9m
| Warning | 1261 | Row 15888 doesn't contain data for all columns
| Warning | 1366 | Incorrect integer value: '' for column 'Sex' at row 18307
| Warning | 1366 | Incorrect integer value: '' for column 'Sex' at row 20534
| Warning | 1366 | Incorrect integer value: '' for column 'Sex' at row 21174
| Warning | 1366 | Incorrect integer value: '' for column 'Sex' at row 21750
| Warning | 1366 | Incorrect integer value: '' for column 'Sex' at row 22025
| Warning | 1366 | Incorrect integer value: '' for column 'Sex' at row 22078
| Warning | 1366 | Incorrect integer value: '' for column 'Sex' at row 22822
| Warning | 1366 | Incorrect integer value: '' for column 'Sex' at row 23877
| Warning | 1366 | Incorrect integer value: '' for column 'Sex' at row 25114
| Warning | 1366 | Incorrect integer value: '' for column 'Sex' at row 27150
| Warning | 1366 | Incorrect string value: '\xB0\xA2\xD3\xC2\xA3\xAC...' for column 'NickName' at row 27347
| Warning | 1366 | Incorrect integer value: '' for column 'Sex' at row 27733
| Warning | 1366 | Incorrect integer value: '' for column 'Sex' at row 29143
| Warning | 1366 | Incorrect integer value: '' for column 'Sex' at row 29641
| Warning | 1366 | Incorrect integer value: '' for column 'Sex' at row 29714
| Warning | 1366 | Incorrect integer value: '' for column 'Sex' at row 30212
| Warning | 1366 | Incorrect string value: '\xBE\xDD\xCB\xB5\xCA\xD6...' for column 'NickName' at row 30356
| Warning | 1366 | Incorrect integer value: '' for column 'Sex' at row 30527
| Warning | 1366 | Incorrect integer value: '' for column 'Sex' at row 30531
| Warning | 1366 | Incorrect string value: '\xB4\xEF\xB6\xFB\xCE\xC4' for column 'City' at row 31051
+---------+------+---------------------------------------------------------------------------------------------
35 rows in set (0.03 sec)
mysql>
mysql> load data local infile 'F:/cmsphp/phpStudy1/WWW/charset/usr_gt311761.dat' replace into table usr001
Query OK, 1535 rows affected, 1 warning (0.53 sec)
Records: 1535 Deleted: 0 Skipped: 0 Warnings: 1
mysql> show warnings;
+---------+------+---------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: '' for column 'Sex' at row 501 |
+---------+------+---------------------------------------------------------+
1 row in set (0.03 sec)
