A New Set of Solar Fireworks

MySQL SET Data Type

The following is an excerpt from an article i have referenced numerous times myself from the original source at MySQL : AB
(the official MySQL developer zone, located at http://dev.mysql.com/tech-resources/articles/). The SET datatype is kind of like an ENUM, except there’s an option to pick groups of items as a reply, instead of only one of the many possible choices.

For example, if you wanted to ask which five of the Baskin Robbins flavors of ice cream are your favorite– you could use set– you offer the available flavors as you would an enum, but you also expand the response quality by taking from one, as an enum, and allowing for groups– as a SET. that’s probably a fair at best explanation, but at least it’ll get your gears turning. Google it for more resources. For now, i offer this snipped i enjoy from MySQL dot com

Why You Shouldn’t Use SET

The MySQL SET datatype is not commonly used for a few reasons; First, using the MySQL SET datatype limits you to 64 elements. While you could get around this by using multiple SETs, this still represents a loss of versatility. Second, you cannot include commas in your set elements, as the comma is an element separator. Third, using a set means your data is not normalized. In our above example, we are tracking a person’s interests for a hypothetical dating site. In a normalized schema, there should be three tables: one for the person, one for all possible interests, and one that links a person to their particular interests. Fourth, an INDEX on a set datatype is going to refer to the set as a whole and will not be used for searching individual elements (this may or may not be a problem for certain applications).

So why do we use the MySQL SET datatype?

Well there are a few reasons; The MySQL SET datatype allows us to handle multiple values with a lot less trouble than if we had our table fully normalized. Our schema is simplified because we only need one column instead of three tables to store our list of interests. The MySQL SET datatype allows us to compare multiple values without using complex JOIN operations. We can manipulate the set with binary functions to do complex comparisons by comparing bit values on a single column instead of comparing multiple rows of multiple tables.

You may find that the MySQL SET datatype is something that you never need to use. If you find that it’s advantages outweigh the disadvantages, read on to discover how to use it.

There are two ways to INSERT data into MySQL SET columns: by decimal value or by using strings. To INSERT the combination of Travel and Sports into our sample tables, we can use the following:

INSERT INTO set_field(myset) VALUES(3);
INSERT INTO set_field(myset) VALUES(‘Travel,Sports’);

Because the decimal value of Travel is 1 and the decimal value of Sports is 2, the combination of the two can be inserted at the sum of the decimal values, or 3. Another example of matching INSERT statements:

INSERT INTO set_field(myset) VALUES(7);
INSERT INTO set_field(myset) VALUES(‘Travel,Sports,Dancing’);

There are three kinds of UPDATE that we need to deal with: complete replacement of the SET data, addition of set members, and removal of set members. We will deal with these in the order listed above.

To completely replace the data in a SET, we simply specify the new values:

UPDATE set_field SET myset = ‘Travel,Dining’ WHERE rowid = 1;
UPDATE set_field SET myset = 2 WHERE rowid = 2;

To add an element to an existing SET we use the CONCAT() function to add the new element to our comma separated list. To work with decimal values, we can use the bitwise OR operator |.

UPDATE set_field SET myset = CONCAT(myset,”,Travel”)
WHERE rowid = 3;
UPDATE set_field SET myset = myset | 1 WHERE rowid = 3;

We can also use the CONCAT_WS() function, which handles list separators for us:

UPDATE set_field SET myset = CONCAT_WS(‘,’,myset,’Dancing’)
WHERE rowid = 6;

Important Note:

If you find this iformation to be useful, then please go read the original resource for the complete, original text. This is merely a fraction of the information available from the original, and in no way should this excerpt be considered a reflection of the quality of material which is available at MySQL.com, the original source

the MySQL SET Data Type
MySQL AB :: the Developer Zone
“MySQL: the Worlds Most Popular Open Source Database”


Whatchu do

Leave a Reply

Your email address will not be published. Required fields are marked *