选择MySQL表的前N列 [英] select first N columns of MySQL table

问题描述

由于可以从表中选择前N行，是否有任何方法可以从MySQL数据库表中选择前N列? 感谢您的答复，也许还感谢PHP中的部分代码.

As it is possible to select top N rows from table, is there any way to select first N columns from MySQL database tables?
Thanks for your replies and maybe some parts of code in PHP.

推荐答案

请先看看Bill Karwin的 answer .但是，如果您知道如何对列名进行排序，那么可能会有一个利用动态查询的解决方案.

Please have a look at Bill Karwin's answer first. But if you know how to order your column names there could be a solution that makes use of a dynamic query.

要从表中选择所有列名称，可以使用如下查询:

To select all column names from a table, you can use a query like this:

SELECT `column_name` 
FROM   `information_schema`.`columns` 
WHERE  `table_schema`=DATABASE() 
       AND `table_name`='yourtablename';

(请查看此 answer ).并利用 GROUP_CONCAT :

(please have a look at this answer). And making use of GROUP_CONCAT:

GROUP_CONCAT(CONCAT('`', column_name, '`') ORDER BY column_name)

我们可以在一行中返回所有列名，并用逗号分隔:

we can return all column names in a single row, separated by commas:

`col1`, `col2`, `col3`, ...

(我还在列名的周围加上了引号，请注意，我们必须以某种方式对列列表进行排序，否则不能保证列名的返回顺序).

(I also added quotes around the name of the column, and please notice that we have to order our list of columns somehow, otherwise there are no guarantees about the order in which column names are returned).

然后，我们可以使用

Then we can cut the returned string, using SUBSTRING_INDEX, in order to get, for example, the first 2 column names:

SUBSTRING_INDEX(columns, ',', 2)

和我们的最终查询，将'SELECT '，上面的选定列和' FROM Tab1'连接起来，并将结果字符串插入@sql变量中是这样的:

and our final query, that concatenates 'SELECT ', the selected columns above, and ' FROM Tab1', and inserts the resulting string into the @sql variable is this:

SELECT
  CONCAT(
    'SELECT ',
    SUBSTRING_INDEX(
      GROUP_CONCAT(CONCAT('`', column_name, '`') ORDER BY column_name),
      ',',
      2),
    ' FROM Tab1'
  )
FROM
  information_schema.columns 
WHERE
  table_schema=DATABASE() 
  AND table_name='Tab1'
INTO @sql;

它的价值将是这样的:

@sql = "SELECT `col1`, `col2` FROM Tab1"

，然后您可以准备您的陈述，然后执行:

and you can then prepare your statement, and execute it:

PREPARE stmt FROM @sql;
EXECUTE stmt;

请在此处看到小提琴.

这篇关于选择MySQL表的前N列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！