本文介绍了通过PowerShell脚本使用MySQL控制台的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我必须编写一个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控制台的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

05-28 20:25
查看更多