摘要:本文主要向大家介绍了MySQL数据库开发的数据导入--loaddata,通过具体的代码向大家展示,希望对大家学习MySQL数据库开发有所帮助。
本文主要向大家介绍了MySQL数据库开发的数据导入--loaddata,通过具体的代码向大家展示,希望对大家学习MySQL数据库开发有所帮助。
起因:
朋友的数据库,用的版本是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)
本文由职坐标整理并发布,希望对同学们学习MySQL有所帮助,更多内容请关注职坐标数据库MySQL数据库频道!
您输入的评论内容中包含违禁敏感词
我知道了
请输入正确的手机号码
请输入正确的验证码
您今天的短信下发次数太多了,明天再试试吧!
我们会在第一时间安排职业规划师联系您!
您也可以联系我们的职业规划师咨询:
版权所有 职坐标-一站式IT培训就业服务领导者 沪ICP备13042190号-4
上海海同信息科技有限公司 Copyright ©2015 www.zhizuobiao.com,All Rights Reserved.
沪公网安备 31011502005948号