选择MySQL以外的所有列？

Select all columns except one in MySQL?

我正在尝试使用select语句来获取某个MySQL表中的所有列，除了一个。有一个简单的方法吗？

编辑：此表中有53列(不是我的设计)

实际上有一种方法，你需要有权利这样做...

1
2
3
4

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;

替换

, and

在mysql定义(手册)中没有这样的东西。但是如果你有很多列col1，...，col100，以下内容可能很有用：

1
2
3

mysql> CREATE TEMPORARY TABLE temp_tb SELECT * FROM orig_tb;
mysql> ALTER TABLE temp_tb DROP col_x;
mysql> SELECT * FROM temp_tb;

在这种情况下，View会更好吗？

1
2
3
4
5
6
7
8
9

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

你可以做：

1	SELECT column1, column2, column4 FROM table WHERE whatever

没有得到column3，虽然你可能正在寻找更通用的解决方案？

如果您要排除某个字段的值，例如对于安全问题/敏感信息，您可以将该列检索为null。

例如

1	SELECT *, NULL AS salary FROM users

据我所知，没有。你可以这样做：

1	SELECT col1, col2, col3, col4 FROM tbl

并手动选择所需的列。但是，如果你想要很多列，那么你可能只想做一个：

1	SELECT * FROM tbl

并且忽略你不想要的东西。

在您的特定情况下，我建议：

1	SELECT * FROM tbl

除非你只想要几列。如果您只想要四列，那么：

1	SELECT col3, col6, col45, col 52 FROM tbl

没问题，但是如果你想要50列，那么任何使查询的代码都会变得(太？)难以阅读。

在尝试@Mahomedalid和@Junaid的解决方案时，我发现了一个问题。所以想分享它。如果列名称具有空格或连字符(如签入)，则查询将失败。简单的解决方法是在列名称周围使用反引号。修改后的查询如下

1
2
3
4

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;

如果您不想选择的列中包含大量数据，并且由于速度问题而您不想包含它，并且您经常选择其他列，我建议您创建一个新表使用您通常不会使用原始表的键选择的一个字段，并从原始表中删除该字段。在实际需要额外字段时加入表。

您可以使用DESCRIBE my_table并使用其结果动态生成SELECT语句。

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

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

1
2
3
4
5
6
7
8
9

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') )

结果是users表中的所有列，以及从元表连接的另外两列。

我喜欢@Mahomedalid的回答，除了@Bill Karwin的评论中提供的这个事实。 @Jan Koritak引发的可能问题确实是我面对的，但我找到了一个技巧，只是想在这里分享面向问题的任何人。

我们可以用Prepared语句的子查询中的where子句替换REPLACE函数，如下所示：

使用我的表和列名称

1
2
3

