如何在MySQL中从表中选择*，但省略某些列？

Question

我有一个包含以下列的表：

id,name,age,surname,lastname,catgory,active

不要使用：SELECT name,age,surname,lastname,catgory FROM table

如何实现这样的查询：SELECT * FROM table [但不选择id和active]

- Mostafa Elkady

---

可能是[在MySQL中选择除一个列之外的所有列？]的重复问题（https://dev59.com/HnVD5IYBdhLWcg3wWKPc） - tstenner

@tstenner 是的，我相信这是你链接的那个问题的重复。虽然这个问题还没有得到回答，但我认为donl给出的答案更好、更准确。 - volderArt

Answer 1

虽然很多人说最好的做法是明确列出每个想要返回的列，但在某些情况下，你可能想节省时间并从结果中省略某些列（例如测试）。下面给出了两个解决此问题的选项。

1. 创建一个函数来检索所有想要的列名：（我创建了一个称为 functions 的模式来保存这个函数）

DELIMITER $$

CREATE DEFINER=`root`@`%` FUNCTION `getTableColumns`(_schemaName varchar(100), _tableName varchar(100), _omitColumns varchar(200)) RETURNS varchar(5000) CHARSET latin1
BEGIN
    SELECT GROUP_CONCAT(COLUMN_NAME) FROM information_schema.columns 
    WHERE table_schema = _schemaName AND table_name = _tableName AND FIND_IN_SET(COLUMN_NAME,_omitColumns) = 0 ORDER BY ORDINAL_POSITION;
END

创建并执行选择语句：

SET @sql = concat('SELECT ', (SELECT 
functions.getTableColumns('test', 'employees', 'age,dateOfHire')), ' FROM test.employees'); 
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

2. 或者不写函数，您可以：

SET @sql = CONCAT('SELECT ', (SELECT GROUP_CONCAT(COLUMN_NAME) FROM 
information_schema.columns WHERE table_schema = 'test' AND table_name = 
'employees' AND column_name NOT IN ('age', 'dateOfHire')), 
' from test.eployees');  
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

*将test替换为您自己的架构名称

**将employees替换为您自己的表名

***用您想要省略的列替换age,dateOfHire（您可以将其留空以返回所有列，或只输入一个列名以省略）

** **您可以调整函数中varchar的长度以满足您的需要

- donL

---

不要忘记增加group_concat_max_len的大小，否则您的列可能会被截断。设置SESSION group_concat_max_len = 20480。 - c_Reg_c_Lark

如果您测试此SQL语句，您可能需要为测试添加一些“LIMIT”限制。例如：' from test.employees LIMIT 100'。例如，在phpmyadmin中显示 - 如果没有限制，这将在大表上超时，准备显示。然后在最终处理时删除该限制。 - ToolmakerSteve

将结果存储到表中可能很有用。例如，我这样做是为了导出一个没有ID的表（通过导出temptable而不是原始表）。 SET @sql = CONCAT('CREATE TABLE temptable SELECT '... - ToolmakerSteve

Answer 2

我所知道的唯一方法是枚举您需要的每一列... 我不知道任何负面过滤器。

select name, age, surname, lastname, category from table

- aw crud

Answer 3

你太高级了。

我见过的唯一支持你语法的数据语言是D语言，它有一个“...ALL BUT ...”结构：

维基百科 - D 语言规范

有一些参考实现可用，但主要用于教学目的。

- Martin

---

链接现在无效。你能更新一下吗？ - undefined

Answer 4

抱歉，你不能这样做。实际上，如果可能的话，你也不应该这样做——明确指定这些内容总是更好的，因为假设其他开发人员添加了新字段，你的应用程序将会失败。

- Andrey

Answer 5

没有SQL语法支持：

select * from table but not select id,active

如果你想要除了一个或多个列之外的所有列，你必须明确地定义你想要的列列表。

- OMG Ponies

Answer 6

SET @sql = CONCAT('SELECT ',
    (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), '<columns_to_delete>,', '')
       FROM INFORMATION_SCHEMA.COLUMNS
       WHERE TABLE_NAME = '<table>'
         AND TABLE_SCHEMA = '<database>'),
     ' FROM <table>');

PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

- Satish

---

当回答一个已经有答案的老问题时，请解释你的答案与众不同之处，并说明在什么情况下你的答案可能更可取。谢谢。 - ToolmakerSteve

Answer 7

除非在MySql中有一些特殊的扩展，否则您无法这样做。您要么获取全部内容，要么必须明确指定您想要的内容。最佳实践是始终命名列，即使基础表发生更改，这也不会改变查询行为。

- Anders Abel

Answer 8

无论如何，您都不应该使用 select *。枚举您想要的列，并且只选择您需要的列，这是最佳实践。

- HLGEM

Answer 9

我相当确定你不行。我能想到的最好方法可能是创建一个视图：SELECT name, age, surname, lastname, category FROM table，然后只需执行SELECT * FROM view。无论如何，我总是倾向于从视图中进行选择。

然而，正如其他人指出的那样，如果视图中添加了其他列，你的应用程序可能会出错。在某些系统（如PostgreSQL）中，你不能在不先删除视图的情况下更改表，因此这变得有点麻烦。

- Andy Shellam

Answer 10

如果原因是为了避免列重复错误而不必指定一长串的列：

1. 临时更改重复的列名，以便能够创建视图。

2. 从选择和保存的视图中删除重复的列。

3. 重新命名已更改的列名。

如果原因只是为了省略一个或多个列：

1. 创建视图并从选择中删除列。

- haz