C#连接MySQL仅显示默认数据库，无法列出自建数据库

Hey, I’ve run into this exact issue before—let’s break down why your custom databases aren’t showing up and how to fix it.

Most Likely Cause: Insufficient User Permissions #

By far the most common reason for this is that the MySQL user you’re connecting with doesn’t have permission to view all databases. Default users (like restricted non-root accounts) won’t see your custom databases unless explicitly granted access.

Fix the Permissions: #

1. Log into your MySQL server with an admin user (like root).
2. Grant the necessary permissions to your application user (replace your_username and your_host with your actual credentials):

   -- Grant permission to view all databases
   GRANT SHOW DATABASES ON *.* TO 'your_username'@'your_host';
   

   If you need the user to interact with your custom databases, grant broader access instead:

   -- Full access to a specific custom database
   GRANT ALL PRIVILEGES ON your_custom_db.* TO 'your_username'@'your_host';
   -- Or global access if needed
   GRANT ALL PRIVILEGES ON *.* TO 'your_username'@'your_host';
   

3. Flush privileges to apply changes immediately:

   FLUSH PRIVILEGES;
   

4. Verify permissions were applied:

   SHOW GRANTS FOR 'your_username'@'your_host';
   

Check Your Code for Potential Issues #

While permissions are the main culprit, let’s refine your code to follow best practices and ensure correct result reading:

public static List<string> GetDatabases() 
{ 
    List<string> databases = new List<string>(); 
    string query = "SHOW DATABASES;"; 

    // Use using statements to auto-dispose resources and avoid leaks
    using (MySqlCommand command = new MySqlCommand(query, connection)) 
    { 
        try 
        {
            connection.Open(); 
            using (MySqlDataReader reader = command.ExecuteReader()) 
            { 
                // Read the first (and only) column from the result set
                while (reader.Read()) 
                { 
                    string dbName = reader.GetString(0); 
                    databases.Add(dbName); 
                } 
            } 
        }
        catch (MySqlException ex)
        {
            // Add logging or error handling as needed for your app
            Console.WriteLine($"Failed to retrieve databases: {ex.Message}");
        }
        finally
        {
            // Guarantee the connection is closed even if an error occurs
            if (connection.State == ConnectionState.Open)
                connection.Close();
        }
    }

    return databases; 
}

Key Code Improvements: #

• using statements automatically clean up MySqlCommand and MySqlDataReader resources.
• Explicitly reads the first column (GetString(0)), since SHOW DATABASES returns a single column of database names.
• A finally block ensures the connection is closed, preventing lingering open connections.

Double-Check Your Connection String #

Make sure the username in your connection string matches the user you just granted permissions to. It’s easy to accidentally use a restricted default user without noticing. A valid connection string might look like:

Server=your_server_address;Uid=your_username;Pwd=your_password;

(Note: You don’t need to specify a Database value when running SHOW DATABASES.)

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