Does MySQL have any support for custom data types? For example, zip codes might be stored in a
varchar(10) field, but they could be compacted in to an
int, with options for blank, and a flag as to whether it is a 5 digit or 5+4 digit zip code.
Is there a way to install seamless data types for such things? As far as the application is concerned, it would be a string type, there would simply be data truncation (with or without warning), if the application passed invalid data.
Custom functions could be used (for example, there is a built in function
INET_ATON for IPv4 addresses. But that does not allow for such things as
zip LIKE '12345%' which should be properly indexed. A well written support for custom data types would allow a data type to be marked as sortable. So the compact
zip int, when sorted, would sort just as if it was a
This would allow for the column to be fixed width, it would allow 6 or 10 byte variable storage to be cut down to 4 byes of fixed width.
There are several applicable uses
- Zip Codes
- IPv6 addresses
- Custom timestamp fields with minute level precision and capacity beyond
2038with less storage usage than
datetime, but no need to support dates before the year of implementation (say the min could be 2007 if those are the oldest dates in the system)
- Timestamps that implement DST (which don't seem to exist)
- Two letter US state can be stored in a single byte
ENUMs can be separated into a custom data type so that
DESCRIBE's output would not be so messy looking with all the wrapping.
I expect the data type handlers would be stored similar to the way functions are stored.
Is there anything remotely like this on any database engine? I mostly use MySQL, but I am curious if this has ever been implemented, short of making the application call a function like the
MS SQL does seem to have something of that nature, but I would like to know if it is more than just a synonym. (for example
boolean could be a synonym for
postal_code for one of
10)) Synonyms are not what I am asking about here.