MySQL学习笔记—触发程序

180次阅读

共计 4200 个字符，预计需要花费 11 分钟才能阅读完成。

MySQL 学习笔记—触发程序

触发程序是与表有关的命名数据库对象，当表上发生特定事件时，将触发执行相应的触发程序。

CREATE TRIGGER 语法

CREATE TRIGGER 语法：

CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name 
   FOR EACH ROW 
   trigger_stmt

触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。

触发程序与命名为 tbl_name 的表相关。tbl_name 必须引用永久性表。不能将触发程序与 TEMPORARY 表或视图关联起来。

trigger_time 是触发程序的动作时间。它可以是 BEFORE 或 AFTER，以指明触发程序是在激活它的语句之前或之后触发。

trigger_event 指明了激活触发程序的语句的类型。trigger_event 可以是下述值之一：

1.

插入数据：将新行插入表时激活触发程序，例如，通过 INSERT、LOAD DATA 和 REPLACE 语句。

2.

更新数据：更改某一行时激活触发程序，例如，通过 UPDATE 语句。

3.

删除数据：从表中删除某一行时激活触发程序，例如，通过 DELETE 和 REPLACE 语句。

trigger_event 与以表操作方式激活触发程序的 SQL 语句并不很类似，这点很重要。例如，关于 INSERT 的 BEFORE 触发程序不仅能被 INSERT 语句激活，也能被 LOAD DATA 语句激活。

LOAD DATA INFILE 语句简述

LOAD DATA INFILE 语句用于高速地从一个文本文件中读取行，并装入一个表中。文件名称必须为一个文字字符串。
其语法为：

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char' ]
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...)]

如果使用 LOW_PRIORITY，则 LOAD DATA 语句的执行被延迟，直到没有其它的客户端从表中读取为止。

如果一个 MyISAM 表满足同时插入的条件（即该表在中间有空闲块），并且对这个 MyISAM 表指定了 CONCURRENT(适用于多线程共享情况)，则当 LOAD DATA 正在执行时，其它线程会从表中重新获取数据。即使没有其它线程在同时使用本表格，使用本选项也会略微影响 LOAD DATA 的性能。

如果指定了 LOCAL，则被认为与连接的客户端有关：

如果指定了 LOCAL，则文件会被客户主机上的客户端读取，并被发送到服务器。文件会被给予一个完整的路径名称，以指定确切的位置。如果给定的是一个相对的路径名称，则此名称会被理解为相对于启动客户端时所在的目录。
如果 LOCAL 没有被指定，则文件必须位于服务器主机上，并且被服务器直接读取。

当在服务器主机上为文件定位时，服务器使用以下规则：

如果给定了一个绝对的路径名称，则服务器使用此路径名称。
如果给定了带有一个或多个引导组件的相对路径名称，则服务器会搜索相对于服务器数据目录的文件。
如果给定了一个不带引导组件的文件名称，则服务器会在默认数据库的数据库目录中寻找文件。

注意，这些规则意味着名为./myfile.txt 的文件会从服务器数据目录中被读取，而名为 myfile.txt 的同样的文件会从默认数据库的数据库目录中读取。

如果指定了 REPLACE，则输入行会替换原有行（对一个主索引或唯一索引具有相同值的行）。

如果指定 IGNORE，则把原有行复制到唯一关键字值的输入行被跳过。如果这两个选项都不指定，则运行情况根据 LOCAL 关键词是否被指定而定。不使用 LOCAL 时，当出现重复关键字值时，会发生错误，并且剩下的文本文件被忽略。使用 LOCAL 时，默认的运行情况和 IGNORE 被指定时的情况相同；这是因为在运行中间，服务器没有办法中止文件的传输。

LOAD DATA INFILE 是 SELECT…INTO OUTFILE 的补语。要从一个表中把数据写入一个文件中，应使用的是 SELECT…INTO OUTFILE 语句。要读取文件，放回到表中，应使用 LOAD DATA INFILE。

FIELDS 和 LINES 子句的语法对于两个语句是一样的。两个子句都是自选的，但是如果两个都被指定了，FIELDS 必须位于 LINES 的前面。

如果不指定 FIELDS 子句，则默认值为如下语句的值：

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'

如果不指定 LINES 子句，则默认值为如下语句的值：

LINES TERMINATED BY '\n' STARTING BY ''

即，当读取输入值时，默认值会使 LOAD DATA INFILE 按如下方式运行：

