SET TYPE

aopen

How do you use set type in mysql? Can you give me an example?

Thanks

alimadzi

Have you read this?

http://dev.mysql.com/doc/refman/5.0/en/set.html

aopen

of course. can you give me real example when it can be use?

alimadzi

aopen wrote:
of course. can you give me real example when it can be use?

OK, sorry. We can never be sure around here, so I had to check...

I honestly don't find the SET type very useful because it locks you into a static set of possible values. If any of the set values could change or go away or if you might need to add to them later, I would recommend a separate table to store them individually and then a full-join table to associate them with your original data.

Here's a cheesy contrived example for you. Suppose you were throwing a party and wanted to assign different people to bring different things. You might create a table like this...

CREATE TABLE Guest
(
  guest_id  INT NOT NULL AUTO INCREMENT,
  guest_name VARCHAR(64),
  bringing SET('chips', 'dip', 'salsa', 'beer'),

  PRIMARY KEY(guest_id)
);

See I told you this would be a contrived example. 😉

So each person could bring any combination of those items like so...

INSERT INTO Guest (guest_name, bringing) VALUES ('aopen', 'chips,dip,salsa');
INSERT INTO Guest (guest_name, bringing) VALUES ('alimadzi', 'beer');

You can also insert or retrieve values based on an integer equivalent that works the same as UNIX file permissions. In this example, 'chips' would have a value of 1, 'dip' is 2, 'salsa' is 4, and 'beer' is 8. So the above queries could be written this way...

INSERT INTO Guest (guest_name, bringing) VALUES ('aopen', '7');
INSERT INTO Guest (guest_name, bringing) VALUES ('alimadzi', '8');

Hope this helps... What kind of beer should I bring? 😉

johanafm

Personally I find it useful in a select few cases, such as tracking project status or customer support status. While that can just as readily be handled by a separate table as suggested above and certainly won't make use of the "binary flag" capability, it can reduce the number of tables in use, as well as improving code readability.
In databases where you have to deal with data from 50+ different tables in some cases I find this to be a good thing. However, I've no idea what most people would choose to do.