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

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   t  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)