数据库中的MYSQL / SQL查询无法正常工作

I have an application that works like a web crawler; it gets me a bunch of information about players on a certain game. in this information are things like their Level and their Online Status (online or offline). What I want to do is add all players to the database. But, if they exist it should only update their already existing row.

我有一个像网络爬虫一样的应用程序;它让我获得了一些关于特定游戏中玩家的信息。这些信息包括他们的等级和他们的在线状态(在线或离线)。我想要做的是将所有玩家添加到数据库中。但是,如果它们存在,它应该只更新它们已经存在的行。

To do this, I decided to first of all DELETE all the names in the database that matches the name I am going to insert. Their name is stored in @name. I then want to set everyone in the table to onlinestatus=0 (meaning they are offline). And then I want to set onlinestatus to 1, to the players I am inserting. The players I am currently inserting are the only ones online in the game. So if they are not in my list that I insert, they should be offline. That's why I first try to reset their online status.

为此,我决定首先删除数据库中与我要插入的名称相匹配的所有名称。他们的名字存储在@name中。然后我想将表中的每个人都设置为onlinestatus = 0(意味着他们处于离线状态)。然后我想将onlinestatus设置为1,对于我插入的玩家。我目前正在插入的玩家是游戏中唯一在线玩家。因此,如果它们不在我插入的列表中,则它们应该脱机。这就是为什么我首先尝试重置他们的在线状态。

I then also want to reset the levels to 0, of all the players I am going to insert. And then at the end I insert their things again. But it seems not to be working and I don't know whats the best way to do things.

然后我还想将我要插入的所有玩家的等级重置为0。然后在最后我再次插入他们的东西。但它似乎没有起作用,我不知道什么是最好的做事方式。

Any help is appreciated!

任何帮助表示赞赏!

What I want to do:

我想做的事:

1. Insert a list of players into the database

   将玩家列表插入数据库

2. If they already exist in the database, do not insert them, but only update their level and onlinestatus

   如果它们已存在于数据库中,请不要插入它们,而只是更新它们的级别和onlinestatus

3. onlinestatus = 1 when I am inserting them. It's a list of players. If they are not in the list, make them offline.

   我插入时,onlinestatus = 1。这是一个球员名单。如果它们不在列表中,请使它们脱机。

       using (var connection = new MySqlConnection(connString))
       {
           connection.Open();
           using (var command = new MySqlCommand())
           {
               command.Connection = connection;
               command.CommandText = @"DELETE FROM rookstayers WHERE name = @name;
               UPDATE rookstayers SET onlinestatus CASE WHEN name = @name THEN 1 ELSE 0 END;UPDATE rookstayers SET level = 0 WHERE name = @name; INSERT INTO rookstayers (name, sex, vocation, level, achievements, world, lastlogin, accountstatus, onlinestatus) VALUES (@name, @sex, @vocation, @level, @achievements, @world, @lastLogin, @accountStatus, @onlineStatus)";
               command.Prepare();
   
               foreach (var rooker in Players)
               {
                   command.Parameters.Clear();
                   command.Parameters.AddWithValue("@name", rooker.Name);
                   command.Parameters.AddWithValue("@sex", rooker.Sex);
                   command.Parameters.AddWithValue("@vocation", rooker.Vocation);
                   command.Parameters.AddWithValue("@level", rooker.Level);
                   command.Parameters.AddWithValue("@achievements", rooker.Achievements);
                   command.Parameters.AddWithValue("@world", rooker.World);
                   command.Parameters.AddWithValue("@lastLogin", rooker.LastLogin);
                   command.Parameters.AddWithValue("@accountStatus", rooker.AccountStatus);
                   command.Parameters.AddWithValue("@onlineStatus", rooker.OnlineStatus);
                   command.ExecuteNonQuery();
               }
   
               Players.Clear();
           }
           connection.Close();
       }
   

5 个解决方案

#1

You need something like this, because your name is unique. Please note below is just rough guide. You can use MySQL IF Function.

你需要这样的东西,因为你的名字是独一无二的。请注意下面的粗略指南。您可以使用MySQL IF功能。

IF(name == @name) THEN
-- update

ElSE

-insert query

END IF;

#2

After deleting all players that are in your list, you can't update them.

删除列表中的所有播放器后,您无法更新它们。

So you probable need to do this for everyone left in the table:

因此,您可能需要为表中的所有人执行此操作:

UPDATE rookstayers SET onlinestatus = 0;
UPDATE rookstayers SET level = 0;

#3

The first UPDATE statement is missing an =:

第一个UPDATE语句缺少一个=:

UPDATE rookstayers SET onlinestatus CASE WHEN name = 
  -- Need an equal sign here ------^

Additionally, I suspect the Parameters.Clear() call may be throwing things off. It actually removes all the parameters from the collection, when you all really need to do is change the values. Try this instead:

另外,我怀疑Parameters.Clear()调用可能会抛弃一些东西。它实际上删除了集合中的所有参数,当你真正需要做的就是更改值。试试这个:

command.Prepare();
//Guessing a column names/types here. Use actual column types from your DB
command.Parameters.Add("@name", MySqlDbType.VarString, 20);
command.Parameters.Add("@sex", MySqlDbType.VarString, 1);
command.Parameters.Add("@vocation", MySqlDbType.VarString, 20);
command.Parameters.Add("@level", MySqlDbType.Int32);
command.Parameters.Add("@achievements", MySqlDbType.VarString, 255);
command.Parameters.Add("@world", MySqlDbType.VarString, 20);
command.Parameters.Add("@lastLogin", MySqlDbType.DateTime);
command.Parameters.Add("@accountStatus", MySqlDbType.Int32);

