MYSQL - ENUM SET TYPE

ENUM
최대 65535 개의 엘리먼트를 가질 수 있음.
인덱스를 사용. 인덱스는 1부터 시작.

테이블을 작성하여 예를 들어가면서 설명.

1. 테이블 작성

mysql> create table enum_test ( numbers ENUM('0' , '1' , '2' ));
mysql> desc enum_test;
+---------+-------------------+------+-----+---------+-------+
| Field   | Type              | Null | Key | Default | Extra |
+---------+-------------------+------+-----+---------+-------+
| numbers | enum('0','1','2') | YES  |     | NULL    |       |
+---------+-------------------+------+-----+---------+-------+
1 row in set (0.01 sec)

2. 다음 값으로 테이블에 넣기

mysql> insert into enum_test(numbers) values (2), ('2') , ('3') ,('4') ,   (NULL);

3. 다음과 같은 결과가 나온다.

mysql> select * from enum_test;
+---------+
| numbers |
+---------+
| 1       |
| 2       |
| 2       |
|         |
| NULL    |
+---------+
5 rows in set (0.00 sec)

설명:

• ENUM 에 무효한 값을 삽입하면 빈 문자열 "" 이 삽입된다.
  -> 위의 2. 번의 insert into enum_test(numbers) values ('4') 의 경우 '4' 값은 1. 번의 테이블작성시 지정한 허용 값에 포함되어 있지 않음.
  따라서 '4' 를 삽입하려 하면 3. 번의 결과의 4번째 행처럼 빈문자열 "" 이 삽입되나.
  
• insert into enum_test(numbers) values (2) 처럼 숫자를 삽입하면 ENUM 의 허용 리스트의 인덱스값으로 인식된다.
  
  위 테이블 작성시 ENUM 리스트의 인덱스값은 다음과 같다.
  +------+------+
  |Values|INDEX |
  +------+------+
  | NULL | NULL |
  | ''   |   0  |
  | '0'  |   1  |
  | '1'  |   2  |
  | '2'  |   3  |
  +------+------+
  
  인덱스가 2인 값은 '1' 이므로 3. 번 결과의 첫번째 행의 값은 '1' 이되는것이다.
  
• insert into enum_test(numbers) values  ('2'); 처럼 '2' 를 삽입하면 허용리스트에 포함되어 있으므로 그대로 삽입되어 '2' 가 들어감.
  
• insert into enum_test(numbers) values ('3'); 처럼 ENUM 리스트에 포함되어 있지않은 값을 삽입하면 인덱스로 인식된다. 인덱스 값이 3인 값은 '2' 가 삽입된다.
  이런 이유로 되도록이면 ENUM 리스트 정의시 숫자식문자열을 사용하지 않을 것을 권하고 있다.
  
• insert into enum_test(numbers) values (NULL); 그대로 NULL 이 삽입됨.
  
  추가로 삽입값들의 인덱스 값을 뽑아보면 다음과 같다.
  
  
  mysql> select * , numbers+0 as indices from enum_test;
  +---------+---------+
  | numbers | indices |
  +---------+---------+
  | 1       |       2 |
  | 2       |       3 |
  | 2       |       3 |
  |         |       0 |
  | NULL    |    NULL |
  | 0       |       1 |
  +---------+---------+
  6 rows in set (0.01 sec)

SET
최대 64개의 다른 멤버를 가질 수 있음.
ENUM 하고 비슷하지만 다른 부분은 모든 요소(엘리먼트)가 사용된다.

테이블을 작성해서 예를 들어가면서 설명

테이블 작성

mysql> CREATE TABLE set_test(
            season SET('SPRING' ,'SUMMER' , 'FALL' ,'WINTER' )
                            );
Query OK, 0 rows affected (0.05 sec)

위와 같은 테이블을 작성하면 다음과 같은 값을 갖게 된다.

