两套不同结构的数据库转移数据用到的sql代码
温馨提示：批量执行多条sql语句每行结尾加分号;会员，资讯1、两个数据库之前批量导入数据insert into new08cms.cms_news(name,content,thumb,abstract) select title,info,img,description from old08cms.fang_article2、查看导入之后的情况SELECT * from cms_houses order by id desc3、批量更新需要修改的字段update cms_
admin
温馨提示：批量执行多条sql语句每行结尾加分号;
会员，资讯
1、两个数据库之前批量导入数据
insert into new08cms.cms_news(name,content,thumb,abstract) select title,info,img,description from old08cms.fang_article
2、查看导入之后的情况
SELECT * from cms_houses order by id desc
3、批量更新需要修改的字段
update cms_houses set checked=1,category=3,keywords='无锡房产网',author='无锡房产网企划部',source='无锡房产网',models='news',uid=1,classify=1
4、批量替换字段需要替换的内容
update cms_houses set thumb=replace(thumb,'/uploads/','https://www.wxfcw.cn/uploads/')
会员
insert into new08cms.cms_users(username,email,tel,create_time,update_time,status) select user_name,nick_name,mobile,reg_time,login_time,model from old08cms.fang_user
insert into cms_userinfo(usertype,uid,nicename) select status,id,email from cms_users where id>4935
update cms_userinfo set usertype=11 where usertype=1;
update cms_userinfo set usertype=15 where usertype=2;
update cms_users set status=1;
update cms_users set email='';
update cms_users set password='51ddd8f477971694bebedf8f21ba1450';
新房
update fang_houses s set info = (select info from fang_house_data where house_id = s.id)
insert into new08cms.cms_houses(name,thumb,dj,map,address,tel,kpdate,house_developer,xkzh,sldz) select title,img,price,CONCAT(lng,',',lat),address,sale_phone,opening_time,developer_name,license_key,sale_address from old08cms.fang_house
小区
insert into new08cms.cms_houses(name,thumb,dj,map,address,kpdate,type) select title,img,price,CONCAT(lng,',',lat),address,years,status from old08cms.fang_estate
update cms_houses set checked=1,area=109;
update cms_houses set thumb=replace(thumb,'/userfiles/','https://www.wxfcw.cn/userfiles/');
二手房
SELECT * from cms_sale
**********第一步先创建字段，把另外一张表的相同字段赋值给创建的字段**********
update fang_second_house s set info = (select info from fang_second_house_data where house_id = s.id)
update fang_second_house s set file = (select file from fang_second_house_data where house_id = s.id)
**********第二步把整张表的数据直接插入到另外一个数据库**********
insert into new08cms.cms_sale(name,thumb,lpmc,zj,dj,mj,shi,ting,wei,szlc,zlc,zxcd,cx,content,tel,address,tujis,create_time,update_time)
select title,img,estate_name,price,average_price,acreage,room,living_room,toilet,floor,total_floor,renovation,orientations,info,contacts,address ,file,create_time,update_time from old08cms.fang_second_house
**********第三步插入完成后修改需要修改的字段**********
update cms_sale set checked=1,area=109,uid=1;
update cms_sale set thumb=replace(thumb,'/uploads/user/','https://www.wxfcw.cn/uploads/user/');
update cms_sale set thumb=replace(thumb,'/uploads/image/','https://www.wxfcw.cn/uploads/image/');
update cms_sale set tujis=replace(tujis,'"url":','"href":');
update cms_sale set tujis=replace(tujis,'\/uploads','https:\/\/www.wxfcw.cn\/uploads');
update cms_sale set tel=replace(tel,'{"contact_name":"','');
update cms_sale set tel=replace(tel,'","contact_phone":"','');
update cms_sale set tel=replace(tel,'"}','');
update cms_sale set shi=146 where shi=1;
update cms_sale set shi=147 where shi=2;
update cms_sale set shi=148 where shi=3;
update cms_sale set shi=168 where shi=4;
update cms_sale set shi=169 where shi=5;
update cms_sale set shi=254 where shi=6;
update cms_sale set shi=255 where shi=7;
update cms_sale set ting=170 where ting=1;
update cms_sale set ting=171 where ting=2;
update cms_sale set ting=172 where ting=3;
update cms_sale set wei=175 where wei=1;
update cms_sale set wei=176 where wei=2;
update cms_sale set wei=177 where wei=3;
update cms_sale set wei=178 where wei=4;
update cms_sale set wei=179 where wei=5;
update cms_sale set cx=89 where cx=20;
update cms_sale set cx=90 where cx=22;
update cms_sale set cx=91 where cx=21;
update cms_sale set cx=92 where cx=23;
update cms_sale set cx=133 where cx=24;
update cms_sale set cx=134 where cx=25;
update cms_sale set cx=135 where cx=27;
update cms_sale set cx=136 where cx=26;
update cms_sale set cx=138 where cx=28;
update cms_sale set cx=137 where cx=29;
update cms_sale set zxcd=97 where zxcd=41;
update cms_sale set zxcd=109 where zxcd=42;
update cms_sale set zxcd=110 where zxcd=43;
update cms_sale set zxcd=112 where zxcd=44;
update cms_sale set zxcd=111 where zxcd=101
**********处理联系人和联系电话**********
update cms_sale set xingming=left(tel,char_length(tel)-11);
update cms_sale set tel=right(tel,11);
**********所在楼层暂时不弄**********
update cms_sale set szlc= where szlc=38;
update cms_sale set szlc= where szlc=39;
update cms_sale set szlc= where szlc=40;
update cms_sale set szlc= where szlc=105
租房
SELECT * from cms_rent
**********第一步先创建字段，把另外一张表的相同字段赋值给创建的字段**********
update fang_rental s set info = (select info from fang_rental_data where house_id = s.id)
update fang_rental s set file = (select file from fang_rental_data where house_id = s.id)
**********第二步把整张表的数据直接插入到另外一个数据库**********
insert into new08cms.cms_rent(name,thumb,lpmc,czj,mj,shi,ting,wei,szlc,zlc,zxcd,cx,content,tel,address,fkfs,zlfs,tujis,create_time,update_time)
select title,img,estate_name,price,acreage,room,living_room,toilet,floor,total_floor,renovation,orientations,info,contacts,address ,pay_type,rent_type,file,create_time,update_time from old08cms.fang_rental
**********第三步插入完成后修改需要修改的字段**********
update cms_rent set checked=1,area=109,uid=1;
update cms_rent set thumb=replace(thumb,'/uploads/user/','https://www.wxfcw.cn/uploads/user/');
update cms_rent set thumb=replace(thumb,'/uploads/image/','https://www.wxfcw.cn/uploads/image/');
update cms_rent set tujis=replace(tujis,'"url":','"href":');
update cms_rent set tujis=replace(tujis,'\/uploads','https:\/\/www.wxfcw.cn\/uploads');
update cms_rent set tel=replace(tel,'{"contact_name":"','');
update cms_rent set tel=replace(tel,'","contact_phone":"','');
update cms_rent set tel=replace(tel,'"}','');
update cms_rent set shi=146 where shi=1;
update cms_rent set shi=147 where shi=2;
update cms_rent set shi=148 where shi=3;
update cms_rent set shi=168 where shi=4;
update cms_rent set shi=169 where shi=5;
update cms_rent set shi=254 where shi=6;
update cms_rent set shi=255 where shi=7;
update cms_rent set ting=170 where ting=1;
update cms_rent set ting=171 where ting=2;
update cms_rent set ting=172 where ting=3;
update cms_rent set wei=175 where wei=1;
update cms_rent set wei=176 where wei=2;
update cms_rent set wei=177 where wei=3;
update cms_rent set wei=178 where wei=4;
update cms_rent set wei=179 where wei=5;
update cms_rent set cx=89 where cx=20;
update cms_rent set cx=90 where cx=22;
update cms_rent set cx=91 where cx=21;
update cms_rent set cx=92 where cx=23;
update cms_rent set cx=133 where cx=24;
update cms_rent set cx=134 where cx=25;
update cms_rent set cx=135 where cx=27;
update cms_rent set cx=136 where cx=26;
update cms_rent set cx=138 where cx=28;
update cms_rent set cx=137 where cx=29;
update cms_rent set zxcd=97 where zxcd=41;
update cms_rent set zxcd=109 where zxcd=42;
update cms_rent set zxcd=110 where zxcd=43;
update cms_rent set zxcd=112 where zxcd=44;
update cms_rent set zxcd=111 where zxcd=101
update cms_rent set zlfs=1 where zlfs=49;
update cms_rent set zlfs=2 where zlfs=50;
update cms_rent set fkfs=1 where fkfs=51;
update cms_rent set fkfs=2 where fkfs=52;
update cms_rent set fkfs=3 where fkfs=53
**********处理联系人和联系电话**********
update cms_rent set xingming=left(tel,char_length(tel)-11);
update cms_rent set tel=right(tel,11);
**********所在楼层暂时不弄**********
update cms_rent set szlc= where szlc=38;
update cms_rent set szlc= where szlc=39;
update cms_rent set szlc= where szlc=40;
update cms_rent set szlc= where szlc=105
房源关联不到经纪人
如出现编码错误，先将两个数据表的编码方式统一，然后执行下面的一句
alter table cms_users convert to character set utf8 collate utf8_unicode_ci
出租房关联经纪人
update cms_rent s set uid=(select id from cms_users where username=s.xingming and tel=s.tel limit 1)
二手房关联经纪人
update cms_sale s set uid=(select id from cms_users where username=s.xingming and tel=s.tel limit 1)