foreach (var rooker in Players)
{
     command.Parameters["@name"].Value = rooker.Name;
     command.Parameters["@sex"].Value = rooker.Sex;
     command.Parameters["@vocation"].Value = rooker.Vocation;
     command.Parameters["@level"].Value = rooker.Level;
     command.Parameters["@achievements"].Value = rooker.Achievements;
     command.Parameters["@world"].Value = rooker.World;
     command.Parameters["@lastLogin"].Value = rooker.LastLogin;
     command.Parameters["@accountStatus"].Value = rooker.AccountStatus;
     command.Parameters["@onlineStatus"].Value = rooker.OnlineStatus;
     command.ExecuteNonQuery();
}

Once you've made those changes, post what your actual error is, or what you're seeing for the expected vs observed results.

完成这些更改后,发布您的实际错误,或者您对预期与观察到的结果所看到的内容。

#4

I think this is a really good use-case for Entity Framework. It is really easy to work here with unless you are dealing with massive tables. All you need to do is install the following packages via NuGet and set up your app.config to connect to the appropriate database instance.

我认为这是Entity Framework的一个非常好的用例。除非你正在处理大量的表格,否则在这里工作真的很容易。您需要做的就是通过NuGet安装以下软件包并设置app.config以连接到相应的数据库实例。

Nuget packages:

EntityFramework
MySql.Data
MySql.Data.Entity

Example connection string in app.config:

app.config中的连接字符串示例:

<connectionStrings>
    <add name="RookstayersDbContext" 
        connectionString="server=localhost;userid=root;password=mypass;database=mydb" 
        providerName="MySql.Data.MySqlClient" />
</connectionStrings>

Player class:

public class Player
{
    public int Id { get; set; }
    public string Sex { get; set; }
    public string Vocation { get; set; }
    public int Level { get; set; }
    public string Achievements { get; set; }
    public string World { get; set; }
    public DateTime? LastLogin { get; set; }
    public string AccountStatus { get; set; }
    public bool IsOnline { get; set; }
}

RookstayersDbContext:

public class RookstayersDbContext : DbContext
{
    public RookstayersDbContext() : base("RookstayersDbContext") { }
    public DbSet<Player> Players { get; set; }
}

Your ETL code:

你的ETL代码:

//Replace this with wherever you pull the info from (as a Player entity)
var currentPlayersData = new List<Player>();

using(var ctx = new RookstayersDbContext())
{
    //Iterate through your new data collection
    foreach(var player in currentPlayersData)
    {
        var dbPlayer = ctx.Players.FirstOrDefault(x => x.Name == player.Name);

        //Insert new player if not existing
        if(dbPlayer == null)
        {
            player.IsOnline = true; //ensure flagged as online
            ctx.Players.Add(player); //add new player
            continue; //proceed through to next current player
        }

        //If player already exists, flag as online
        dbPlayer.IsOnline = true;
    }

    //Now iterate through your database collection
    foreach(var player in ctx.Players)
    {
        //If no player information found in current players data, flag as offline
        if (!currentPlayersData.Any(x => x.Name == player.Name))
            player.IsOnline = false; //flag player as offline
    }

    //Save any changes made in this context to database
    ctx.SaveChanges();
}

#5

            try
            {
                using (var connection = new MySqlConnection(connString))
                {
                    connection.Open();
                    using (var command = new MySqlCommand())
                    {
                        command.Connection = connection;
                        command.CommandText = @"SELECT FROM rookstayers where name = @name";
                        MySqlDataReader reader = command.ExecuteReader();

                        foreach (var rooker in Players)
                        {
                            while (reader.Read())
                            {
                                if (reader.HasRows())
                                {
                                    command.CommandText = @"UPDATE rookstayers SET onlinestatus CASE WHEN name = @name THEN 1 ELSE 0 END; UPDATE rookstayers SET level = 0 WHERE name = @name";
                                    command.Parameters.AddWithValue("@name", rooker.Name);
                                    command.Parameters.AddWithValue("@onlineStatus", rooker.OnlineStatus);
                                    command.ExecuteNonQuery();
                                }
                                else
                                {
                                    command.CommandText = @"INSERT INTO rookstayers (name, sex, vocation, level, achievements, world, lastlogin, accountstatus, onlinestatus) VALUES (@name, @sex, @vocation, @level, @achievements, @world, @lastLogin, @accountStatus, @onlineStatus)";
                                    command.Parameters.AddWithValue("@name", rooker.Name);
                                    command.Parameters.AddWithValue("@sex", rooker.Sex);
                                    command.Parameters.AddWithValue("@vocation", rooker.Vocation);
                                    command.Parameters.AddWithValue("@level", rooker.Level);
                                    command.Parameters.AddWithValue("@achievements", rooker.Achievements);
                                    command.Parameters.AddWithValue("@world", rooker.World);
                                    command.Parameters.AddWithValue("@lastLogin", rooker.LastLogin);
                                    command.Parameters.AddWithValue("@accountStatus", rooker.AccountStatus);
                                    command.Parameters.AddWithValue("@onlineStatus", "1");
                                    command.ExecuteNonQuery();
                                } 

                            }
                        }
                    }
                }
            }
            finally
            {
                Players.Clear();
                connection.Close();
            }