如何在MySQL查询结果中去除重复user_id以创建视图？

创建视图时触发错误 #1060 - Duplicate column name 'user_id'，原因是关联user_profile（别名A）与user_personal_info（别名B）后，结果集存在重复的user_id字段。需要保留A表的user_id并移除B表的该字段，同时不想逐个指定字段（避免后续新增字段时语句冗余）。

当前使用的查询及建视图语句：

SELECT * FROM user_profile as A
INNER JOIN user_personal_info as B 
on A.user_id=B.user_id

CREATE View Studentinfo as
SELECT * FROM user_profile as A
INNER JOIN user_personal_info as B 
on A.user_id=B.user_id

解决方案 #

如果你的MySQL版本在8.0.31及以上，可使用EXCEPT语法直接排除B表的user_id，无需逐个列举其他字段：

CREATE View Studentinfo as
SELECT A.*, B.* EXCEPT (user_id)
FROM user_profile as A
INNER JOIN user_personal_info as B 
ON A.user_id = B.user_id

若MySQL版本较低不支持EXCEPT，则只能逐个指定B表除user_id外的字段（后续新增字段需同步更新语句）：

CREATE View Studentinfo as
SELECT A.*, B.name, B.gender, B.birth_date, B.phone, B.address
FROM user_profile as A
INNER JOIN user_personal_info as B 
ON A.user_id = B.user_id

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