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

最新推荐文章于 2024-04-23 17:34:54 发布

汪小君

最新推荐文章于 2024-04-23 17:34:54 发布

阅读量1.9k

点赞数

文章标签： microsoft sqlserver 数据库

本文链接：https://blog.csdn.net/Function_JX_/article/details/128465301

版权

本文介绍如何为ThinkPHP框架定制SQLServer数据库驱动，通过重写核心组件实现特定需求，如调整SQL语法、优化查询性能等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

先感谢思路
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

<?php

namespace 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) 2006-2012 http://thinkphp.cn All rights reserved.
// +----------------------------------------------------------------------
// | Licensed ( http://www.apache.org/licenses/LICENSE-2.0 )
// +----------------------------------------------------------------------
// | Author: liu21st <liu21st@gmail.com>
// +----------------------------------------------------------------------

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_nullable
        FROM    INFORMATION_SCHEMA.TABLES AS t
        JOIN    INFORMATION_SCHEMA.COLUMNS AS c
        ON  t.table_catalog = c.table_catalog
        AND t.table_schema  = c.table_schema
        AND t.table_name    = c.table_name
        WHERE   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_NAME
            FROM INFORMATION_SCHEMA.TABLES
            WHERE 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/