mysql两个数据库里的数据迁移

1、基础用法

/*清空Asql数据库里的tdevice表*/
TRUNCATE Asql.tdevice;
/*迁移fm.device->Asql.tdevice*/
INSERT INTO Asql.tdevice(ID,DeviceName,Mac,IPv4,Mask,Gateway,UDisk)
SELECT id,name,code,ipv4,netmask,getway,udisk
FROM fm.device;

2、将查询到的关联表数据写入到表里

TRUNCATE Asql.tasset;
INSERT INTO Asql.tasset(ID,StationId,AssetName,AssetModel,Type,FactoryId,NetAIp,update_time)
SELECT id,substation_id,name,model,type,(SELECT id from Asql.tfactory WHERE name = vendor),ip,NOW()
FROM fm.substation_device;

3、删除当前表里非管理员的数据

DELETE u FROM Asql.t_right_v1_user u,
(SELECT id FROM Asql.t_right_v1_user WHERE id NOT IN(1,2)) t
WHERE u.id = t.id;

4、将非管理员数据写入

INSERT INTO Asql.t_right_v1_user(name,code)
SELECT realname,username
FROM fm.`user` WHERE id NOT IN(1);

5、批量修改参数

UPDATE Asql.t_right_v1_user
SET reserve = CASE reserve
WHEN 1 THEN 2
WHEN 3 THEN 3
WHEN 2 THEN 1
END;