beomil ideas

10.4.3. The BLOB and TEXT Types

BLOB은 가변형 데이터를 저장하는 바이너리 대용량 객체이다. BLOB은 TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB이 있다. 이것은 저장 용량의 최대 길이에 따른 분류이다. TEXT는 TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT가 있다. TEXT 타입은 BLOB 타입과 일치하며 최대 길이와 용량도 같다.

BLOB 컬럼은 바이너리 스트링값(바이트형)을 가지며 TEXT 컬럼은 넌바이너리 스트링값(캐릭터형)을 가진다. BLOB 컬럼은 캐릭터셋이 없으며 컬럼값에서 바이트형의 숫자값으로 비교하고 정렬된다. TEXT 컬럼은 캐릭터셋을 가지며 캐릭터셋의 collation 에 따라 비교하여 정렬된 값을 갖는다.

strict SQL mode가 enable이 아니면 컬럼의 최대 길이를 초과하는 값을 BLOB이나 TEXT 컬럼에 대입할 것이고 그 값은 길이에 맞게 버림을 할 것이며 경고가 발생하게 된다. 공백이 없는 캐릭터를 자르면 (경고보다는)에러가 발생하게 되며 strict SQL mode를 사용하여 값의 삽입을 막을 수 있다.

MySQL 5.1.24의 시작에서, SQL mode와 상관없이, TEXT 컬럼에 삽입된 값에서 생성된 공백의, 초과를 자르면 항상 경고가 발생한다.

TEXT 컬럼이 인덱스되어있으면 인덱스 엔트리의 비교로 공백을 채운다. 즉, 인덱스에 유니크 값을 넣어야 하는데 동일한 키를 넣게 되면 앞서 넣은 값과는 다른 값을 넣으라고 에러가 발생한다. 예를 들자면, 테이블에 ‘a’를 넣고 다시 ‘a’를 저장하려고 하면 동일한 키로 인해 에러가 발생한다. 그러나 BOLB 컬럼에서는 그렇지 않다.

많은 값을 넣는데 대부분 BLOB 컬럼을 VARBINARY 컬럼으로 생각하기도 한다. 이와 유사하게 TEXT 컬럼을 VARCHAR 컬럼이라고 생각한다. 그러나 BLOB과 TEXT는 VARBINARY, VARCHAR와 여러 면에서 다르다.

– BLOB과 TEXT에서 인덱스의 prefix길이를 설정해야 하지만 CHAR와 VARCHAR에서는 선택이다.

– BLOB과 TEXT은 디폴트 값을 가질 수 없다.

LONG과 LONG VARCHAR는 MEDIUMTEXT 데이터 타입과 대응한다. 이것은 호환성을 말하는데, TEXT 데이터 타입의 BINARY 속성을 사용하면 컬럼은 캐릭터셋 컬럼의 binary collation으로 지정된다.

MySQL Connector/ODBC 는 BLOB 값을 LONGVARBINARY로 TEXT 값을 LONGVARCHAR로 정의한다.

BLOB과 TEXT 값은 매우 길기 때문에 두 타입을 사용할 때는 다음과 같은 제약사항이 존재한다.

– 정렬할 때는 컬럼의 최초 max_sort_length 바이트를 사용하며 디폴트 값은 1024바이트이다. 이 값은 MySQL서버를 시작할 때 설정하는 max_sort_length=N 옵션에 따라 다르다. 런타임시 max_sort_length 값의 증가를 그룹화하거나 정렬함으로서 더 큰 바이트를 나타낼 수 있다. 클라이언트는 각 세션의 max_sort_length 변수를 변경할 수 있다.

mysql> SET max_sort_length = 2000;mysql> SELECT id, comment FROM t -> ORDER BY comment;

max_sort_length 바이트 이상의 값을 나타내는데 GROUP BY나 ORDER BY를 사용하면 길이가 긴 값을 BLOB이나
TEXT 컬럼에 넣으면 컬럼 값을 고정된 길이의 객체로 변환할 수 있다. 이때 일반적으로 SUBSTRING() 함수를 사용한다. 그 예로, 다음 statement는 comment 컬럼의 2000바이트를 정렬을 뜻한다.

mysql> SELECT id, SUBSTRING(comment,1,2000) FROM t -> ORDER BY SUBSTRING(comment,1,2000);

- BLOB과 TEXT 객체의 최대 크기는 타입에 따라 결정된다. 서버와 클라이언트 간에 전송할 수 있는 최댓값은 가용메모리의 양과 communications 버퍼의 크기에 의해 결정된다. message 버퍼의 크기는 max_allowed_packet 변수로 변경할 수 있으며 서버와 클라이언트 프로그램을 위해 변경해야 한다. MySQL과 MySQLdump에서는 클라이언트측의 max_allowed packet 변수를 변경할 수 있게 한다. 또한 패킷 사이즈와 저장된 데이터 객체의 사이즈를 비교할 수 있다.

각 BLOB이나 TEXT 값은 개별적으로 할당된 객체에 의해 내부적으로 대응된다. 테이블이 개방되었을 때 컬럼에 하나씩 공간이 할당되는 것은 다른 모든 데이터 타입과는 다르다.

BLOB이나 TEXT 컬럼에서 바이너리 데이터를 미디어 파일로 저장하는 것이 나은 경우도 있다. 그러한 데이터를 처리하기에 유용한 MySQL의 string 처리함수를 찾아보는 것이다. 보안이나 여러 가지 이유로 사용된다.

Posted in MySQL