CODEWITHSUNDEEP
mysqlsqlenums
Valky
Valky

Reputation: 1866

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

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) ?

Upvotes: 5

Views: 9593

Answers (1)

Valky
Valky

Reputation: 1866

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.

Upvotes: 7

Related Questions