Xaprb

Stay curious!

SHOW COLUMN TYPES: A hidden MySQL treat?

with 4 comments

I was poking around in the MySQL source code, namely in sql/sql_yacc.yy, and found a hidden treat: there’s a SHOW COLUMN TYPES command. It’s fun to read source code!

The command is undocumented, but does work. It returns just two rows, which give information about TINYINT and TINYINT UNSIGNED. And it has been in the source for a long time, certainly since version 4.1.21. There’s even a status counter for it in the SHOW STATUS command: Com_show_column_types.

It’s clearly unfinished (here’s a bug report about it), and I think I can guess why: it’s just duplicate information that, like code comments, can get out of date and be a pain to maintain. If it were me, I’d yank it out of the source, and fix up the online help instead, which on version 5.0.24a-log says pretty much nothing helpful:

mysql> help column types;
Name: 'Column Types'
Description:
AUTO_INCREMENTExamples:
N

Another possibility would be to put it in the INFORMATION_SCHEMA instead, but I don’t think the SQL standard mentions such information being there. I could be wrong about this, but I don’t see it. Can anyone confirm this? It strikes me as an odd omission, since there are other views that seem less essential, such as DATA_TYPE_PRIVILEGES, COLLATIONS, and USER_DEFINED_TYPES.

Written by Xaprb

September 19th, 2006 at 9:04 pm

Posted in Uncategorized

«
»

4 Responses to 'SHOW COLUMN TYPES: A hidden MySQL treat?'

Subscribe to comments with RSS

  1. Nice one!

    Did not know this feature existed.

    As for the information schema: I think the standard does not define a separate view for data types because it silently assumes existence of the standard datatypes.

    I know SQL server does expose this information in the systypes (or sysdatatypes, can’t remember exactly which one) system table.

    I actually filed a feature request to add this information to the information schema (http://bugs.mysql.com/bug.php?id=11665) but it was bumped. I still think it would be incredibly useful (for GUI tools and such): the fact that it is not defined in the standard cannot be the reason to not expose the information.

    (There are already proprietary additions to the MySQL information schema, and the the SQL standard has a habit of following practice rather than heading it)

    Roland Bouman

    20 Sep 06 at 4:12 am

  2. Xaprb,

    Great find man. I didn’t know either.

    Frank

    Frank Mash

    20 Sep 06 at 10:29 am

  3. hm, 1st works, but 2nd doesn’t:

    root:test> help column types;
Nothing found
Please try to run 'help contents' for a list of all accessible topics

    Server version: 5.0.24a-standard-log

    Dmitry

    20 Sep 06 at 3:01 pm

  4. wow!!! Great stuff.

    sheeri

    21 Sep 06 at 9:56 pm

Leave a Reply