iNikkz iNikkz - 18 days ago 6
MySQL Question

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 ?

Answer

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
Comments