SET

SET

Suppose that there can be multiple reasons for a piece of luggage to go missing. Perhaps it was checked in late, the airline misrouted it from London, UK, to London, Ontario, and then the suitcase was stolen in a daring, armed raid on a baggage cart at the London, Ontario, airport.

This is an example of a one-to-many relationship (that is, one piece of luggage can have many misfortunes). You would typically split the reasons off into their own table, and then create a row for each reason in that table. However, you could also use the SET attribute as follows:

 CREATE TABLE missing_luggage (     missing_luggage_id INT AUTO_INCREMENT PRIMARY KEY,     luggage_id INT NOT NULL,     missing_reason SET ('Airline error', 'Late check in', 'Possible theft',     'Unknown'), ... ...     FOREIGN KEY (luggage_id) REFERENCES checked_luggage(luggage_id) ON DELETE CASCADE ) ENGINE = INNODB; 

This lets you create very handy SQL for inserting or updating values into the missing_reason column. You can use integers to set the bits for the possible values, which translates to all possible permutations for missing_reason.

In this case, the possible values would translate to the following:

Value	Decimal	Binary
'Airline error'	1	0001
'Late check in'	2	0010
'Possible theft'	4	0100
'Unknown'	8	1000

With up to 64 possible values, you can represent many potential SET permutations with simple SQL:

 INSERT INTO missing_luggage (missing_reason) VALUES (1) 

This inserts a row and sets the missing_reason column to 'Airline error' only.

 INSERT INTO missing_luggage (missing_reason) VALUES (7) 

This inserts a record that matches the unfortunate piece of luggage described earlier: The check in was late (2), the airline made an error (1), and then the luggage was stolen (4).

If using these kinds of numeric values is not clear enough for you, you can also define mnemonic values in your application code. Your compiler will then swap these more readable values for the numeric settings listed previously. You can also provide a comma- separated list when inserting this type of information.

You can use the same syntax when searching for matching records:

 SELECT * FROM missing_luggage WHERE missing_reason = 9 

This returns all missing luggage in which the reason is both 'Airline error' and 'Unknown.'

If you don't want to use that style of query, you can use the LIKE '%value%' syntax or the FIND_IN_SET() function.

Note that MySQL always sorts the results in the order in which they were entered as possible SET values when the table was created, not the order in which they were entered into the table as data.