MySQL 4.0이상에서 형변환 함수, CAST(), CONVERT()

프로그래머던, 아니면 DB를 작업하는 사람이던 MySQL에서의 형변환을 항상 기대해 왔을 것이다.(Oracle과 Mssql의 이 기능이 부러웠던 것은 사실이다.) 완전하지는 않지만 꽤 쓸만한 기능들이 4.0이상 부터 추가되기 시작했다. 아래의 글은 mysql.com의 매뉴얼에서 발견하고 소개하는 것이다.


6. 형변환 함수(Cast Functions)

MySQL 4.0.2부터 추가된 함수로 CAST()와 CONVERT() 함수는 한 타입의 값을 취해서 다른 타입의 값으로 사용된다. 구문은 아래와 같다.
CAST(expression AS type)
CONVERT(expression,type)
CONVERT(expr USING transcoding_name)

타입값은 아래의 것들 중 하나가 될 것이다:
• BINARY
• CHAR
• DATE
• DATETIME
• SIGNED
• TIME
• UNSIGNED

CAST()와 CONVERT()는 4.0.2부터 가능하고, CHAR 형변환은 4.0.6부터 가능하다. CONVERT()의 USING 형식은 4.1.0부터 가능하다.

CAST()와 CONVERT(... USING ...)는 SQL-99 구문이다. USING을 사용하지 않는 CONVERT()의 형식은 ODBC 구문이다.

cast 함수는 CREATE ... SELECT 구문에서 특정 타입으로 컬럼을 생성하고자 하는데 유용하다:

CREATE TABLE new_table SELECT CAST('2000-01-01' AS DATE)

이 함수는 ENUM 컬럼을 사전순으로 정렬하는데도 유용하게 사용된다. 보통 ENUM 컬럼의 sorting은 내부 수치값을 사용하여 발생한다. 그 값을 CHAR 결과값으로 형변환하면 사전순으로 정렬된다:

SELECT enum_col FROM tbl_name ORDER BY CAST(enum_col AS CHAR);

CAST(string AS BINARY)는 BINARY string과 동일하다. CAST(expr AS CHAR)는 구문을 디폴트 캐릭터 셋을 가진 문자열로 취급한다.

NOTE: MysQL 4.0에서 DATE, DATETIME, 또는 TIME으로 CAST()를 하면 그 컬럼은 특정 타입으로만 표시되고 그러나 컬럼의 값은 바뀌지 않는다. MySQL 4.1.0에서 그 값은 사용자에게 전송될 때, 정확한 컬럼 타입으로 변환된다:

mysql> SELECT CAST(NOW() AS DATE);
-> 2003-05-26

다른 포맷으로 데이터를 추출하는데 CAST()를 사용해선 안된다. 대신 LEFT나 EXTRACT()와 같은 string 함수를 사용할 수 있다. 6.3.4 Date and Time Functions. 참고

string을 숫자값으로 형변환 하려면, 보통 아무 것도 할 필요가 없다.; 단지 문자열값을 숫자로 사용하기만 하면 된다:

mysql> SELECT 1+'1';
-> 2

만약 문자열에서 숫자를 사용하게 되면, 숫자는 자동적으로 binary string으로 변환된다.

mysql> SELECT CONCAT("hello you ",2);
-> "hello you 2"

MySQL은 signed, unsigned 64bit 값을 가진 수학적인 연산을 지원한다. 더하기(+)와 같은 수연산을 하고, 연산자 중의 하나가 unsigned integer라면, 결과는 unsigned로 나타나게 될 것이다. 이것을 SIGNED와 UNSIGNED 형변환 연산자를 이용하여 연산을 signed나 unsigned 64bit integer로 각각 형변환하는데 오버라이드할 수 있다.

mysql> SELECT CAST(1-2 AS UNSIGNED)
-> 18446744073709551615

mysql> SELECT CAST(CAST(1-2 AS UNSIGNED) AS SIGNED);
-> -1

두 연산자 모두 실수 포인트값을 가지고 있다면 결과도 실수 포인트 값이며, 위의 룰에 영향을 받지 않음을 상기하자. (문맥상, 십진수 값은 실수값으로 간주된다.)


mysql> SELECT CAST(1 AS UNSIGNED) - 2.0;
-> -1.0

수학 연산에서 문자열을 사용하고 있다면, 실수 포인트값으로 변환될 것이다.

unsigned 값의 조작은 MySQL 4.0에서 변경되어 BIGINT 값을 적절히 지원할 수 있도록 변동되었다. MySQL 4.0과 3.23(이 버전에서는 CAST()를 쓰지 못하겠지만) 모두 실행되기를 원하는 코드가 있다면, 두개의 unsigned integer 컬럼에서 뺄셈을 할 때 부호가 있는 정수값을 다음과 같은 테크닉을 사용해서 얻을 수 있다:

SELECT (unsigned_column_1+0.0)-(unsigned_column_2+0.0);

핵심은 뺄셈이 일어나기 전에 그 컬럼이 실수 포인트 값으로 형변환된다는 것이다.

MySQL 4.0으로 포팅을 할 때 예전 MySQL 어플리케이션에서 UNSIGNED 컬럼에서 문제가 발생한다면 --sql-mode=NO_UNSIGNED_SUBTRACTION 옵션을 mysqld를 시작할 때 쓸 수 있다. 그러나 이것을 쓰게 되면, BIGINT UNSIGNED 컬러 타입을 쓸 수 없다는 것은 명심해야 한다.

USING을 사용한CONVERT() 는 서로 다른 문자셋 사이의 데이터를 형변환하는데 사용된다. MySQL에서는 코드변환 이름은 상응하는 문자셋 이름과 동일하다. 예를 들어, 이 구문은 서버의 기본 문자셋의 문자열 ‘abc’를 utf8 캐릭터 셋의 문자열에 상응하도록 변환한다.

SELECT CONVERT('abc' USING utf8);