解决thinkphp6读取sqlserver报Microsoft[SQL Server]对象名 ‘information_schema.tables‘ 无效错误

先感谢思路

https://weiku.co/article/37/

需要修改thinkphp里面sqlserver数据库驱动，不要去改composer里面的东西。所以只能自己重写驱动，并且在database.php配置文件中，显示指定builder和type参数来完成非侵入式的修改。

'sqlserver' => [// 这两个配置需要指定对应的文件'builder' => '\app\common\driver\builder\FunSqlsrv','type' => '\app\common\driver\connector\FunSqlsrv',//下面配置都是正常写// 'type' => env('yifei_database.type', 'sqlsrv'),// 服务器地址'hostname' => env('yifei_database.hostname', ''),// 数据库名//不同账套不一样,需要配置一下'database' => env('yifei_database.database', ''),// 数据库用户名'username' => env('yifei_database.username', ''),// 数据库密码'password' => env('yifei_database.password', ''),// 数据库连接端口'hostport' => env('yifei_database.hostport', '1433'),// 数据库连接参数'params' => [],// 数据库编码默认采用utf8'charset' => env('yifei_database.charset', 'utf8'),// 数据库表前缀'prefix' => '',// 增加证书信任'trust_server_certificate'=>true,],

2个核心文件代码

FunSqlsrv.php

<?phpnamespace app\common\driver\builder;use think\db\Builder;use think\db\exception\DbException as Exception;use think\db\Query;use think\db\Raw;/*** Sqlsrv数据库驱动*/class FunSqlsrv extends Builder{/*** SELECT SQL表达式* @var string*/protected $selectSql = 'SELECT T1.* FROM (SELECT thinkphp.*, ROW_NUMBER() OVER (%ORDER%) AS ROW_NUMBER FROM (SELECT %DISTINCT% %FIELD% FROM %TABLE%%JOIN%%WHERE%%GROUP%%HAVING%) AS thinkphp) AS T1 %LIMIT%%COMMENT%';/*** SELECT INSERT SQL表达式* @var string*/protected $selectInsertSql = 'SELECT %DISTINCT% %FIELD% FROM %TABLE%%JOIN%%WHERE%%GROUP%%HAVING%';/*** UPDATE SQL表达式* @var string*/protected $updateSql = 'UPDATE %TABLE% SET %SET% FROM %TABLE% %JOIN% %WHERE% %LIMIT% %LOCK%%COMMENT%';/*** DELETE SQL表达式* @var string*/protected $deleteSql = 'DELETE FROM %TABLE% %USING% FROM %TABLE% %JOIN% %WHERE% %LIMIT% %LOCK%%COMMENT%';/*** INSERT SQL表达式* @var string*/protected $insertSql = 'INSERT INTO %TABLE% (%FIELD%) VALUES (%DATA%) %COMMENT%';/*** INSERT ALL SQL表达式* @var string*/protected $insertAllSql = 'INSERT INTO %TABLE% (%FIELD%) %DATA% %COMMENT%';/*** order分析* @access protected* @param Query $query 查询对象* @param mixed $order* @return string*/protected function parseOrder(Query $query, array $order): string{if (empty($order)) {return ' ORDER BY rand()';}$array = [];foreach ($order as $key => $val) {if ($val instanceof Raw) {$array[] = $this->parseRaw($query, $val);} elseif ('[rand]' == $val) {$array[] = $this->parseRand($query);} else {if (is_numeric($key)) {[$key, $sort] = explode(' ', strpos($val, ' ') ? $val : $val . ' ');} else {$sort = $val;}$sort = in_array(strtolower($sort), ['asc', 'desc'], true) ? ' ' . $sort : '';$array[] = $this->parseKey($query, $key, true) . $sort;}}return ' ORDER BY ' . implode(',', $array);}/*** 随机排序* @access protected* @param Query $query 查询对象* @return string*/protected function parseRand(Query $query): string{return 'rand()';}/*** 字段和表名处理* @access public* @param Query $query 查询对象* @param mixed $key 字段名* @param bool $strict 严格检测* @return string*/public function parseKey(Query $query, $key, bool $strict = false): string{if (is_int($key)) {return (string)$key;} elseif ($key instanceof Raw) {return $this->parseRaw($query, $key);}$key = trim($key);if (strpos($key, '.') && !preg_match('/[,\'\"\(\)\[\s]/', $key)) {[$table, $key] = explode('.', $key, 2);$alias = $query->getOptions('alias');if ('__TABLE__' == $table) {$table = $query->getOptions('table');$table = is_array($table) ? array_shift($table) : $table;}if (isset($alias[$table])) {$table = $alias[$table];}}if ($strict && !preg_match('/^[\w\.\*]+$/', $key)) {throw new Exception('not support data:' . $key);}if ('*' != $key && !preg_match('/[,\'\"\*\(\)\[.\s]/', $key)) {$key = '[' . $key . ']';}if (isset($table)) {$key = '[' . $table . '].' . $key;}return $key;}/*** limit* @access protected* @param Query $query 查询对象* @param mixed $limit* @return string*/protected function parseLimit(Query $query, string $limit): string{if (empty($limit)) {return '';}$limit = explode(',', $limit);if (count($limit) > 1) {$limitStr = '(T1.ROW_NUMBER BETWEEN ' . $limit[0] . ' + 1 AND ' . $limit[0] . ' + ' . $limit[1] . ')';} else {$limitStr = '(T1.ROW_NUMBER BETWEEN 1 AND ' . $limit[0] . ")";}return 'WHERE ' . $limitStr;}public function selectInsert(Query $query, array $fields, string $table): string{$this->selectSql = $this->selectInsertSql;return parent::selectInsert($query, $fields, $table);}}

FunSqlsrv.php

<?php// +----------------------------------------------------------------------// | ThinkPHP [ WE CAN DO IT JUST THINK IT ]// +----------------------------------------------------------------------// | Copyright (c) - All rights reserved.// +----------------------------------------------------------------------// | Licensed ( /licenses/LICENSE-2.0 )// +----------------------------------------------------------------------// | Author: liu21st <liu21st@>// +----------------------------------------------------------------------namespace app\common\driver\connector;use PDO;use think\db\PDOConnection;/*** Sqlsrv数据库驱动 魔改版*/class FunSqlsrv extends PDOConnection{/*** 默认PDO连接参数* @var array*/protected $params = [PDO::ATTR_CASE => PDO::CASE_NATURAL,PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,PDO::ATTR_ORACLE_NULLS => PDO::NULL_NATURAL,PDO::ATTR_STRINGIFY_FETCHES => false,];/*** 获取当前连接器类对应的Builder类* @access public* @return string*///public function getBuilderClass(): string//{// return '\\app\\common\\driver\\builder\\FunSqlsrv';//}/*** 解析pdo连接的dsn信息* @access protected* @param array $config 连接信息* @return string*/protected function parseDsn(array $config): string{$dsn = 'sqlsrv:Database=' . $config['database'] . ';Server=' . $config['hostname'];if (!empty($config['hostport'])) {$dsn .= ',' . $config['hostport'];}if (!empty($config['trust_server_certificate'])) {$dsn .= ';TrustServerCertificate=' . $config['trust_server_certificate'];}return $dsn;}/*** 取得数据表的字段信息* @access public* @param string $tableName* @return array*/public function getFields(string $tableName): array{[$tableName] = explode(' ', $tableName);strpos($tableName, '.') && $tableName = substr($tableName, strpos($tableName, '.') + 1);$sql = "SELECT column_name, data_type, column_default, is_nullableFROM INFORMATION_SCHEMA.TABLES AS tJOIN INFORMATION_SCHEMA.COLUMNS AS cON t.table_catalog = c.table_catalogAND t.table_schema = c.table_schemaAND t.table_name = c.table_nameWHERE t.table_name = '$tableName'";$pdo = $this->getPDOStatement($sql);$result = $pdo->fetchAll(PDO::FETCH_ASSOC);$info = [];if (!empty($result)) {foreach ($result as $key => $val) {$val = array_change_key_case($val);$info[$val['column_name']] = ['name' => $val['column_name'],'type' => $val['data_type'],'notnull' => (bool)('' === $val['is_nullable']), // not null is empty, null is yes'default' => $val['column_default'],'primary' => false,'autoinc' => false,];}}$sql = "SELECT column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE table_name='$tableName'";$pdo = $this->linkID->query($sql);$result = $pdo->fetch(PDO::FETCH_ASSOC);if ($result) {$info[$result['column_name']]['primary'] = true;}return $this->fieldCase($info);}/*** 取得数据表的字段信息* @access public* @param string $dbName* @return array*/public function getTables(string $dbName = ''): array{$sql = "SELECT TABLE_NAMEFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_TYPE = 'BASE TABLE'";$pdo = $this->getPDOStatement($sql);$result = $pdo->fetchAll(PDO::FETCH_ASSOC);$info = [];foreach ($result as $key => $val) {$info[$key] = current($val);}return $info;}}

参考

https://weiku.co/article/37/

如果觉得《解决thinkphp6读取sqlserver报Microsoft[SQL Server]对象名 ‘information_schema.tables‘ 无效错误》对你有帮助，请点赞、收藏，并留下你的观点哦！