Mysql – Is there such thing as Custom Data Types

datatypesMySQLtype conversion

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.

Best Answer

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.

Related Question