选择 MySQL 表的前 N 列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17258970/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
select first N columns of MySQL table
提问by parsaeed
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.
由于可以从表中选择前 N 行，有没有办法从 MySQL 数据库表中选择前 N 列？
感谢您的回复以及 PHP 中的某些代码部分。
回答by Bill Karwin
SQL requires that you name the columns you want, or else use the *wildcard.
SQL 要求您命名所需的列，否则使用*通配符。
In relational theory, there is no concept of "first N columns" because columns have no implicit order. Of course in any concrete implementation of SQL, they must have some storage order, but the SQL language doesn't have any support for fetching columns by "position" in the table, nor is there any support for fetching sequences of columns (except for *).
在关系理论中，没有“前 N 列”的概念，因为列没有隐含的顺序。当然在SQL的任何具体实现中，它们都必须有一定的存储顺序，但是SQL语言不支持按表中的“位置”取列，也不支持取列的序列（除了*）。
回答by fthiella
Please have a look at Bill Karwin's answerfirst. But if you know how to order your column names there could be a solution that makes use of a dynamic query.
请先看看Bill Karwin 的回答。但是，如果您知道如何对列名进行排序，那么可能有一种使用动态查询的解决方案。
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';
(please have a look at this answer). And making use of GROUP_CONCAT:
（请看一下这个答案）。并利用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剪切返回的字符串，以获得前 2 个列名：
SUBSTRING_INDEX(columns, ',', 2)
and our final query, that concatenates 'SELECT ', the selected columns above, and ' FROM Tab1', and inserts the resulting string into the @sqlvariable is this:
和我们的最终查询，连接'SELECT '，上面选定的列和' FROM Tab1'，并将结果字符串插入到@sql变量中是这样的：
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;
It's value will be something like this:
它的价值将是这样的：
@sql = "SELECT `col1`, `col2` FROM Tab1"
and you can then prepare your statement, and execute it:
然后你可以准备你的语句，并执行它：
PREPARE stmt FROM @sql;
EXECUTE stmt;
Please see fiddle here.
请在此处查看小提琴。
回答by Erik Schierboom
You cannot do this directly in MySQL, you must do this server-side. In PHP this might look like this:
您不能直接在 MySQL 中执行此操作，必须在服务器端执行此操作。在 PHP 中，这可能如下所示：
<?php
$mysqli->real_query("SELECT id, title, name FROM test ORDER BY id ASC");
$res = $mysqli->use_result();
$numberOfColumnsToShow = 2;
while ($row = $res->fetch_assoc()) {
// Only select the first $numberOfColumnsToShow columns
$rowWithSpecifiedNumberOfColumns = array_slice($row, 0, $numberOfColumnsToShow);
// $rowWithSpecifiedNumberOfColumns only contains the first two columns (id, title)
}
?>
