Yii2中mssql和sqlserver數據庫如何使用

本篇文章給大家分享的是有關Yii2中mssql和sql server數據庫如何使用，小編覺得挺實用的，因此分享給大家學習，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著小編一起來看看吧。

創新互聯專業提供成都主機托管四川主機托管成都服務器托管四川服務器托管，支持按月付款！我們的承諾：貴族品質、平民價格，機房位于中國電信/網通/移動機房，成都服務器托管服務有保障！

1.首先找到yii2解析mssql的scheme：

vendor/yiisoft/yii2/db/mssql/Schema.php

2.更改解析代碼：

⑴找到findColumns 的函數 大概在232行左右

⑵替代解析 代碼如下 ：

/**

* Collects the metadata of table columns.

* @param TableSchema $table the table metadata

* @return boolean whether the table exists in the database

*/

protected function findColumns($table)

{

$pdo = $this->db->getSlavePdo();

$version = explode('.', $pdo->getAttribute(\PDO::ATTR_SERVER_VERSION));

//var_dump($version);exit;

if (intval($version[0]<9)) { 　　$sql = << 　　SELECT 　　column_name = a.name, 　　is_identity = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in ( 　　SELECT name FROM sysindexes WHERE indid in( 　　SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then 1 else 0 end, 　　data_type = case 　　when b.name in ('ntext','datetime') then b.name 　　when isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0)>0 then

b.name + '(' + convert(varchar(20), COLUMNPROPERTY(a.id,a.name,'PRECISION')) + ','+convert(varchar(10), COLUMNPROPERTY(a.id,a.name,'Scale'))+')'

else b.name + '(' + convert(varchar(20), COLUMNPROPERTY(a.id,a.name,'PRECISION')) + ')' end ,

is_nullable = case when a.isnullable=1 then 'Yes' else 'No' end,

column_default = isnull(e.text,''),

comment = isnull(g.[value],'')

FROM

syscolumns a

left join

systypes b

on

a.xusertype=b.xusertype

inner join

sysobjects d

on

a.id=d.id and d.xtype='U' and d.name<>'dtproperties'

left join

syscomments e

on

a.cdefault=e.id

left join

sysproperties g

on

a.id=g.id and a.colid=g.smallid

left join

sysproperties f

on

d.id=f.id and f.smallid=0

where

d.name='{$table->fullName}'

order by

a.id,a.colorder

SQL2000;

}

else {

$columnsTableName = 'INFORMATION_SCHEMA.COLUMNS';

$whereSql = "[t1].[table_name] = '{$table->name}'";

if ($table->catalogName !== null) {

$columnsTableName = "{$table->catalogName}.{$columnsTableName}";

$whereSql .= " AND [t1].[table_catalog] = '{$table->catalogName}'";

}

if ($table->schemaName !== null) {

$whereSql .= " AND [t1].[table_schema] = '{$table->schemaName}'";

}

$columnsTableName = $this->quoteTableName($columnsTableName);

$sql = << 　　SELECT 　　[t1].[column_name], [t1].[is_nullable], [t1].[data_type], [t1].[column_default], 　　COLUMNPROPERTY(OBJECT_ID([t1].[table_schema] + '.' + [t1].[table_name]), [t1].[column_name], 'IsIdentity') AS is_identity, 　　CONVERT(VARCHAR, [t2].[value]) AS comment 　　FROM {$columnsTableName} AS [t1] 　　LEFT OUTER JOIN [sys].[extended_properties] AS [t2] ON 　　[t2].[minor_id] = COLUMNPROPERTY(OBJECT_ID([t1].[TABLE_SCHEMA] + '.' + [t1].[TABLE_NAME]), [t1].[COLUMN_NAME], 'ColumnID') AND 　　OBJECT_NAME([t2].[major_id]) = [t1].[table_name] AND 　　[t2].[class] = 1 AND 　　[t2].[class_desc] = 'OBJECT_OR_COLUMN' AND 　　[t2].[name] = 'MS_Description' 　　WHERE {$whereSql} 　　SQL; 　　} 　　try { 　　$columns = $this->db->createCommand($sql)->queryAll();

if (empty($columns)) {

return false;

}

} catch (\Exception $e) {

return false;

}

foreach ($columns as $column) {

$column = $this->loadColumnSchema($column);

foreach ($table->primaryKey as $primaryKey) {

if (strcasecmp($column->name, $primaryKey) === 0) {

$column->isPrimaryKey = true;

break;

}

}

if ($column->isPrimaryKey && $column->autoIncrement) {

$table->sequenceName = '';

}

$table->columns[$column->name] = $column;

}

return true;

}

3.分析這段代碼：

⑴獲取數據庫的版本：

$pdo = $this->db->getSlavePdo();

$version = explode('.', $pdo->getAttribute(\PDO::ATTR_SERVER_VERSION));

⑵根據版本解析：

if (intval($version[0]<9)) { }else{} 　　⑶解析sql2000： 　　$sql = << 　　SELECT 　　column_name = a.name, 　　is_identity = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in ( 　　SELECT name FROM sysindexes WHERE indid in( 　　SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then 1 else 0 end, 　　data_type = case 　　when b.name in ('ntext','datetime') then b.name 　　when isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0)>0 then

b.name + '(' + convert(varchar(20), COLUMNPROPERTY(a.id,a.name,'PRECISION')) + ','+convert(varchar(10), COLUMNPROPERTY(a.id,a.name,'Scale'))+')'

else b.name + '(' + convert(varchar(20), COLUMNPROPERTY(a.id,a.name,'PRECISION')) + ')' end ,

is_nullable = case when a.isnullable=1 then 'Yes' else 'No' end,

column_default = isnull(e.text,''),

comment = isnull(g.[value],'')

FROM

syscolumns a

left join

systypes b

on

a.xusertype=b.xusertype

inner join

sysobjects d

on

a.id=d.id and d.xtype='U' and d.name<>'dtproperties'

left join

syscomments e

on

a.cdefault=e.id

left join

sysproperties g

on

a.id=g.id and a.colid=g.smallid

left join

sysproperties f

on

d.id=f.id and f.smallid=0

where

d.name='{$table->fullName}'

order by

a.id,a.colorder

SQL2000;

⑷else 就是原來的解析方式 3.打開sql的數據庫scheme的緩存，在db.php文件中添加 ：'enableSchemaCache'=>true 既可以。

以上就是Yii2中mssql和sql server數據庫如何使用，小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注創新互聯行業資訊頻道。

本文標題：Yii2中mssql和sqlserver數據庫如何使用
當前鏈接：http://m.kartarina.com/article22/geshcc.html

成都網站建設公司_創新互聯，為您提供靜態網站、網頁設計公司、服務器托管、企業建站、動態網站、網站維護

廣告

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