What's the most elegant way to retrieve a MySQL table comment?

Question

I'm trying to programmatically retrieve the comment for a MySQL table. The first method suggested to me was:

$shown = $db->query('show create table ' . TABLE_NAME)->fetch_row();
preg_match("/COMMENT='(.*)'/", $shown[0], $m);
$comment = $m[1];

But that kind of workaround makes me cringe. I stumbled upon another way:

$result = $db->query("select table_comment from information_schema.tables where table_schema = '" .
    DATABASE_NAME . "' and table_name = '" TABLE_NAME '\'')->fetch_row();
$comment = $result[0];

It's a little better (no string parsing), but it still makes me uncomfortable because I'm digging into internal structures where I don't feel like I belong.

Is there a nice, simple way to get at the table comment in code?

Answer 1

Information schema isn't really an internal structure where you don't belong. It's part of the ANSI SQL standard and its purpose is to give you a legitimate way to query metadata.

I would feel no hesitation to use it.

The one disadvantage is that MySQL's implementation of information schema tends to have pretty poor performance. So be careful about running queries against IS in routines that should be quick.