如何从MySQL中的表中“选择*”但省略某些列？

I have a table with the following columns:

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

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

Instead of: SELECT name,age,surname,lastname,catgory FROM table

而不是：SELECT name，age，surname，lastname，catgory FROM table

How can I make something like this: SELECT * FROM table [but not select id,active]

我怎么能这样做：SELECT * FROM table [但不选择id，active]

9 个解决方案

#1

While many say it is best practice to explicitly list every column you want returned, there are situations where you might want to save time and omit certain columns from the results (e.g. testing). Below I have given two options that solve this problem.

虽然许多人说明确列出您想要返回的每一列是最佳做法，但在某些情况下您可能希望节省时间并省略结果中的某些列（例如测试）。下面我给出了两个解决这个问题的方法。

1. Create a Function that retrieves all of the desired column names: ( I created a schema called functions to hold this function)

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

Create and execute select statement:

创建并执行select语句：

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

2. OR without writing a function you could:

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;

*Replace test with your own schema name

*用您自己的架构名称替换测试

**Replace employees with your own table name

**用您自己的表名替换员工

***Replace age,dateOfHire with the columns you want to omit (you can leave it blank to return all columns or just enter one column name to omit)

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

** **You can adjust the lengths of the varchars in the function to meet your needs

** **您可以调整功能中的varchars长度以满足您的需求

#2

The only way to do that that I know if is to enumerate each column you do want... no negative filters that I'm aware of.

我知道的唯一方法是枚举你想要的每一列......没有我知道的负面过滤器。

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

#3

you can't do that, sorry. Actually you shouln't have done it if you could - specifying these things explicitly is always better, assume other developer adds new field and your application will fail

抱歉，你不能这样做。实际上，如果可以的话，你还没有做到 - 明确指定这些事情总是更好，假设其他开发人员添加新字段并且您的应用程序将失败

#4

You are too advanced.

你太先进了。

The only data language that I have seen that supports your syntax is the D language with its "...ALL BUT ..." construct:

我见过的唯一支持语法的数据语言是D语言及其“... ALL BUT ...”结构：

Wikipedia - D Language Specification

* - D语言规范

There are some reference implementations available, but mostly for teaching purposes.

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

#5

Unless there's some special extension in MySql you cannot do that. You either get all, or have to explicitly state what you want. It is best practice to always name columns, as this will not alter the query behaviour even if the underlying table changes.

除非在MySql中有一些特殊的扩展，否则你不能这样做。你要么得到所有，要么必须明确说明你想要的。最好始终为列命名，因为即使基础表发生更改，这也不会改变查询行为。

#6

There is no SQL syntax to support:

没有SQL语法支持：

select * from table but not select id,active

If you want all but one or more columns, you have to explicitly define the list of columns you want.

如果您想要除一个或多个列之外的所有列，则必须显式定义所需的列列表。

#7

You should not be using select * anyway. Enumerate the columns you want and only the columns you want, that is the best practice.

你不应该使用select *。枚举所需的列，只列出所需的列，这是最佳实践。

#8

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

SET @sql = CONCAT（ 'SELECT'，（SELECT REPLACE（GROUP_CONCAT（COLUMN_NAME）， ' ' ''）FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '' AND TABLE_SCHEMA = ''），' FROM'）;

PREPARE stmt1 FROM @sql; EXECUTE stmt1;

PREPARE stmt1 FROM @sql;执行stmt1;

#9

I'm fairly certain you can't. Probably the best way I can think of is to create SELECT name, age, surname, lastname, category FROM table as a view, then just SELECT * FROM view. I prefer to always select from a view anyway.

我相当肯定你做不到。可能我能想到的最好的方法是创建SELECT名称，年龄，姓氏，姓氏，类别FROM表作为视图，然后只是SELECT * FROM视图。无论如何，我总是喜欢从视图中选择。

However, as others have pointed out, if another column gets added to the view your application could fail. On some systems as well (PostgreSQL is a candidate) you cannot alter the table without first dropping the view so it becomes a bit cumbersome.

但是，正如其他人指出的那样，如果将另一列添加到视图中，您的应用程序可能会失败。在某些系统上（PostgreSQL是一个候选者）你不能在没有先删除视图的情况下改变表，因此它变得有点麻烦。

#1