如何基于含部分相同字段的两张MySQL表创建符合需求的SQL视图

核心实现逻辑 #

直接使用UNION ALL拼接两张表的查询结果，各自补全对方独有字段为NULL，同时给UserId拼接对应表名前缀即可实现需求，无需复杂关联逻辑。

可用CREATE VIEW语句 #

以下为MySQL语法示例，其他数据库仅需修改字符串拼接函数即可（SQL Server用+拼接、PostgreSQL用||拼接）：

CREATE VIEW view_members AS
-- 处理男性表数据，补全女性表独有字段为NULL
SELECT
    CONCAT('male_', UserId) AS UserId,
    Fname,
    Lname,
    Contact,
    DOB,
    NULL AS Email,
    NULL AS Age
FROM Male

UNION ALL

-- 处理女性表数据，补全男性表独有字段为NULL
SELECT
    CONCAT('female_', UserId) AS UserId,
    Fname,
    Lname,
    NULL AS Contact,
    NULL AS DOB,
    Email,
    Age
FROM Female;

验证查询 #

视图创建完成后，执行你要求的查询即可得到对应结果：

SELECT * FROM view_members WHERE UserId LIKE '%_abcd1';

如果想要避免模糊匹配提升查询效率，也可以在视图中新增original_user_id字段存储原始UserId，优化后的视图语句如下：

CREATE VIEW view_members AS
SELECT
    CONCAT('male_', UserId) AS UserId,
    UserId AS original_user_id,
    Fname,
    Lname,
    Contact,
    DOB,
    NULL AS Email,
    NULL AS Age
FROM Male

UNION ALL

SELECT
    CONCAT('female_', UserId) AS UserId,
    UserId AS original_user_id,
    Fname,
    Lname,
    NULL AS Contact,
    NULL AS DOB,
    Email,
    Age
FROM Female;

优化后可以直接精确查询：

SELECT UserId,Fname,Lname,Contact,DOB,Email,Age FROM view_members WHERE original_user_id = 'abcd1';

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