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.
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)
Xaprb,
Great find man. I didn’t know either.
Frank
hm, 1st works, but 2nd doesn’t:
Server version: 5.0.24a-standard-log
wow!!! Great stuff.