Because MySQL's SET type is simply a STRING type. Putting more than one item of data into the same field makes it very awkward, if not impossible, to use the data for anything more than straight SELECT.
For example, if you wanted to get a list of people who selected option 3, you'd have to do something like
SELECT userid FROM table WHERE set_field LIKE '%3%';
or
SELECT userid FROM table WHERE FIND_IN_SET('3', set_field)>0;
MySQL does not support functional indexing, so queries like this will always force a sequencial scan, and this slow things down quite a lot.
Apart from that, MySQL stores the data as a string, so what you'll get back from a select is "1,4,7" when options 1 4 and 7 were selected. Now you have to split that string up and process it manually.
Some less obvious problems:
- For historical data: if you decide to change the options lateron, and change the SET values in the table definition, you instantly invalidate all the SET records you already had. If you change from SET('1','2','3') to SET('6','7','8'), then all your data about 1,2 and 3 is changed to whatever MySQL feels like doing. Some is lost, some is converted. Chaos.
Portability is zero. Other databases simply don't support this datatype.
Updating your data becomes very tricky. If you mistakenly entered that every user had option 2 selected, when it should have been 3, you will have to manually select the value of the SET column for every row, explode it into an array, find the element with value=2, replace the 2 with a 3, implode the array back into a string, and update the record in the database. Great fun at 10 rows, a nightmare 10k rows.
