如何使用PowerShell连接并填充phpMyAdmin创建的SQL数据库

Got it, since you're already working with these phpMyAdmin-managed databases (I’m assuming they’re MySQL/MariaDB—phpMyAdmin’s go-to database systems), connecting and populating them with PowerShell is totally feasible. Let’s walk through the steps clearly, starting with the basics you need to get started.

Since you use PHP to interact with them, they’re almost certainly MySQL/MariaDB. For PowerShell, you have two main approaches to connect—let’s start with the simplest one first.

If you already have the MySQL client tools installed (they often come alongside PHP’s MySQL extensions), you can call mysql.exe directly from PowerShell. This is great for quick scripts or if you don’t want to mess with .NET drivers.

Connect to Your Database #

Run this command (replace placeholders with your actual credentials):

# Connect to the database (you'll be prompted for password if you omit -p's value)
mysql -u your_db_username -p'your_db_password' -h localhost -D your_database_name

• -h localhost: Use this if your database is on the same machine as PowerShell (same as your PHP setup)
• -D your_database_name: Skips the USE your_db; step once connected

Populate Data via Command-Line #

You can run SQL statements directly, or pipe a SQL script file into mysql.exe:

# Run a single insert command
mysql -u your_user -p'your_pass' -h localhost -D your_db -e "INSERT INTO your_table (col1, col2) VALUES ('test_val', 456);"

# Run a full SQL script (great for bulk inserts/table setup)
mysql -u your_user -p'your_pass' -h localhost -D your_db < "C:\path\to\your\data_script.sql"

For more control (like dynamic data generation, CSV imports), use the official MySQL .NET Connector. Here’s how to set it up:

Step 1: Install the MySQL .NET Connector #

Download it from the MySQL website and install it. Note the path to MySQL.Data.dll (e.g., C:\Program Files\MySQL\MySQL Connector Net 8.0.x\Assemblies\net6.0\MySQL.Data.dll).

Step 2: Connect & Insert Data (PowerShell Script Example) #

# Load the MySQL .NET driver (adjust the path to match your installation)
Add-Type -Path "C:\Program Files\MySQL\MySQL Connector Net 8.0.33\Assemblies\net6.0\MySQL.Data.dll"

# Set up connection details (avoid hardcoding passwords! Use Get-Credential instead)
$cred = Get-Credential -Message "Enter MySQL Database Credentials"
$connectionString = "server=localhost;user id=$($cred.UserName);password=$($cred.GetNetworkCredential().Password);database=your_db_name;"

# Establish connection
$connection = New-Object MySql.Data.MySqlClient.MySqlConnection($connectionString)
try {
    $connection.Open()
    Write-Host "Successfully connected to the database!"

    # Example 1: Insert a single row with parameterized query (prevents SQL injection)
    $insertSql = "INSERT INTO your_table (column1, column2) VALUES (@val1, @val2);"
    $command = New-Object MySql.Data.MySqlClient.MySqlCommand($insertSql, $connection)
    $command.Parameters.AddWithValue("@val1", "Sample Text")
    $command.Parameters.AddWithValue("@val2", 789)
    $rowsAffected = $command.ExecuteNonQuery()
    Write-Host "Inserted $rowsAffected row(s)"

    # Example 2: Bulk insert from a CSV file (super useful for populating large datasets)
    $csvData = Import-Csv -Path "C:\path\to\your\data.csv"
    foreach ($row in $csvData) {
        $bulkInsertSql = "INSERT INTO your_table (col1, col2) VALUES (@col1, @col2);"
        $bulkCommand = New-Object MySql.Data.MySqlClient.MySqlCommand($bulkInsertSql, $connection)
        $bulkCommand.Parameters.AddWithValue("@col1", $row.Col1) # Match CSV column names
        $bulkCommand.Parameters.AddWithValue("@col2", $row.Col2)
        $bulkCommand.ExecuteNonQuery()
    }
    Write-Host "Bulk insert completed!"
}
catch {
    Write-Error "Error: $_"
}
finally {
    # Always close the connection
    if ($connection.State -eq 'Open') {
        $connection.Close()
    }
}

• Test Connection First: Start with a simple SELECT 1; query to confirm your connection works before trying inserts.
• Avoid Hardcoding Credentials: Use Get-Credential or environment variables (e.g., $env:MYSQL_PASSWORD) to keep passwords secure.
• Check Permissions: Make sure your database user has INSERT permissions (same as your PHP user—since PHP works, this should already be set, but double-check if you hit errors).
• Start Small: Write a script that inserts one row first, then scale up to bulk operations or CSV imports.

内容的提问来源于stack exchange，提问作者thatOneGuy