MySQL数据导入--loaddata

程序员文章站 2022-09-14 16:10:21

起因：朋友的数据库，用的版本是5.5.19；服务端和客户端字符集都是utf8，因为某些原因，系统经过好多人的开发和处理，同一个表存在多种字符集写入；so乱码问题，时有发生。为了...

起因：

朋友的数据库，用的版本是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命令行备份及恢复数据库的步骤

下一篇： SQL语句入门必学

MySQL数据导入--loaddata

随机生成八位优惠码并保存至Mysql数据库

Mysql5.7中使用group concat函数数据被截断的问题完美解决方法

PHP结合Mysql数据库实现留言板功能

MySQL 数据库对服务器端光标的限制

SQLServer导出数据到MySQL实例介绍

实现PHP框架系列文章(6)mysql数据库方法

Mysql 数据库访问类

MySQL 数据库跨操作系统的最快迁移方法

MySQL 数据库的临时文件究竟储存在哪里

MySQL 数据类型和建库策略