`
guyunduzai
  • 浏览: 16748 次
  • 性别: Icon_minigender_1
  • 来自: 南京
社区版块
存档分类
最新评论
阅读更多

       在项目中,有时候我们需要做数据迁移,这边我介绍下一个简单的方式,就是先把数据导出到文件中,然后再通过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服务器中完成的,如果有更好的数据迁移方案,还请大神告知~

 

 

 

 

分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics