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数据库如何使用，小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注创新互联行业资讯频道。