List columns for a MySQL table

There seems to be no simple way to get a list of just columns in a MySQL table through the standard DBD::mysql module, so this snippet of code uses DBI's table_info method to do just that.

Given an existing database handle and a string matching a table name, it returns a simple array of column names. No muss, no fuss.

Of course, if someone knows a Better Way™, or sees an optimization for this code, by all means fire away!

EDIT: added use of map, cred to jhourcle
EDIT2: replaced use of column_info with a query which is about 40% more efficient. props to jZed for coming up with that.

# SYNTAX: getColumnList($dbh, "TestTable")
sub getColumnList
{
  # grab aliases to a DB handle and a table name
  my($dbh,$table) = @_;
  
  my $sth = $dbh->prepare("SELECT * FROM $table WHERE 1=0;");
  $sth->execute;
  my @cols = @{$sth->{NAME}}; # or NAME_lc if needed
  $sth->finish;

  # return now complete list of columns for the given table
  return @cols;
}
[download]

Comment on List columns for a MySQL table Select or Download Code

Replies are listed 'Best First'.
Re: List columns for a MySQL table by jZed (Prior) on Jun 28, 2006 at 16:55 UTC
Personally, I'd do this instead: `my $sth = $dbh->prepare("SELECT * FROM $table WHERE 1=0"); $sth->execute; my @cols = @{$sth->{NAME_lc}}; $sth->finish;` [download]	[reply] [d/l]
Re^2: List columns for a MySQL table by EvanK (Chaplain) on Jun 28, 2006 at 17:25 UTC
that'd certainly work as well :) ~~it'll throw an error as is, though, you need a semicolon to end the SQL statement~~ apparently, my server is strangely configured or something, as this isnt the norm. __________ Build a man a fire, and he'll be warm for a day. Set a man on fire, and he'll be warm for the rest of his life. - Terry Pratchett	[reply]
Re^3: List columns for a MySQL table by jZed (Prior) on Jun 28, 2006 at 17:37 UTC
Sorry, you're wrong. AFAIK, no DBI driver requires a semicolon at the end of a SQL statement and many will throw an error if you put one in (though DBD::mysql seems to allow, but not require it). update : To quote from the DBI docs: "Some command-line SQL tools use statement terminators, like a semicolon, to indicate the end of a statement. Such terminators should not normally be used with the DBI."	[reply]
Re^4: List columns for a MySQL table by EvanK (Chaplain) on Jun 28, 2006 at 18:38 UTC
Re: List columns for a MySQL table by jhourcle (Prior) on Jun 28, 2006 at 16:32 UTC
`# loop through ref and extract only the column name my @cols; foreach my $i (0..$#{$ref}) { push(@cols, $ref->[$i]->[3]); }` [download] Any time you're taking an array, and creating another array based on values from it, you should think about using map, rather than an initialize & push in a foreach loop: `my @cols = map { $_->[3] } @$ref;`	[reply] [d/l] [select]
Re: List columns for a MySQL table by dbwiz (Curate) on Jun 29, 2006 at 13:04 UTC
If you want to get the columns from a MySQL table (not an arbitrary query), it's simpler, as shown in DBI Recipes, to use `selectcol_arrayref` combined with describe. `my $col_names = $dbh->selectcol_arrayref( qq{describe $table} );` [download]	[reply] [d/l] [select]
Re: List columns for a MySQL table by davorg (Chancellor) on Jun 29, 2006 at 13:25 UTC
Or, for a more portable approach, use the Catalogue methods that the DBI spec defines and that all DBDs should implement. Update: Ok. I see you originally used `column_info` and changed for performance reasons. If `column_info` is that much slower than raw SQL then I'd suggest that someone should raise a bug against DBD::mysql :-) -- <http://dave.org.uk> "The first rule of Perl club is you do not talk about Perl club." -- Chip Salzenberg	[reply]
Re^2: List columns for a MySQL table by EvanK (Chaplain) on Jun 30, 2006 at 18:02 UTC
i dont think it was that `column_info()` itself was slower, but the processing required on the output of it to get an array of column names probably was...this was the original snippet (with jhourcle's improvement of using `map()`): `my $sth = $dbh->column_info( undef, $db, $table, '%'); my $ref = $sth->fetchall_arrayref; my @cols = map { $_->[3] } @$ref;` [download] __________ Build a man a fire, and he'll be warm for a day. Set a man on fire, and he'll be warm for the rest of his life. - Terry Pratchett	[reply] [d/l] [select]