Why ENUM does not store multiple values in MySQL?

Question

I want to use ENUM feature in table using MySQL.

I have created a table tbl_test having id as primary key and enum_col field as ENUM data type.

CREATE TABLE tbl_test(
id INT NOT NULL AUTO_INCREMENT,
enum_col ENUM('a','b','c') NOT NULL,
PRIMARY KEY ( id )
);

When I try to store single enum value, it got inserted but when I try to store multiple enum values then it throws SQL error.

ERROR:

 Data truncated for column 'enum_col' at row 1

Single ENUM value (CORRECT):

INSERT INTO tbl_test(id, enum_col) values(1, 'a');

Multiple ENUM values (FAILED):

INSERT INTO tbl_test(id, enum_col) values(2, 'a,b');

Any idea to store multiple values in ENUM data type ?

ENUM is a value type, not a set or array type. Instead of storing arrays of values, it would be better to normalize your data so that all the normal querying and integrity features in MySQL can be used. — reaanb
– reaanb, Commented Jul 11, 2016 at 11:03

oakymax · Accepted Answer · 2016-07-11 11:00:50Z

4

You should use SET data type instead of ENUM if you want to store multiple values

http://dev.mysql.com/doc/refman/5.7/en/set.html

answered Jul 11, 2016 at 11:00

oakymax

1,4741 gold badge14 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ravindra Singh Over a year ago

It's look nice solution.

Saltern Over a year ago

i changed enum to set - i have problem with create and edit page ! create not saved and edit doesn't show data from database !

oakymax Over a year ago

@Saltern sounds a bit confusing ) I trying to imaging a kind of problem that You faced with, but there is evidently not enough details in Your comment...

juergen d · Accepted Answer · 2016-07-11 11:16:23Z

3

That is because you can only store one value in it and in fact you absolutely should store only one value in whatever type of column.

Use a seperate table. Then you can store as much values as you like with multiple records. Example:

tbl_test
--------
id   |  name
1    |  test_X
2    |  test_Y
3    |  test_Z


tbl_test_enums
--------------
test_id  | enum_value
1        | a
1        | b
2        | a
3        | c

edited Jul 11, 2016 at 11:16

answered Jul 11, 2016 at 10:59

juergen d

205k40 gold badges304 silver badges377 bronze badges

3 Comments

iNikkz Over a year ago

Thanks. @juergen. Is it be well, if I use SET instead of ENUM because values are not duplicated ?

juergen d Over a year ago

Never store multiple values in a single column! Just don't. It is bad for performance and poor table design.

1000111 Over a year ago

Please go through this post: Is storing a delimited list in a database column really that bad?. @iNikkz

Collectives™ on Stack Overflow

Why ENUM does not store multiple values in MySQL?

2 Answers 2

3 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related