先感谢思路
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文章来源：https://www.toymoban.com/news/detail-514584.html
<?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/文章来源地址https://www.toymoban.com/news/detail-514584.html
到了这里，关于解决thinkphp6读取sqlserver报Microsoft[SQL Server]对象名 ‘information_schema.tables‘ 无效错误的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
