【資料圖】

powershell連接SQL Server一般都是直接使用Invoke-sqlcmd，這個(gè)很方便，但是有時(shí)候還是會(huì)需要連接到其他數(shù)據(jù)庫，像MySQL。

連接MySQL是通過.net框架的，所以必須先安裝 MySQL .Net Connector (??http://dev.mysql.com/downloads/connector/net/??)。如果不想安裝MySQL .Net connector 也可以直接在其他已安裝的機(jī)器上拷貝MySql.Data.dll 到需要使用的機(jī)器上。

連接方式：如果是安裝了MySQL .Net Connector

[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")

如果是直接通過MySQL.Data.dll文件：

$mySQLDataDLL = "C:\scripts\mysql\MySQL.Data.dll"[void][system.reflection.Assembly]::LoadFrom($mySQLDataDLL)

代碼內(nèi)容如下：

[void][system.Reflection.Assembly]::LoadFrom("C:\\Program Files (x86)\\MySQL\\MySQL Connector Net 8.0.16\\Assemblies\\v4.5.2\\MySql.Data.dll")$Server="10.2.3.144"$Database="test" #數(shù)據(jù)庫名$user="user" #賬戶$Password="123456" #密碼$connectionString = "server=$Server;uid=$user;pwd=$Password;database=$Database;charset=$charset"$connection = New-Object MySql.Data.MySqlClient.MySqlConnection($connectionString)$connection.Open()#GET 某個(gè)賬戶的MemeberID$getsql= "SELECT MEMBER_ID from  org_principal WHERE LOGIN_NAME = "$username""$getcommand = New-Object MySql.Data.MySqlClient.MySqlCommand($getsql, $connection)$dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($getcommand)$getid = New-Object System.Data.DataSet$recordCount = $dataAdapter.Fill($getid)Write-Host "共有" $recordCount "條記錄"$memberid=$getid.Tables.ROWS.MEMBER_ID#更新這個(gè)ID到AD綁定表中$insertsql = "INSERT INTO ctp_org_user_mapper (ID,TYPE,LOGIN_NAME,EX_LOGIN_NAME,EX_PASSWORD,EX_ID,MEMBER_ID,EX_UNIT_CODE) VALUES ("$userid","ad.member","$username","$username","null","670869647114347","$memberid","$usercnname");"$insertcommand = New-Object MySql.Data.MySqlClient.MySqlCommand$insertcommand.Connection=$connection$insertcommand.CommandText=$insertsql$insertcommand.ExecuteNonQuery()$connection.Close()