在 mysql 中使用带表前缀的触发器/例程

发布于 2024-10-14 13:44:14 字数 632 浏览 2 评论 0原文

我习惯在 php 脚本中使用 mysql 表前缀。然而，触发器和例程有时也非常有用。好的。假设我有一张表：“pre_customers”。还有一个像
这样的程序

CREATE FUNCTION `get_all_clients`() RETURNS int(11)
    BEGIN
    DECLARE sum INT ;
    SELECT COUNT(id) INTO sum FROM pre_customers ;
    RETURN sum;
END

没什么大不了的，只是举例而已。还有一个常量

<?php
define( 'DB_PREFIX', 'pre_' ) ;

It 用于更改表前缀。如果我需要在脚本中发出 sql 请求，我会这样做好吧

$query = "SELECT * FROM " . DB_PREFIX . "customers" ;
$result = mysql_query( $query ) ;
...

，但是如果我想在 php 脚本中更改此前缀以及表名，它将破坏所有存储的例程和触发器，它们仍然会应用到“pre_customers”表。所以问题是程序员通常如何解决这个问题？

原文

I'm used to use mysql table prefixes in my php scripts. Yet triggers and routines sometimes are very useful too. Ok. Let's say i have a table: 'pre_customers'. And a procedure sth like

CREATE FUNCTION `get_all_clients`() RETURNS int(11)
    BEGIN
    DECLARE sum INT ;
    SELECT COUNT(id) INTO sum FROM pre_customers ;
    RETURN sum;
END

No big deal, just for example. And there is also a constant

<?php
define( 'DB_PREFIX', 'pre_' ) ;

It's being used for changing table prefixes. If i need to make an sql-request in the script i make it like this

$query = "SELECT * FROM " . DB_PREFIX . "customers" ;
$result = mysql_query( $query ) ;
...

Alright, but if i want to change this prefix in the php-script along with the table names it's gonna ruin all stored routines and triggers, they still will apply to 'pre_customers' table. So the question is is there a common practice how normally programmers solve this problem.

分享到QQ

分享到微博