能否向UNION查询结果中新增自增列作为新建表的主键？

实现方法 #

你可以在CREATE TABLE语句中先显式声明自增主键列，再拼接原来的UNION查询即可，数据库会自动为合并后的每一行记录按顺序生成唯一自增ID，修改后的SQL参考如下：

CREATE TABLE NEWTABLE (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键'
)
SELECT * FROM (
    SELECT T1.TEXT as TEXT
       -- 此处保留你原来的其余查询字段
       FROM TABLE1 T1
       LEFT JOIN TABLE2 T2
            on T1.TEXT = T2.TEXT
    UNION
    SELECT T2.TEXT as TEXT
       -- 此处保留你原来的其余查询字段
       FROM TABLE1 T1
       RIGHT JOIN TABLE2 T2
            on T1.TEXT = T2.TEXT
) AS union_result;

注意事项 #

• 自增列的数据类型可根据你的数据量级调整，单表数据量预计超过65535可以改用BIGINT类型
• 将UNION查询包裹为子查询可以避免UNION的排序逻辑干扰自增ID生成顺序，保证ID和你查询结果的返回顺序一一对应
• 如果你使用的是PostgreSQL数据库，语法略有差异，把自增列定义改为id SERIAL PRIMARY KEY或者id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY即可，整体逻辑一致

内容的提问来源于stack exchange，提问作者Excorpion