如何在SELECT *查询时为列设别名且避免列重复（无需枚举所有字段）

假设存在一张名为people的表，数据示例如下：

fullName: "John Doe",
id: 1,
isActive: True

我尝试用以下SQL查询：

SELECT p.fullName AS "name",
p.*
FROM people p

但结果里会同时出现name和fullName两列，数据重复。现在需要一种方法，既能避免列重复，又不用逐个枚举表中超过50个的所有属性。

核心思路：利用数据库元数据生成动态SQL #

所有主流数据库都提供了存储表结构信息的系统视图，我们可以通过查询这些视图，自动拼接出排除指定列（这里是fullName）的SQL语句，不用手动枚举所有字段。以下是不同数据库的实现方式：

MySQL/MariaDB

-- 拼接除fullName外的所有列
SET @cols = (SELECT GROUP_CONCAT(column_name SEPARATOR ', ')
             FROM information_schema.columns
             WHERE table_name = 'people'
               AND column_name != 'fullName');

-- 组装完整查询语句
SET @sql = CONCAT('SELECT fullName AS name, ', @cols, ' FROM people');

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

PostgreSQL

DO $$
DECLARE
  cols text;
BEGIN
  -- 拼接目标列
  SELECT string_agg(column_name, ', ')
  INTO cols
  FROM information_schema.columns
  WHERE table_name = 'people'
    AND column_name != 'fullName';

  -- 执行动态生成的查询
  EXECUTE 'SELECT fullName AS name, ' || cols || ' FROM people';
END $$;

SQL Server

DECLARE @cols NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

-- 拼接排除fullName后的列列表
SELECT @cols = STRING_AGG(column_name, ', ')
FROM information_schema.columns
WHERE table_name = 'people'
  AND column_name != 'fullName';

-- 组装查询语句并执行
SET @sql = N'SELECT fullName AS name, ' + @cols + N' FROM people';
EXEC sp_executesql @sql;

可选方案：创建可复用的视图 #

如果需要重复使用这个查询，可以用动态SQL创建一个视图，之后直接查询视图即可：

以MySQL为例：

-- 生成创建视图的SQL
SET @cols = (SELECT GROUP_CONCAT(column_name SEPARATOR ', ')
             FROM information_schema.columns
             WHERE table_name = 'people'
               AND column_name != 'fullName');

SET @sql = CONCAT('CREATE VIEW people_simplified AS SELECT fullName AS name, ', @cols, ' FROM people');

-- 创建视图
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 后续直接查询视图
SELECT * FROM people_simplified;

注意事项 #

• 执行动态SQL需要对应的数据库权限（比如SELECT元数据的权限、执行动态语句的权限）
• 如果表结构发生变更（新增/删除列），需要重新执行动态SQL来更新查询语句或视图

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