How to insert and update multiple enum values for a row?

Question

As I read this answer, I guess if it possible to insert and update multiple ENUM "checked" values for a row, with MySQL.

Entity example:

TABLE
id INT auto_increment
day_list ENUM ('1','2','3,'4','5,'6','7')
gender ENUM ('m','f')

I tried the following (to specify that the week end is checked and the two genders too) :

INSERT INTO TABLE (day_list,gender) VALUE ('{6,7}','{m,f}')

It doesn't return any error, and insert correctly the record, but the day_list and the gender fields are empty.

So, it seems that it doesn't work with mySQL. And it doesn't seem to be possible in phpMyAdmin (3.2.0), as the values are checked by radio buttons (and not checkboxes).

Any idea on how to make it work ? Or another solution to record and select those checked days without LIKE request and without association entity (like TABLE_DAYS and TABLE_GENDER) ?

Valky · Accepted Answer · 2013-07-01 13:46:14Z

7

In MySQL, an ENUM type field can accept only one value.

After doing some searches in phpMyAdmin, it seems that a SET type will do the job.

So the entity :

TABLE
id INT auto_increment
day_list SET ('1','2','3,'4','5,'6','7')
gender SET ('m','f')

How to insert :

INSERT INTO TABLE (day_list,gender) VALUE ('6,7','m,f')

And about searching for values :

If values are integers : IN can work if data is ordered.
If values are not integers : FIND_IN_SET is required and search data doesn't need to be ordered.

Limitation : SET type only accept 64 members, for more a foreign association entity will be required.

edited Jul 1, 2013 at 13:46

answered Jul 1, 2013 at 13:40

Valky

1,8664 gold badges20 silver badges38 bronze badges

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

Collectives™ on Stack Overflow

How to insert and update multiple enum values for a row?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related