我试图使用一个选择语句从某个MySQL表中获得除一个以外的所有列。有什么简单的方法吗?

编辑:在这个表格中有53列(不是我的设计)


当前回答

我同意列出所有列的“简单”解决方案,但这可能会造成负担,而且打字错误可能会浪费大量时间。我使用“getTableColumns”函数检索适合粘贴到查询中的列的名称。然后我要做的就是删除我不想要的。

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

其他回答

你可以:

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'

我同意列出所有列的“简单”解决方案,但这可能会造成负担,而且打字错误可能会浪费大量时间。我使用“getTableColumns”函数检索适合粘贴到查询中的列的名称。然后我要做的就是删除我不想要的。

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

我同意@Mahomedalid的回答,但我不想做一些准备好的语句,我不想输入所有的字段,所以我有一个愚蠢的解决方案。

去phpmyadmin->sql->select表,它转储查询:复制,替换和完成!:)

如果你想排除一个字段的值,例如安全问题/敏感信息,你可以检索该列为空。

e.g.

SELECT *, NULL AS salary FROM users