MySQL trigger learning (MySQL data can be synced to Redis)

Source: Internet
Author: User
Tags crc32

1. A simple example

1.1. Create a table:

CREATE TABLE T (S1 integer);

1.2. Trigger:

delimiter | create  trigger   t_trigger before insert  on   for  each row begin  set  @x = "Hello trigger"         set  new.s1 =; end |

1.3. If the trigger creates an error, it may only be deleted, at least I tried not to replace

Drop trigger T_trigger;

1.4. When the insert is executed:

INSERT into T values (1);

1.5. Triggers are executed T_trigger

Select @x,t.* from T;

1.6. You can see the results:

1.7 You can use SHOW TRIGGERS; To view a new trigger

2. Maintenance trigger for URL query hash value

2.1 CREATE TABLE Pseudohash.

2.2 Creating triggers, triggering triggers when inserting and updating tables

Delimiter |
Create trigger Pseudohash_crc_ins before insert on Pseudohash
Begin Set @x = "Hello trigger";
Set NEW.URL_CRC=CRC32 (New.url);

|
Create trigger PSEUDOHASH_CRC_UPD before update on Pseudohash
Begin Set @x = "Hello trigger";
Set NEW.URL_CRC=CRC32 (New.url);

|

delimiter;

2.3 Insert Operation

Insert into Pseudohash (URL) VALUES ("http://www.baidu.com");

Insert into Pseudohash (URL) VALUES ("http://www.163.com");

2.4 Viewing the data in a table (the data after the update operation)

2.5 Update

Update pseudohash Set url = ' www.163.com ' where id = 1;

What you can see is that after the insert and update operations, their URL_CRC are different

----------------------------------------------------------------------------

2.6 The above is derived from an example of indexing a URL, and there is a way to index it: to build a pseudo-index on a B + tree, which, unlike a real index, looks on a B + tree index, but uses a hash of the key to find instead of the key itself, which speeds up the lookup

2.6.1 Create the URLs table, note that the memory storage engine is used

CREATE TABLE ' URLs ' (
' URL ' varchar (255) DEFAULT NULL,
' URL_CRC ' int (one) DEFAULT ' 0 ',
KEY ' url ' (' url ') USING HASH
) Engine=memory DEFAULT Charset=utf8;

2.6.2 then insert the URL and URL_CRC, for example

INSERT into URLs values (' www.gougou.com ', CRC32 (' www.gougou.com '));

Like the above, or use a trigger

2.6.3 then query using hash index query

SELECT * from urls where url = "www.baidu.com" and URL_CRC = CRC32 ("www.baidu.com");

SELECT * from URLs where URL_CRC = CRC32 ("www.baidu.com");

SELECT * from urls where url = "Www.baidu.com"

The above 3 query results are of course the same, but the speed of the hash is much faster

3. Trigger syntax

3.1 CREATE TRIGGER trigger_name trigger_time trigger_event

              On tbl_name for each ROW trigger_stmt

Trigger_time is the action time of the triggering program. It can be a before or aftertrigger_event that indicates the type of statement that activates the triggering program. Trigger_event can be one of the following values:

·             Insert: Activates the trigger when inserting a new row into the table, for example, through the INSERT, LOAD data, and replace statements.             Update: Activates a trigger when a row is changed, for example, through an UPDATE statement.             Delete: Activates the trigger when a row is deleted from the table, for example, through the Delete and replace statements.
      3.2 Issues that may be encountered

If you insert/update in the trigger with the data you just inserted, it will cause a loop call.

Such as:

Create trigger test before update on test for each row update test set new.updatetime = Now () where id=new.id; END

You should use set:

Create trigger test before update on test for each row set new.updatetime = Now (); END

3.3 Triggers and stored procedures

The trigger cannot invoke the stored program that returns the data to the client, nor can it use dynamic SQL with the call statement

(Allows the stored program to return data to the trigger via parameters).

The stored procedure can accept parameters and give the result scope to the application

MySQL trigger learning (MySQL data can be synced to Redis)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.