PHP PDO 读取 MSSQL 乱码

Question

首先看一下freeTDS配置:

#
# This file is installed by FreeTDS if no file by the same
# name is found in the installation directory.
#
# For information about the layout of this file and its settings,
# see the freetds.conf manpage "man freetds.conf".

# Global settings are overridden by those in a database
# server specific section
[global]
        # TDS protocol version
        tds version = 8.0

        # Whether to write a TDSDUMP file for diagnostic purposes
        # (setting this to /tmp is insecure on a multi-user system)
;       dump file = /tmp/freetds.log
;       debug flags = 0xffff

        # Command and connection timeouts
;       timeout = 10
;       connect timeout = 10

        # To reduce data sent from server for BLOBs (like TEXT or
        # IMAGE) try setting 'text size' to a reasonable limit
;       text size = 64512

        # If you experience TLS handshake errors and are using openssl,
        # try adjusting the cipher list (don't surround in double or single quotes)
        # openssl ciphers = HIGH:!SSLv2:!aNULL:-DH

# A typical Sybase server
[egServer50]
        host = symachine.domain.com
        port = 5000
        tds version = 5.0

# A typical Microsoft server
[SqlServer]
        host = 10.30.16.14
        port = 1433
        tds version = 8.0
        client charset = UTF-8

再看看 ODBC:

[ODBC Data Sources]
ppc= ppc sql server

[SqlServer]
Driver          = FREETDS
Description     = ppc sql server
Trace           = No
Servername      = SqlServer
Database        = medexmemrs

再看看代码：

try {
       $conn = new PDO("odbc:SqlServer", "sa", "sa");
       $conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
       $conn->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
       $conn->setAttribute(PDO::ODBC_ATTR_ASSUME_UTF8, true);

     } catch (PDOException $e) {
        log_message('error', '数据库连接失败：'.$e->getMessage(), 'get_pacs_img');
       exit;
   }

   $sql = "select * from dbo.TJ_Report where status = '已检查' and union_request_no = '$request_no'";

   log_message('debug', '[union_request_no]:'.$request_no.'; 查询SQL: '.$sql, 'get_pacs_img');
    $query = $conn->query($sql);

    $pacs_data = $query->fetch();

    if(! $pacs_data) {
      return [];
    }
    
    var_dump($pacs_data);

显示结果：

array(19) {
  ["cust_name"]=>
  string(6) "��^^t"
  ["status"]=>
  string(9) "已检�"
  ["sa"]=>
  string(24) "TJ2111220019"
  ["laiyuan"]=>
  string(8) "SO�h-N�_"
  ["studyclass0"]=>
  string(11) "CT核磁�"
  ["patient_id"]=>
  string(0) ""
  ["union_request_no"]=>
  string(26) "2111220019323"
  ["union_code"]=>
  string(0) ""
  ["union_name"]=>
  string(0) ""
  ["examiner_id"]=>
  string(10) "01113"
  ["examiner_name"]=>
  string(0) ""
  ["examiner_time"]=>
  string(23) "2021-11-22 14:48:52.000"
  ["reviewer_id"]=>
  string(10) "01113"
  ["reviewer_name"]=>
  string(4) "Ng�e"
  ["reviewer_time"]=>
  string(23) "2021-11-22 16:46:36.077"
  ["check_desc"]=>
  string(317) "采用5mm层厚作横轴位从肺尖至肺底连续扫描示：两肺纹理分布规则，肺实质内未见明显异常密度影，肺门结构清晰，气管通畅，纵隔结构无移位，气管及大血管形态、大小正常，间隙清晰，未见异常肿块及肿大淋巴结。胸腔未见积液。



"
  ["check_conclusion"]=>
  string(38) "两肺及纵隔未见明显异常。
"
  ["abnormal_flags"]=>
  string(6) "正常"
  ["report_data"]=>
  string(190) "ftp://10.30.16.14:3030//Report/ImageData/2111221448572662/211122144857266220211122161743801.jpg"
}

试着使用mb_convert_encoding($pacs_data, 'UTF-8', 'GBK') 结果：

array(19) {
  ["cust_name"]=>
  string(8) "闂鱚^t"
  ["status"]=>
  string(13) "宸叉鏌"
  ["浣撴鍙"]=>
  string(24) "TJ2111220019"
  ["laiyuan"]=>
  string(10) "SO纇-N胈"
  ["studyclass0"]=>
  string(13) "CT鏍哥"
  ["patient_id"]=>
  string(0) ""
  ["union_request_no"]=>
  string(26) "2111220019323"
  ["union_code"]=>
  string(0) ""
  ["union_name"]=>
  string(0) ""
  ["examiner_id"]=>
  string(10) "01113"
  ["examiner_name"]=>
  string(0) ""
  ["examiner_time"]=>
  string(23) "2021-11-22 14:48:52.000"
  ["reviewer_id"]=>
  string(10) "01113"
  ["reviewer_name"]=>
  string(5) "Ng宔"
  ["reviewer_time"]=>
  string(23) "2021-11-22 16:46:36.077"
  ["check_desc"]=>
  string(470) "閲囩敤5mm灞傚帤浣滄í杞翠綅浠庤偤灏栬嚦鑲哄簳杩炵画鎵弿绀猴細涓よ偤绾圭悊鍒嗗竷瑙勫垯锛岃偤瀹炶川鍐呮湭瑙佹槑鏄惧紓甯稿瘑搴﹀奖锛岃偤闂ㄧ粨鏋勬竻鏅帮紝姘旂閫氱晠锛岀旱闅旂粨鏋勬棤绉讳綅锛屾皵绠″強澶ц绠″舰鎬併€佸ぇ灏忔甯革紝闂撮殭娓呮櫚锛屾湭瑙佸紓甯歌偪鍧楀強鑲垮ぇ娣嬪反缁撱€傝兏鑵旀湭瑙佺Н娑层€



"
  ["check_conclusion"]=>
  string(56) "涓よ偤鍙婄旱闅旀湭瑙佹槑鏄惧紓甯搞€
"
  ["abnormal_flags"]=>
  string(9) "姝ｅ父"
  ["report_data"]=>
  string(190) "ftp://10.30.16.14:3030//Report/ImageData/2111221448572662/211122144857266220211122161743801.jpg"
}

MSSQL 排序规则： Chinese_PRC_CI_AS

SELECT COLLATIONPROPERTY( 'chinese_prc_ci_as', 'codepage' ); //936

不知道各位有没有遇到过！ 3>