2

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?

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

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.

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.

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...

Then when the system doing the insert into MyText('THIS is My Text',1);, it should stop doing other queries below it.

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.

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?

Community
  • 1
  • 1
Tum
  • 3,614
  • 5
  • 38
  • 63

1 Answers1

4

I would recommend not using a trigger for this because of performance reasons.

Instead, create an additional column to store an MD5 or SHA1 hash of your value, and make that column unique using a constraint.

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.

Robby Cornelissen
  • 91,784
  • 22
  • 134
  • 156
  • This. But be aware that you may get [hash collisions](https://www.princeton.edu/~achaney/tmve/wiki100k/docs/Hash_collision.html). Make sure your application is resilient to these. – Michael Green Jul 28 '14 at 02:24
  • @MichaelGreen While I agree with the theoretical possibility of a hash collision, in practice the probability would be low to the point of not even worth considering. See also this [answer](http://stackoverflow.com/a/2480819/3558960) – Robby Cornelissen Jul 28 '14 at 02:30
  • I was taught that risk is a function of likelihood and impact. I agree the likelihood of hash collision is low but cannot possibly judge the impact it may have on his system. That's why I suggest the application should be "resilient" i.e. can continue to function in a meaningful way, not "is re-coded to resolve" collisions. – Michael Green Jul 30 '14 at 01:11