Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 zip varchar(10).

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 2038 with 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
  • long 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 INET_ATON function.

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 tinyint(1), or postal_code for one of char or varchar (5 or 9 or 10)) Synonyms are not what I am asking about here.

share|improve this question
    
simple answer afaik for mysql: no. This would be interesting to see how other RDBMS's handle those things –  Derek Downey Aug 26 '11 at 16:40
    
@DTest: you have CREATE TYPE in SQL Server: useful for CLR types now. msdn.microsoft.com/en-us/library/ms175007(SQL.90).aspx –  gbn Aug 26 '11 at 16:47
1  
@George Bailey: your SQL Server link is out of date: it's for SQL Server 2000. And for MySQL see stackoverflow.com/q/2451435/27535 –  gbn Aug 26 '11 at 16:48
    
@gbn, (response to first comment) Is type translation included, will it convert for example an IPv6 into a binary(16), or is it limited to aliasing (which could be used for states becoming enums) –  George Bailey Aug 26 '11 at 16:50
1  
Please be aware that storing zip-codes as an int will not work for an internationalized database - Canada, for example, uses the format of 'A9A A9A'. Creating a custom data-type for zip-codes is probably a good idea; however, you may wish to re-examine how you are planning on dealing with some of them (storing the state in a single-byte field has potential 'other' issues, for example). And don't re-implement date/time datatypes, unless the provided ones are un-usuably deficient (size doesn't count) - you'll just confuse people. –  Clockwork-Muse Sep 7 '11 at 18:07
show 2 more comments

1 Answer 1

up vote 6 down vote accepted

Does MySQL have any support for custom data types?

Simple answer: no

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 INET_ATON function.

Oracle has CREATE TYPE which is analogous to some degree to a OO class, including features like member functions and inheritance

Postgres has CREATE TYPE which is a bit less like OO classes (no member functions or inheritance) but are incredibly flexible and useful, even allowing you to create new base types. There is also CREATE DOMAIN which allows a form of inheritance or sub-typing and basically extends a base type with some constraints. Postgres also has quite a few interesting base types by default, eg inet and geometric types

SQL Server has CREATE TYPE which allows you to create a custom data type based on an existing system data type. For example I could create a type called SSN which is basically defined as VARCHAR(11) but this way I don't have to remember how big a field it is.

share|improve this answer
1  
Please feel free to pile in and edit this answer so we can get a definitive reference. I felt the question lends itself to a CW answer... –  Jack Douglas Aug 26 '11 at 18:11
1  
Apparently I don't have the rep to make this a CW. –  mrdenny Aug 26 '11 at 18:49
1  
I made it CW when I posted it - is that not showing for you? –  Jack Douglas Aug 26 '11 at 18:52
5  
It is, I'm just stupid. It's Friday. –  mrdenny Aug 26 '11 at 19:12
5  
OK, who's the smart ass that voted up my "I'm just stupid" comment? :) –  mrdenny Aug 26 '11 at 22:05
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.