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, Jul 11 '16 at 11:03

score 4 · Answer 1 · answered Jul 11 '16 at 11:00

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 '16 at 11:00

oakymax

1,454
1
14
21

It's look nice solution. – Ravindra Singh Dec 25 '18 at 14:14
i changed enum to set - i have problem with create and edit page ! create not saved and edit doesn't show data from database ! – Saltern Dec 27 '20 at 07:08
@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... – oakymax Dec 30 '20 at 09:10

juergen d · Accepted Answer · 2016-07-11T11:16:23.853

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 '16 at 11:16

answered Jul 11 '16 at 10:59

juergen d

201,996
37
293
362

Thanks. @juergen. Is it be well, if I use SET instead of ENUM because values are not duplicated ? – iNikkz Jul 11 '16 at 11:27
1

Never store multiple values in a single column! Just don't. It is bad for performance and poor table design. – juergen d Jul 11 '16 at 11:30
2

Please go through this post: [**Is storing a delimited list in a database column really that bad?**](http://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad). @iNikkz – 1000111 Jul 11 '16 at 11:55

Why ENUM does not store multiple values in MySQL?

2 Answers2

Linked