Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Which database systems support an ENUM data type, which don't?

Following up this question: "Database enums - pros and cons", I'd like to know which database systems support enumeration data types, and a bit of detail on how they do it (e.g. what is stored internally, what are the limits, query syntax implications, indexing implications, ...).

Discussion of use cases or the pros and cons should take place in the other questions.

like image 744
Tomalak Avatar asked Dec 03 '08 12:12

Tomalak


People also ask

Does SQL support enum?

Porting it to other RDBMS could be a hard task because ENUM is not an SQL's standard datatype and not many database systems provide support to it. It isn't possible to insert more values to the enumerated column.

What is enum type in database?

An ENUM is a string object with a value chosen from a list of permitted values that are enumerated explicitly in the column specification at table creation time. See Section 11.3. 1, “String Data Type Syntax” for ENUM type syntax and length limits.

Why is enum bad in MySQL?

6. You can't reuse the member-list of an ENUM column in other tables. When you create a list of possible members in an ENUM column, there's no easy and consistent way to re-use that list in other tables. With a reference table, the same set of data can be related to as many other tables as required.

How is enum stored in database?

First of all, in order to save enum values in a relational database using JPA, you don't have to do anything. By default, when an enum is a part of an entity, JPA maps its values into numbers using the ordinal() method. What it means is that without customizations JPA stores enum value as numbers.


1 Answers

I know that MySQL does support ENUM:

  • the data type is implemented as integer value with associated strings
  • you can have a maximum of 65.535 elements for a single enumeration
  • each string has a numerical equivalent, counting from 1, in the order of definition
  • the numerical value of the field is accessible via "SELECT enum_col+0"
  • in non-strict SQL mode, assigning not-in-list values does not necessarily result in an error, but rather a special error value is assigned instead, having the numerical value 0
  • sorting occurs in numerical order (e.g. order of definition), not in alphabetical order of the string equivalents
  • assignment either works via the value string or the index number
  • this: ENUM('0','1','2') should be avoided, because '0' would have integer value 1
like image 159
Tomalak Avatar answered Sep 25 '22 06:09

Tomalak