C#程序连接MySQL数据库（VS Code+XAMPP）认证失败问题求助

我正在用C#开发一款控制台程序，用于展示大学校园内宿舍区域的商家信息。程序启动后显示主菜单，包含「餐饮」「服饰」「杂项」「退出」四个选项：选择前三者时从数据库读取对应表数据展示，之后返回主菜单；选择「退出」则终止程序。

测试时，选择前三个选项（如餐饮）无法展示对应表数据，抛出以下错误：

Error: Authentication to host 'localhost' for user 'root' using method 'caching_sha2_password' failed with message: Access denied for user 'root'@'localhost' (using password: NO)

环境说明 #

• 数据库通过XAMPP的phpMyAdmin管理，包含6张表：Tenant_list（商家个人信息）、dormitory_building（商家所在楼宇信息）、product_junction（商家与产品关联表）、food_and_drink（餐饮产品信息）、clothing_table（服饰产品信息）、Miscellaneous（杂项产品信息）
• 使用VS Code中Weijan Chen开发的「MySQL」插件连接数据库
• 已调整root用户权限，且root用户认证插件为mysql_native_password（默认配置）

附C#代码 #

using System;
using System.Threading;
using MySql.Data.MySqlClient;

namespace MyApplication
{
    class Program
    {
        static void DisplayTableData(string tableName)
        {
            string connectionString = "Server=localhost;Database=telyu_vendor_dorm;User ID=root;";
            string query = $"SELECT * FROM {tableName}";

            try
            {
                using (MySqlConnection connection = new MySqlConnection(connectionString))
                {
                    connection.Open();
                    using (MySqlCommand command = new MySqlCommand(query, connection))
                    {
                        using (MySqlDataReader reader = command.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                Console.WriteLine($"Data from table '{tableName}':");
                                while (reader.Read())
                                {
                                    for (int i = 0; i < reader.FieldCount; i++)
                                    {
                                        Console.Write($"{reader.GetName(i)}: {reader[i]}  ");
                                    }
                                    Console.WriteLine();
                                }
                            }
                            else
                            {
                                Console.WriteLine($"No data found in table '{tableName}'.");
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}");
            }
        }

        static void Foodie()
        {
            Console.WriteLine("You have chosen the Food and Drink category!");
            Thread.Sleep(1000);
            DisplayTableData("Food_and_Drink");
        }

        static void Clothes()
        {
            Console.WriteLine("You have chosen the Clothing category!");
            Thread.Sleep(1000);
            DisplayTableData("Clothes");
        }

        static void TheOtherStuff()
        {
            Console.WriteLine("You have chosen the Miscellaneous category!");
            Thread.Sleep(1000);
            DisplayTableData("Miscellaneous");
        }

        static void Main(string[] args)
        {
            int itsTimeToStop = 0;

            Console.WriteLine("Telkom University Dormitory Market (test version)");
            Thread.Sleep(1000);

            Console.WriteLine("What do you want to do today?");
            Console.WriteLine("");

            while (itsTimeToStop < 1)
            {
                Console.WriteLine(" | [1] Food and Drinks | [2] Clothing | [3] Miscellaneous | [4] Exit");
                Console.WriteLine("");

                Console.Write("Your chosen category: ");
                int choice = Convert.ToInt32(Console.ReadLine());

                switch (choice)
                {
                    case 1:
                        Foodie();
                        break;
                    case 2:
                        Clothes();
                        break;
                    case 3:
                        TheOtherStuff();
                        break;
                    case 4:
                        Console.WriteLine("You have exited the website!");
                        itsTimeToStop++;
                        break;
                    default:
                        Console.WriteLine("Please choose a valid option.");
                        break;
                }
            }
        }
    }
}

解决方案 #

1. 补充数据库连接密码参数 #

错误提示显示using password: NO，说明连接字符串未提供密码（即使root为空密码，也需要显式声明）。修改DisplayTableData方法中的连接字符串：

// 空密码情况
string connectionString = "Server=localhost;Database=telyu_vendor_dorm;User ID=root;Password=;";
// 若root设置了密码，替换为实际密码
// string connectionString = "Server=localhost;Database=telyu_vendor_dorm;User ID=root;Password=your_root_password;";

2. 修正表名不匹配问题 #

代码中调用的表名与数据库实际表名不一致：

• Clothes方法中调用DisplayTableData("Clothes");，但数据库实际表名为clothing_table，需修改为：

  static void Clothes()
  {
      Console.WriteLine("You have chosen the Clothing category!");
      Thread.Sleep(1000);
      DisplayTableData("clothing_table");
  }
  

• 建议统一表名大小写（MySQL在Linux环境下大小写敏感），将Food_and_Drink改为food_and_drink，与数据库表名保持一致。

3. 验证root用户认证插件 #

确认root@localhost的认证插件为mysql_native_password：

1. 打开XAMPP的phpMyAdmin，进入「用户账户」页面
2. 找到root@localhost，查看「认证插件」列
3. 若不是mysql_native_password，执行以下SQL修改：

   ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';
   FLUSH PRIVILEGES;
   

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