13

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.

  • 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) – Bryan Field 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
13

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.

  • 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
0

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.

  • Is Firebird MySQL? – Anthony Genovese Aug 7 at 19:08
  • No, Firebird is Firebird :) I just mentioned as nother RDMS that has such a feature. – Tibor Aug 25 at 3:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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