关于php中使用odbc或者pdo连接sqlserver时如果查询条件中存在中文会导致查询失败的问题

来源：互联网发布：rete 算法编辑：程序博客网时间：2024/06/10 07:28

这两天，可以说三天，一直在调试一段程序，或者一个功能，就是查询sqlserver2005/2008数据库，这段程序很简单，就是查询条件中包含了中文：

我使用的是thinkphp框架，代码如下：

        $m_id = '大商家';        $result = D('SqlServer')->table('shopname')->where(array('shopname'=>$m_id))->field('id,shopid,shopname')->order('id desc')->find();

返回的结果，$result为null

如果我将查询条件换成int类型时，则结果正常，例如：

        $shopid='1100';        $result = D('SqlServer')->table('shopname')->where(array('shopid'=>$shopid))->field('id,shopid,shopname')->order('id desc')->find();

这时是可以正确查询出结果的。

所以PHP对数据库的连接应该是没有问题的，那么问题可能是出在了中文的编码上，于是我打算把查询的中文进行编码，因为sqlserver默认编码好像是gb2312，所以我这样：

        $m_id = '大商家';        $m_id = iconv('utf-8','gb2312',$m_id);        $result = D('SqlServer')->table('shopname')->where(array('shopname'=>$m_id))->field('id,shopid,shopname')->order('id desc')->find();

但是结果仍然是null，我甚至尝试将iconv换过来，变成iconv('gb2312',utf-8',$m_id)，或者换成gbk：iconv('gbk',utf-8',$m_id)，或者最后都用了mb_convert_encoding($m_id, 'utf8', 'utf8, gbk, gb2312')和mb_convert_encoding($m_id, 'gbk', 'utf8, gbk, gb2312');

都不行，然后我最后甚至怀疑整个sql可能都要进行转码，于是我这样了：

        $sql = "select * from shopname where shopname='大商家'";        $sql = mb_convert_encoding($sql, 'utf8', 'utf8, gbk, gb2312');        $result = D('SqlServer')->query($sql);

可以看出来为了正确的结果我已经没有原则了，但是，得出的结果还是：null

到底是哪里出了问题呢？？？？

在同事的提醒下，我想到了可能是odbc或者pdo驱动在源头上就有问题，于是我开始查看我的freetds驱动配置：freetds.conf

于是我去查询，结果如下：

Last login: Thu Aug 7 11:11:17 on ttys001

AndyMacBookPro:~ andy$ sudo find / -name freetds.conf

find: /dev/fd/3: Not a directory

find: /dev/fd/4: Not a directory

/Users/andy/Downloads/freetds-0.91/freetds.conf

/usr/local/Cellar/freetds/0.91/etc/freetds.conf

/usr/local/etc/freetds.conf

/usr/local/freetds/etc/freetds.conf

AndyMacBookPro:~ andy$

于是我修改了这个下面这个文件：

AndyMacBookPro:~ andy$ sudo vim /usr/local/freetds/etc/freetds.conf

我在配置文件中添加了红字的一行：

# $Id: freetds.conf,v 1.12 2007/12/25 06:02:36 jklowden Exp $

# 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 = 4.2

# 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

client charset = UTF-8

# If you get out-of-memory errors, it may mean that your client

# is trying to allocate a huge buffer for a TEXT field.

# Try setting 'text size' to a more reasonable limit

text size = 64512

# A typical Microsoft server

[dev]

host = 192.168.100.79

port = 1433

tds version = 7.0

[TEST1dsn]

host = aa.bb.zz

port = 1433

tds version = 7.0

然后保存退出刷新页面，得到的仍然是：null

然后我就认为错误不是出在这里，然后又去找其他原因，还是无果，直到第二天，我想起了我查询时包含了两个位置的freetds.conf配置：

AndyMacBookPro:~ andy$ ll /usr/local/etc/freetds.conf

lrwxr-xr-x 1 andy admin 39 7 1 13:08 /usr/local/etc/freetds.conf@ -> ../Cellar/freetds/0.91/etc/freetds.conf

AndyMacBookPro:~ andy$

其中这两个配置的是同一个，只不过是一个软链接，然后我本地电脑上安装的freetds是用brew安装的，所以正常的freetds应该是上图这个，而不是下图这个：

AndyMacBookPro:~ andy$ ll /usr/local/freetds/etc/freetds.conf

-rw-r--r--@ 1 root admin 1071 8 6 17:06 /usr/local/freetds/etc/freetds.conf

AndyMacBookPro:~ andy$

这个只是我本地自己安装的一个freetds而已，没有起到任何作用，我之前修改的就是它。。。。。。

于是我去修改：

AndyMacBookPro:~ andy$ sudo vim /usr/local/etc/freetds.conf

内容如下（添加了红字部分）：

# $Id: freetds.conf,v 1.12 2007/12/25 06:02:36 jklowden Exp $

# 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 = 4.2

# 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

# If you get out-of-memory errors, it may mean that your client

# is trying to allocate a huge buffer for a TEXT field.

# Try setting 'text size' to a more reasonable limit

text size = 64512

client charset = UTF-8

# A typical Microsoft server

[TEST1dsn]

host = aa.bb.zz

port = 1433

tds version = 8.0

[TEST2dsn]

host = 192.168.10.81

port = 1433

tds version = 7.0

[prddsn]

host = aa.bb.zz

port = 1433

tds version = 7.0

保存退出，重新刷新页面，天呢，结果出来了~~~

SELECT T1.* FROM (SELECT ROW_NUMBER() OVER ( ORDER BY id desc) AS ROW_NUMBER, thinkphp.* FROM (SELECT id,shopid,shopname FROM shopname WHERE ( shopname = '大商家' )) AS thinkphp) AS T1 WHERE (T1.ROW_NUMBER BETWEEN 1 AND 1)

array (size=4)  'ROW_NUMBER' => string '1' (length=1)  'id' => string '2049' (length=4)  'shopid' => string '1100' (length=4)  'shopname' => string '大商家' (length=9)

3天啊~~~

就是因为中文字符编码的问题~~~

加上两个文件这么有迷惑性，自己也没看清和确认~~~

这种错误，以后要切记。

0 0