[Mysql] 쿼리문 내에서 encoding 변경...

2가지 방법... 1. convert 또는 case 함수 이용... ??  2. 이노성(lnsium) 님의 모듈이용.

1.

출처 : http://dev.mysql.com/doc/refman/5.1/en/charset-convert.html

9.1.9.2. CONVERT() and CAST()

CONVERT() provides a way to convert data between different character sets. The syntax is:

CONVERT(expr USING transcoding_name)

In MySQL, transcoding names are the same as the corresponding character set names.

Examples:

SELECT CONVERT(_latin1'Müller' USING utf8);

INSERT INTO utf8table (utf8column)

    SELECT CONVERT(latin1field USING utf8) FROM latin1table;

CONVERT(... USING ...) is implemented according to the standard SQL specification.

You may also use CAST() to convert a string to a different character set. The syntax is:

CAST(character_string AS character_data_type CHARACTER SET charset_name)

Example:

SELECT CAST(_latin1'test' AS CHAR CHARACTER SET utf8);

If you use CAST() without specifying CHARACTER SET, the resulting character set and collation are defined by the character_set_connection and collation_connection system variables. If you use CAST() with CHARACTER SET X, the resulting character set and collation are X and the default collation of X.

You may not use a COLLATE clause inside a CONVERT() or CAST() call, but you may use it outside. For example, CAST(... COLLATE ...) is illegal, but CAST(...) COLLATE ... is legal:

SELECT CAST(_latin1'test' AS CHAR CHARACTER SET utf8) COLLATE utf8_bin;

2.
출처 : http://database.sarang.net/?inc=read&aid=25074&criteria=mysql&subcrit=&id=&limit=&keyword=&page=

안녕하세요. fedora core 2가 나온 기념으로 udf 하나 만들어 봤습니다.

예전에 문자셋 인코딩 어떻게 하는건지 몰라 헤맸는데 iconv이라는 라이브러리가 있다는 것을 최근에 알았답니다..-.-;;

 

글구 fedor core 2가 문자셋을 utf-8로 전환함에 따라 mysql client에서 udf-8로 저장된 데이타는 깨져서 나오더군요. 그래서 iconv UDF가 필요하게 됐답니다. phpMyAdmin는 알아서 iconv를 적용하더군요. 정말 phpMyAdmiin은 짱입니다..-.-;;

 

g++ -shared -o mysql_udf_iconv iconv.so iconv.cpp -I/usr/include/mysql -liconv (컴파일)

 

mysql_udf_iconv.so를 /usr/lib/에 옮겨둡니다.

 

create function ICONV returns string soname "mysql_udf_iconv.so" (함수등록)

 

select iconv(data_field, "udf-8", "euckr") from table; (함수사용)

 

테스트는 알아서...-.-;;

소스입니다.

 

#include <stdio.h>

#include <string.h>

#include <iconv.h>

#include <mysql.h>

 

extern "C" my_bool ICONV_init(UDF_INIT* initid, UDF_ARGS* args, char* message);

extern "C" void ICONV_deinit(UDF_INIT* initid);

extern "C" char* ICONV(UDF_INIT* initid, UDF_ARGS* args, char* result,

unsigned long* length, char* is_null, char* /*error*/);

 

const int BufSize = 65536; //65535(64KB)+1

static iconv_t cd;

 

my_bool ICONV_init(UDF_INIT* initid, UDF_ARGS* args, char* message)

{

enum Item_result* type = args->arg_type;

 

if(args->arg_count != 3 || type[0] != STRING_RESULT || type[1] != STRING_RESULT || type[2] != STRING_RESULT)

{

strcpy(message,"Wrong arguments to iconv");

return 1;

}

 

cd = iconv_open((const char *)args->args[1], (const char *)args->args[2]);

if(cd == (iconv_t)-1)

{

strcpy(message, "iconv_open() failed");

return 1;

}

 

initid->ptr = new char[BufSize];

if(!initid->ptr)

{

strcpy(message, "Memory alloc failed");

return 1;

}

initid->max_length = BufSize;

return 0;

}

 

void ICONV_deinit(UDF_INIT* initid)

{

iconv_close(cd);

if(initid->ptr)

delete[] initid->ptr;

}

 

char* ICONV(UDF_INIT* initid, UDF_ARGS* args, char* result,

unsigned long* length, char* is_null, char* /*error*/)

{

const char* inBuf = args->args[0];

size_t inByteLeft = args->lengths[0];

char* outBuf = initid->ptr;

size_t outByteLeft = BufSize;

size_t ret;

 

while(1)

{

ret = iconv(cd, &inBuf, &inByteLeft, &outBuf, &outByteLeft);

if(!inByteLeft)

break;

if(ret == (size_t)-1)

break;

}

 

*length = BufSize - outByteLeft;

return initid->ptr;

}

 

짧죠?

버그 나오면 알아서 고쳐주시고 저한텐 알려주지 마세요..ㅠ.ㅠ