問題描述
我有一個表，其中包含一個 url 和一個表示其參數(shù)的字符串.問題是我想要一個 url 和一個參數(shù)字符串作為表的唯一約束 - 也就是沒有條目可以具有相同的 url AND 參數(shù)字符串.參數(shù)字符串可以是任意長度(大于 800 字節(jié)左右，這是 MySql 鍵的最大長度，所以我不能使用 Unique(url, params) 因為它會引發(fā)錯誤......).
I have a table containing an url and a string representing its parameters. The problem is I want an url and a parameterstring to be the unique constraint for the table - aka no entries can have the same url AND parameter string. The parameter string can be of arbitrary length (longer than 800bytes or so which is the max length for a MySql key, so I cant use Unique(url, params) since it throws an error...).
我考慮過使用觸發(fā)器來執(zhí)行此操作，但是如果觸發(fā)器發(fā)現(xiàn)插入即將插入重復(fù)條目，我該如何拋出異常/引發(fā)錯誤?我想我想像 MySql 那樣拋出一個 MySqlException 并使用重復(fù)的主鍵等，這樣我就可以在我的 C# 代碼中捕獲它.
I thought about using triggers to do this, but how do I throw an exception/raise an error if the trigger discovers the insert is about to insert a duplicate entry? I imagine I would like to have a MySqlException thrown like MySql does with duplicate primary keys etc so I can catch it in my C# code.
我的觸發(fā)器中有兩部分需要幫助:... 中止向 C# 拋出異常 ... 如何向 C# 拋出異常等?... 允許插入 ... - 如果沒有重復(fù)條目，我如何只允許插入?
I have two pieces in the trigger I need to get help with: ... Abort throw exception to C# ... How do I throw an exception etc to C#? ... Allow insert ... - how do I just allow the insert if there is no duplicate entry?
觸發(fā)代碼如下:
CREATE TRIGGER urls_check_duplicates
BEFORE INSERT ON urls
FOR EACH ROW
BEGIN
DECLARE num_rows INTEGER;
SELECT COUNT(*)
INTO num_rows
FROM urls
WHERE url = NEW.url AND params = NEW.params;
IF num_rows > 0 THEN
... ABORT/throw exception to C# ...
ELSE
... Allow insert ...
END
推薦答案
mysql 文檔中關(guān)于觸發(fā)器的評論 表明 mysql 中沒有這樣的功能.您能做的最好的事情是為您的觸發(fā)器錯誤創(chuàng)建一個單獨的表，如同一頁面上的建議.
The comments in the mysql documentation about triggers suggest that there is no such feature in mysql. The best you can do is to create a separate table for your trigger errors, as suggested on the same page.
這篇關(guān)于如何在 MySql 觸發(fā)器中中止 INSERT 操作?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！