如何联合查询两个结构相同的表并且id不重复

Question

1.有两个相同结构的表guangdong_student,shanghai_student,希望联合查询表一，表二，得到表三的结果。使用union会导致id重复，如何做到id自增

2.示例：

表一：guangdong_student
+----+----------+
| id | username |
+----+----------+
|  1 | jhon     |
|  2 | may      |
|  3 | abcd1111 |
+----+----------+

表二：shanghai_student
+----+----------+
| id | username |
+----+----------+
|  1 | jhon     |
|  2 | mike     |
|  3 | abcd     |
+----+----------+

希望合并后的表：student
+----+----------+
| id | username |
+----+----------+
|  1 | jhon     |
|  2 | may      |
|  3 | abcd1111 |
|  4 | mike     |
|  5 | abcd     |
+----+----------+

Answer 1

在id是Primary key并且为auto_increment的情况下，执行下面语句可以做到。
insert into guangdong_student(username) select username from shanghai_student;

Answer 2

set @id=0;
select @id := @id+1 id,distinct username, 
from ((select * from guangdong_student) gd 
    union 
    (select * from shanghai_student) sh) student;

Answer 3

select @id := @id+1 id, username
from (
    select  username
    from guangdong_student
    union
    select  username
    from shanghai_student
) t1,
(SELECT @id:=0) t2