mysql操作：一个表a字段更新另一个表b字段；表多字段去重

1、全表同步操作：使用一个表的字段t1.a更新表的另一个表的字段t2.b，整个表t1.a字段全部替换，关联条件 t1.c=t2.c

UPDATE
  user_record t1,
  `user` t2
SET
  t1.a = t2.b
WHERE t1.c = t2.c;

2、归总操作：一个表的全部内容根据user_id和type去重，选最新记录，归总到到另一个表里

INSERT INTO `user_choose` (user_id, TYPE, username, phone)
(SELECT
  user_id,
  TYPE,
  username,
  phone
FROM
  user_choose_log
WHERE id IN
  (SELECT
    MAX(id)
  FROM
    user_record
  WHERE user_id > 0
  GROUP BY user_id,
    TYPE)
);

3、同一个表中，变更表字段顺序：username、phone字段都放到id字段之后

ALTER TABLE `user`
  MODIFY `username` VARCHAR (63) DEFAULT NULL AFTER `id`,
  MODIFY `phone` VARCHAR (63) DEFAULT NULL AFTER `id`;

 4、b库je_log与a库rt_rights原本有关联，但开发未记录关联id，所以我们用时间关联：上下一分钟内创建、关联两个表数据，查询出结果后，把right.id记录进je_log表，使两个表直接关联起来

SELECT rights.id AS rights_id,rights.create_time,t.je_id
FROM a.rt_rights AS rights
LEFT JOIN 
(
SELECT id as je_id,user_id,create_time,DATE_ADD(create_time, INTERVAL -1 MINUTE) AS start_t,DATE_ADD(create_time, INTERVAL 1 MINUTE) AS end_t
FROM b.je_log
) AS t
ON rights.user_id=t.user_id
WHERE rights.create_time >t.start_t AND rights.create_time <t.end_t;

5 跟新表，指定记录里，创建时间加1天 (若是减一天，INTERVAL -1即可)

UPDATE sign SET create_time = DATE_ADD(create_time,INTERVAL 1 DAY) WHERE id=15;