如何将MySQL生成的UUID转换为C＃变量 [英] How to get UUID generated by MySQL into a C# variable

问题描述

这里是查询：

string query = @"INSERT INTO session (PK_Id, user_id, login_time, machine_ip, machine_fingerprint) 
                             VALUES (UUID(), @UId, @LogInTime, @MIp, @MFingerPrint);
                ";

现在我需要最后插入的ID，它是MySQL生成的UUID。据我所读，UUID没有select_last_insert_id（）函数！我为php阅读，您可以先将UUID（）函数分配给变量，然后返回该值。但是如何在C＃中做到这一点呢？

Now I need this last inserted id back, which is a UUID generated by MySQL. As far as I read there is no select_last_insert_id() function for UUIDs!! And I read for php you could assign UUID() function first to a variable and then return that value. But how to go about that in C#?

类似这样的东西，但不完全是：

Something like this, but not exactly:

string query = @"INSERT INTO session (PK_Id, user_id, login_time, machine_ip, machine_fingerprint) 
                             VALUES (@UUID = SELECT UUID(), @UId, @LogInTime, @MIp, @MFingerPrint);
                ";                  //how to do this here?

这里是我的更多代码：

string query = @"INSERT INTO session (PK_Id, user_id, login_time, machine_ip, machine_fingerprint) 
                 VALUES (@UUID = SELECT UUID(), @UId, @LogInTime, @MIp, @MFingerPrint);
                ";

try
{
    if (_conn.State != ConnectionState.Open)
        _conn.Open();
    MySqlCommand cmd = new MySqlCommand(query, _conn);
    cmd.Parameters.AddWithValue("@UId", Utility.usr.Id);
    cmd.Parameters.AddWithValue("@LogInTime", DateTime.Now);
    cmd.Parameters.AddWithValue("@MIp", GetMachineIP());
    cmd.Parameters.AddWithValue("@MFingerPrint", GetHardwareFingerPrint());

    var s= Convert.ToString(cmd.ExecuteScalar()); //this returns an empty string :(
    //I need to get it to any .NET data type, string, or Guid or byte[] or anything. 

但是我需要将此数据类型 s 用于其他 WHERE 子句，例如：

But I need this datatype of s to be used in another WHERE clause in a query like this:

string query = @"UPDATE session SET logout_time = @LogOutTime 
                 WHERE user_id = @UId AND PK_Id = @SessionId";

try
{
    if (_conn.State != ConnectionState.Open)
        _conn.Open();
    MySqlCommand cmd = new MySqlCommand(query, _conn);
    cmd.Parameters.AddWithValue("@UId", Utility.usr.Id);
    cmd.Parameters.AddWithValue("@SessionId", s);
    cmd.Parameters.AddWithValue("@LogOutTime", DateTime.Now);
    cmd.ExecuteScalar();

此处 @ SessionId 是同一表中的UUID字段。因此，基本上，我在C＃中获取MySQL varbinary字段，以便可以使用该类型通过在另一个查询中指定WHERE来确定日期？

Here @"SessionId" is the UUID field in the same table. So basically, how can I get the MySQL varbinary field in C# so that I could use that type to update by specifying WHERE in another query?

在MySQL表中，UUID字段是varbinary （我希望看到一些不是另一个php链接或不要求我切换到数据库中的char数据类型：））。

In MySQL table the UUID field is varbinary (I hope to see some solution that is not another php link or that is not asking me to switch to char datatype in the database :) ).

编辑：这里的问题是我们已经在表中添加了MySQL生成的大量UUID，因此对于将MySQL UUID更改为.NET Guid感到有些担忧。如果这是唯一的解决方法，我会考虑的。只是这是我们第一次需要返回插入的UUID值，以便可以在另一个时间点在另一个查询中进行更新。

The problem here is we have already added plenty of UUIDs generated by MySQL into the table, so I'm a bit apprehensive about changing MySQL UUID to .NET Guid. If that's the only workaround, I'll consider that. Just that this is the first time we needed the inserted UUID value back so that I can update in another query another point of time.

一个子问题：.NET Guid与MySQL UUID完全一样吗？

A sub question: Is .NET Guid exactly the same thing as MySQL UUID?

推荐答案

我认为您可以不必依赖MS Guid就可以继续进行较早的实现，但是我担心我来不及了：）

I think you can go ahead with your earlier implementation without having to rely on MS Guid, but I fear I am too late :)

string query = @"INSERT INTO session (PK_Id, user_id, login_time, machine_ip, machine_fingerprint) 
                             VALUES (UUID(), @UId, @LogInTime, @MIp, @MFingerPrint); 
                 SELECT PK_Id FROM session WHERE login_time=@LogInTime AND machine_fingerprint=@MFingerPrint; //or something similar which gives you the exact same id - UUID
                ";

try
{
    if (_conn.State != ConnectionState.Open)
        _conn.Open();
    MySqlCommand cmd = new MySqlCommand(query, _conn);
    cmd.Parameters.AddWithValue("@UId", Utility.usr.Id);
    cmd.Parameters.AddWithValue("@LogInTime", DateTime.Now);
    cmd.Parameters.AddWithValue("@MIp", GetMachineIP());
    cmd.Parameters.AddWithValue("@MFingerPrint", GetHardwareFingerPrint());

    MySqlDataReader r = cmd.ExecuteReader();

    if (r.Read()) //ensure if it is read only once, else modify your `WHERE` clause accordingly
    {
        var s = (Guid)r[0];
    }
    //or even (Guid)cmd.ExecuteScalar() would work

现在，您可以像这样查询更新：

Now you can query in update like this:

string query = @"UPDATE session SET logout_time = @LogOutTime 
                        WHERE user_id = @UId AND PK_Id = @SessionId";

try
{
    if (_conn.State != ConnectionState.Open)
        _conn.Open();
    MySqlCommand cmd = new MySqlCommand(query, _conn);
    cmd.Parameters.AddWithValue("@UId", Utility.usr.Id);
    cmd.Parameters.AddWithValue("@SessionId", s.ToByteArray());
    cmd.Parameters.AddWithValue("@LogOutTime", DateTime.Now);
    cmd.ExecuteNonQuery();

注意：这里我转换了Guid变量 s 在查询之前为字节数组。这很重要，在 WHERE 子句中，是 UPDATE 还是 SELECT 查询中的语句。我会要求您从varbinary移至MySQL表中的二进制字段。

Note: Here I have converted the Guid variable s to byte array before querying. This is important, in WHERE clause, be it UPDATE or SELECT statements in query. I would ask you to move to binary field in MySQL table from varbinary.

编辑：如果您的表会急剧增大，则插入并选择这是一个坏主意，因为 SELECT 查询是正在运行的附加查询。在这种情况下，@ PinnyM的选择更好。我真的不认为MySQL或任何其他数据库会使用默认的方式来返回不是数据库生成的自定义插入ID。简而言之，我建议您不要这么做。

If your table would grow dramatically large then inserting and selecting is a bad idea since SELECT query is an additional query being run. In that case @PinnyM's choice is better. I really do not think MySQL or any other database would have a default way to give back "custom" inserted ids which are not something database generated. So in short I advice you to not go for this..

Edit2 ：请参见答案以获取.NET数据类型的二进制值。有时，根据MySQL .NET连接器版本，强制转换无法正常工作。

Edit2: See this answer for getting binary value to .NET datatype. Sometimes casting do not work depending on MySQL .NET connector version..

这篇关于如何将MySQL生成的UUID转换为C＃变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！