在新行处寻找行的边界。
不会跳过任何行前缀。
在制表符处把行分解为字段。
不希望字段被包含在任何引号字符之中。
出现制表符、新行、或在‘\’前有‘\’时，理解为作为字段值一部分的文字字符。

相反的，当输出值时，默认值会使 SELECT…INTO OUTFILE 按如下方式运行：

在字段之间写入制表符 tab。
不把字段包含在任何引号字符中。
当字段值中出现制表符、新行或‘\’时，使用‘\’进行转义。
在行的末端写入新行。

要写入 FIELDS ESCAPED BY‘\’，您必须为待读取的值指定两个反斜杠，作为一个单反斜杠使用。

如果已经在 Windows 系统中生成了文本文件，可能必须使用 LINES TERMINATED BY‘\r\n’来正确地读取文件，因为 Windows 程序通常使用两个字符作为一个行终止符。

IGNORE number LINES 选项可以被用于在文件的开始处忽略行。例如，您可以使用 IGNORE 1 LINES 来跳过一个包含列名称的起始标题行，即跳过第一行。

例：
首先，我在数据库中创建了一个 loadtest 表
MySQL 学习笔记—触发程序

在 D 盘下的 data.txt 文件中的数据为
MySQL 学习笔记—触发程序

在 windows 操作系统中，一开始加载的时候没有添加 LINES 字段，默认换行符为’\n’，此时读入数据会出现错误
MySQL 学习笔记—触发程序

添加 LINES 字段指定换行符为’\r\n’后，可以正确将数据载入
MySQL 学习笔记—触发程序

现在继续看 trigger 的语法。

对于具有相同触发程序动作时间和事件的给定表，不能有两个触发程序。例如，对于某一表，不能有两个 BEFORE UPDATE 触发程序。但可以有 1 个 BEFORE UPDATE 触发程序和 1 个 BEFORE INSERT 触发程序，或 1 个 BEFORE UPDATE 触发程序和 1 个 AFTER UPDATE 触发程序。

trigger_stmt 是当触发程序激活时执行的语句。如果你打算执行多个语句，可使用 BEGIN … END 复合语句结构。

测试

先用创建四个表：

CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  b4 INT DEFAULT 0
);

然后定义触发程序，该触发程序解读为
– 在向 test1 表插入数据前触发 testref 程序
– 向 test2 插入数据
– 删除 test3 中 a3 与插入 test1 的新数据相同的那一行数据
– 更新 test4 中的数据

DELIMITER |

CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW 
  BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;  
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
  END
|

DELIMITER ;

使用别名 OLD 和 NEW，能够引用与触发程序相关的表中的列。OLD.col_name 在更新或删除它之前，引用已有行中的 1 列。NEW.col_name 在更新它之后引用将要插入的新行的 1 列或已有行的 1 列。
激活触发程序时，对于触发程序引用的所有 OLD 和 NEW 列，需要具有 SELECT 权限，对于作为 SET 赋值目标的所有 NEW 列，需要具有 UPDATE 权限。

接下来先向 test3 与 test4 表中插入一些预数据

INSERT INTO test3 (a3) VALUES 
  (NULL), (NULL), (NULL), (NULL), (NULL), 
  (NULL), (NULL), (NULL), (NULL), (NULL);

INSERT INTO test4 (a4) VALUES 
  (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

然后开始向 test1 插入数据

INSERT INTO test1 VALUES 
    (1), (3), (1), (7), (1), (8), (4), (4);

最后可查得各个表中的数据如下：

MySQL 学习笔记—触发程序

DROP TRIGGER 语法

DROP TRIGGER [schema_name.]trigger_name

舍弃触发程序。方案名称（schema_name）是可选的。如果省略了 schema（方案），将从当前方案中舍弃触发程序。

执行触发程序过程中的错误处理方式

在触发程序的执行过程中，MySQL 处理错误的方式如下：

如果 BEFORE 触发程序失败，不执行相应行上的操作。
仅当 BEFORE 触发程序（如果有的话）和行操作均已成功执行，才执行 AFTER 触发程序。
如果在 BEFORE 或 AFTER 触发程序的执行过程中出现错误，将导致调用触发程序的整个语句的失败。
对于事务性表，如果触发程序失败（以及由此导致的整个语句的失败），该语句所执行的所有更改将回滚。对于非事务性表，不能执行这类回滚，因而，即使语句失败，失败之前所作的任何更改依然有效。

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2016-10/136280.htm

正文完

星哥玩云-微信公众号