问题描述
我必须编写一个PowerShell脚本,该脚本需要使用mysql.exe
控制台并执行一些查询.我开始了:
I have to write a PowerShell script that needs to use the mysql.exe
console and execute some queries. I started it:
$mysqlC = 'C:\Users\I16A1\Downloads\mysql\bin\mysql.exe'
$arg1 = '-u asset -ptest testasset'
& $mysqlC $arg1
show databases;
如您所见,设置控制台位置和连接参数后,我使用调用运算符(&
)运行MySQL控制台.
As you can see, after setting the console placement and the arguments of connection, I use the call operator (&
) to run the MySQL console.
一切都很好,MySQL控制台正在运行,但是调用运算符之后的行未运行,例如show databases;
.
Everything is fine, MySQL console is running but the lines after the call operator are not running, like show databases;
.
问题是我希望我的PowerShell脚本自行执行所有操作.
The thing is I want my PowerShell script to do everything on its own.
有人知道这样做的方法吗?也许有所不同?
Someone know a way to do it? Maybe differently?
推荐答案
您要尝试执行的操作将不起作用,因为您的代码要从mysql.exe
退出后才能到达show databases;
.在脚本中使用mysql.exe
的通常方法是在每个查询中运行可执行文件:
What you're trying to do won't work, because your code won't get to the show databases;
until you exit from mysql.exe
. The usual way to use mysql.exe
from a script is to run the executable with each query:
$db = 'testasset'
$user = 'asset'
$pass = 'test'
$mysql = 'C:\Users\I16A1\Downloads\mysql\bin\mysql.exe'
$params = '-u', $user, '-p', $pass, $db
& $mysql @params -e 'SHOW DATABASES'
& $mysql @params -e '...'
...
使用 splatting 提供常用参数.
通常,您还需要添加参数-B
(--batch
,非交互式执行,没有奇特的输出格式)和-N
(--skip-column-names
,不显示输出中的列标题)以获取更多信息这样的可消化"输出:
Normally you'd also add the parameters -B
(--batch
, non-interactive execution, no fancy output formatting) and -N
(--skip-column-names
, don't show column titles in output) to get more "digestible" output like this:
information_schema
mysql
performance_schema
test
与其获取这样的默认输出,不如将数据解析出表以进行进一步处理:
instead of getting default output like this, that would require parsing the data out of the table for further processing:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
话虽如此,比使用命令行客户端更好的方法是使用.网络连接器,例如像这样:
With that said, a much better approach than working with the commandline client would be using the .Net Connector, e.g. like this:
$server = 'localhost'
$db = 'testasset'
$user = 'asset'
$pass = 'test'
$cs = "server=$server;user id=$user;password=$pass;database=$db;pooling=false"
[void][Reflection.Assembly]::LoadWithPartialName('MySQL.Data')
$cn = New-Object MySql.Data.MySqlClient.MySqlConnection
$cn.ConnectionString = $cs
$cn.Open()
$cmd= New-Object MySql.Data.MySqlClient.MySqlCommand
$cmd.Connection = $cn
$cmd.CommandText = 'SHOW DATABASES'
$reader = $cmd.ExecuteReader()
$tbl = New-Object Data.DataTable
$tbl.Load($reader)
$reader.Close()
$cn.Close()
$tbl | Format-Table -AutoSize
这样,您获得的输出将是实际的对象,而不是字符串.
That way the output you get will be actual objects instead of strings.
这篇关于通过PowerShell脚本使用MySQL控制台的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!