guid mysql_關于MySQL：MySQL-如何搜索GUID

我正在使用第三方.NET庫(Rhino Security)，將其標識符存儲為mysql數據庫中binary(16)字段中的向導。一切都可以從該應用程序完美地工作，但是當我嘗試通過查詢編輯器(對于MySQL為TOAD)手動運行查詢時，沒有行返回我知道存在的標識符。例如，如果我運行以下查詢，則不會得到任何結果：

SELECT Id, EntitySecurityKey, Type

FROM mydb.security_entityreferences

where EntitySecurityKey ?= ?'02a36462-49b7-406a-a3b6-d5accd6695e5'

在不使用過濾器的情況下運行同一查詢將返回許多結果，其中一個在EntitySecurityKey字段中具有上述GUID。還有另一種方法可以編寫查詢以搜索GUID /二進制字段嗎？

謝謝！

編輯

我發現TOAD返回一個字符串而不是一個丑陋的Blob很有趣。使用不同的編輯器返回結果(用于未過濾的查詢)，我得到了原始二進制數據。我以為我的查詢將使用二進制關鍵字運行，但以下兩項均無效：

SELECT Id, EntitySecurityKey, Type

FROM mydb.security_entityreferences

where EntitySecurityKey ?= ?BINARY '02a36462-49b7-406a-a3b6-d5accd6695e5'

SELECT Id, EntitySecurityKey, Type

FROM mydb.security_entityreferences

where BINARY ?EntitySecurityKey ?= ?'02a36462-49b7-406a-a3b6-d5accd6695e5'

我不熟悉Rhino Security，但據我所知它使用NHibernate來存儲到數據庫。

.net Guid內部使用1個Int32、2個Int16和8個字節來存儲128位Guid值。

當NHibernate(3.2)將guid值存儲到BINARY(16)列中或從中提取一個Guid值時，它分別使用.Net ToByteArray()方法和Guid(Byte [] ...)構造函數。這些方法基本上將字節順序交換為Int32和Int16值。您可以考慮一下方法以查看確切的代碼，但這是前4個字節的簡單示例：

guidBytes[0] = (byte)this._int32member;

guidBytes[1] = (byte)(this._int32member >> 8),

guidBytes[2] = (byte)(this._int32member >> 16),

guidBytes[3] = (byte)(this._int32member >> 24);

這可能是造成您問題的原因。

例如，您的Guid以不同的方式存儲在MySql中。

02a36462-49b7-406a-a3b6-d5accd6695e5-Guid.ToString()的指導

6264a302-b749-6a40-a3b6-d5accd6695e5-您使用MySql hex(Guid)在MySql中進行的引導

注意：hex(x)不添加破折號；為了進行比較，我手動添加了破折號。

請注意，前8個字節的順序不同。

要測試這是否是您的問題，請運行以下查詢：

SELECT Id, EntitySecurityKey, Type

FROM mydb.security_entityreferences

where EntitySecurityKey = UNHEX(REPLACE(6264a302-b749-6a40-a3b6-d5accd6695e5,'-',''));

如果是這樣，我在自己的旅行中發現了其他一些事項：

Toad for MySql也使這一點變得復雜，因為它會在select語句輸出中將Guid顯示為.net等效項。如果將Guid包含在select的where子句中(至少根據我的經驗)，它將不會應用相同的轉換。您將需要使用MySql存儲的Guid。

您可能需要在執行數據庫查詢時手動轉換Guid，或者可能需要引入自定義NHibernate類型以不同方式轉換Guid。

您可以參考：

如何將.NET Guid讀取為Java UUID

http://pastebin.com/M07wnK5K(我沒有使用此代碼，但發現它對參考很有用)

更新：

我只是在上面的pastebin鏈接上嘗試了自定義NH類型。字節順序不正確，至少對于我的環境而言。我會需要：

private static readonly int[] ByteOrder = new[] { 3, 2, 1, 0, 5, 4, 7, 6, 8, 9, 10, 11, 12, 13, 14, 15 };

使.Net輸出與從表輸出中選擇的十六進制(Guid列)匹配。

讓我感到非常有趣的是，您將GUID存儲在binary(16)字段中，重點放在16上。根據手冊，binary字段的長度以字節為單位，并將截斷它上面的所有內容(僅在嚴格模式)。您的GUID是否有可能被截斷？使用示例GUID 02a36462-49b7-406a-a3b6-d5accd6695e5，嘗試使用前16個字符查詢數據庫：

