在 MySQL 中选择除一列之外的所有列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9122/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select all columns except one in MySQL?
提问by Tom Grochowicz
I'm trying to use a select statement to get all of the columns from a certain MySQL table except one. Is there a simple way to do this?
我正在尝试使用 select 语句从某个 MySQL 表中获取除一个之外的所有列。有没有一种简单的方法可以做到这一点？
EDIT: There are 53 columns in this table (NOT MY DESIGN)
编辑：此表中有 53 列（不是我的设计）
采纳答案by Mahomedalid
Actually there is a way, you need to have permissions of course for doing this ...
实际上有一种方法，您当然需要拥有权限才能执行此操作...
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;
Replacing <table>, <database> and <columns_to_omit>
更换 <table>, <database> and <columns_to_omit>
回答by ghionoiu
In mysql definitions (manual) there is no such thing. But if you have a really big number of columns col1, ..., col100, the following can be useful:
在 mysql 定义（手册）中没有这样的事情。但是，如果您有大量的列col1, ..., col100，则以下内容可能很有用：
DROP TABLE IF EXISTS temp_tb;
CREATE TEMPORARY TABLE ENGINE=MEMORY temp_tb SELECT * FROM orig_tb;
ALTER TABLE temp_tb DROP col_x;
SELECT * FROM temp_tb;
回答by Brian Childress
Would a View work better in this case?
在这种情况下，视图会更好吗？
CREATE VIEW vwTable
as
SELECT
col1
, col2
, col3
, col..
, col53
FROM table
回答by Mike Stone
You can do:
你可以做：
SELECT column1, column2, column4 FROM table WHERE whatever
without getting column3, though perhaps you were looking for a more general solution?
没有获得第 3 列，但也许您正在寻找更通用的解决方案？
回答by Sean O
If you are looking to exclude the value of a field, e.g. for security concerns / sensitive info, you can retrieve that column as null.
如果您希望排除某个字段的值，例如出于安全考虑/敏感信息，您可以将该列检索为空值。
e.g.
例如
SELECT *, NULL AS salary FROM users
回答by Thomas Owens
To the best of my knowledge, there isn't. You can do something like:
据我所知，没有。您可以执行以下操作：
SELECT col1, col2, col3, col4 FROM tbl
and manually choose the columns you want. However, if you want a lot of columns, then you might just want to do a:
并手动选择所需的列。但是，如果您想要很多列，那么您可能只想执行以下操作：
SELECT * FROM tbl
and just ignore what you don't want.
忽略你不想要的。
In your particular case, I would suggest:
在您的特定情况下，我建议：
SELECT * FROM tbl
unless you only want a few columns. If you only want four columns, then:
除非你只想要几列。如果你只想要四列，那么：
SELECT col3, col6, col45, col 52 FROM tbl
would be fine, but if you want 50 columns, then any code that makes the query would become (too?) difficult to read.
会很好，但是如果你想要 50 列，那么任何使查询变得（太？）难以阅读的代码。
回答by Suraj
While trying the solutions by @Mahomedalid and @Junaid I found a problem. So thought of sharing it. If the column name is having spaces or hyphens like check-in then the query will fail. The simple workaround is to use backtick around column names. The modified query is below
在尝试@Mahomedalid 和@Junaid 的解决方案时，我发现了一个问题。于是想到了分享。如果列名包含空格或连字符，如签入，则查询将失败。简单的解决方法是在列名周围使用反引号。修改后的查询如下
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;
回答by Stacey Richards
If the column that you didn't want to select had a massive amount of data in it, and you didn't want to include it due to speed issues and you select the other columns often, I would suggest that you create a new table with the one field that you don't usually select with a key to the original table and remove the field from the original table. Join the tables when that extra field is actually required.
如果您不想选择的列中有大量数据，并且由于速度问题而不想包含它并且您经常选择其他列，我建议您创建一个新表使用您通常不使用原始表的键选择的一个字段，然后从原始表中删除该字段。当实际需要额外的字段时加入表。
回答by jammycakes
You could use DESCRIBE my_tableand use the results of that to generate the SELECT statement dynamically.
您可以使用DESCRIBE my_table并使用其结果动态生成 SELECT 语句。
回答by cwd
My main problem is the many columns I get when joining tables. While this is not the answer to your question (how to select all but certain columns from onetable), I think it is worth mentioning that you can specify table.to get all columns from a particular table, instead of just specifying .
我的主要问题是连接表时得到的许多列。虽然这不是您问题的答案（如何从一个表中选择除某些列之外的所有列），但我认为值得一提的是，您可以指定从特定表中获取所有列，而不仅仅是指定.table.
Here is an example of how this could be very useful:
这是一个如何非常有用的示例：
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') )
The result is all the columns from the users table, and two additional columns which were joined from the meta table.
结果是来自 users 表的所有列，以及从元表连接的两个附加列。
