我试图使用一个选择语句从某个MySQL表中获得除一个以外的所有列。有什么简单的方法吗?
编辑:在这个表格中有53列(不是我的设计)
我试图使用一个选择语句从某个MySQL表中获得除一个以外的所有列。有什么简单的方法吗?
编辑:在这个表格中有53列(不是我的设计)
当前回答
也许我有一个解决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,尽管您可能在寻找一个更一般的解?
也许我有一个解决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
col1
, col2
, col3
, col..
, col53
FROM table
我使用这个工作,尽管它可能是“离题”-使用mysql工作台和查询生成器-
打开列视图 Shift选择所有你想在你的查询列(在你的情况下,所有但这是我所做的) 右键单击并选择发送到SQL编辑器->名称短。 现在你有了列表,然后你可以复制粘贴查询到任何地方。
我很晚才想出一个答案,坦率地说,这是我一直在做的事情,它比最好的答案要好100倍,我只希望有人能看到它。发现它很有用
//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]);