WHERE EntitySecurityKey = '02a36462-49b7-40'

根據對此問題的公認答案，字段應為char(16) binary來存儲GUID，而不僅僅是binary(16)。但是，我無法在示例表中使用它。

對我有用的是使用char(36)以及binary(36)。嘗試將您的字段長度更新為36，而不是16(為了安全起見，我會先在測試表上執行此操作)。

這是我的測試表：

CREATE TABLE test_security_keys (

test_key1 char(16) not null,

test_key2 char(16) binary not null,

test_key3 char(36) not null,

test_key4 binary(16) not null,

test_key5 binary(36) not null

);

然后，我運行了一個腳本來插入許多GUID(一行中的每一列都插入一個GUID)。您可以使用示例GUID對其進行測試：

INSERT INTO test_security_keys

VALUES ('02a36462-49b7-406a-a3b6-d5accd6695e5', '02a36462-49b7-406a-a3b6-d5accd6695e5', '02a36462-49b7-406a-a3b6-d5accd6695e5', '02a36462-49b7-406a-a3b6-d5accd6695e5', '02a36462-49b7-406a-a3b6-d5accd6695e5');

使用簡單的SELECT * FROM test_security_keys將顯示除大小為36的列以外的所有列。另外，無論是否binary，我都能通過常規的字符串比較成功查詢列：

SELECT * FROM test_security_keys WHERE test_key3 = '02a36462-49b7-406a-a3b6-d5accd6695e5';

SELECT * FROM test_security_keys WHERE test_key5 = '02a36462-49b7-406a-a3b6-d5accd6695e5';

如果您已確認當前帶有binary(16)的列沒有被截斷，那么我建議您在WHERE子句中使用CAST()。以下應該適用(與您的示例查詢一起使用)：

SELECT Id, EntitySecurityKey, Type

FROM mydb.security_entityreferences

WHERE

EntitySecurityKey = CAST('02a36462-49b7-406a-a3b6-d5accd6695e5' AS binary(16));

如果您CAST(.. AS binary(16))并且輸入數據長于16個字節，則MySQL應該發出警告(應該是看不見的，不影響任何內容)，指出它必須截斷數據(如果得到了，請嘗試SHOW WARNINGS;)。這是預料之中的，但這也意味著您不能使用binary(16)來存儲GUID，而需要使用binary(36)或char(36)。

*我沒有使用TOAD嘗試任何一種方法，但是我同時使用了命令行MySQL和Navicat，并且兩者的結果相同。

這不是問題，GUID只是一個128位的數字，即16個字節，可以存儲在一個包含16個字節的二進制字段中-這就是binary(16)。問題在于，與其他系統相比，.NET guid.ToByteArray()處理字節序的方式有所不同。請閱讀下面的@ user1748279答案以獲取適當的信息，或者直接訪問stackoverflow.com/questions/5745512/

當我評估其他人如何"搜索"主鍵為二進制類型(主要是指示GUID的binary(16))的記錄并將其與我自己的記錄進行比較時，遇到了這個問題。我必須承認我有點退縮，答案迅速脫離了用戶希望根據二進制字段進行手動搜索的想法，而是專注于將列類型本身更改為字符，在這種情況下，這是對InnoDB存儲的謀殺。

對于缺少結果的嘗試解決方案是針對Id列而不是EntitySecurityKey列進行搜索。給定JP的原始查詢，修改后的工作版本為：

SELECT Id, EntitySecurityKey, Type

FROM mydb.security_entityreferences

where Id ?= ?UNHEX(REPLACE('02a36462-49b7-406a-a3b6-d5accd6695e5', '-', ''));

同樣，這假定Id字段是binary(16)類型。 (我無法確定哪一列：Id，EntitySecurityKey是二進制文件(16))。如果EntitySecurityKey是binary(16)列，則：

SELECT Id, EntitySecurityKey, Type

FROM mydb.security_entityreferences

where EntitySecurityKey ?= ?UNHEX(REPLACE('02a36462-49b7-406a-a3b6-d5accd6695e5', '-', ''));

如果未能產生期望的結果，則針對此問題(如何將.NET Guid讀取為Java UUID)，嘗試進行相同的查詢但GUID的前三個部分的字節序顛倒了，這將很有意義。

SELECT Id, EntitySecurityKey, Type

FROM mydb.security_entityreferences