BLOB и TEXTENUM
SET
Buy this Reference Manual in softcover from Barnes & Noble!
SET
SET - это строковый тип, который может принимать ноль или более значений, каждое из которых должно быть выбрано из списка допустимых значений, определенных при создании таблицы. Элементы множества SET разделяются запятыми. Как следствие, сами элементы множества не могут содержать запятых.
Например, столбец, определенный как SET("один", "два") NOT NULL может принимать такие значения:
"" "один" "два" "один,два"
Множество SET может иметь максимум 64 различных элемента.
Начиная с 3.23.51, оконечные пробелы удаляются из значений множества SET в момент создания таблицы.
MySQL сохраняет значения SET в численном виде, где младший бит сохраненной величины соответствует первому элементу множества. Если вы делаете выборку столбца SET в числовом контексте, полученное значение содержит соответствующим образом установленные биты, создающие значение столбца. Например, вы можете сделать выборку численного значения SET-столбца таким образом:
mysql> SELECT set_col+0 FROM tbl_name;
Если делается вставка в столбец SET, биты, установленные в двоичном представлении числа определяют элементы множества. Допустим, столбец определен как SET("a","b","c","d"). Тогда элементы имеют такие биты установленными:
SET элемент
|
числовое значение | двоичное значение |
a |
1 |
0001 |
b |
2 |
0010 |
c |
4 |
0100 |
d |
8 |
1000 |
Если вы вставляет значение 9 в этот столбец, это соответствует 1001 в двоичном представлении, так что первый ("a") и четвертый ("d") элементы множества выбираются, что в результате дает "a,d".
Для значения, содержащего более чем один элемент множестве, не играет никакой роли, в каком порядке эти элементы перечисляются в момент вставки значения. Также не играет роли, как много раз то или иное значение перечислено. Когда позже это значение выбирается, каждый элемент будет присутствовать только единожды, и элементы будут перечислены в том порядке, в котором они перечисляются в определении таблицы. Например, если столбец определен как SET("a","b","c","d"), тогда "a,d", "d,a", и "d,a,a,d,d" будут представлены как "a,d".
Если вы вставляете в столбец SET некорректую величины, это значение будет проигнорировано.
SET-значения сортируются в соответствии с числовым представлением. NULL-значения идут в первую очередь.
Обычно, следует выполнять SELECT для SET-столбца, используя оператор LIKE или функцию FIND_IN_SET():
mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%';
mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;
Но и такая форма также работает:
mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2'; mysql> SELECT * FROM tbl_name WHERE set_col & 1;
Первый оператор в каждом примере делает выборку точного значения. Второй оператор делает выборку значений, содержащих первого элемента множества.
Если вам нужно получить все возможные значения для столбца SET, вам следует вызвать SHOW COLUMNS FROM table_name LIKE set_column_name и проанализировать SET-определение во втором столбце.
| Posted by Jason on Wednesday December 18 2002, @5: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 on Wednesday July 3 2002, @10:35am | [Delete] [Edit] |
To insert data in a SET column:
INSERT INTO table SET set_col = 'val1,val2,val3'
| Posted by on Thursday July 11 2002, @2:11am | [Delete] [Edit] |
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:
[email protected], thank you.
| Posted by Aseem Mohanty on Tuesday May 28 2002, @2:19pm | [Delete] [Edit] |
It would be really really really HELPFUL if there
were details as to how to insert data into a SET
column type (ANYWHERE on the site for that matter)
or to update a SET column type. btw I could not
find it anywhere on the site...
AM
| Posted by Mathieu Duchesneau on Tuesday July 2 2002, @2:55pm | [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 Mikko Tynkkynen on Sunday July 7 2002, @4:41am | [Delete] [Edit] |
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.
| Posted by Monte Ohrt on Wednesday December 18 2002, @5:27pm | [Delete] [Edit] |
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 Monday September 16 2002, @2:50pm | [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 on Tuesday September 17 2002, @1:17pm | [Delete] [Edit] |
to retrieve a list of set elements, instead of "show
columns...", try "DESCRIBE table column_name"
| Posted by on Wednesday December 18 2002, @5:27pm | [Delete] [Edit] |
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.
| Posted by on Friday October 18 2002, @7: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 Wednesday December 18 2002, @5:27pm | [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 carmoda on Saturday November 30 2002, @10:03pm | [Delete] [Edit] |
It would be helpful to include information on
INSERTing both SET & non-SET types simultaneously.
| Posted by on Tuesday January 7 2003, @8:15pm | [Delete] [Edit] |
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.
| Posted by Snowkrash on Sunday January 19 2003, @12:53pm | [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 Saturday January 25 2003, @9: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 on Friday February 7 2003, @3:09pm | [Delete] [Edit] |
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?
Add your own comment.