本文共 1871 字，大约阅读时间需要 6 分钟。
目录
SELECT - retrieve data from the a databaseINSERT - insert data into a tableUPDATE - updates existing data within a tableDELETE - deletes all records from a table, the space for the records remainCALL - call a PL/SQL or Java subprogramEXPLAIN PLAN - explain access path to dataLOCK TABLE - control concurrency
SET @OLDTMP_SQL_MODE=@@SQL_MODE, SQL_MODE='';DELIMITER //CREATE TRIGGER `members_mobile_insert` BEFORE INSERT ON `members_mobile` FOR EACH ROW BEGIN insert into members_location(id,province,city) select NEW.id,mobile_location.province,mobile_location.city from mobile_location where mobile_location.id = md5(LEFT(NEW.number, 7));END//DELIMITER ;SET SQL_MODE=@OLDTMP_SQL_MODE;
INSERT IGNORE 与INSERT INTO的区别就是INSERT IGNORE会忽略数据库中已经存在 的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。
insert ignore into table(name) select name from table2
create table foo (id serial primary key, u int, unique key (u));insert into foo (u) values (10);insert into foo (u) values (10) on duplicate key update u = 20;mysql> select * from foo;+----+------+| id | u |+----+------+| 1 | 20 |+----+------+
DROP TRIGGER IF EXISTS `cms`.`jc_content_BEFORE_DELETE`;DELIMITER $$USE `cms`$$CREATE DEFINER=`5kwords`@`%` TRIGGER `jc_content_BEFORE_DELETE` BEFORE DELETE ON `jc_content`FOR EACH ROW BEGIN insert into `cms`.elasticsearch_trash(id) values(OLD.content_id) on duplicate key update ctime = now(); insert into `cms`.trash(id,`type`, site_id) values(OLD.content_id, "delete", OLD.site_id) on duplicate key update `type`="delete", ctime = now();END$$DELIMITER ;