时间:2021-07-01 10:21:17 帮助过:2人阅读
触发程序是与表有关的命名数据库对象,当表上出现特定事件时,将激活该对象。
触发程序与命名为tbl_name的表相关。tbl_name必须引用永久性表。不能将触发程序与TEMPORARY表或视图关联起来。
trigger_time是触发程序的动作时间。它可以是BEFORE或AFTER,以指明触发程序是在激活它的语句之前或之后触发。
trigger_event指明了激活触发程序的语句的类型。trigger_event可以是下述值之一:
插入数据:将新行插入表时激活触发程序,例如,通过INSERT、LOAD DATA和REPLACE语句。
更新数据:更改某一行时激活触发程序,例如,通过UPDATE语句。
删除数据:从表中删除某一行时激活触发程序,例如,通过DELETE和REPLACE语句。
trigger_event与以表操作方式激活触发程序的SQL语句并不很类似,这点很重要。例如,关于INSERT的BEFORE触发程序不仅能被INSERT语句激活,也能被LOAD DATA语句激活。
LOAD DATA INFILE语句用于高速地从一个文本文件中读取行,并装入一个表中。文件名称必须为一个文字字符串。
其语法为:
- <code class="language-mysql hljs r">LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE <span class="hljs-string">‘file_name.txt‘</span>
- [REPLACE | IGNORE]
- INTO TABLE tbl_name
- [FIELDS
- [TERMINATED BY <span class="hljs-string">‘string‘</span>]
- [[OPTIONALLY] ENCLOSED BY <span class="hljs-string">‘char‘</span>]
- [ESCAPED BY <span class="hljs-string">‘char‘</span> ]
- ]
- [LINES
- [STARTING BY <span class="hljs-string">‘string‘</span>]
- [TERMINATED BY <span class="hljs-string">‘string‘</span>]
- ]
- [IGNORE number LINES]
- [(col_name_or_user_var,<span class="hljs-keyword">...</span>)]
- [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子句,则默认值为如下语句的值:
- <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子句,则默认值为如下语句的值:
- <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按如下方式运行:
在新行处寻找行的边界。
不会跳过任何行前缀。
在制表符处把行分解为字段。
不希望字段被包含在任何引号字符之中。
出现制表符、新行、或在‘\’前有‘\’时,理解为作为字段值一部分的文字字符。
相反的,当输出值时,默认值会使SELECT…INTO OUTFILE按如下方式运行:
在字段之间写入制表符tab。
不把字段包含在任何引号字符中。
当字段值中出现制表符、新行或‘\’时,使用‘\’进行转义。
在行的末端写入新行。
要写入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复合语句结构。
先用创建四个表:
- <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>
- <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>
- <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>
- <span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> test4(
- 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>,
- b4 <span class="hljs-keyword">INT</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-number">0</span>
- );</span>
- </code>
然后定义触发程序,该触发程序解读为
- 在向test1表插入数据前触发testref程序
- 向test2插入数据
- 删除test3中a3与插入test1的新数据相同的那一行数据
- 更新test4中的数据
- <code class="language-mysql hljs sql">DELIMITER |
- <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
- <span class="hljs-keyword">FOR</span> <span class="hljs-keyword">EACH</span> <span class="hljs-keyword">ROW</span>
- <span class="hljs-keyword">BEGIN</span>
- <span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> test2 <span class="hljs-keyword">SET</span> a2 = NEW.a1;</span>
- <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>
- <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>
- <span class="hljs-operator"><span class="hljs-keyword">END</span>
- |
- DELIMITER ;</span>
- </code>
使用别名OLD和NEW,能够引用与触发程序相关的表中的列。OLD.col_name在更新或删除它之前,引用已有行中的1列。NEW.col_name在更新它之后引用将要插入的新行的1列或已有行的1列。
激活触发程序时,对于触发程序引用的所有OLD和NEW列,需要具有SELECT权限,对于作为SET赋值目标的所有NEW列,需要具有UPDATE权限。
接下来先向test3与test4表中插入一些预数据
- <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>
- (<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 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>
- <span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> test4 (a4) <span class="hljs-keyword">VALUES</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 class="hljs-number">0</span>);</span>
- </code>
然后开始向test1插入数据
- <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>
- (<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>
- </code>
最后可查得各个表中的数据如下:
DROP TRIGGER语法
- <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学习笔记—触发程序
标签: