选择以特定字母开头的特定列类型，然后一起提取数据 [英] Select a particular type of columns that begin with a certain letter and fetch data all together

问题描述

我试图在选择特定类型的列的同时获取数据
我正在做的是:

I am trying to fetch data at the same time I select specific type of columns
what I`m doing is:

 SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = 'table'
        AND table_schema = 'DB'
        AND column_name LIKE 'f%'

此查询将给我列名，是否可以选择同时获取那些列的数据?还是我需要cols的数组并进行另一个查询?假设我要获取所有数据，没关系.
谢谢.

this query will give me the col names, there is option to fetch data of those colum at the same time? or I need to take the array of the cols and make another query? let say I want to fetch all the data, it doesnt matter.
thanks.

推荐答案

您可以为此使用存储过程以及动态sql.

You can use a stored procedure for this, with dynamic sql.

首先，您必须创建此存储过程:

first, you must create this stored procedure:

CREATE PROCEDURE FetchColumns (IN pSchemaName char(50), IN pTableName char(50), IN     pColumnLikeCondition char(50))
BEGIN
  DECLARE v_cols char(200);
  SELECT group_concat(COLUMN_NAME separator ' , ') INTO v_cols FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = pTableName
       AND table_schema = pSchemaName
       AND column_name LIKE pColumnLikeCondition;

  SET @s = CONCAT('SELECT ',v_cols,' FROM ',pTableName );
  PREPARE stmt FROM @s;
  EXECUTE stmt;
END

最后，您要做的就是调用它，就像这样:

and finally, all you have to do is calling it, like this:

call FetchColumns('DB', 'table', 'f%')

这篇关于选择以特定字母开头的特定列类型，然后一起提取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！