Skip navigation links
10.5 Data Type Storage Requirements »
Section Navigation      [Toggle]

10.4.5. The SET Type

A SET is a string object that can have zero or more values, each of which must be chosen from a list of allowed values specified when the table is created. SET column values that consist of multiple set members are specified with members separated by commas (“,”). A consequence of this is that SET member values should not themselves contain commas.

For example, a column specified as SET('one', 'two') NOT NULL can have any of these values: 

''
'one'
'two'
'one,two'

A SET can have a maximum of 64 different members.

Duplicate values in the definition cause a warning, or an error if strict SQL mode is enabled.

Trailing spaces are automatically deleted from SET member values in the table definition when a table is created.

When retrieved, values stored in a SET column are displayed using the lettercase that was used in the column definition. Note that SET columns can be assigned a character set and collation. For binary or case-sensitive collations, lettercase is taken into account when assigning values to the column.

MySQL stores SET values numerically, with the low-order bit of the stored value corresponding to the first set member. If you retrieve a SET value in a numeric context, the value retrieved has bits set corresponding to the set members that make up the column value. For example, you can retrieve numeric values from a SET column like this: 

mysql> SELECT set_col+0 FROM tbl_name;

If a number is stored into a SET column, the bits that are set in the binary representation of the number determine the set members in the column value. For a column specified as SET('a','b','c','d'), the members have the following decimal and binary values.

SET Member Decimal Value Binary Value
'a' 1 0001
'b' 2 0010
'c' 4 0100
'd' 8 1000

If you assign a value of 9 to this column, that is 1001 in binary, so the first and fourth SET value members 'a' and 'd' are selected and the resulting value is 'a,d'.

For a value containing more than one SET element, it does not matter what order the elements are listed in when you insert the value. It also does not matter how many times a given element is listed in the value. When the value is retrieved later, each element in the value appears once, with elements listed according to the order in which they were specified at table creation time. For example, suppose that a column is specified as SET('a','b','c','d'): 

mysql> CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));

If you insert the values 'a,d', 'd,a', 'a,d,d', 'a,d,a', and 'd,a,d': 

mysql> INSERT INTO myset (col) VALUES 
-> ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

Then all of these values appear as 'a,d' when retrieved: 

mysql> SELECT col FROM myset;
+------+
| col  |
+------+
| a,d  |
| a,d  |
| a,d  |
| a,d  |
| a,d  |
+------+
5 rows in set (0.04 sec)

If you set a SET column to an unsupported value, the value is ignored and a warning is issued: 

mysql> INSERT INTO myset (col) VALUES ('a,d,d,s');
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> SHOW WARNINGS;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'col' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.04 sec)

mysql> SELECT col FROM myset;
+------+
| col  |
+------+
| a,d  |
| a,d  |
| a,d  |
| a,d  |
| a,d  |
| a,d  |
+------+
6 rows in set (0.01 sec)

If strict SQL mode is enabled, attempts to insert invalid SET values result in an error.

SET values are sorted numerically. NULL values sort before non-NULL SET values.

Functions such as SUM() or AVG() that expect a numeric argument cast the argument to a number if necessary. For SET values, the cast operation causes the numeric value to be used.

Normally, you search for SET values using the FIND_IN_SET() function or the LIKE operator: 

mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;
mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%';

The first statement finds rows where set_col contains the value set member. The second is similar, but not the same: It finds rows where set_col contains value anywhere, even as a substring of another set member.

The following statements also are legal: 

mysql> SELECT * FROM tbl_name WHERE set_col & 1;
mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2';

The first of these statements looks for values containing the first set member. The second looks for an exact match. Be careful with comparisons of the second type. Comparing set values to 'val1,val2' returns different results than comparing values to 'val2,val1'. You should specify the values in the same order they are listed in the column definition.

If you want to determine all possible values for a SET column, use SHOW COLUMNS FROM tbl_name LIKE set_col and parse the SET definition in the Type column of the output.


User Comments

Posted by Jason Terry on December 18 2002 3:28pm[Delete] [Edit]

If you need to run an UPDATE without UNsetting any
values in the set try this

UPDATE theTable SET asetcolumn =
concat(asetcolumn, ',newdata') WHERE 1

Posted by Mathieu Duchesneau on July 2 2002 10:55am[Delete] [Edit]

I tried the update trick but it didn't work, and, i've got no
idea on how to insert it right away.... On my tries i get
simply no value

it would be good if Mysql's staff would include tips on how to
insert data in SET colums, wouldn't it? :)

Posted by Monte Ohrt on December 18 2002 3:27pm[Delete] [Edit]

