• 6 Справочник по языку MySQL
  • 6.2 Типы данных столбцов
    • 6.2.3 Символьные типы данных
      • 6.2.3.1 Типы данных CHAR и VARCHAR
      • 6.2.3.2 Типы данных BLOB и TEXT
      • 6.2.3.3 Тип перечисления ENUM
      • 6.2.3.4 Тип множества SET

Additional languages

• English
• French
• German
• Japanese
• Portuguese

Buy this Reference Manual in softcover from Barnes & Noble!

Additional formats

• HTML, one page per chapter, tarball
• HTML, all on one page, tarball
• HTML, all on one page, ZIP

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.

<?
/**
* @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     |
+----+---------+

+----+---------+
| id | set_col |
+----+---------+
|  1 | b,c     |
+----+---------+

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

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

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.

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,

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.

Manual says:
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;

Put like that seems they behave the same, but they dont. It doesnt say that using LIKE on SET and ENUM columns is a bit dangerous since it treats field contents as string, not as SET or ENUM.

Suppose you have SET-field SET('user','superuser') then this retrieves only those records which are ONLY 'user' and not both user and superuser:
SELECT * FROM table WHERE field LIKE 'user'

This retrieves records which are 'user' and/or 'superuser':
SELECT * FROM table WHERE field LIKE '%user%'

Add your own comment.