当前位置:Gxlcms > 数据库问题 > MySQL学习笔记—触发程序

MySQL学习笔记—触发程序

时间:2021-07-01 10:21:17 帮助过:2人阅读

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语句用于高速地从一个文本文件中读取行,并装入一个表中。文件名称必须为一个文字字符串。
其语法为:

  1. <code class="language-mysql hljs r">LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE <span class="hljs-string">‘file_name.txt‘</span>
  2. [REPLACE | IGNORE]
  3. INTO TABLE tbl_name
  4. [FIELDS
  5. [TERMINATED BY <span class="hljs-string">‘string‘</span>]
  6. [[OPTIONALLY] ENCLOSED BY <span class="hljs-string">‘char‘</span>]
  7. [ESCAPED BY <span class="hljs-string">‘char‘</span> ]
  8. ]
  9. [LINES
  10. [STARTING BY <span class="hljs-string">‘string‘</span>]
  11. [TERMINATED BY <span class="hljs-string">‘string‘</span>]
  12. ]
  13. [IGNORE number LINES]
  14. [(col_name_or_user_var,<span class="hljs-keyword">...</span>)]
  15. [SET col_name = expr,<span class="hljs-keyword">...</span>)]</code>

如果使用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子句,则默认值为如下语句的值:

  1. <code class="language-mysql hljs oxygene">FIELDS TERMINATED <span class="hljs-keyword">BY</span> <span class="hljs-string">‘\t‘</span> ENCLOSED <span class="hljs-keyword">BY</span> <span class="hljs-string">‘‘</span> ESCAPED <span class="hljs-keyword">BY</span> <span class="hljs-string">‘\\‘</span></code>

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

  1. <code class="language-mysql hljs oxygene">LINES TERMINATED <span class="hljs-keyword">BY</span> <span class="hljs-string">‘\n‘</span> STARTING <span class="hljs-keyword">BY</span> <span class="hljs-string">‘‘</span></code>

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

  1. 在新行处寻找行的边界。

  2. 不会跳过任何行前缀。

  3. 在制表符处把行分解为字段。

  4. 不希望字段被包含在任何引号字符之中。

  5. 出现制表符、新行、或在‘\’前有‘\’时,理解为作为字段值一部分的文字字符。

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

  1. 在字段之间写入制表符tab。

  2. 不把字段包含在任何引号字符中。

  3. 当字段值中出现制表符、新行或‘\’时,使用‘\’进行转义。

  4. 在行的末端写入新行。

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

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

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

例:
首先,我在数据库中创建了一个loadtest表
技术分享

在D盘下的data.txt文件中的数据为
技术分享

在windows操作系统中,一开始加载的时候没有添加LINES字段,默认换行符为’\n’,此时读入数据会出现错误
技术分享

添加LINES字段指定换行符为’\r\n’后,可以正确将数据载入
技术分享


现在继续看trigger的语法。

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

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


测试


先用创建四个表:

  1. <code class="language-mysql hljs sql"><span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> test1(a1 <span class="hljs-keyword">INT</span>);</span>
  2. <span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> test2(a2 <span class="hljs-keyword">INT</span>);</span>
  3. <span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> test3(a3 <span class="hljs-keyword">INT</span> <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span> AUTO_INCREMENT <span class="hljs-keyword">PRIMARY</span> <span class="hljs-keyword">KEY</span>);</span>
  4. <span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> test4(
  5. a4 <span class="hljs-keyword">INT</span> <span class="hljs-keyword">NOT</span> <span class="hljs-keyword">NULL</span> AUTO_INCREMENT <span class="hljs-keyword">PRIMARY</span> <span class="hljs-keyword">KEY</span>,
  6. b4 <span class="hljs-keyword">INT</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-number">0</span>
  7. );</span>
  8. </code>

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

  1. <code class="language-mysql hljs sql">DELIMITER |
  2. <span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TRIGGER</span> testref <span class="hljs-keyword">BEFORE</span> <span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">ON</span> test1
  3. <span class="hljs-keyword">FOR</span> <span class="hljs-keyword">EACH</span> <span class="hljs-keyword">ROW</span>
  4. <span class="hljs-keyword">BEGIN</span>
  5. <span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> test2 <span class="hljs-keyword">SET</span> a2 = NEW.a1;</span>
  6. <span class="hljs-operator"><span class="hljs-keyword">DELETE</span> <span class="hljs-keyword">FROM</span> test3 <span class="hljs-keyword">WHERE</span> a3 = NEW.a1;</span>
  7. <span class="hljs-operator"><span class="hljs-keyword">UPDATE</span> test4 <span class="hljs-keyword">SET</span> b4 = b4 + <span class="hljs-number">1</span> <span class="hljs-keyword">WHERE</span> a4 = NEW.a1;</span>
  8. <span class="hljs-operator"><span class="hljs-keyword">END</span>
  9. |
  10. DELIMITER ;</span>
  11. </code>

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

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

  1. <code class="language-mysql hljs sql"><span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> test3 (a3) <span class="hljs-keyword">VALUES</span>
  2. (<span class="hljs-keyword">NULL</span>), (<span class="hljs-keyword">NULL</span>), (<span class="hljs-keyword">NULL</span>), (<span class="hljs-keyword">NULL</span>), (<span class="hljs-keyword">NULL</span>),
  3. (<span class="hljs-keyword">NULL</span>), (<span class="hljs-keyword">NULL</span>), (<span class="hljs-keyword">NULL</span>), (<span class="hljs-keyword">NULL</span>), (<span class="hljs-keyword">NULL</span>);</span>
  4. <span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> test4 (a4) <span class="hljs-keyword">VALUES</span>
  5. (<span class="hljs-number">0</span>), (<span class="hljs-number">0</span>), (<span class="hljs-number">0</span>), (<span class="hljs-number">0</span>), (<span class="hljs-number">0</span>), (<span class="hljs-number">0</span>), (<span class="hljs-number">0</span>), (<span class="hljs-number">0</span>), (<span class="hljs-number">0</span>), (<span class="hljs-number">0</span>);</span>
  6. </code>

然后开始向test1插入数据

  1. <code class="language-mysql hljs sql"><span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> test1 <span class="hljs-keyword">VALUES</span>
  2. (<span class="hljs-number">1</span>), (<span class="hljs-number">3</span>), (<span class="hljs-number">1</span>), (<span class="hljs-number">7</span>), (<span class="hljs-number">1</span>), (<span class="hljs-number">8</span>), (<span class="hljs-number">4</span>), (<span class="hljs-number">4</span>);</span>
  3. </code>

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

技术分享

技术分享

技术分享

技术分享


DROP TRIGGER语法


DROP TRIGGER语法

  1. <code class="language-mysql hljs sql"><span class="hljs-operator"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">TRIGGER</span> [schema_name.]trigger_name</span></code>

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


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


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

  • 如果BEFORE触发程序失败,不执行相应行上的操作。

  • 仅当BEFORE触发程序(如果有的话)和行操作均已成功执行,才执行AFTER触发程序。

  • 如果在BEFORE或AFTER触发程序的执行过程中出现错误,将导致调用触发程序的整个语句的失败。

  • 对于事务性表,如果触发程序失败(以及由此导致的整个语句的失败),该语句所执行的所有更改将回滚。对于非事务性表,不能执行这类回滚,因而,即使语句失败,失败之前所作的任何更改依然有效。

MySQL学习笔记—触发程序

标签:

人气教程排行