Geek Answers Handbook

How to tell MySQL about hash launch before update / insert?

I am trying to create a trigger in MySQL, so every time I insert a value into a column called title , a HASH must be created and saved in the title_hash column. Since I do not know how this works, I found this code during the search:

 CREATE TRIGGER insertModelHash BEFORE INSERT ON products FOR EACH ROW SET NEW.model_hash = CONV(RIGHT(MD5(NEW.products_model), 16), 16, 10)

The MySQL link tells me what this means:

  • Create a trigger called insertModelHash ...
  • ... before inserting an int table products row ...
  • use the MD5, RIGHT, CONV functions in the products_model column on every new row that I intend to insert.

3. the point needs further explanation:

  • I assume that NEW is a kind of newlines identifier. Therefore, NEW.products_model points to the products_model column in the current (new) row.
  • Then MD5 is issued. Since I want to use SHA-2, it is obvious to me to change MD5(NEW.products_model), 16) ===> SHA2(NEW.products_model), 224) .
  • And now I'm fighting: why does this guy use CONV(RIGHT(...)...) ? Is it really necessary?

Additional information: I am doing now

 hashlib.sha224(title).hexdigest()

in Python and save this value.

I appreciate any suggestions / explanations!

+4
source share
1 answer

To answer three questions:

The NEW keyword refers to the "pseudo-table" for the record to be inserted. In the updated trigger, you can access both "NEW" and "OLD", as well as delete, only "OLD".

Yes, MD5 is used to create a hash. However, in your question, you have a part of the parameter to the enabled "RIGHT" function. This is only MD5(NEW.products_model) (not there , 16) ). And yes, you can replace SHA2 with MD5 if it is available (it is only available if MySQL is configured to support SSL).

RIGHT(string, number) just takes the correct characters 'number' from 'string'. The CONV () function is used to convert numbers between stems. The combination of these last two functions takes the correct 16 hash characters and converts them from base 16 (hexadecimal) to base 10 (decimal).

And the answer is no, you do not need it if all you want to do is save the hash itself.

  NEW.model_hash = SHA2(NEW.products_model)
+5
source

More articles:


All Articles