php批量生成mysql触发器定义语句

最新推荐文章于 2023-07-28 00:27:47 发布

bestscw

最新推荐文章于 2023-07-28 00:27:47 发布

阅读量90

点赞数

分类专栏： PHP 文章标签： MySQL PHP SQL Cache

本文链接：https://blog.csdn.net/bestscw/article/details/83913188

版权

PHP 专栏收录该内容

21 篇文章 0 订阅

订阅专栏


<?php
define('TRIGGER_AFTER_INSERT_SQL',"DELIMITER |
DROP TRIGGER IF EXISTS %s;
CREATE TRIGGER %s
AFTER INSERT ON %s
FOR EACH ROW BEGIN
    SET @result_set = (select clear_cache());
END |
DELIMITER ;\r\n\r\n");

define('TRIGGER_AFTER_UPDATE_SQL',"DELIMITER |
DROP TRIGGER IF EXISTS %s;
CREATE TRIGGER %s
AFTER UPDATE ON %s
FOR EACH ROW BEGIN
    SET @result_set = (select clear_cache());
END |
DELIMITER ;\r\n\r\n");

define('TRIGGER_AFTER_DELETE_SQL',"DELIMITER |
DROP TRIGGER IF EXISTS %s;
CREATE TRIGGER %s
AFTER DELETE ON %s
FOR EACH ROW BEGIN
    SET @result_set = (select clear_cache());
END |
DELIMITER ;\r\n\r\n");

mysql_connect('127.0.0.1:3307','root','') or die(mysql_error());
mysql_select_db('db');
mysql_query('SET NAMES UTF8');

$triggerDDL = '';

$rs = mysql_query('SHOW TABLES');
while($row = mysql_fetch_array($rs))
{
	$tblName = $row['Tables_in_db'];


	$triggerDDL .= "\r\n--\r\n-- 表的触发器 `".$tblName."`\r\n--\r\n";

	$triggerName = $tblName.'_insert';
	$triggerDDL .= sprintf(TRIGGER_AFTER_INSERT_SQL,$triggerName,$triggerName,$tblName);

	$triggerName = $tblName.'_update';
	$triggerDDL .= sprintf(TRIGGER_AFTER_UPDATE_SQL,$triggerName,$triggerName,$tblName);

	$triggerName = $tblName.'_delete';
	$triggerDDL .= sprintf(TRIGGER_AFTER_DELETE_SQL,$triggerName,$triggerName,$tblName);
}

file_put_contents('db_trigger.sql',$triggerDDL);
?>