转换一个MySQLGuid(从Devart dotConnect for MySQL)到字符串的SQL

Question

我正在使用Devart的DotConnect产品将。net应用程序连接到MySQL数据库。一切都工作得很好，但我有一个问题，当在数据库中使用SQL。

应用程序在数据库中使用Guids作为行id(最初来自SQL Server应用程序)，这些行id在数据库中存储为BINARY(16)(并在通过。net应用程序访问时使用DevArt的MySQLGuid类)

问题是，当我想查询数据库中的特定行时，我不能只是在数据库中粘贴GUID的字符串表示，所以我试图找出一个函数来将GUID的二进制表示转换为字符串。

首先，我认为在查询中调用HEX(id)是一个相当简单的问题，然后如果我想要一个友好的可读输出，我需要做的就是在适当的位置添加-'s。

select语句可能看起来像这样(在实践中，我将其包装为一个函数):

 LOWER(CONCAT(LEFT(HEX(theme_id), 8), '-', MID(HEX(theme_id), 9,4), '-', MID(HEX(theme_id), 13,4), '-', MID(HEX(theme_id), 17,4), '-', RIGHT(HEX(theme_id), 12)))

返回的GUID不完全正确。例如，如果我存储d1dfd973-fa3d-4b90-a1eb-47217162cd40，那么上面的选择语句返回73d9dfd1-3dfa-904b-a1eb-47217162cd40

看起来前8个字节在组中的顺序颠倒了(取字符串GUID的第一部分，我们有73d9dfd1，从字节顺序的角度来看(即将两个字符的组视为一个字节)是d1dfd973，这是正确的输出。

我的问题是-是否有一个操作，我可以做MySQL的方言的SQL，将允许我反转相关节的字节顺序?我可以写一个更复杂的LEFT/MID/RIGHT语句，但这感觉不是一个好方法。

Answer 1

例如，如果我存储d1dfd973-fa3d-4b90-a1eb-47217162cd40，那么上面的select语句返回73d9dfd1-3dfa-904b-a1eb-47217162cd40

我认为你的guid存在小端序/大端序问题。请参阅wiki页面上的"二进制编码"一节，了解guid: http://en.wikipedia.org/wiki/Globally_unique_identifier

GUIDs和uuid是16字节的值，分成4块:

Data1 : 4 byte int : big endian for UUID, little endian for ms/x86 GUIDs
Data2 : 2 byte int : big endian for UUID, little endian for ms/x86 GUIDs
Data3 : 2 byte int : big endian for UUID, little endian for ms/x86 GUIDs
Data4 : 16 bytes   : stored in the same order for both UUIDs and GUIDs

guid和uuid通常写成十六进制字符串，使用连字符分隔数据组件。这里是关键， uuid和GUIDs字符串都以大端序用Data 1-3编写。相同的字符串可以用不同的字节模式表示，这取决于它们是存储在小端序平台上的UUID还是GUID中。

让我们分解示例GUID: d1dfd973的4字节Data1块。如果这个字符串在microsoft/intel平台上表示GUID，那么这些字节将按照以下顺序出现在内存中:

guid[0] = 0x73 // LSB first
guid[1] = 0xd9
guid[2] = 0xdf
guid[3] = 0xd1 // MSB last

我认为这可能是Devart写数据库时的字节顺序。然后，您的代码尝试使用

提取此内容。

LEFT(HEX(theme_id), 8)

，它将生成字符串73d9dfd1，因为它只是按照存储的顺序使用数据。

有一个线索，我是在正确的轨道上:你的代码读取Data4以正确的顺序(a1eb-47217162cd40在您的示例GUID)。无论平台如何，无论我们谈论的是uuid还是guid，数据都以相同的字节顺序存储。

你有几个选项

• 要求所有内容都以小端guid的形式存储在数据库中。使用像Andrzej这样的转换例程将其转换回字符串表示。

• 强制所有内容存储为大端uid。运行一次转换以对现有行的字节重新排序。您可以使用Andrzej的例程进行转换，但在此之后，您可以直接从二进制转换为字符串形式。

Answer 2

第一步

我正在创建Mysql表

CREATE TABLE `table_code` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(45) DEFAULT NULL,
  `guid` binary(16) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

步骤2)

插入一行:

INSERT INTO table_code (code,guid) 
VALUES ('d1dfd973fa3d4b90a1eb47217162cd40',UNHEX('d1dfd973fa3d4b90a1eb47217162cd40'));

第3步

I run

SELECT 
id,
code,
LOWER(CONCAT(LEFT(HEX(guid), 8),'-', MID(HEX(guid), 9,4), '-',MID(HEX(guid), 13,4), '-', MID(HEX(guid), 17,4), '-', RIGHT(HEX(guid), 12))),
LOWER(HEX(guid))
FROM test.table_code;

步骤4)结果为:

1   d1dfd973fa3d4b90a1eb47217162cd40    d1dfd973-fa3d-4b90-a1eb-47217162cd40 d1dfd973fa3d4b90a1eb47217162cd40

一切看起来都很好-在Mysql方面。

所以结论，你确定你正在插入'd1dfd973fa3d4b90a1eb47217162cd40'(这应该通过使用UNHEX('d1dfd973fa3d4b90a1eb47217162cd40')或其他(反向)值来完成。

不管怎样，转这样的字符串:"73d9dfd13dfa904ba1eb47217162cd40"到这个"d1dfd973-fa3d-4b90-a1eb-47217162cd40"

你可以使用这样的代码:

LOWER(CONCAT(
MID(HEX(guid), 7,2),
MID(HEX(guid), 5,2),
MID(HEX(guid), 3,2),
LEFT(HEX(guid), 2),'-',
MID(HEX(guid), 11,2),
MID(HEX(guid), 9,2),'-',
MID(HEX(guid), 15,2),
MID(HEX(guid), 13,2),'-',
MID(HEX(guid), 17,4), '-', 
RIGHT(HEX(guid), 12)))

我认为Mysql没有给我们更简单的方法，因为没有这样的函数操作二进制数据。

查看:

步骤5)

INSERT INTO table_code (code,guid) 
VALUES ('73d9dfd13dfa904ba1eb47217162cd40',UNHEX('73d9dfd13dfa904ba1eb47217162cd40'));

步骤6)

    SELECT 
    id,
    code as `raw code`,
    LOWER(CONCAT(LEFT(HEX(guid), 8),'-', MID(HEX(guid), 9,4), '-',MID(HEX(guid), 13,4), '-', MID(HEX(guid), 17,4), '-', RIGHT(HEX(guid), 12))),
    LOWER(HEX(guid)) `decoded`,
LOWER(CONCAT(
MID(HEX(guid), 7,2),
MID(HEX(guid), 5,2),
MID(HEX(guid), 3,2),
LEFT(HEX(guid), 2),'-',
MID(HEX(guid), 11,2),
MID(HEX(guid), 9,2),'-',
MID(HEX(guid), 15,2),
MID(HEX(guid), 13,2),'-',
MID(HEX(guid), 17,4), '-', 
RIGHT(HEX(guid), 12))) as switched
    FROM test.table_code;

步骤7)看起来还可以。