mysql 导入注意事项_mysql load 导入数据注意事项

最新推荐文章于 2022-12-07 13:31:12 发布

太閤秀吉

最新推荐文章于 2022-12-07 13:31:12 发布

阅读量197

点赞数

文章标签： mysql 导入注意事项

本文链接：https://blog.csdn.net/weixin_34449886/article/details/113643158

版权

自己整理测试数据，从excle导出为txt，使用tab区分。导入数据库后，数据看起来是对的。在ext grid 下可以显示出来。但诡异的是，comb方式加载数据后，隐藏域的值一会是displayfield，一会是valuefield。查其缘由是由于数据库数据正确造成的，数据displayfield后有'\r\n'。

使用如下，去除\r\n'

CHARACTER SET utf8 FIELDS TERMINATED BY '\t' Lines Terminated By '\r\n'

数据导入是使用load方式，一次性到入多个表的测试数据，bat脚本如下

@echo off

set errorlevel=0

rem 修改为mysql安装路径

set path_home_mysql=D:\\jakehu\\dev\\MySQL\\MySQL Server 5.1

set path_bin_mysql=%path_home_mysql%\\bin

rem 修改为mysql使用的数据名(test)

set database_mysql=idms

rem 修改为mysql登陆用户名密码(root,root)

set user_mysql=root

set password_mysql=xxxxx

rem 修改要执行的.sql文件(init.sql)

"%path_bin_mysql%\\mysql" -u%user_mysql% -p%password% %database_mysql%< init.sql

导入数据的脚本 init.sql

truncate table ip_sequence;

truncate table administrative_devision;

load data inFile 'd:\\dataInit\\devision.txt' into table administrative_devision CHARACTER SET utf8 FIELDS TERMINATED BY '\t' Lines Terminated By '\r\n' (code,name);

truncate table net_work_operator;

load data inFile 'd:\\dataInit\\operator.txt' into table net_work_operator CHARACTER SET utf8 FIELDS TERMINATED BY '\t' Lines Terminated By '\r\n' (code,name);

truncate table industry_category;

load data inFile 'd:\\dataInit\\industry_category.txt' into table industry_category CHARACTER SET utf8 FIELDS TERMINATED BY '\t' Lines Terminated By '\r\n'(code,name);

truncate table ip_type;

load data inFile 'd:\\dataInit\\ip_type.txt' into table ip_type CHARACTER SET utf8 FIELDS TERMINATED BY '\t' Lines Terminated By '\r\n' (code,name);

truncate table net_band_width;

load data inFile 'd:\\dataInit\\net_band_width.txt' into table net_band_width CHARACTER SET utf8 FIELDS TERMINATED BY '\t' Lines Terminated By '\r\n'(width);

truncate table user_type;

load data inFile 'd:\\dataInit\\user_type.txt' into table user_type CHARACTER SET utf8 FIELDS TERMINATED BY '\t' Lines Terminated By '\r\n'(code,name);

truncate table distribution_method;

load data inFile 'd:\\dataInit\\distribution_method.txt' into table distribution_method CHARACTER SET utf8 FIELDS TERMINATED BY '\t' Lines Terminated By '\r\n'(code,name);

load data inFile 'd:\\dataInit\\province_ip_sequence.txt' into table ip_sequence CHARACTER SET utf8 FIELDS TERMINATED BY '\t' Lines Terminated By '\r\n' (type,begin_ip,end_ip,operator_code,type_code) ;

分享到：