Mysql and mysql download

Mysql and mysql download

In the previous project, there were not many applications. If there was no way to use the trigger, it was easy to cause disorder because it was not well maintained in the future.

In my recent project, database design (designed by others) causes some of the simplest features and queries to be complicated. A large number of tables are involved, and tables are partially related to each other. this is the worst database design I have ever seen. finally, the company architect gave the trigger solution.

I. triggers

In the project, I created a new relational table, updated some necessary and valid relationships to a table using triggers, and created indexes in the table. then, when reading the data, connect to the relational table to obtain the final valid data. it seems like the catch-up of read/write splitting, but this is not between multiple database servers.

Due to the work relationship, I cannot use the database in the project for record, so let's have a few tables to play with. Create three tables first.

Create table 'tch _ teacher '('id' INT (11) not null AUTO_INCREMENT, 'sex' SMALLINT (6) default null, 'bid' VARCHAR (36) default null, 'No' VARCHAR (20) default null, 'name' VARCHAR (30) default null, 'isdeleted' bit (1) DEFAULT B '0' PRIMARY KEY ('id '), KEY 'index _ Sex '('sex') using btree, KEY 'index _ bid' ('bid') using btree) ENGINE = INNODB AUTO_INCREMENT = 21 default charset = latin1; create table 'tch _ contact '('id' INT (11) not null AUTO_INCREMENT, 'tid' INT (11) default null, 'qq' VARCHAR (15) default null, 'weixin' VARCHAR (50) default null, 'phone' VARCHAR (15) default null, primary key ('id'), KEY 'index _ tid' ('tid ') using btree) ENGINE = INNODB AUTO_INCREMENT = 11 default charset = latin1 COMMENT = 'Contact info table'; create table tch_all (Id int not null, Sex SMALLINT, BId VARCHAR (36 ), no varchar (20), name varchar (30), qq varchar (15), Weixin VARCHAR (50), Phone VARCHAR (15) COMMENT 'full table ';

 

I am using a trigger to maintain the tch_all table. The example is not good. It mainly introduces functions. Sorry.

delimiter $drop trigger if EXISTS tg_insert_all;create trigger tg_insert_all after insert on tch_teacher for each ROWBEGIN　　insert into tch_all(Sex,BId,NO,NAME) values(new.sex, new.bid, new.no, new.name);end $delimiter;

1. Syntax

Create trigger name before/after insert/update/delete on table name for each row

Begin

End

 

1. triggering time before/after

The trigger is triggered after the data in the tch_teacher table is inserted. that is to say, after the tch_teacher is successfully inserted, the data will be inserted into the tch_all table. note the following. if an error is reported when tch_teacher is inserted into tch_all, the newly inserted data of tch_teacher will be rolled back.

If it is triggered after insertion, It is triggered before insertion. You only need to change "after" to "before.

If before is triggered, you first want to insert data into tch_all, and then insert data into tch_teacher. data will be rolled back no matter which step fails during the insertion process. so don't worry. If the trigger fails, will it cause redundant or wrong data.

 

2. trigger mode: insert/update/delete

Trigger method: This is triggered when you insert, modify, or delete a file. In this example, I only wrote the insert trigger method. The other two methods are used in the same way.

 

3. Original data reference old/new

There is a problem here: I modified the data, so how can I reference them? Since the data has been modified, there must be a reference of the data before the modification and the new data after the modification,

Here we use old to direct to the data before the modification, and new to the data after the modification. Here we point to the data in tch_teacher, not the data in tch_all.

 

4. Note

In the example, I used only one sentence. The example is simple. In fact, it is not that simple in actual use. Let's take an example.

In many cases, because of the importance of the data, the data is not deleted directly. Instead, the data status is updated to indicate that the data is no longer in use. Here isdeleted is used to indicate that the data is usable, and 1 indicates that the data is no longer used.

When I update the isdeleted value of tch_teacher to 1, the modify trigger is triggered. In the trigger, I need to determine the value of new. isdeleted to determine whether to delete the data in the relational table.

delimiter $DROP TRIGGER IF EXISTS tg_update_all ; CREATE TRIGGER tg_update_all AFTER UPDATE ON tch_teacher FOR EACH ROWBEGINIF new.isdeleted = 1 THEN    DELETE FROM tch_all WHERE id = old.id ;ELSE    UPDATE tch_all set sex=new.sex, bid=new.bid, NO=new.NO, NAME=new.NAME where id = old.id;END IF ;END$delimiter ;

At this point, I found that it is not necessary to continue this example. Okay, that's it.

 

Ii. View

View function: simplifies query and improves query speed.

The earlier version of mysql does not support view subqueries, but the new version of mysql already supports.

In the above example, you can also use the view method to solve complicated logic.

In terms of query performance alone, I think the trigger method is faster. After all, the trigger maintains a new table, and the new table can be indexed to increase the query speed, which means it is difficult to maintain.

Delimiter $ drop view if EXISTS v_all; -- delete view create view v_all AS -- create view select tch_teacher. *, tch_contact.QQ, tch_contact.Weixin, tch_contact.Phone from tch_teacher left join tch_contact on tch_teacher.Id = tch_contact.TId where limit = 0 $ delimiter;

Here I used one thing: delimiter, which is used for separation in mysql.

"Delimiter $" to "$ delimiter;" is independent. Therefore, you can execute the trigger script and View Script in one script.

If it is a single execution, you do not need to add that.