其实就是告诉mysql解释器,该段命令是否已经结束了,mysql是否可以执行了。默认情况下,delimiter是分号;。在命令行客户端中,如果有一行命令以分号结束,那么回车后,mysql将会执行该命令。
如下脚本, 在 workbench 中只有加 delimiter 才能正常执行下去。
#insert
drop trigger if exists trig_tag_I;
DELIMITER $$
CREATE TRIGGER trig_tag_I
after INSERT on tag for each row
BEGIN
if exists(SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA=database() and table_name ='dc_sync_log')
THEN
INSERT INTO dc_sync_log ( tableName, pkName, pkValue, opType) values( 'tag', 'id',NEW.id,'I' );
end if;
END
$$
#delete
DELIMITER $$
drop trigger if exists trig_tag_D
$$
DELIMITER $$
CREATE TRIGGER trig_tag_D
after delete on tag for each row
BEGIN
if exists(SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA=database() and table_name ='dc_sync_log')
THEN
INSERT INTO dc_sync_log ( tableName, pkName, pkValue, opType) values( 'tag', 'id',OLD.id,'D' );
end if;
END
$$
#update
DELIMITER $$
drop trigger if exists trig_tag_U
$$
DELIMITER $$
CREATE TRIGGER trig_tag_U
after update on tag for each row
BEGIN
if exists(SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA=database() and table_name ='dc_sync_log')
THEN
INSERT INTO dc_sync_log ( tableName, pkName, pkValue, opType) values( 'tag', 'id',NEW.id,'U' );
end if;
END
$$
参考: 点击打开链接