将文本中的数据导入到数据库中

       在项目中，有时候我们需要做数据迁移，这边我介绍下一个简单的方式，就是先把数据导出到文件中，然后再通过sql将文件中的数据导入到另外一张表中，下面是依次执行顺序。

 

第一步：从原数据表中导出数据

将原数据表中导出数据，使用sql如下：

EXPORT TO shopInfo.del OF del
SELECT  
       A.SHOPID AS "shopID", 
       A.CSHOPNAME AS "shopName",       
       A.TELPHONE AS "telPhone",    
       A.SHOPTYPE AS "shopType"       
  FROM SOURCE_SHOP A  

 此solr语句很简单，就是把source_shop中的shopid,cshopname,telphone,shoptype字段导出到shopInfo.del文件中，具体导出的数据是如下格式的（截取部分数据）：

 

 

"70056269","旗舰店1","12345678910","3"
"70056265","旗舰店2","12345678811","2"
"70056328","旗舰店3",,"1"
"70056324","旗舰店4",,"3"
"70056329","超级旗舰店1",,"3"
"70056326","超级旗舰店2",,"4"
"70056327","超级旗舰店3",,"3"
"70056346","超级旗舰店4",,"5"
"70055178","装修压测店铺1","18988888888","5"
"70055153","狼人家居旗舰店","15955454065","0"
"70056196","博洋家纺旗舰店",,"3"

 

 

第二步：将数据导入到目标表中

所需要的sql语句如下：

load data local infile "D:\shopInfo.del"  ignore into table shop_info character set utf8 fields terminated by ',' enclosed by '"' lines terminated by '\r\n' (shop_id,shop_name,shop_phone,shop_type);

 关于该sql语句的解释：

load data local infile "D:\shopInfo.del"--加载要导入到表中的目标数据
ignore into table shop_info character set utf8--设置插入表中的数据格式
fields terminated by ',' --文件中是根据逗号分开字段的
enclosed by '"'--字段所对应的值是在双引号之内
lines terminated by '\r\n' --文本中每行数据对应一条记录
(shop_id,shop_name,shop_phone,shop_type);--要插入的数据对应的字段

 

将以上sql执行，便会将文件中的数据导入到表中，以上示例是在mysql服务器中完成的，如果有更好的数据迁移方案，还请大神告知~