查询MySQL数据库 [英] Querying the MySQL db

问题描述

$Query1 = "INSERT INTO answers (gid,Key1,key2,key3,key4,key5) VALUES ($gid,'$key1','$key2','$key3','$key4','$key5');"

function Invoke-MySql {
  param($Query)

  $MySQLAdminUserName = 'root'
  $MySQLAdminPassword = <password>
  $MySQLDatabase = <username>
  $MySQLHost = 'localhost'
  $ConnectionString = "server=" + $MySQLHost + ";port=3306;uid=" +
                      $MySQLAdminUserName + ";pwd=" + $MySQLAdminPassword +
                      ";database=" + $MySQLDatabase
  Write-Log "Trying to connect MySql" 0

  try {
    [void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
    $Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
    $Connection.ConnectionString = $ConnectionString
    $Connection.Open()


    $Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
    $DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
    $DataSet = New-Object System.Data.DataSet
    $RecordCount = $dataAdapter.Fill($dataSet, "data")
    $DataSet.Tables[0] | ft -AutoSize
    Write-Log "Querying the table with following query: $Query" 0
  } catch {
    Write-Log "ERROR : Unable to run query : $query `n$Error[0]" 2
  } finally {
    $Connection.Close()
    Write-Log "MySql connection closed" 0
  }
}

$Result1 = Invoke-MySql -Query $Query1

输出:

PS D:\MYSQL> D:\MYSQL\MySql_Insert&Query.ps1

   gid key1      key2    key3    key4  key5
   --- ----      ----    ----    ----  ----
 31657 c1        c2      c3      c4    c5
 31667 b1        b2      b3      b4    b5
112322 aa        bb      cc      dd    ee
212322 aa        bb      cc      dd    ee
212982 aa        bb      cc      dd    ee
215982 aa        bb      cc      dd    ee
215987 aa        bb      cc      dd    ee
315987 aa        bb      cc      dd    ee

所以这是输出.我已经从MySQL提取了结果，但是现在我想过滤结果，例如只提取一个值，例如gid = 2122322的key3.

So this is the output. I've fetched the results from MySQL, but now I want to filter the results like where I want to fetch only one value eg., key3 of gid=2122322.

推荐答案

您的函数返回的是格式化输出，而不是表本身:

Your function is returning formatted output instead of the table itself:

$DataSet.Tables[0] | ft -AutoSize

删除| ft -AutoSize，您将可以使用实际的表数据:

Remove the | ft -AutoSize and you'll be able to work with the actual table data:

$Result1 = Invoke-MySql -Query $Query1 |
           Where-Object { $_.gid -eq 2122322 } |
           Select-Object -Expand key3

Format-* cmdlet用于将数据呈现给用户时对其进行格式化. 从不在需要进一步处理的数据上使用它们(基本上，从不在函数内部使用它们).

Format-* cmdlets are for formatting data when it's presented to the user. Never use them on data that should be subject to further processing (basically, never use them inside a function).

这篇关于查询MySQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！