<PRE>
To add an element to an existing set, use this syntax:

update TABLE set COLUMN = COLUMN | NUM;

where TABLE is your table name, COLUMN is your
column of type SET(), and NUM is the decimal value
of the element you want to add, which would be one of
1,2,4,8,16,...

To add more than one element, use the sum.
So to add set elements 1, 2 and 8, use 11 as your
NUM value (1 + 2 + 8).

To remove an element, use this syntax:

update TABLE set COLUMN = COLUMN & ~NUM;

again, where NUM is one of 1,2,4,8,16,... or sum of
them to remove multiple elements.

Posted by [name withheld] on September 16 2002 10:50am[Delete] [Edit]

"If you want to get all possible values for a SET
column, you should use: SHOW COLUMNS FROM
table_name LIKE set_column_name and parse the
SET definition in the second column."

I can't get this to work & I don't understand the bit
about parsing the SET definition in the second
column - I've tried Google for about half an hour but
have drawn a blank.

Can anyone explain in simple terms how to retrieve
all possible values from a SET column?

(I am making a page to add entries to a database
with a SET column by checking checkboxes and I
need to list all the possible values...)


Posted by [name withheld] on October 18 2002 3:21am[Delete] [Edit]

Adding a new element into the possible values of a
set column:

if the set column myColumn is ('first','second','third')
and you want to add a 'fourth', do...

ALTER TABLE myTable CHANGE `myColumn`
`myColumn` SET('first','second','third','fourth') NOT
NULL;


I'm using MySQL 3.23.32 with phpMyAdmin 2.2.5
The phpMyAdmin showed me how to do it!

Posted by Richard Nuttall on December 11 2003 5:04pm[Delete] [Edit]

Here's some PHP code to return the possible
options of a set as an array.

<?
/**
 * @return array
 * @param table DB table
 * @param column Column name
 * @desc Return an array of the possible values for a SET
 */
function get_set($table,$column)
{
    $sql = "SHOW COLUMNS FROM $table LIKE '$column'";
    if (!($ret = mysql_query($sql)))
        die("Error: Could not show columns");

    $line = mysql_fetch_assoc($ret);
    $set  = $line['Type'];
    $set  = substr($set,5,strlen($set)-7); // Remove "set(" at start and ");" at end
    return preg_split("/','/",$set); // Split into and array
}
?>

Posted by Snowkrash on January 19 2003 10:53am[Delete] [Edit]

Working with SET goes as follows:
---------------------------------
>>> Example table:
CREATE TABLE `settest`
(`id` SMALLINT NOT NULL AUTO_INCREMENT
,`set_col` SET('a','b','c','d')
,PRIMARY KEY (`id`)
);

>>> INSERTING DATA:
INSERT INTO `settest`
VALUES (0,'a,d');

+----+---------+
| id | set_col |
+----+---------+
|  1 | a,d     |
+----+---------+

>>> UPDATE 1 - replaces all content in set_col
('a,d' -> 'b,c'):
UPDATE `settest`
SET `set_col` = 'b,c' WHERE `id` = 1;
+----+---------+
| id | set_col |
+----+---------+
|  1 | b,c     |
+----+---------+

>>> UPDATE 2 - adding new values in set_col (+ value 'a'):
UPDATE `settest`
SET `set_col` = CONCAT_WS(',',`set_col`,'a')
WHERE `id` = 1;
+----+---------+
| id | set_col |
+----+---------+
|  1 | a,b,c   |
+----+---------+

Posted by Michael Chu on January 25 2003 7:51am[Delete] [Edit]

Hi, all

I think this is a good news for you all.

1st, I wanna to answer the "SHOW COLUMNS FROM table_name LIKE set_column_name and parse ... ...", this sentence means that you can see the set column on your screen and the 2nd column is your defination of the SET type column, it didn't mean you can see the numeric values (like 1,2,4,8...) on that column, I think you must know that value by yourself. The numeric values (the binary code) is decided by the sequence of the values in your SET type colume. For example, if you define a SET type column like this:

sports set('swimming','skatting','running','tennis','football');

Then the numberic values is like this:
values binary code numeric value
swimming 00000001 1
skatting 00000010 2
running 00000100 4
tennis 00001000 8
football 00010000 16

I think this will be very clear, OK?!

2nd, here someone had told us how to add a new value to the SET type column, it did work well, I have learned that. But nobody tell us how to delete a value from an existed SET column in the table. After my hard thinking and trying, I worked out finally. It's very easy than I thought.

For example, I have insert a record in above table. The SET type column's value is:

name sports
Michael ('running','tennis','football')

now I wanna to remove the 'football' from the column, use the following sql:

UPDATE table_name SET sports=REPLACE(sports,'football','') WHERE name = 'Michael'.

That's all, is it easy. Any problem, pls. let me know. BTW, Mysql is a very convenient and powerful database. Enjoy it!

Posted by ken easson on March 29 2003 6:21pm[Delete] [Edit]

Reading through the list i noted the absence of the logical or (|) solution to UPDATE a SET column type.

the following:
UPDATE table_name SET set_col = (set_col | 4) WHERE condition;

this sets the 3rd bit (value) of the set column to true without affecting any other of the bits,
since 1 | 0 = 1 and 0 | 0 = 0 thus for all the other columns the bits are not affected, but for the column where there is a forced 1 set, the column will always be true since 1 | 1 = 1 and 0 | 1 = 1.

to set multiple columns to true, simpy add the decimal values for each bit. ie:
col_val = (col_val | 7) sets the first three bits to true. without affecting any of the others.


Posted by Andreas Westling on March 30 2003 6:12am[Delete] [Edit]

If you want to remove a bit from a set you can use try something like this:
UPDATE table SET status=status&~pow(2,find_in_set('set_member', status)-1 WHERE find_in_set('set_member', status)

In this case you don't have to care about the ordering of the set-members...

Posted by moritz moeller on April 9 2003 3:56am[Delete] [Edit]

i have a question regarding index usage for SET queries.

for example, if i have the following query:

SELECT * FROM items WHERE (
(setcol & (1+2+8) == 0) AND
(setcol & (4+16) == 4+16)
)

this query returns all elements of table items where the setcol has the 1., 2. and 4. set value NOT set, and the 3. and 5. value set.

how does such a query perform on a very big table? and can mysql make use of an index?


Posted by Willem-Jan van Dinter on May 24 2003 2:20pm[Delete] [Edit]

If you want to get an array in php with all possible values for an ENUM or SET column, you should use: SHOW COLUMNS FROM table_name LIKE 'enum_column_name' (with or without %-wildcards), and parse it with the following php script:

$result=mysql_query($query);
if(mysql_num_rows($result)>0){
$row=mysql_fetch_row($result);
$options=explode("','",preg_replace("/(enum|set)\('(.+?)'\)/","\\2",$row[1]));
}

Now $options is an array with all possible values

Hope it helps.

Posted by [name withheld] on December 10 2003 12:49am[Delete] [Edit]

Scenario: you have a subset of the possible values for a set column (call it your "master list") and you want to pull out all rows from the table where the set column has at least one matching value to your "master list".

Not too hard to figure out, but here's one way:

Figure out the numeric/binary representation of your master list, then in the where clause of your query:

.... WHERE (set_column & master_list) > 0

Have fun...

Posted by [name withheld] on January 15 2004 9:16am[Delete] [Edit]

It should be noted that changing the value of a set member using the ALTER TABLE syntax has the (possibly) unexpected side-effect of unsetting that set member throughout the table. This means that if you want to change the value of a set member you must first create a new set member with the proper label, update all the rows that have the old set member to include the new set member, then you can delete the old set member from the table.

Posted by Are you mortal? Then prepare to die. on February 11 2004 3:28pm[Delete] [Edit]

One really cool way to get set like behaviour is using a 'cross tab' query...

Set up your table like this (kind of semi normalized for set values).

COL1 COL2
A 1
A 2
A 3
A 4
A 5
B 2
B 3
B 4

COL1 = old primary key
COL2 = old set values (paired as appropriate).

Now the magic...

Then do your query like this...

SELECT
COL1,
MAX(IF(COL1=1,1,0)), # Enumerate set values hear
MAX(IF(COL1=2,1,0)), # Use code to do this
MAX(IF(COL1=3,1,0)), # Have as many as you like!
MAX(IF(COL1=5,1,0)) # Performance is fine!
FROM
me_set_table
WHERE
whatever
GROUP BY # VERY IMPORTANT!
COL1; # The key to the whole thing!

Now, for each key, you have a series of yes no columns which read off set occupancy in the order you listed you set elements in the query.

Wrap that series of "MAX(IF("s in a

CONCAT(
...
) AS VECTOR

and you can do all kinds of nice things with vector arithmatic and your set!

If you are programatically inclined you could even add something like

DEC(
CONCAT(
...
)
) AS VECTOR_INDEX

And you can then load your sets into an array, indexed by the set members!

If you prefer fuzzy sets, or bags, you can do something more like...


COL1 COL2 COL3
A 1 9
A 2 10
A 3 11
A 4 3
A 5 45
B 2 4
B 3 33
B 4 54

COL3 = set occupancy statistic or count. I.e. Person B has set attribute 4 to a value of 54.

SELECT
COL1,
SUM(IF(COL1=1,COL3,0))/SUM(COL3),
SUM(IF(COL1=2,COL3,0))/SUM(COL3),
SUM(IF(COL1=3,COL3,0))/SUM(COL3),
SUM(IF(COL1=5,COL3,0))/SUM(COL3)

But don't ask me about fuzzy set mathmatics! I imagine you could do some quite wacky set matching for your 'get-love-online.cum' site (only 2 more days to get developing!).

Happy Valentines,









Posted by Örvar Kárason on February 13 2004 11:17am[Delete] [Edit]

You can use the BIT_OR function to create a superset of all the members present in a SET column.

For instance if you want to display only categories that are not empty.

SELECT BIT_OR( category_column) FROM some_table WHERE ...;

Then you check which bits are set in the the number that the query returns.

See http://www.mysql.com/doc/en/GROUP-BY-Functions.html for info on the BIT_OR function.

Posted by Jason Boyd on March 20 2004 1:25pm[Delete] [Edit]

Don't use ALTER TABLE for user data. This may be an obvious tip, but then again it may not.

In many cases, there is a good reason to do this to dynamically change the allowed values of a SET column. However, if you need to allow users to do this, you may be blurring boundaries between database structure and data. If this matters to you (it should), a better alternative is to avoid the SET type in this case and prefer a key into a separate table. Eg:

TABLE movies
id INT
title VARCHAR
category_ids VARCHAR (e.g. ":2:4:7:")
...

TABLE categories
cat_id INT
name VARCHAR

On the down side, this requires you parse the category_ids field and so may require an extra query, (though if there is a small fixed limit on how many categories one movie can be you could instead have a set of fields cat_id_1, cat_id_2, etc.).

A sample 2 step query:

"SELECT title, category_ids FROM movies WHERE [...] ;"

Store category_ids in a string variable $cats.

"SELECT name
FROM categories
WHERE LOCATE($cats, CONCAT(':',cat_id,':')) > 0;"

This will return a list/array of categories for the movie.

This approach has several advantages, including simplicity of code to add new categories, greater stability of the table structures which can ease maintenance and compatibility between installations, and of course separation of structure from data. Always prefer simplicity, even if it requires more complexity :)