SET @SQL = CONCAT('SELECT ', (SELECT GROUP_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;

因此，这将仅排除字段id而不排除company_id

希望这能帮助任何寻求解决方案的人。

问候

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

所以我建议你在声明中写下每一列的名称(不包括你不想要的那一列)。

1
2
3
4
5
6
7
8

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

FROM table

做就是了

1	SELECT * FROM table WHERE whatever

然后将列放在您最喜欢的编程语言中：php

1
2
3
4
5
6

while (($data = mysql_fetch_array($result, MYSQL_ASSOC)) !== FALSE) {
unset($data["id"]);
foreach ($data as $k => $v) {
echo"$v,";
}
}

我同意列出所有列的"简单"解决方案，但这可能是繁重的，而错别字会导致大量浪费的时间。我使用函数"getTableColumns"来检索适合粘贴到查询中的列的名称。然后，我需要做的就是删除那些我不想要的东西。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

CREATE FUNCTION `getTableColumns`(tablename varchar(100))
RETURNS varchar(5000) CHARSET latin1
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE res VARCHAR(5000) DEFAULT"";

DECLARE col VARCHAR(200);
DECLARE cur1 CURSOR FOR
select COLUMN_NAME from information_schema.columns
where TABLE_NAME=@table AND TABLE_SCHEMA="yourdatabase" ORDER BY ORDINAL_POSITION;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO col;
IF NOT done THEN
set res = CONCAT(res,IF(LENGTH(res)>0,",",""),col);
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
RETURN res;

您的结果返回逗号分隔的字符串，例如......

col1,col2,col3,col4,...col53

起初我以为你可以使用正则表达式，但是因为我一直在阅读MYSQL文档，所以你似乎不能。如果我是你，我会使用另一种语言(如PHP)来生成您想要获取的列的列表，将其存储为字符串然后使用它来生成SQL。

同意@ Mahomedalid的回答。但我不想做像预备语句这样的事情，我不想输入所有字段。
所以我所拥有的是一个愚蠢的解决方案。
转到phpmyadmin-> sql-> select中的表，它转储查询副本替换并完成！ :)

我同意Select *是不够的，如果你不需要的那个，如其他地方提到的那样，是BLOB，你不希望有那个开销蔓延。

我将创建一个包含所需数据的视图，然后您可以Select *舒适 - 如果数据库软件支持它们。否则，将大量数据放在另一个表中。

我也想要这个，所以我创建了一个函数。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

public function getColsExcept($table,$remove){
$res =mysql_query("SHOW COLUMNS FROM $table");

while($arr = mysql_fetch_assoc($res)){
$cols[] = $arr['Field'];
}
if(is_array($remove)){
$newCols = array_diff($cols,$remove);
return"`".implode("`,`",$newCols)."`";
}else{
$length = count($cols);
for($i=0;$i<$length;$i++){
if($cols[$i] == $remove)
unset($cols[$i]);
}
return"`".implode("`,`",$cols)."`";
}
}

所以它的工作原理是你输入表格，然后输入你不想要的列或数组：array("id"，"name"，"whatevercolumn")

所以在select中你可以像这样使用它：

1	mysql_query("SELECT".$db->getColsExcept('table',array('id','bigtextcolumn'))." FROM table");

要么

1	mysql_query("SELECT".$db->getColsExcept('table','bigtextcolumn')." FROM table");

是的，虽然它可能是高I / O，这取决于表，这是我找到的解决方法。

1
2
3
4
5
6
7
8

SELECT *
INTO #temp
FROM table

ALTER TABLE #temp DROP COlUMN column_name

SELECT *
FROM #temp

也许我有一个解决Jan Koritak指出的差异的解决方案

1
2
3
4
5
6
7
8
9
10
11
12
13

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' );

表：

1
2
3

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

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

1
2
3
4

table_name column_name
employee eid
employee name_eid
employee sal

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

查询结果：

1	'SELECT name_eid,sal FROM employee'

虽然我同意托马斯的答案(+1;))，但我想补充一点，我会假设你不想要的专栏几乎没有任何数据。如果它包含大量文本，xml或二进制blob，则请花时间单独选择每个列。否则你的表现会受到影响。干杯!

根据@Mahomedalid的回答，我做了一些改进，以支持"选择除mysql中的一些列以外的所有列"

1
2
3
4
5
6
7
8
9
10
11
12
13
14

SET @database = 'database_name';
SET @tablename = 'table_name';
SET @cols2delete = 'col1,col2,col3';

SET @sql = CONCAT(
'SELECT ',
(
SELECT GROUP_CONCAT( IF(FIND_IN_SET(COLUMN_NAME, @cols2delete), NULL, COLUMN_NAME ) )
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tablename AND TABLE_SCHEMA = @database
),
' FROM ',
@tablename);

SELECT @sql;

如果你有很多cols，使用这个sql来改变group_concat_max_len

1	SET @@group_concat_max_len = 2048;

Mahomedalid发布的答案有一个小问题：

内部替换功能代码将","替换为"，如果要替换的字段是concat字符串中的最后一个，因为最后一个没有char逗号"，并且不是从字符串中删除。

我的建议：

1
2
3
4
5

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

替换