+----------+----------+---------+
| 요소      | 10진값   | 2진값    |
+----------+----------+---------+
| SPRING   |        1 |   0001  |
| SUMMER   |        2 |   0010  |
| FALL     |        4 |   0100  |
| WINTER   |        8 |   1000  |
+----------+----------+---------+

인서트

mysql> INSERT INTO set_test VALUES
                ('SPRING,SUMMER,FALL,WINTER') ,
                ('SPRING,SUMMER') ,
                ('SPRING') ,
                ('SUMMER') ,
                ('FALL') ,
                ('WINTER');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from set_test;
+---------------------------+
| season                    |
+---------------------------+
| SPRING,SUMMER,FALL,WINTER |
| SPRING,SUMMER             |
| SPRING                    |
| SUMMER                    |
| FALL                      |
| WINTER                    |
+---------------------------+
6 rows in set (0.00 sec)

mysql> select * , season+0 as decimals from set_test;
+---------------------------+----------+
| season                    | decimals |
+---------------------------+----------+
| SPRING,SUMMER,FALL,WINTER |       15 |
| SPRING,SUMMER             |        3 |
| SPRING                    |        1 |
| SUMMER                    |        2 |
| FALL                      |        4 |
| WINTER                    |        8 |
+---------------------------+----------+
6 rows in set (0.00 sec)

mysql> select * from set_test where season& 9;
+---------------------------+
| season                    |
+---------------------------+
| SPRING,SUMMER,FALL,WINTER |
| SPRING,SUMMER             |
| SPRING                    |
| WINTER                    |
+---------------------------+
4 rows in set (0.02 sec)

위에서 9 값은 1 + 8 이 된다. 즉  SPRING 과 WINTER 의 값을 더한 값이다.
따라서 테이블에서 SPRING 과 WINTER 값이 포함된 행들을 뽑아내면 된다.

함수 find_in_set()을 사용하는 방법.

mysql> select * from set_test where find_in_set('SPRING',season)>0;
+---------------------------+
| season                    |
+---------------------------+
| SPRING,SUMMER,FALL,WINTER |
| SPRING,SUMMER             |
| SPRING                    |
+---------------------------+
3 rows in set (0.02 sec)

LIKE 를 사용하는 방법.

mysql> select * from set_test where season LIKE '%SPRING%';
+---------------------------+
| season                    |
+---------------------------+
| SPRING,SUMMER,FALL,WINTER |
| SPRING,SUMMER             |
| SPRING                    |
+---------------------------+
3 rows in set (0.00 sec)

mysql> select * from set_test where season = 'SPRING,SUMMER';
+---------------+
| season        |
+---------------+
| SPRING,SUMMER |
+---------------+
1 row in set (0.00 sec

set 타입을 사용하면 여러가지 장점을
살릴수 있습니다.

--------------------------------------------
(초보자를 위해서 조금 길게 적었습니다.)
----------------------------------------------

우선

100 과 같은 비트단위는 문자형 타입으로 결정하려면
CHAR(50) 또는 VARCHAR(50) 으로 지정해야 하는데 크기가 50 Bytes라서
부적절합니다.

반면 숫자형 타입으로 결정하려면 BIGINT(50) UNSIGNED ZEROFILL 으로
해야하는데 이때는 크기가 8 Bytes 입니다.
(INTEGER 로서는 2^50 = 1,125,899,906,842,624 의 크기를 모두 담을 수 없습니다.)
참고로 BIGINT 는 최대 2^63 까지 가능합니다.

숫자형 타입으로 또한 DOUBLE 형도 가능하는데 이들
숫자형 타입은 모두 좀 알아보기가 힘들다는 단점이 있습니다.

따라서 제일 적절한 타입은 SET 타입으로 64개의 리스트 즉,
최대 2^64 이라는 경우의 수를 만들 수 있습니다.
크기는 숫자형타입과 마찬가지로 리스트 개수(L) 이 32 < L < 64 이면
8 Bytes 입니다.

SET 타입의 장점으로는
그 리스트를 bit 형 숫자 또는 문자열 모두 또는 이들 두개의 조합으로도
가능하기 때문에 잘 구성하면 테이블에서 직접 눈으로 쉽게 판별할수도 있습니다.

예를들어, 위의 경우라면,

colanme SET('1','2','3','4','5',...) DEFAULT ...
또는
colanme SET('1','2','4','8','16',...) DEFAULT ...       <---- bit
또는
colanme SET('1st','2st','3st','4st','5st',...) DEFAULT ...
또는
colanme SET('a','b','c','d','e',...) DEFAULT ...
...

등등 여러가지 형태로 가능하고 실제로 이들은 내부적으로
모두 두번째 예인 비트단위로 DB 에 저장됩니다.

4번째의 경우라면

a => 1
b => 2
c => 4
d => 8
e => 16

이와같이 내부는 실제의 문자나 문자열로 연산하지 않고 오른쪽의 비트단위인 숫자로
증가하면서 저장되고 숫자형으로 비교하기 때문에 상당히 빠른다는 장점이
있습니다.

참고로, ENUM 은 1씩 증가하고 택일이지만, SET 타입은 비트 단위로 증가하고
다중선택이 가능합니다. 물론 크기는 ENUM 이 더 작습니다.

SET 타입의 자료입력은

예)
INSET INTO SET ... colname = 'a';
INSET INTO SET ... colname = 'a,b';
INSET INTO SET ... colname = 'b,a'; // 순서가 바뀌면 a,b 순서로 자동으로 저장
INSET INTO SET ... colname = 'd,e';
INSET INTO SET ... colname = 'b,c,d';

