mysql数据库转移数据

换数据库
1．建立数据库和相关表
drop database if exists imm_frontend;
如果imm_frontend 存在删除，不存在忽略

create database imm_frontend default charset=utf8;
创建数据库 imm_frontend 默认编码utf8 可以保存 utf8一个汉字占3个字节

drop table if exists `imm_frontend`.` friendstore `;
如果imm_frontend库的friendstore表存在删除，不存在忽略

CREATE TABLE `imm_frontend`.`friendstore` (
`id` int(11) NOT NULL auto_increment,
`storeId` int(11) NOT NULL default '0',
`friendStoreId` int(11) NOT NULL default '0',
`orderKey` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
在imm_frontend库中创建表friendstore，innodb引擎是使用外键，事务等功能

2．转移数据

######### friendstore ##########
增加friendstore表字段
## add a temporay column to identify which table the record immigrate from, 1 from brokerfriend, 2 from companybroker ##
alter table imm_frontend.friendstore add column friendType int(11);

把brokerfriend中brokerId ,friendBrokerId ,orderKey数据导入到friendstore中
## immigrate friendstore from brokerfriend ##
insert ignore into imm_frontend.friendstore (storeId, friendStoreId, orderKey, friendType) select f.brokerId, f.friendBrokerId, f.orderKey, 1 from frontend.brokerfriend f;

把companybroker中companyId, friendBrokerId, orderKey数据导入到friendstore中
## immigrate friendstore from companybroker ##
insert ignore into imm_frontend.friendstore (storeId, friendStoreId, orderKey,friendType) select f.companyId, f.friendBrokerId, orderKey, 2 from frontend.companybroker f;

更新storeId
## update storeId in friendstore, before this the storeId is actually the brokerId ##
update imm_frontend.friendstore friendstore, imm_frontend.store store, frontend.brokerperson p set friendstore.storeId=store.id where friendstore.storeId=p.id and p.userId=store.userId and friendstore.friendType=1;

## update storeId in friendstore, before this the storeId is actually the companyId ##
update imm_frontend.friendstore friendstore, imm_frontend.store store, frontend.brokercompany c set friendstore.storeId=store.id where friendstore.storeId=c.id and c.userId=store.userId and friendstore.friendType=2;

## update friendStoreId, before this the friendStoreId is actually the brokerId ##
update imm_frontend.friendstore friendstore, imm_frontend.store store, frontend.brokerperson p set friendstore.friendStoreId=store.id where friendstore.friendStoreId=p.id and p.userId=store.userId;

## drop temporay column friendType ##
alter table imm_frontend.friendstore drop column friendType;

######### friendstore end ##########

保存文件.sql 执行