在 sqlite select 中使用 guid，其中 guid 作为二进制文件存储在 sqlite 数据库中
我在 SQLite 数据库中有一个表Employee。其主键是GUID类型的“ID”。我尝试使用以下查询查找 id = guid 'a8828ddf-ef22-4d36-935a-1c66ae86ebb3' 的记录，但没有成功：
SELECT * FROM Employee
WHERE Employee.Id = 'a8828ddf-ef22-4d36-935a-1c66ae86ebb3'
任何人都可以告诉我该怎么办写查询？
谢谢，
I have a table Employee in the SQLite database. Whose primary key is "ID" of GUID type. I try to find the record with id = guid 'a8828ddf-ef22-4d36-935a-1c66ae86ebb3' with the following query with no luck:
SELECT * FROM Employee
WHERE Employee.Id = 'a8828ddf-ef22-4d36-935a-1c66ae86ebb3'
Can anyone tell me how should I write the query?
Thanks,
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
GUID 可能存储为二进制 blob；尝试：
The GUID is probably being stored as a binary blob; try:
这是一个老问题，但会出现搜索，虽然所选答案是正确的，但它可能并不适合每个人。例如，当使用 .NET 绑定到 SQLite 时。
原因是，当 GUID 的格式为
a8828ddf-ef22-4d36-935a-1c66ae86ebb3时，您不能只删除破折号并将其放入X'...'。 GUID 分为多个部分，并且数据库以不同的方式存储二进制文件。当 GUID 为
a8828ddf-ef22-4d36-935a-1c66ae86ebb3时，它将以小端存储为X'df8d82a822ef364d5a93b3eb86ae661c'。请注意，字符串中的所有部分都是相反的。当然，如果您通过删除破折号直接插入数据
X'a8828ddfef224d36935a1c66ae86ebb3'您也可以用它来检索它，但如果您让 .NET 处理转换，GUID 将存储为小端字节表示。（这一切都假设为小端架构，尚未在大端架构上进行测试）
This is a old question, but comes up with searches and while the selected answer is correct, it might not work for everyone easily. For example when using .NET bindings to SQLite.
The reason is that when the GUID is in format
a8828ddf-ef22-4d36-935a-1c66ae86ebb3you cannot just remove the dashes and put it toX'...'. The GUID is in several parts and the database is storing the binary differently.When the GUID is
a8828ddf-ef22-4d36-935a-1c66ae86ebb3it will be stored in little endian asX'df8d82a822ef364d5a93b3eb86ae661c'. Note that all parts are reversed within the string.Of course if you insert the data directly by removing the dashes as
X'a8828ddfef224d36935a1c66ae86ebb3'you can also retrieve it with that, but if you let .NET to handle the conversion, GUID will be stored as little endian byte representations.(This all assumes little endian architecture, haven't tested on big endian)
萨米的回答为我指明了正确的方向。
.NET 以不同的方式输出 Guid，因此如果您使用 .NET 存储它，而不是使用 rawsql 获取数据，则需要以相同的方式转换 rawsql 中的 guid。
然而，就我而言，这不是小端表示法。
使用以下代码，您可以找到正确的字符串：
在我的计算机上，返回：“DF8D82A822EF364D935A1C66AE86EBB3”
因此我的原始sql必须是
为什么我需要这个；因为我使用使用 EF Core 填充的内存 SQLite 数据库，我想在其中测试 rawSql 命令。
这对于处于相同情况的其他人来说可能会很方便。但是，请记住，在 SQLite 中工作的 rawSql 可能无法在另一个数据库上工作（反之亦然）。
The answer of Sami pointed me in the right direction.
.NET outputs a Guid in a different way, so if you store it by using .NET and than use rawsql to fetch the data, then the guid in the rawsql need to be transformed in the same way.
Howerver, in my case it was not the little endian notation.
With the following code you can find the correct string:
On my computer, this returns: "DF8D82A822EF364D935A1C66AE86EBB3"
Hence my raw sql will have to be
Why I needed this; because I use an in-memory SQLite DB that I populate using EF Core, where I want to test a rawSql command.
This might be handy for others in the same situation. However, keep in mind that a rawSql that works in SQLite might not work on another DB (and vice versa).
Sqlite 还可以配置为将 GUID 存储为文本。这是在连接字符串中完成的。
数据源=c:\mydb.db;Version=3;BinaryGUID=False;请参阅https://www.connectionstrings.com/sqlite/
Sqlite can also be configured to store GUIDs as text. This is done in the connection string.
Data Source=c:\mydb.db;Version=3;BinaryGUID=False;See https://www.connectionstrings.com/sqlite/