企业官网建设流程全解析

PHP数据库触发器和事件调度

MySQL触发器和事件可以在数据库层面执行业务逻辑。PHP可以管理和调用这些数据库对象。今天说说PHP中数据库触发器和事件的使用。

创建触发器。

```php
$pdo->exec("
CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
SET NEW.created_at = NOW(), NEW.updated_at = NOW()
");

$pdo->exec("
CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
INSERT INTO order_log (order_id, action, created_at)
VALUES (NEW.id, 'created', NOW());
END
");
?>

管理触发器。

```php
class TriggerManager
{
private PDO $pdo;

public function __construct(PDO $pdo)
{
$this->pdo = $pdo;
}

public function list(): array
{
return $this->pdo->query("SHOW TRIGGERS")->fetchAll();
}

public function drop(string $name): void
{
$this->pdo->exec("DROP TRIGGER IF EXISTS {$name}");
}

public function create(string $name, string $table, string $timing, string $event, string $body): void
{
$sql = "CREATE TRIGGER {$name} {$timing} {$event} ON {$table} FOR EACH ROW {$body}";
$this->pdo->exec($sql);
}
}
?>

MySQL事件调度。

```php
// 开启事件调度
$pdo->exec("SET GLOBAL event_scheduler = ON");

// 创建定时事件
$pdo->exec("
CREATE EVENT daily_cleanup
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 02:00:00'
DO
DELETE FROM logs WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY)
");

// 创建每分钟执行的事件
$pdo->exec("
CREATE EVENT every_minute_heartbeat
ON SCHEDULE EVERY 1 MINUTE
DO
INSERT INTO heartbeats (beat_at) VALUES (NOW())
");
?>

管理事件。

```php
class EventManager
{
private PDO $pdo;

public function __construct(PDO $pdo)
{
$this->pdo = $pdo;
}

public function list(): array
{
return $this->pdo->query("SHOW EVENTS")->fetchAll();
}

public function create(string $name, string $schedule, string $body): void
{
$sql = "CREATE EVENT {$name} ON SCHEDULE {$schedule} DO {$body}";
$this->pdo->exec($sql);
}

public function drop(string $name): void
{
$this->pdo->exec("DROP EVENT IF EXISTS {$name}");
}

public function enable(string $name): void
{
$this->pdo->exec("ALTER EVENT {$name} ENABLE");
}

public function disable(string $name): void
{
$this->pdo->exec("ALTER EVENT {$name} DISABLE");
}
}

$manager = new EventManager($pdo);
print_r($manager->list());
?>

MySQL触发器和事件可以在数据库层面实现业务逻辑。触发器在数据变更时自动执行，事件定时执行任务。PHP可以通过SQL管理这些数据库对象。但触发器和事件调试困难，建议在应用层实现复杂逻辑。