Posted by ScotJam on April 27 2004 10:53am[Delete] [Edit]

After searching the internet, I found this excellent article by a chap called Mike Hillyer on smart ways of using SET in mysql.

http://www.vbmysql.com/articles/mysqlsetdatatype.html

Posted by Max on January 31 2005 10:00pm[Delete] [Edit]

I suggest that the following usage,

SELECT * FROM tbl WHERE set_col LIKE '%value%'

given in the manual, should be marked as "depreciated" or be accompagnied by a
WARNING: this can produce unwanted results if one value is a substring of another one.

E.g. for SET('member','admin','guest','exmember'), selecting ... LIKE '%member%' gives not only members, but also the ex-members.
It can be less evident in other situations... e.g. SET('dog','cat','stallion','donkey','elephant','lion'), could be dangerous for some horses...

Posted by Matt Wolfe on April 6 2005 6:23am[Delete] [Edit]

A few nice functions i wrote (with some help) for dealing with sets in php..

this one alters a set (inserting a new item in it, and sorts the set, which is nice if you ever want to sort a column by the name of a set). There are probably other ways of doing this but it worked well for me.

function insert_set_item($table, $which_set, $item) {
if (!get_magic_quotes_gpc()) {
$item = mysql_real_escape_string($item);

}

$set = get_set($table, $which_set);
for($i=0; $i<count($set); $i++) {
if ($set[$i] == $item) {
return "$which_set $item already exists.";
}
}
if (strlen($item) < 1) { // you may not need this
return "$which_set needs to be more than 1 character";
} else {
array_push($set, $item);
sort($set);
$st = implode("','", $set);
$st = "'" . $st . "'";

$query = "ALTER TABLE $table CHANGE $which_set $which_set SET($st)";
$results = mysql_query($query);
if (!$results) {
return "Sorry but $which_set could not be inserted";
}
}
}

//this function removes an item in a set.
function remove_set_item($table, $which_set, $item) {
$set = get_set($table, $which_set);
$key = array_search($item, $set);
if (isset($key)) { //check that this item actually does exist in the set
unset($set[$key]);
$st = implode("','", $set);
$st = "'$st'";
mysql_query("ALTER TABLE $table CHANGE $which_set $which_set SET($st)");

} else {
return "This $which_set does not exist.";
}
}


