如何创建触发器以防止在MYSQL 5.0.27中插入重复的行? [英] How to create Trigger to prevent inserting the duplicate row in MYSQL 5.0.27?
问题描述
This question follows up the following previous question. is it possible to make a field in Mysql table to to have length max=700, to be unicode (3 bytes) and to be unique?
我有一个表MyText，其中有ID列，text列&许多其他列
I have a table MyText which have ID column, text column & many other columns
Id - text - type ..& other fileds
1 - this is my text
2 - xxxx
我希望text列支持最大长度可以包含700个Unicode字符的unicode.我无法设置Unique (text)，因为MYSQL仅支持765字节的唯一列最大长度，而Unicode需要3个字节，因此我需要2100字节(700 * 3)唯一列.
I want the text column support unicode with max length can hold 700 Unicode characters. I can't set Unique (text) because MYSQL only supports 765 bytes max length for unique column while Unicode takes 3 bytes so I need 2100 bytes (700*3) unique column.
因此，解决方案是创建一个触发器，以防止用户插入重复项.例如，如果用户在MyText表中插入"THIS is My Text"(我们不在乎大小写)，则Mysql将完全中止包含该插入语句的所有查询，并生成一个SQLException以防止系统执行进行其他查询.
So, the solution is to crate a trigger that prevents the user to insert the duplicate. For example, if user inserts "THIS is My Text" (We won't care case sensitive) into MyText table, then Mysql will abort completely ALL Queries that contain that Inserting Statement and will generate an SQLException to prevent the system to do other query.
好吧，假设您必须在Java代码中运行一系列sql语句
Ok, suppose you have to run a series of sql statements in your Java code
insert into MyText('THIS is My Text',1);
insert into OtherTable ('some text');
update othetTable...
然后，当系统执行insert into MyText('THIS is My Text',1);时，它应该停止在其下进行其他查询.
Then when the system doing the insert into MyText('THIS is My Text',1);, it should stop doing other queries below it.
另外，有人建议做prefix index来帮助Nysql更快地完成select，但是我不确定这样做是否有必要，因为我获得了ID列索引.
Also, some people suggests to do the prefix index to help Nysql to do the select quicker, but I am not sure it is too necessary since I got ID colum which was indexed.
注意:MYSQL 5.027是2006年的版本，已经很老了，但我喜欢它
Note: MYSQL 5.027 is 2006 version which is pretty old, but I love it
那么如何创建符合我的要求的触发器或如何解决我的问题?
SO how to create trigger that meets my requirement or how to fix my problem?
推荐答案
出于性能原因，我不建议为此使用触发器.
I would recommend not using a trigger for this because of performance reasons.
相反，创建一个附加列以存储 MD5 或 SHA1 您的值的哈希值，并使用约束使该列唯一.
Instead, create an additional column to store an MD5 or SHA1 hash of your value, and make that column unique using a constraint.
根据以上链接，这两个哈希函数都存在于您的MySQL版本中.另外，如果更轻松地将其集成到Java代码中，则可以使用
As per the above links, both hashing functions exist in your version of MySQL. Alternatively, if it's easier to integrate this in your Java code, you could do the hashing in Java using the MessageDigest class.
如果您的插入部分由于重复而失败，则表明您的问题中的部分表明您不应执行进一步的查询，最好使用
The part in your question where you indicate that no further queries should be executed if the insert statement fails because of a duplicate, is best handled using transactions. These are also supported in Java using plain JDBC or most ORM frameworks.
这篇关于如何创建触发器以防止在MYSQL 5.0.27中插入重复的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
