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.
-
While there may be a valid reason for doing this, the implication is that there's something wrong with your storage model.Strawberry– Strawberry2013-06-23 10:00:11 +00:00Commented Jun 23, 2013 at 10:00
-
@strawbery there is no problem with the storage model but i have a valid reason for lots of different query i need.parsaeed– parsaeed2013-06-23 11:23:08 +00:00Commented Jun 23, 2013 at 11:23
-
2A valid reason is simply browsing tables from the mysql prompt while coding (that's how I arrived at this page).felwithe– felwithe2017-06-02 00:27:14 +00:00Commented Jun 2, 2017 at 0:27
-
@michael back in 2013, I might have cared. I can assure you, I don't now.Strawberry– Strawberry2018-06-07 21:50:23 +00:00Commented Jun 7, 2018 at 21:50
Add a comment
|
3 Answers
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';
(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)
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;
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.
4 Comments
parsaeed
Nice and complete answer. thanks alot. i think if you add more information and add the table views of this fiddle to your answer and also add some useful comments on the codes lines, it become a nice wiki answer.
fthiella
@parsaeed thank you! you are right, i edited and added more informations to my answer, i hope everything is clear! tnx
osyan
Great job! it's like a nice learning class now. thanks alot fthiella.
Annabel
For a general case where the columns aren't in alphabetical order and you still want the 1st N columns: change the
ORDER BY column_name to ORDER BY ordinal_position.SQL requires that you name the columns you want, or else use the * wildcard.
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 *).
3 Comments
Joost
That said, you could of course artificially achieve this using PHP, and by naming the columns according to some ordered convention. But no, not with SQL.
parsaeed
Thanks Bill for your complete information, it helped me alot and i vote it up. But fthiella and Erik answer's helped me more, because of codes they shared to me and all people visiting this question later.
BClaydon
'In relational theory, there is no concept of "first N columns" because columns have no implicit order. ' Yes. This.
You cannot do this directly in MySQL, you must do this server-side. In PHP this might look like this:
<?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)
}
?>
2 Comments
parsaeed
I should try this. i think it could be accepted as answer but let me try first. and see if a better answer arrive :) just one question. would you do this for huge database? will this make me speed problem?
Erik Schierboom
@parsaeed Well it depends on how big your database is. The fact is that MySQL needs to return all columns, including the columns you won't be displaying. If it will be a performance problem remains to be seen, for that you should do some performance benchmarks.