在MySQL中选择除一列之外的所有列？

Question

我正在尝试使用select语句获取某个MySQL表中除了某一列之外的所有列。有没有简单的方法可以做到这一点？

编辑：该表中有53个列（不是我设计的）。

- Tom

---

53个列？在这种情况下，我会像Thomas建议的那样坚持使用SELECT *...除非额外的列有大量数据需要检索，这可能不是我们想要的。 - Mike Stone

除了一列...我想你知道应该忽略哪一列，因此INFORMATION_SCHEMA.columns是正确的方式。 - Alfabravo

常见用途是排除自动递增的ID列。例如，选择要插入到另一个具有自己ID的表中的数据。 - ToolmakerSteve

常见的用法是在检索用户信息时排除密码哈希值。 - Gianluca Ghettini

老实说，没有简单的方法来做这件事情，这相当荒谬。 - Enerccio

Answer 1

实际上有一种方法，当然你需要有相应的权限来执行此操作...

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;

替换<table>、<database>和<columns_to_omit>

- Mahomedalid

---

注意：INFORMATION_SCHEMA查询性能相当差，因此请小心，确保这种类型的查询不会成为任何关键路径上的内容。 - Bill Karwin

就像@Jan Koritak在下面所说的，如果您想要删除的标题列也是您希望保留的任何列的子字符串，则此答案实际上不起作用。这里有一个更好的答案与此类似，可以在此处找到。 - donL

这远不如指定列名好，指定列名是一种已知的最佳实践。 - HLGEM

如果列名中包含空格，这种方法就行不通。应该更新为始终使用反引号<column_name>来围绕列名。 - adamF

如果要忽略的列是表结构中列的最后一列（因为替换不会匹配尾随逗号），则此方法也无效。 - Vincent Pazeller

Answer 2

（不要在大表上尝试此操作，结果可能会令人惊讶！）

临时表

DROP TABLE IF EXISTS temp_tb;
CREATE TEMPORARY TABLE ENGINE=MEMORY temp_tb SELECT * FROM orig_tb;
ALTER TABLE temp_tb DROP col_a, DROP col_f,DROP col_z;    #// MySQL
SELECT * FROM temp_tb;

DROP语法可能因数据库而异。@Denis Rozhnev

- bortunac

---

第三步给了我错误信息：“在第一行，列计数与值计数不匹配”。于是我将第二步更改为“UPDATE temp_tb SET id = NULL”，然后它就起作用了。 - oyvey

好的，这个可以运行。但是当我再次运行这个查询时，它会给我一个错误，即temp_tb已经存在。temp_tb在内存中存在多长时间？如果这是一个愚蠢的问题，请原谅。附言：我已经为你的答案点赞了。 - Karan

如需重复运行此查询，请在开头添加另一条命令：DROP TABLE IF EXISTS temp_tb;。该命令的作用是删除名为 "temp_tb" 的数据表，如果该表存在的话。请注意，这个命令会导致该表及其所有数据被永久性地删除。 - gregn3

@Karan 要指定内存引擎，请使用命令：CREATE TEMPORARY TABLE temp_tb ENGINE=MEMORY (SELECT * FROM orig_tb);，否则默认保存到磁盘，并且可以在服务器重启后继续存在。(感谢) - gregn3

非常好的答案。如果要删除多列，请参考此答案。 - S3DEV

嘿，数据库，将所有数据复制到内存中，这样我就可以读取其中的一个东西了。 - MuhsinFatih

Answer 3

在这种情况下，使用视图是否更好？

CREATE VIEW vwTable
as  
SELECT  
    col1  
    , col2  
    , col3  
    , col..  
    , col53  
FROM table

- Brian Childress

---

哈哈！是的，当然。现在你该如何构建视图以包括除一个列之外的所有列呢？我想你能看出这个问题原本存在的困难了。实际上，我找到了这个帖子，特别是因为我想创建一个视图来排除某些列，而不被强制在视图定义中明确列出所有剩余的列。 - Chris Nadovich

@ChrisNadovich 但是使用View，你只需要列出一次。 - João Pimentel Ferreira

@JoãoPimentelFerreira 我正在构建越来越复杂的视图（在这些视图中，我可能会替换简单视图中的值）-- 这使得我必须在每个地方都明确表达。 - undefined

Answer 4

您可以做：

SELECT column1, column2, column4 FROM table WHERE whatever

如果你正在寻找更一般的解决方案，那么可以不获取column3。

- Mike Stone

---

大部分评分较高的答案基本上只是找到了一些生成这个查询的方法，而不需要手动输入。 - krethika

为了维护的目的，拥有一个“除了...之外”的查询方式非常有用。否则，如果以后添加了新字段，则必须更新显式字段列表。 - leiavoia

