Is there such thing as Custom Data Types?

Question

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.

• Derek Downey: simple answer afaik for mysql: no. This would be interesting to see how other RDBMS's handle those things
• gbn: @DTest: you have CREATE TYPE in SQL Server: useful for CLR types now. msdn.microsoft.com/en-us/library/ms175007(SQL.90).aspx
• gbn: @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
• 700 Software: @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)
• Clockwork-Muse: 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.

Answer 1

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. In Postgres one can write an extension in C for a custom datatype, such as in this example here with base36 data type.

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.

• Jack Douglas: 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...

Answer 2

Firebird also has it's solution via domains.

In Firebird, the concept of a "user-defined data type" is implemented in the form of the domain. Creating a domain does not truly create a new data type, of course. A domain provides the means to encapsulate an existing data type with a set of attributes and make this "capsule" available for multiple usage across the whole database. If several tables need columns defined with identical or nearly identical attributes, a domain makes sense.

Domain usage is not limited to column definitions for tables and views. Domains can be used to declare input and output parameters and variables in PSQL code.

• Anthony Genovese: Is Firebird MySQL?
• Tibor: No, Firebird is Firebird :) I just mentioned as nother RDMS that has such a feature.