MSSQL - PHP 일본한자 데이터 출력

목적: 인코딩이 CP949인 MSSQL 데이터를 UTF-8 환경인 PHP에서 출력하고 PostgreSQL로 이관
환경 : CentOS 7.6, PHP 7.2.20, PostgreSQL 9.6.14, Laravel Framework 5.6.26

MSSQL 버전 확인

SELECT @@VERSION;

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1790.0 (X64)
Apr 22 2011 11:55:34
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

MSSQL 데이터베이스 확인

EXEC sp_helpdb 'dico';

name = dico
db_size : 1996.38 MB
owner = HP02\Administrator
dbid : 7
created : 01 31 2018
status = Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=661, Collation=Korean_Wansung_CI_AS, SQLSortOrder=0, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics
compatibility_level = 90

MSSQL 컬럼 인코딩 확인

SELECT column_name, data_type, character_set_name, collation_name FROM information_schema.columns where table_name = 'member';

addr, nvarchar, UNICODE, Korean_Wansung_CI_AS

Korean_Wansung_CI_AS

• Korean_Wansung : 한글 완성형. encoding = CP949
• C : 대소문자 구분 여부
• A : 악센트 구분 여부
• K : 히라가나, 가타카나 구분 여부
• W : 전박, 반각 구분 여부
• S (Sensitive) : 활성화 여부 'YES'
• I (Insensitive) : 활성화 여부 'NO'

=> CS : 대소문자 구분하여 소문자를 앞에 정렬
=> AS : 악센트를 구분하여 정렬

조회

MSSQL은 기본적으로 varchar가 아닌 nvarchar로 저장되고 조회한다.

SELECT CONVERT(varbinary(MAX), name) AS name FROM member;

varbinary, ntext를 출력하려고 하면 오류 발생

SQLSTATE[HY000]: General error: 20018 Unicode data in a Unicode-only collation or ntext data cannot be sent to clients using DB-Library (such as ISQL) or ODBC version 3.7 or earlier.

해결 방법

• MSSQL 서버 정보 파일(freetds.conf) 수정

# sudo vi /etc/freetds.conf
...
[global]
;       tds version = 4.2
tds version = 8.0
client charset = UTF-8

출력

.env

DB_CONNECTION=mssql
DB_HOST=112.119.93.501
DB_PORT=3927
DB_DATABASE=dico
DB_USERNAME=dico
DB_PASSWORD=dico123!!

config/database.php

'connections' => [
	'dico' => [
		'driver' => 'sqlsrv',
		'host' => env('DB_HOST'),
		'port' => env('DB_PORT'),
		'database' => env('DB_DATABASE'),
		'username' => env('DB_USERNAME'),
		'password' => env('DB_PASSWORD'),
		'charset' => 'cp949',
		'collation' => 'korean_wansung_ci_as',
		'prefix' => '',
	],
],

migration.php

public function main()
{
	$conn = DB::connection('dico');
	$tableName = 'member';
	$all = Self::getAllData($conn, $tableName, 'no');
	foreach($all as $key => $data)
        {
		Log::debug($data['name']);
	}
}


public function getAllData($conn, $tableName, $order='')
{
	// 전체 조회 쿼리문 생성
	$sql = Self::getColumnString($conn, $tableName);
	$notBinary = Self::getColumnString($conn, $tableName, false);

	$tableName = 'dico.dbo.'.$tableName;
	$tableName = Self::jpEncode($tableName);

	// 데이터 조회
	$query = $conn->table($tableName)
			->select(DB::raw($sql));
	if(!empty($order))
	{
		$query = $query->orderBy(Self::jpEncode($order));
	}
	$res = $query->get();
	foreach($res as $key => $val)
	{
		foreach($val as $col => $v)
		{
			$col = Self::jpDecode($col);
			if(empty($notBinary[$col]))
			{
				try {
					$v = Self::binaryDecode($v);
				} catch(\Exception $e) {
					Log::debug('------------------------- binaryDecode ERROR : '.$col);
					$v = Self::jpDecode($v);
				}
			}
			else
			{
				$v = Self::jpDecode($v);
			}

			$data[$key][$col] = trim($v);
		}
	}

	if(isset($data))
	{
		return $data;
	}
	else
	{
		return '';
	}
}

public function getColumnString($conn, $tableName, $flag=true)
{
	$res = $conn->table('information_schema.columns')
			->select('column_name', 'data_type', 'character_set_name', 'collation_name')
			->where('table_name', Self::jpEncode($tableName))
			->get();
	foreach($res as $key => $val)
	{
		$val->column_name = Self::jpDecode($val->column_name);

		if($val->data_type === 'nvarchar' && $val->character_set_name === 'UNICODE' && $val->collation_name === 'Korean_Wansung_CI_AS')
		{
			$arrColumn[] = 'CONVERT(varbinary(max), '.$val->column_name.') as '.$val->column_name;
		}
		else
		{
			$arrColumn[] = $val->column_name;
			$arrNaV[$val->column_name] = 1;
		}
	}

	if(isset($arrColumn))
	{
		if($flag)
		{
			$sql = implode(',', $arrColumn);
			return Self::jpEncode($sql);
		}
		else
		{
			return $arrNaV;
		}
	}
	else
	{
		return '';
	}
}

/**
*	UTF-16 : 가변길이 유니코드 인코딩. 2byte, 4byte로 표현
*/
public function binaryDecode($str)
{
	return mb_convert_encoding($str, 'UTF-8', 'UTF-16LE');
}

/**
*	CP949(MS949) : EUC-KR 확장형. 하위 호환성 있음
*/
public function jpEncode($str)
{
	return mb_convert_encoding($str, 'CP949', 'UTF-8');
}

public function jpDecode($str)
{
	return mb_convert_encoding($str, 'UTF-8', 'CP949');
}