| 知乎專欄 ｜ 多維度架構 |
目錄
SELECT - retrieve data from the a database INSERT - insert data into a table UPDATE - updates existing data within a table DELETE - deletes all records from a table, the space for the records remain CALL - call a PL/SQL or Java subprogram EXPLAIN PLAN - explain access path to data LOCK 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 ;