DB 데이터 타입 - 문자열편

15.1 문자열

CHAR와 VARCHAR 타입의 차이는?

공통점으로 문자열을 저장할 수 있는 데이터 타입
차이점 : 고정 길이와 가변 길이의 차이
고정 길이는 실제 입력되는 컬럼 값의 길이에 따라 사용하는 저장 공간의 크기가 변하지 않는다. CHAR 타입은 이미 저장 공간의 크기가 고정적이다. 실제 저장된 값의 유효 크기가 얼마인지 별도로 저장할 필요가 없으므로 추가로 공간이 필요하지 않는다.
가변 길이는 최대로 저장할 수 있는 값의 길이는 제한되어 있지만 그 이하 크기의 값이 저장되면 그만큼 저장공간이 줄어든다. 하지만 VARCHAR 타입은 저장된 값의 유효크기가 얼마인지를 별도로 저장해 둬야 하므로 1~2바이트의 저장 공간이 추가로더 필요하다.
VARCHAR 타입의 최대 길이는 65,536 이상으로 설정할 수 없다.
문자열 값의 길이가 항상 일정하다면 CHAR을 사용하고 가변적이라면 VARCHAR을 사용하는 것이 일반적이다.
CHAR 타입은 유효값은 유효값대로 채워지고 남은 바이트 수 만큼 공백으로 채워진다.
VARCHAR 타입은 첫번째 바이트에 저장된 칼럼 값의 유효 바이트 수가 저장되고 두 번째 바이트부터 실제 값이 저장된다.
VARCHAR 타입에서 현재 들어가 있는 값의 길이 보다 더 큰 값으로 변경될 때에는 레코드 자체를 다른 공간으로 옮기거나 컬럼 값의 나머지 부분을 다른 공간에 저장해야 한다.

CHAR 타입과 VARCHAR 타입을 결정할 때의 판단 기준

저장되는 문자열의 길이가 대개 비슷한가?
컬럼의 값이 자주 변경되는가?

예시

주민등록번호 - CHAR
부서 번호, 게시물의 상태 값 - CHAR

문자열 데이터 타입을 사용할 때 주의사항

CHAR나 VARCHAR 키워드 뒤에 인자로 전달하는 숫자 값의 의미를 알아야 한다.
ex) CHAR(4)
MySQL에서 CHAR나 VARCHAR 뒤에 지정하는 숫자는 문자의 수를 의미한다.
CHAR(10) 타입을 사용하더라도 이 칼럼이 실제적으로 디스크나 메모리에서 사용하는 공간은 각각 달라진다.

15.1.2 비교 방식

MySQL에서 문자열 칼럼을 비교하는 방식은 CHAR와 VARCHAR가 거의 같다. CHAR 타입의 칼럼을 SELECT를 실행했을 때 다른 DBMS처럼 사용되지 않는 공간에 공백 문자가 채워져서 나오지 않는다.
MySQL에서는 CHAR 타입이나 VARCHAR 타입을 비교할 때 칼럼 값의 뒷쪽에 붙은 공백 문자는 모두 제거하고 비교를 수행한다.
문자열 비교의 경우 예외적으로 LIKE를 사용한 문자열 패턴 비교에서는 공백 문자가 유효 문자로 취급된다.

15.1.3 문자집합(캐릭터 셋)

각 테이블의 컬럼은 모두 서로 다른 문자집합을 사용해 문자열 값을 저장할 수 있다.
문자집합은 문자열을 저장하는 CHAR와 VARCHAR, TEXT 타입에만 설정할 수 있다.
즉, 테이블의 문자집합을 UTF-8로 설정하면 칼럼의 문자집합을 별도로 지정하지 않아도 해당 테이블에 속한 컬럼은 UTF-8 문자집합을 사용한다. 물론 테이블의 기본 문자집합이 UTF-8이라 하더라도 각 칼럼에 대해 문자집합을 EUC-KR이나 ASCII 등으로 별도로 지정할 수 있다.
- 한글 기반의 서비스에서는 euckr 또는 utf8 문자집합을 사용하며, 일본어인 경우에는 cp932 또는 utf8을 적용하는 것이 일반적이다. 한글 윈도우에서 사용되는 MS949는 UTF-KR보다 조금 확장된 형태의 문자집합으로 유닉스 계열의 운영체제에서 사용하는 CP949와 똑같은 문자집합이다.
- 최근의 웹 서비스나 스마트폰 앱은 여러 나라의 언어를 동시에 지원하기 위해 기본적으로 UTF-8을 많이 사용하는 추세다.
MySQL 서버에서 사용 가능한 문자집합은 “SHOW CHARACTER SET” 명령으로 확인해 볼 수 있다.

기본 콜레이션

