我试图使用一个选择语句从某个MySQL表中获得除一个以外的所有列。有什么简单的方法吗?
编辑:在这个表格中有53列(不是我的设计)
我试图使用一个选择语句从某个MySQL表中获得除一个以外的所有列。有什么简单的方法吗?
编辑:在这个表格中有53列(不是我的设计)
当前回答
在尝试@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;
其他回答
基于@Mahomedalid的答案,我做了一些改进,以支持“选择mysql中除某些列外的所有列”
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
SET @@group_concat_max_len = 2048;
(不要在大桌子上尝试,结果可能是……令人惊讶的!)
临时表
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语法可能因数据库而异
如果愿意,可以使用SQL生成SQL,并对生成的SQL进行评估。这是一种通用的解决方案,因为它从信息模式中提取列名。下面是一个Unix命令行的示例。
替换
MYSQL的MYSQL命令 带有表名的TABLE 包含排除字段名的EXCLUDEDFIELD
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
实际上,您只需要以这种方式提取列名一次,就可以构造排除该列的列列表,然后只需使用已构造的查询。
比如:
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字符串。
也许我有一个解决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'
只做
SELECT * FROM table WHERE whatever
然后用您最喜欢的编程语言php删除该列
while (($data = mysql_fetch_array($result, MYSQL_ASSOC)) !== FALSE) {
unset($data["id"]);
foreach ($data as $k => $v) {
echo"$v,";
}
}