Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Storing Enums in MySQL Database

I need the most clean and efficient solution for my case. I'm trying to store Marks of student in a MySQL DB. A student may be "ABSENT" or "COPY CASE", I want to store such info also in DB.

I thought of assigning codes for above case like -1 for ABSENT, -2 for COPY CASE, etc. This codes will be stored in Marks column only.

More ever, while reading them with a select query, I should get their display values i.e. ABSENT, COPY CASE, etc only.

Can all these be achieved at DB end only?

Or do I need to implement these things at Application level only?

Are there any API's available for Java for such functionality?

like image 771
user545647 Avatar asked Nov 26 '11 13:11

user545647


People also ask

Can we store enum 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.

Is enum good in MySQL?

ENUM is great for data that you know will fall within a static set. If you are using Mysql 5+, storage is almost always better with an ENUM type for data in a static set, as the official MySQL reference shows. Not to mention that the data is readable, and you have an extra layer of validation.

How do I store multiple enums in a database?

You can use the '|' (bitwise OR ) operator to mask multiple roles together in a single 64-bit integer, which you can store in the database in an integer field (a bigint ). RoleEnum userRoles = RoleEnum. User | RoleEnum.

How do I declare an enum in MySQL?

ENUM values are sorted based on their index numbers, which depend on the order in which the enumeration members were listed in the column specification. For example, 'b' sorts before 'a' for ENUM('b', 'a') . The empty string sorts before nonempty strings, and NULL values sort before all other enumeration values.


1 Answers

mysql supports enums:

CREATE TABLE students (name varchar(64), mark ENUM('ABSENT','COPY CASE' ));
INSERT INTO students VALUES ('john', 'COPY CASE');

In java, you can treat this column as a string type column, but in the database, values are stored efficiently, and trying to store a value not contained in the enum will result in an error.

like image 186
stew Avatar answered Oct 19 '22 18:10

stew