也许我有一个解决Jan Koritak指出的矛盾的方法

SELECT CONCAT('SELECT ',
( SELECT GROUP_CONCAT(t.col)
FROM
(
    SELECT CASE
    WHEN COLUMN_NAME = 'eid' THEN NULL
    ELSE COLUMN_NAME
    END AS col 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_NAME = 'employee' AND TABLE_SCHEMA = 'test'
) t
WHERE t.col IS NOT NULL) ,
' FROM employee' );

表:

SELECT table_name,column_name 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'employee' AND TABLE_SCHEMA = 'test'

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

table_name  column_name
employee    eid
employee    name_eid
employee    sal

= = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = =

查询结果:

'SELECT name_eid,sal FROM employee'

实际上有一种方法，当然你需要有权限才能这样做…

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

PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

替换<表>，<数据库>和< columns_to_省略>

是的，尽管根据表的不同，I/O可能会很高，但我找到了一个解决方案。

SELECT *
INTO #temp
FROM table

ALTER TABLE #temp DROP COlUMN column_name

SELECT *
FROM #temp

即使要查询所有列，也最好指定要查询的列。

因此，我建议您在语句中写下每一列的名称(不包括您不想要的列)。

SELECT
    col1
    , col2
    , col3
    , col..
    , col53

FROM table

我同意只选择*是不够的，如果你不需要，正如在其他地方提到的，是一个BLOB，你不希望有这个开销。

我会用所需的数据创建一个视图，然后您可以轻松地选择*——如果数据库软件支持它们的话。否则，将大量数据放到另一个表中。

我想添加另一个观点来解决这个问题，特别是如果你有少量的列要删除。

您可以使用像MySQL Workbench这样的DB工具来为您生成选择语句，因此您只需手动删除生成语句的那些列，并将其复制到SQL脚本中。

在MySQL Workbench中，生成它的方法是:

右键单击表->发送到Sql编辑器->选择所有语句。