6.2.3.4 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 cannot 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.

Starting from 3.23.51 trailing spaces are automatically deleted from SET values when the table is created.

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. Suppose a column is specified as SET("a","b","c","d"). Then the members have the following bit values:

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 will appear once, with elements listed according to the order in which they were specified at table creation time. For example, if a column is specified as SET("a","b","c","d"), then "a,d", "d,a", and "d,a,a,d,d" will all appear as "a,d" when retrieved.

If you set a SET column to an unsupported value, the value will be ignored.

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

Normally, you perform a SELECT on a SET column using the LIKE operator or the FIND_IN_SET() function:

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

But the following will also work:

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

The first of these statements looks for an exact match. The second looks for values containing the first set member.

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.

User Comments

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

Can I somehow retrieve info about all possible
values in a column of type SET (asume the table is
already created and has a finite number of values in
that column, sure)?

The closest way to my need I could imagine is:
DESCRIBE table_name column_of_type_set;

The result is the description of the column in the
common format used for describing the whole table,
so I have to parse the result and get the array with
possible values.

Is there any other way?

PS. Please Cc the answer to my email:
maximy@smtp.ru, thank you.

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? :)

I noticed that the INSERT syntax with SET type is
quite strict. You can insert values with command:
"INSERT INTO table
SETa_set_type_column="value1,value2,value3",
but you cannot insert them with command:
"INSERT INTO table
SET_set_type_column="value1, value2, value3"!
See the difference? There are
WHITESPACEs between the values in the latter
command! My opinion is that mysql should accept
both the commands. In fact it accepts the both,
but only the first one of the latter command's
values is inserted.

<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.

"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...)

to retrieve a list of set elements, instead of "show
columns...", try "DESCRIBE table column_name"

A minor detail not null default 'myDefault'
does not work.

I tried every possible method to insert as default
the first of my setColumn values, but '' ie empty
string was always inserted.
-Methods I tried: inserting null, leaving the
setColumn completely out of the insert statment.
The only method seems to be inserting the value
desired as default.
I'm using mysql-3.23.52 where a default setting
works as expected everywhere else.

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!

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

<PRE>

/**
* @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
}

It would be helpful to include information on
INSERTing both SET & non-SET types simultaneously.

In case anyone was not sure (as I was) - the way to insert set data in a standard INSERT command is specified in the following example:

INSERT INTO table_name VALUES('field1','field2','setitem1,setitem2,setitem3...',field4)

I found this information nowhere on the site, and it bothered me for a while.

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 |
+----+---------+

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!

What is the best way to change values of SET and also update all instances of that value for existing records?

Updating table first is a mistake, I believe.

First change the table records and then the table itself. Correct?

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.

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...

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?

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.

I saw no examples for an INSERT using the decimal values of a SET column,

The way to insert data to a SET column.....

The table:
a numeric column
a string column
the set column ('val1,val2,val3,val4')
another string column

To get the first two set values into the new row, just use the numeric value for the first two SET values: 1 + 2 = 3.

INSERT INTO `table`
(`somenumcol1`,`somestrcol1`,`setcol1`,`somestrcol2`)
VALUES
(4,'hey',3,'tralala');

The 3 inserts val1 and val2 into the set column.

Add your own comment.