我试图使用一个选择语句从某个MySQL表中获得除一个以外的所有列。有什么简单的方法吗?
编辑:在这个表格中有53列(不是我的设计)
当前回答
(不要在大桌子上尝试，结果可能是……令人惊讶的!)
临时表
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语法可能因数据库而异
其他回答
也许我有一个解决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 column1, column2, column4 FROM table WHERE whatever
没有得到列3，尽管您可能在寻找一个更一般的解?
(不要在大桌子上尝试，结果可能是……令人惊讶的!)
临时表
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语法可能因数据库而异
这个问题是关于MySQL的，但我仍然认为值得一提的是，至少谷歌BigQuery和H2原生支持* EXCEPT语法。
SELECT * FROM actor
生产:
|actor_id|first_name|last_name |last_update |
|--------|----------|------------|-----------------------|
|1 |PENELOPE |GUINESS |2006-02-15 04:34:33.000|
|2 |NICK |WAHLBERG |2006-02-15 04:34:33.000|
|3 |ED |CHASE |2006-02-15 04:34:33.000|
而
SELECT * EXCEPT (last_update) FROM actor
生产:
|actor_id|first_name|last_name |
|--------|----------|------------|
|1 |PENELOPE |GUINESS |
|2 |NICK |WAHLBERG |
|3 |ED |CHASE |
也许，MySQL的未来版本也会支持这种语法?
如果你使用MySQL工作台，你可以右键单击你的表，点击发送到sql编辑器，然后选择所有语句，这将创建一个所有字段都被列出的语句，如下所示:
SELECT `purchase_history`.`id`,
`purchase_history`.`user_id`,
`purchase_history`.`deleted_at`
FROM `fs_normal_run_2`.`purchase_history`;
SELECT * FROM fs_normal_run_2.purchase_history;
现在你可以删除那些你不想要的。
