mysql - 在选择搜索中使用 information_schema.COLUMNS

Question

我正在尝试选择除一列之外的所有列，所以我使用的是:

select COLUMN_NAME 
from information_schema.COLUMNS 
where TABLE_NAME='report' and COLUMN_NAME != 'date';

它返回正确的结果，但是当我尝试使用它时:

select 
(
 select COLUMN_NAME 
 from information_schema.COLUMNS 
 where TABLE_NAME='report' and COLUMN_NAME != 'date'
) 
from report limit 1;

它给出错误 ERROR 1242 (21000): Subquery returns more than 1 row

我尝试对它进行 group_concat，但它只返回列(查询):

select 
(
 select group_concat(COLUMN_NAME) 
 from information_schema.COLUMNS 
 where TABLE_NAME='report' and COLUMN_NAME != 'date'
) 
from risk_assessment.report limit 1;

| (select group_concat(COLUMN_NAME) from information_schema.COLUMNS where TABLE_NAME='report' and COLUMN_NAME != 'date')                                                            |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id,ref,creator,status,username,title,site,location,comment,id,status,type,chemical_name,description,title,creator,frequency,location,form,storage,comments,comments_tick,username |

我应该改变什么？

Answer 1

MariaDB 中没有相应的语法。您不能在单个语句中执行类似 (select * except x from table) 之类的操作。

但是，您可以通过几个步骤来完成:

SET @sql = 
    CONCAT('SELECT ',
            (SELECT 
                    GROUP_CONCAT(COLUMN_NAME)
                FROM
                    INFORMATION_SCHEMA.COLUMNS
                WHERE
                    TABLE_SCHEMA = '<schema>'
                    AND TABLE_NAME = '<table>'
                        AND COLUMN_NAME NOT IN ('<column>')),
            ' FROM <table>');
PREPARE s FROM @sql;
EXECUTE s;