CODEWITHSUNDEEP
mysqldatabaseenumsinsert
iNikkz
iNikkz

Reputation: 3819

Why ENUM does not store multiple values in MySQL?

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 ?

Upvotes: 2

Views: 10578

Answers (2)

juergen d
juergen d

Reputation: 204756

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

Upvotes: 3

oakymax
oakymax

Reputation: 1474

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

Upvotes: 4

Related Questions