칼럼에 콜레이션은 명시하지 않고, 문자집합만 지정했을 때 설정도는 콜레이션
character_set_system
- MySQL 서버가 식별자를 저장할 때 사용하는 문자집합이다. 이 값은 항상 utf8로 설정되며, 사용자가 설정하거나 변경할 필요가 없다.
character-set-server
- MySQL 서버의 기본 문자집합이다. DB나 테이블 또는 칼럼에 아무런 문자집합이 설정되지 않을 때 이 시스템 설정 변수에 명시돈 문자집합이 기본으로 사용된다.
character_set_database
- MySQL DB의 기본 문자집합이다. DB를 생성할 때 아무런 문자집합이 명시되지 않았다면 이 시스템 변수에 명시된 문자집합이 기본값으로 사용된다. 만약 이 변수가 정의되지 않으면 character-set-server 설정 변수에 명시된 문자집합이 기본으로 사용된다.
character_set_filesystem
- LOAD DATA INFILE … 또는 SELECT … INTO OUTFILE 문장을 실행할 때 인자로 지정되는 파일의 이름을 해석할 때 사용되는 문자집합이다. 여기서 주의해야 할 것은 데이터 파일의 내용을 읽을 때 사용하는 문자집합이 아니라, 파일의 이름을 찾을 대 사용하는 문자집합이다. 이 설정 값은 각 커넥션에서 임의의 문자집합으로 변경해서 사용할 수 있다.
character-set_client
- MySQL 클라이언트가 보낸 SQL 문장은 character_set_client에 설정된 문자집합으로 인코딩해서 MySQL 서버로 전송한다. 이 값은 각 커넥션에서 임의의 문자집합으로 변경해서 사용할 수 있다.
character_set_connection
- MySQL 서버가 클라이언트로부터 전달받은 SQL 문장을 처리하기 위해 character_set_connection의 문자집합으로 변환한다. 또한 클라이언트로부터 전달받은 숫자 값을 문자열로 변환할 때도 character_set_connection에 설정된 문자집합이 사용된다. 이 변수 값 또한 각 커넥션에서 임의의 문자집합으로 변경해서 사용할 수 있다.
character_set_results
- MySQL 서버가 쿼리의 처리 결과를 클라이언트로 보낼 때 사용하는 문자집합을 설정하는 시스템 변수다. 이 시스템 변수도 각 커넥션에서 임의의 문자집합으로 변경해서 사용할 수 있다.

클라이언트로부터 쿼리를 요청했을 때의 문자집합 변환

MySQL 서버는 클라이언트로부터 받은 메시지가 character_set_client에 지정된 문자집합으로 인코딩돼 있다고 판단하고, 받은 문자열 데이터를 character_set_connection에 정의된 문자집합으로 변환한다. 하지만 SQL 문장에 별도의 문자집합이 지정된 문자열은 변환 대상에 포함하지 않는다.
SQL 문장에서 별도로 문자집합을 설정하는 지정자를 “인트로듀서“라고 한다.
- SELECT emp_no, first_name FROM employees WHERE first_name=’Smith’;
- - character_set_connection으로 문자집합이 변환된 이후 처리 된다.
- SELECT emp_no, first_name FROm employees WHERE first_name = _latin1’Smith’;
- - 인트로듀서(_latin1)가 사용됐으므로 “DRF” 문자열은 character_set_connection이 아니라 latin1 문자집합으로 fd1 칼럼의 값과 비교가 실행된다. 일반적으로 인트로 듀서는 “_문자셋이름”과 같이, 문자열 앞에 언더스코어 기호와 문자집합의 이름을 붙여서 표현한다.

처리 결과를 클라이언트로 전송할 때의 문자집합 변환

character_set_connection에 정의된 문자집합으로 변환해 SQL을 실행한 다음, MySQL 서버는 쿼리의 결과를 character_set_results 변수에 설정된 문자집합으로 변환해 클라이언트로 전송한다.
이때 결과 셋에 포함된 칼럼의 값이나 칼럼명과 같은 메타 데이터도 모두 character_set_results로 인코딩되어 클라이언트로 전송된다.
변환 전의 문자집합과 변환해야할 문자집합이 똑같다면 별도의 문자집합 변환 작업을 모두 생략한다.
SET names : 현재 접속된 커넥션에서만 유효하다.
CHARSET 명령은 같은 프로그램에서 재접속할 때도 문자집합 설정이 유효하도록 만들어 준다.

15.1.4 콜레이션(Collation)

