MySQL i kolumny typu SET

Wszyscy znamy sql’owy SET jako np. określenie zestawu danych wrzucanych w odpowiednie kolumny. Ale SET jako typ kolumny, okazuje się nie wszystkim jest znany. Najprościej mówiąc jest to “trochę inny ENUM” :) A czym się różni i do czego go stosować możecie przeczytać dalej…

Pola ENUM wykorzystywane są dość często, oznaczają wybranie jednej opcji z listy. Taka sql’owa selectlista :) Pole wypełnia się jedną z dopuszczonych w nagłówku kolumny wartością. Na przykład skala ocen z określonego przedziału (uwaga na stopnie typu 3+), czy typ konta klienta. Pola tego typu są dość szybkie w wyszukiwaniu i nie zajmują wiele miejsca, chociaż “klucze” mogą być tekstowe i to całkiem długie (ale nie pamiętam dokładnie jak długie), a samych elementów listy może być ponad 65 tys. Spora lista, nie ma co. Kluczom przypisywane są indeksy od jedynki (zero zarezerwowane dla pustego stringa) i to one są przetrzymywane w samej bazie, z “tłumaczeniem” w locie przy wyciąganiu czy wstawianiu danych.

A jak się ma do tego pole “SET”? Chyba już wszyscy się domyślają o co chodzi. To taka multiselectlista, niby podobna do ENUM, ale trochę inna. Tworząc tabelę określamy zestaw dopuszczalnych wartości, z których dla danego rekordu zapisana może być jedna wartość, dwie, albo i wszystkie. Albo żadna, wolna amerykanka ;) Oczywiście nie może się znaleźć w kolumnie wartość całkowicie spoza naszego zestawu.

Charakterystyka:

• max. 64 elementy “zestawu”. Sporo mniej niż 65tys. ENUM’a, ale za to jedna taka kolumna może nam zastąpić 64 kolumny boolowskie. Też przydatne, nie?

(—tutaj nudniejszy kawałek freak-only, jak ktoś chce “tylko” używać, bez zrozumienia, to może pominąć ;) —)

• dane w bazie są przechowywane bitowo, każda wartość z zestawu to jeden bit oznaczający flagę dla przypisanej sobie wartości. Oznacza to, że na danych można operować liczbowo, z tym że trzeba uważać. Jeżeli stworzymy sobie kolumienkę SET(’a’,’b’,’c’,’d’) i wstawiamy/wyciągamy z niej dane kluczami liczbowymi, to 1 oznacza ‘a’, 2 – ‘b’, ale ‘3′ to ‘a,b’, a dopiero 4 to ‘c’. A ‘d’ ma wartość dopiero 8. Dlaczego? Właśnie z powodu tych flag binarnych – dla 4 wartości brak żadnej wybranej to 0000. ‘a’ to 0001 (…) a ‘d’ to 1000. A jako że 3 w systemie dwójkowym to 0011 to mamy aktywne flagi dla ‘a’ i ‘c’.

(—koniec aż takich nudów—)

• dane możemy więc wstawiać przez wartości ‘1′, ‘3′ – jako ‘a,b’, ale nie przez ‘1,2′. Za to tekstowo możemy szaleć: zadziałają ‘a’, ‘c’, ‘a,c’, ale też ‘c,d,a,b’, a nawet ‘b,d,b,b,b,b,b’ – w efekcie w bazie będzie ‘b,d’ – każda wartość w danym rekordzie zapisuje się tylko raz.

• kolejność – liczy się kolejność elementów podczas tworzenia tabeli, są im przypisywane kolejne klucze, dalej wszystko operuje na tych kluczach.

Teraz trochę o wyciąganiu z bazy rekordów dla których wybrana jest dana wartość z set’a – możemy to zrobić na kilka sposobów:

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

lub

SELECT * FROM tbl_name WHERE set_col LIKE '%value%';

Druga opcja ma w sobie ten haczyk, że jeżeli szukamy np. rekordów, dla których w secie wybrane jest ‘cat’, a w tym samym secie występuje też opcja np. ‘catapult’, to dostaniemy wyniki z wybranym ‘cat’ lub ‘catapult’ (albo jednym i drugim). Dlatego pewniejszy jest pierwszy sposób. Tylko trudniejszy do zapamiętania ;P

Ale to jeszcze nie wszystko, bo możemy zrobić tak:

SELECT * FROM tbl_name WHERE set_col & 1;

co wyciągnie nam wszystkie rekordy z aktywnym pierwszym elementem set’a (tym, który jest pierwszy w definicji kolumny, we wcześniejszym przykładzie było to ‘a’)
albo tak:

SELECT * FROM tbl_name WHERE set_col = 'val1,val2';

co zwróci nam tylko dokładne dopasowanie, wiersze dla których wybrane są obie opcje z set’a val1 i val2 i żadne inne. W dodatku liczy się kolejność – jeżeli w definicji SETa mamy (‘a’,'b’,'c’,'d’) a szukamy set_col = ‘b,a’ to nie znajdziemy nic.

A jak wyciągnąć np. do formularza wszystkie możliwe wartości? Np. taką funkcją w php (Richard Nuttall, komentarze do The SET type w manualu MySQL):

<?php

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

?>

Jak widać pole SET może nam się przydać wszędzie tam, gdzie chcemy zapamiętać jakieś dane z multiselectlisty, czy zestawu checkbox’ów. Nam posłużyło ostatnio np. do określenia które języki mają być dostępne na których stronach większego serwisu.

Pamiętajcie że jest takie coś i że czasem może się przydać, bo jest dużo łatwiejsze w obróbce niż zapisywanie zserializowanych tablic, czy parsowanie stringów z zestawem opcji :)