@lev，你说得对，而且它们应该是这样的！因为你不知道是否想要有任何未来的列（它们可能是元数据列或不适用于特定屏幕的列）。你不希望通过返回多余的内容来损害性能（当你有内部连接时，这是100％正确的）。我建议你阅读一些关于为什么选择*是SQL反模式的文章。 - HLGEM

原文：The OP states there are >50 columns so this is rather impractical. 翻译：发帖者表示有超过50列，这样做相当不切实际。 - augurar

@HLGEM，其实不是这样的，我想选择所有列，但每次添加新列时都不需要维护查询，除了其中一个列，而在我的情况下这种方法并不适用。但无论如何，我已经采用了Sean O的解决方案。 - OverCoder

Answer 5

如果您想要排除字段的值，例如出于安全考虑/敏感信息，您可以将该列检索为空。

例如：

SELECT *, NULL AS salary FROM users

- Sean O

---

为什么？它不起作用。如果您有一个名为“薪水”的列，那么这个查询最终会得到两个名为“薪水”的列的结果，一个充满空值，另一个包含实际的薪水。 - Myforwik

这个查询确实添加了第二个 salary 列。但由于它是在 * 后检索的，因此覆盖了原始列。虽然这不太美观，但它确实有效。 - Sean O

SQL允许结果集中存在重复的列名。如果您想要使其工作，需要使用不支持重复并将优先权赋予最后一个重复项的客户端应用来运行它。官方命令行客户端支持重复项，HeidiSQL也支持。然而，SQL Fiddle不支持重复项，并且显示第一个重复项，而非最后一个。总之：这无法正常工作。 - Álvaro González

当然它不起作用。这是一个很好的例子，说明你应该在mysql本身中测试你的答案，而不是通过与mysql交互的库来测试。 - Myforwik

@SeanO，@Alastair，@所有人，不要覆盖。服务器仍然返回敏感数据。 - Pacerier

Answer 6

据我所知，目前没有。您可以尝试以下方法：

SELECT col1, col2, col3, col4 FROM tbl

并手动选择您想要的列。但是，如果您需要许多列，则可能只需要执行以下操作：

SELECT * FROM tbl 

忽略您不想要的部分。

对于您的特定情况，我建议：

SELECT * FROM tbl

除非你只需要几列数据。如果你只需要四列，那么：

SELECT col3, col6, col45, col 52 FROM tbl

如果只需要50个列，那么使用现有的代码就足够了，但是如果你希望查询结果有50列，那么生成这样一个查询的代码可能会变得（太）难以阅读。

- Thomas Owens

---

SELECT * 总是一个很差的选择，不建议使用它。请阅读为什么它是 SQL 反模式的原因。 - HLGEM

Answer 7

在尝试 @Mahomedalid 和 @Junaid 的解决方案时，我发现了一个问题。所以想要分享一下。如果列名中有空格或连字符，例如“check-in”，则查询将失败。简单的解决方法是在列名周围使用反引号。修改后的查询如下：

SET @SQL = CONCAT('SELECT ', (SELECT GROUP_CONCAT(CONCAT("`", COLUMN_NAME, "`")) FROM
INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'users' AND COLUMN_NAME NOT IN ('id')), ' FROM users');
PREPARE stmt1 FROM @SQL;
EXECUTE stmt1;

- Suraj

Answer 8

如果你不想选择的那一列包含了大量数据，并且由于速度问题，你不想将其包含进来，而你经常选择其他列，那么我建议你创建一个新表，只包含那个你通常不选择的字段，并且加上原始表的键值，然后从原始表中删除该字段。当需要额外字段时，请连接这些表。

- Stacey Richards

Answer 9

您可以使用DESCRIBE my_table命令，然后根据结果动态生成SELECT语句。

- jammycakes

Answer 10

我的主要问题是在连接表时得到的许多列。虽然这不是回答您的问题（如何从一个表中选择除了某些列之外的所有内容），但我认为值得提到的是，您可以指定table.来获取特定表中的所有列，而不仅仅是指定。

以下是一个非常有用的示例：

select users.*, phone.meta_value as phone, zipcode.meta_value as zipcode

from users

left join user_meta as phone
on ( (users.user_id = phone.user_id) AND (phone.meta_key = 'phone') )

left join user_meta as zipcode
on ( (users.user_id = zipcode.user_id) AND (zipcode.meta_key = 'zipcode') )

结果是来自用户表的所有列和两个从元数据表中连接的附加列。

- cwd

---

谢谢，我需要在连接中选择第一个表的所有列和第二个表的一个字段，你的答案帮了我很多。 - mohammad falahat