mysql的insert into from select 和update by select

 

从contact表生成dialog表的uid, contact_uid, last_msg_id, last_msg_time, total_msg_count:

insert into sns_user_00.user_dialog_00(uid, contact_uid,last_msg_time, last_msg_id, totle_msg_count) select uid, contact_uid, ctime as last_msg_time, msg_id as last_msg_id, count(*) as total_msg_count from (select  msg_id, uid, contact_uid, ctime, type from sns_user_00.user_contact_00 order by ctime desc) `temp`  group by uid, contact_uid order by ctime desc;

 

上面语句实现了insert into select，其中包括查询每一个类别中时间最晚的记录，利用嵌套select

 

从contact表生成dialog表的last_recv_time:

 

insert into sns_user_00.user_dialog_00(uid, contact_uid, last_recv_time) select * from ( select uid, contact_uid, ctime as recv_time from sns_user_00.user_contact_00 where type=1 order by ctime desc ) `temp`  group by uid,contact_uid order by recv_time desc  on duplicate key update last_recv_time = values(last_recv_time);

 

上面语句实现了从结果集中去值然后update， 当然如果是多字段的话  last_recv_time = values(last_recv_time)可以写多个，以逗号隔开。

 

有时候可以利用mysql自身实现数据转移，不必从db导出为文本，然后再处理为新的插入语句，最后再执行一次导入。