Is there a way to effect user defined data types in MySQL?
- by Dancrumb
I have a database which stores (among other things), the following pieces of information:
Hardware IDs BIGINTs
Storage Capacities BIGINTs
Hardware Names VARCHARs
World Wide Port Names VARCHARs
I'd like to be able to capture a more refined definition of these datatypes. For instance, the hardware IDs have no numerical significance, so I don't care how they are formatted when displayed. The Storage Capacities, however, are cardinal numbers and, at a user's request, I'd like to present them with thousands and decimal separators, e.g. 123,456.789. Thus, I'd like to refine BIGINT into, say ID_NUMBER and CARDINAL.
The same with Hardware Names, which are simple text and WWPNs, which are hexstrings, e.g. 24:68:AC:E0. Thus, I'd like to refine VARCHAR into ENGLISH_WORD and HEXSTRING.
The specific datatypes I made up are just for illustrative purposes.
I'd like to keep all this information in one place and I'm wondering if anybody knows of a good way to hold this all in my MySQL table definitions. I could use the Comment field of the table definition, but that smells fishy to me.
One approach would be to define the data structure elsewhere and use that definition to generate my CREATE TABLEs, but that would be a major rework of the code that I currently have, so I'm looking for alternatives.
Any suggestions? The application language in use is Perl, if that helps.
