从 C# 在 MySQL 中存储 GUID
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/742099/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Store GUID in MySQL from C#
提问by Tim Skauge
Trying to persist Guid's in a MySQL db from C# (.NET). MySQL column is of type BINARY(16). Any sugestion on how to persist the guid and later get the guid back to from MySQL? Looking for a code answer here :-)
试图将 Guid 保留在 C# (.NET) 的 MySQL 数据库中。MySQL 列的类型为 BINARY(16)。关于如何持久化 guid 并稍后从 MySQL 获取 guid 的任何建议？在这里寻找代码答案:-)
采纳答案by Tim Skauge
Figured it out. Here's how ...
弄清楚了。就是这样 ...
Database schema
数据库模式
CREATE TABLE `test` (
`id` BINARY(16) NOT NULL,
PRIMARY KEY (`id`)
)
Code
代码
string connectionString = string.Format("Server={0};Database={1};Uid={2};pwd={3}", "server", "database", "user", "password");
Guid orgId = Guid.NewGuid();
Guid fromDb = Guid.Empty;
using (MySqlConnection conn = new MySqlConnection(connectionString))
{
conn.Open();
using (MySqlCommand cmd = new MySqlCommand("INSERT INTO test (id) VALUES (?id)", conn))
{
cmd.Parameters.Add("id", MySqlDbType.Binary).Value = orgId.ToByteArray();
cmd.ExecuteNonQuery();
}
using (MySqlCommand cmd = new MySqlCommand("SELECT id FROM test", conn))
{
using (MySqlDataReader r = cmd.ExecuteReader())
{
r.Read();
fromDb = new Guid((byte[])r.GetValue(0));
}
}
}
回答by Magnus Johansson
Apparently, the GetGuid()method in MySQL .NET Connector v5.2.6+ should be fixed so you can use this example.
显然，GetGuid()MySQL .NET Connector v5.2.6+ 中的方法应该是固定的，因此您可以使用此示例。
回答by nawfal
1)You can insert it the way @Tim Skauge does. But while selecting the .Net connector version is important. When I had used v 5.2.1, I needed to do only this:
1）您可以像@Tim Skauge 那样插入它。但是在选择 .Net 连接器版本时很重要。当我使用 v 5.2.1 时，我只需要这样做：
using (MySqlCommand cmd = new MySqlCommand("SELECT id FROM test", conn))
{
using (MySqlDataReader r = cmd.ExecuteReader())
{
r.Read();
Guid id = (Guid)r[0];
}
}
Here the reader itself reads the binary value to .NET Guidtype. You can see it if you check the type of r[0]. But with newer version, ie, 6.5.4, I found the type to be byte[].. ie, it gets the binary value from db to its corresponding byte array. So you do this:
在这里，读取器本身将二进制值读取为 .NETGuid类型。如果您检查r[0]. 但是对于较新的版本，即 6.5.4，我发现类型为byte[].. 即，它将二进制值从 db 获取到其相应的字节数组。所以你这样做：
using (MySqlCommand cmd = new MySqlCommand("SELECT id FROM test", conn))
{
using (MySqlDataReader r = cmd.ExecuteReader())
{
r.Read();
Guid id = new Guid((byte[])r[0]);
}
}
You can read why is it so here in documentation. The alternative to read Guid type directly and not as byte[] is to have this line : Old Guids=truein your connection string.
您可以在文档中阅读为什么会这样。直接读取 Guid 类型而不是 byte[] 的替代方法是Old Guids=true在您的连接字符串中包含以下行：
2)Additionally you can do this straight away to read the binary value as string by asking MySQL to do the conversion, but in my experience this method is slower.
2）此外，您可以通过要求 MySQL 进行转换来立即将二进制值读取为字符串，但根据我的经验，此方法较慢。
Insert:
插入：
using (var c = new MySqlCommand("INSERT INTO test (id) VALUES (UNHEX(REPLACE(@id,'-','')))", conn))
{
c.Parameters.AddWithValue("@id", Guid.NewGuid().ToString());
c.ExecuteNonQuery();
}
or
或者
using (var c = new MySqlCommand("INSERT INTO test (id) VALUES (UNHEX(@id))";, conn))
{
c.Parameters.AddWithValue("@id", Guid.NewGuid().ToString("N"));
c.ExecuteNonQuery();
}
And select:
并选择：
using (MySqlCommand cmd = new MySqlCommand("SELECT hex(id) FROM test", conn))
{
using (MySqlDataReader r = cmd.ExecuteReader())
{
r.Read();
Guid id = new Guid((string)r[0]);
}
}
The one thing you need to notice is that if you are inserting Guids by hexmethod then you got to read it by the unhexapproach. If you are inserting them relying on .NET's ToByteArray()method, then you got to read similarly. Otherwise you will get incorrect guids read since .NET has a peculiar way of ordering the bytes according to endianness. Catch something about it here in the context of inserting and reading Guids in .NET
您需要注意的一件事是，如果您通过hex方法插入 Guid，那么您必须通过方法阅读它unhex。如果您根据 .NET 的ToByteArray()方法插入它们，那么您必须类似地阅读。否则，您将获得不正确的 guid，因为 .NET 有一种特殊的方式根据字节序对字节进行排序。在 .NET 中插入和读取 Guids 的上下文中了解它