이와 같은 형식으로 자료를 입력하면 됩니다.

이들을 각각

'a'     => 1
'a,b'   => 1 + 2 = 3
'd,e'   => 8 + 16 = 24
'b,c,d' => 2 + 4 + 8 = 14

이렇게 내부적인 비트단위로 저장되고 연산됩니다.
출력은 비트단위의 숫자가 아닌 원래의 저정한 형태의 자료형으로
출력됩니다.

검색부분에 대해서도 숫자형 BIGINT 와 같이 숫자형 연산도
가능할 뿐더러 문자열 비교도 할 수 있습니다.

BININT 같은 경우도 LIKE 와 같은 문자열 비교 연산도 가능하지만
잘못된 결과가 나올 수 있기 때문에 역시 곤란합니다.
반면 SET 타입은 문자열로도 취급가능하므로 LIKE 에 대한 잘못된
결과가 나오는 경우는 없습니다.

굳지 예를 들자면(문자형태로 저장되었을 경우),

1) 문자열로 취급할 경우

SELECT ... WHERE colname = 'a,b'; // a 와 b 만 선택한 경우
SELECT ... WHERE colname LIKE 'a%'; // 최소한 a 를 선택한 경우
SELECT ... WHERE colname IN('a','b'); // a 또는 b 만 선택한 경우
SELECT ... WHERE FIND_IN_SET('a',colname); // 최소한 a 를 선택한 경우
SELECT ... WHERE INSTR(colname,'a'); // 최소한 a 를 선택한 경우
...

2) 비트의 숫자형으로 취급할 경우

SELECT ... WHERE colname = 1; // 1 = 2^0 이므로 첫번째인 a 만 선택한 경우임
SELECT ... WHERE colname >= 1; // 최소한 a 를 선택한 경우
SELECT ... WHERE colname = 3; // 이것은 a 와 b 만 선택한 경우임
SELECT ... WHERE


3) 비트단위로 취급할 경우(bit 연산)

SELECT ... WHERE colname & 1; // 최소한 a 를 선택한 경우
SELECT ... WHERE colname & 3; // 최소한 a 또는 b를 선택한 경우(간단한 OR 연산에 이용)

이와 같이 여러가지 형태로 SQL 문을 만들 수 있다는 것이 그 장점중의 하나입니다.

 

출처 : http://woong.koong.net/index.php?MenuID=4&cat=DB&list_count=30&mode=view&idx=753