문자열 칼러므이 값에 대한 비교나 정렬 순서를 위한 규칙을 의미한다. 즉 비교나 정렬 작업에서 영문 대소문자를 같은 것으로 처리할 지 아니면 더 크거나 작은 것으로 판단할 지에 대한 규칙을 정의하는 것이다.
MySQL의 모든 문자열 타입의 칼럼은 독립적인 문자집합과 콜레이션을 가진다. 각 칼럼에 대해 독립적으로 문자집합이나 콜레이션을 지정하든 그렇지 않든 독립적인 문자집합과 콜레이션을 가지는 것이다. 각 칼럼에 대해 독립적으로 지정하지 않으면 MySQL 서버나 DB의 기본 문자집합과 콜레이션이 자동으로 설정된다.
콜레이션이란 문자열 칼럼의 값을 비교하거나 정렬하는 기준이 된다.
문자집합은 2개 이상의 콜레이션을 가지고 있는데, 하나의 문자집합에 속한 콜레이션은 다른 문자집합과 공유해서 사용할 수 없다. 또한 테이블이나 칼럼에 문자집합만 지정하면 해당 문자집합의 디폴트 콜레이션이 해당 칼럼의 콜레이션으로 지정된다. 반대로 칼럼의 문자집합은 지정하지 않고 콜레이션만 지정하면 그 콜레이션이 소속된 문자집합이 묵시적으로 그 칼럼의 문자집합으로 사용된다.
MySQL 서버에서 사용 가능한 콜레이션의 목록은 “SHOW CHARACTER SET” 명령을 이용해 확인할 수 있다.
콜레이션의 이름은 2개 또는 3개의 파트로 구분돼어 있다.

3개의 파트로 구성된 콜레이션 이름

첫 번째 파트는 문자집합의 이름이다.
두 번째 파트는 해당 문자집합의 하위 분류를 나타낸다.
세 번째 파트는 대문자나 소문자의 구분 여부를 나타낸다. 즉, 세 번째 파트가 “ci”이면 대소문자를 구분하지 않는 콜레이션을 의미하며, “cs”이면 대소문자를 별도의 문자로 구분하는 콜레이션이다.

2개의 파트로 구성된 콜레이션 이름

첫 번째, 문자집합의 이름
두 번째, 항상 “bin”이라는 키워드가 사용된다. 여기서 “bin”은 이진 데이터를 의미하며, 이진 데이터로 관리되는 문자열 칼럼은 별도의 콜레이션을 가지지 않는다. 콜레이션이 “xxx_bin”이라면 비교 및 정렬은 실제 문자 데이터의 바이트 값을 기준으로 수행된다.

콜레이션이 대소문자를 구분하지 않는다고 해서 실제 칼럼에 저장되는 값이 모두 소문자나 대문자로 변환되어 저장되는 것은 아니며, 콜레이션과 관계 없이 입력된 데이터의 대소문자는 별도의 변환없이 그대로 저장된다.

MySQL의 문자열 칼럼은 콜레이션 없이 문자집합만 가질 수는 없다. 콜레이션을 명시적으로 지정하지 않았다면 지정된 문자집합의 기본 콜레이션이 묵시적으로 적용된다. 그리고 문자열 칼럼의 정렬이나 비교는 항상 해당 문자열 칼럼의 콜레이션에 의해 판단하므로 문자열 칼럼에서는 CHAR나 VARCHAR와 같은 타입의 이름과 길이만 같다고 해서 똑같은 타입이라고 판단해서는 안된다. 타입의 이름과 문자열의 길이, 그리고 문자집합과 콜레이션까지 일치해야 똑같은 타입이라고 할 수 있다. 문자열 칼럼에서는 문자집합과 콜레이션이 모두 일치해야만 Join이나 Where 조건이 인덱스를 효율적으로 사용할 수 있다.

테이블을 생성할 때 문자집합이나 콜레이션을 적용하는 방법

CREATE DATABASE db_test CHARACTER SET=utf8;

CREATE TABLE tb_member (
    member_id VARCHAR(20) NOT NULL collate latin1_general_cs,
    member_name VARCHAR(20) NOT NULL COLLATE utf8_bin,
    member_email VARCHAR(100) NOT NULL
    ...
);

문자집합이나 콜레이션은 DB 수준에서 설정할 수도 있으며 테이블 수준으로 설정할 수도 있다. 그리고 마지막으로 칼럼 수준에서도 개별적으로 설정할 수 있다.

15.1.5 문자열 이스케이프 처리

MySQL에서 SQL 문장에 사용하는 문자열은 프로그래밍 언어에서처럼 “\“를 이용해 이스케이프 처리를 해주는 것이 가능하다. 즉 “\t”나 “\n”으로 탭이나 개행문자를 표시할 수 있다.
”\%”와 “ \_“는 LIKE를 사용하는 패턴 검색 쿼리의 검색어에서만 사용할 수 있다. LIKE 패턴 검색에서는 %와 _로 와일드 카드로 표현하기 위한 패턴 문자로 사용하므로 실제 %문자나 _문자를 검색하려면 역슬래시( \)를 이용해 이스케이프 처리를 해야 한다.
홑따옴표와 쌍따옾묘를 연속 두 번 표기해도 이스케이프 처리되지 않는다.