，和`

在我的情况下，删除的列被字符串"FIELD_REMOVED"替换，因为我试图安全记忆。 (我删除的字段是大约1MB的BLOB)

如果您愿意，可以使用SQL生成SQL并评估它生成的SQL。这是一种通用解决方案，因为它从信息模式中提取列名称。这是Unix命令行的一个例子。

代

用你的mysql命令MYSQL
带表名的表
带有排除字段名称的EXCLUDEDFIELD

1	echo $(echo 'select concat("select", group_concat(column_name) ," from TABLE") from information_schema.columns where table_name="TABLE" and column_name !="EXCLUDEDFIELD" group by"t"' \| MYSQL \| tail -n 1) \| MYSQL

实际上，您只需要以这种方式提取列名称一次以构造排除该列的列列表，然后只使用您构造的查询。

所以类似于：

1	column_list=$(echo 'select group_concat(column_name) from information_schema.columns where table_name="TABLE" and column_name !="EXCLUDEDFIELD" group by"t"' \| MYSQL \| tail -n 1)

现在，您可以在构造的查询中重用$column_list字符串。

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

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

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

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

如果它始终是相同的一列，那么您可以创建一个没有它的视图。

否则，不，我不这么认为。

我有一个建议，但没有解决方案。
如果您的某些列具有更大的数据集，那么您应该尝试使用以下内容

1	SELECT *, LEFT(col1, 0) AS col1, LEFT(col2, 0) as col2 FROM table

接受的答案有几个缺点。

它在表或列名称需要反引号的地方失败
如果要省略的列在列表中的最后一列，则会失败
它需要列出两次表名(一次用于select，另一次用于查询文本)，这是多余的和不必要的
它可能以错误的顺序返回列名

所有这些问题都可以通过简单地在SEPARATOR中为GROUP_CONCAT包含反引号并使用WHERE条件而不是REPLACE()来解决。为了我的目的(我想象很多其他人)我希望返回的列名与它们在表本身中出现的顺序相同。为此，我们在GROUP_CONCAT()函数中使用了一个显式的ORDER BY子句：

1
2
3
4
5
6
7
8
9
10
11
12
13

SELECT CONCAT(
'SELECT `',
GROUP_CONCAT(COLUMN_NAME ORDER BY `ORDINAL_POSITION` SEPARATOR '`,`'),
'` FROM `',
`TABLE_SCHEMA`,
'`.`',
TABLE_NAME,
'`;'
)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE `TABLE_SCHEMA` = 'my_database'
AND `TABLE_NAME` = 'my_table'
AND `COLUMN_NAME` != 'column_to_omit';

我很晚才找到答案，这就是我一直这样做的方式，坦率地说，它比最好的答案好100倍，我只希望有人能看到它。并发现它很有用

1
2
3
4
5
6
7
8
9
10
11
12

//create an array, we will call it here.
$here = array();
//create an SQL query in order to get all of the column names
$SQL ="SHOW COLUMNS FROM Table";
//put all of the column names in the array
foreach($conn->query($SQL) as $row) {
$here[] = $row[0];
}
//now search through the array containing the column names for the name of the column, in this case i used the common ID field as an example
$key = array_search('ID', $here);
//now delete the entry
unset($here[$key]);

Select *是一个SQL反模式。它不应该在生产代码中使用，原因有很多，包括：

处理需要更长的时间。当事情运行数百万次时，那些微小的位可能很重要。一个缓慢的数据库，其中由这种类型的草率编码引起的缓慢是最难的性能调整。

这意味着您可能发送的数据超出了您的需求，从而导致服务器和网络瓶颈。如果您有内部联接，则发送超出您需要的数据的可能性为100％。

它会导致维护问题，尤其是当您添加了不希望在任何地方看到的新列时。此外，如果您有新列，则可能需要对界面执行某些操作以确定如何处理该列。

它可以打破视图(我知道这在SQl服务器中是正确的，它可能在mysql中也可能不是真的)。

如果有人愚蠢地用不同的顺序重建表格(你不应该这样做，但它会发生在所有时间)，各种代码都会破坏。特别是插入代码，例如突然你将城市放入address_3字段，因为没有指定，数据库只能按列的顺序排列。这在数据类型发生变化时已经足够糟糕了，但是当交换列具有相同的数据类型时更糟糕，因为您可能会在某些时候插入糟糕的数据来清理。您需要关心数据完整性。

如果在插入中使用它，如果在一个表中添加新列而不在另一个表中添加新列，则它将破坏插入。

它可能会破坏触发器。触发问题可能难以诊断。

将所有这些添加到列名称中添加所需的时间(哎呀，你甚至可能有一个允许你拖动列名称的界面(我知道我在SQL Server中做的，我敢打赌有一些方法可以这是你用来编写mysql查询的一些工具。)让我们看看，"我可能会导致维护问题，我可能会导致性能问题而且我可能会导致数据完整性问题，但是嘿，我节省了五分钟的开发时间。"真的只是放了在您想要的特定列中。

我还建议你读这本书：