// This function i used to get the set, i didn't write it, but it //works quite well!
function get_set($table,$column) {
$sql = "SHOW COLUMNS FROM $table LIKE '$column'";
if (!($ret = mysql_query($sql)))
die("Error: Could not show columns");
$line = mysql_fetch_assoc($ret);
$set = $line['Type'];
$set = substr($set,5,strlen($set)-7); // Remove "set(" at start and ");" at end
return preg_split("/','/",$set); // Split into and array
}

Posted by Beat Vontobel on November 12 2005 11:16am[Delete] [Edit]

Take care when adding or removing single set members in UPDATE statements: The most simple approaches you'll find on the web using CONCAT() or CONCAT_WS() (adding) and REPLACE() (removing) generate warnings or even wrong results on some data. They don't handle empty sets and sets with members containing substrings of other members ('bar', 'minibar') correctly.

To emulate the missing ADD_TO_SET() and REMOVE_FROM_SET() functions with string manipulation in a way that handles all cases use the following fairly complex expressions:

-- emulation for ADD_TO_SET(value, set)
CONCAT_WS(',', IF(set = '', NULL, set), value)

-- emulation for REMOVE_FROM_SET(value, set):
TRIM(BOTH ',' FROM REPLACE(CONCAT(',', set, ','), CONCAT(',', value, ','), ','))

See http://www.futhark.ch/mysql/109.html for a discussion of this and for some thoughts on how to add this as a stored function in MySQL 5.0 and it's performance implications.

Posted by Gary Bickford on December 22 2005 8:06pm[Delete] [Edit]

This SELECT statement is handy for showing what bits are set, along with their names and how many of each combination, in a field of type SET. The example below runs in under 1/4 second (for 233,000 rows), according to PHPMyAdmin.

Those who know more about MySQL functions, especially those running something newer than 3.23, may have a better way to do this.

--------------
SELECT distinct setmember, count(*) as count, @num:=(1 * setmember) as num,
CONCAT(@num & 1>0,
@num & 2 > 0,
@num & 4 > 0,
@num & 8 > 0,
@num & 16 > 0,
@num & 32 > 0,
@num & 64 > 0,
@num & 128 > 0,
@num & 256 > 0,
@num & 512 > 0,
@num & 1024 > 0,
@num & 2048 > 0,
@num & 4096 > 0,
@num & 8192 > 0,
@num & 16384 > 0,
@num & 32768 > 0,
@num & 65536 > 0
) as bits
FROM `search_table`
group by setmember
-----------------

*** UPDATE - better way found ***

I found the function EXPORT_SET(set,on,off,sep,nbits) in the manual. It does the same as above, better.

mysql> SELECT distinct setmember, export_set(setmember,'1', '0','',20 ) as bits FROM `search_table` ;

+------------------+----------------------+
| setmember        | bits                 |
+------------------+----------------------+
|                  | 00000000000000000000 |
| lemon            | 01000000000000000000 |
| grape            | 00010000000000000000 |
| lemon,grape      | 01010000000000000000 |
| plum             | 00000001000000000000 |
| grape,plum       | 00010001000000000000 |
| apple            | 00000000000100000000 |
| grape,apple      | 00010000000100000000 |
| plum,apple       | 00000001000100000000 |
| grape,plum,apple | 00010001000100000000 |
+------------------+----------------------+

Posted by Poul Bak on February 16 2006 5:35pm[Delete] [Edit]

Newbies should pay attention to the inconsistency of the syntax used for sets.
When working with members you must use only one string (like 'One,two') but when specifying the SET you must use separate strings (like 'One','two').
You CAN create a SET specifying 'One,two' (no errors),but it will NOT work as intended.

Posted by Boris Korobkov on August 24 2006 10:48am[Delete] [Edit]

"How to remove any value from the set-field"

UPDATE tbl SET set_col = set_col & ~(1 << (FIND_IN_SET('TheValueToBeRemoved', set_col)-1)) WHERE ...

Posted by Bruno Leles on June 5 2007 3:04pm[Delete] [Edit]

I was thinking in how to convert the set value to decimal, hex or bin and this is the best I did. ^^

mysql> SELECT `user_access`, (`user_access` | 0) as 'dec' FROM `tb_users`;

mysql> SELECT `user_access`, HEX(`user_access` | 0) as 'hex' FROM `tb_users`;

mysql> SELECT `user_access`, BIN(`user_access` | 0) as 'bin' FROM `tb_users`;

to get the decimal value of the set column I use the bitwise 'OR' operation with 'ZERO' to NOT change the value of the column, then I uses the mysql functions to convert this value to HEXADECIMAL and BIN.

Add your own comment.