MySQL数据导入--load data

来源：互联网发布：mac口红代购编辑：程序博客网时间：2024/06/05 03:42

起因：

朋友的数据库，用的版本是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);

阅读全文

0 0