使用默认前缀从具有300个表的mysql数据库中选择 [英] select from mysql db with 300 tables using a default prefix

问题描述

我有一个程序，可以从大约200个带前缀的表中进行选择.例如PBN_products，PBN_address，PBN_others. 除了将前缀添加到每个表的select语句之外，还可以将前缀定义为默认值并执行选择吗?

I have a program that selects from about 200 tables with prefix. eg PBN_products, PBN_address, PBN_others. Instead of appending the prefix on each table for the select statement, is there a way of defining the prefix as default value and do the selection?

$prefix=GET['prefix'];
mysql_connect(DB_SERVER, DB_SERVER_USERNAME, DB_SERVER_PASSWORD);
mysql_select_db(DB_DATABASE);
$sql = 'SELECT price, description, title, cost'.
        'FROM products, address, others';

如何定义不包含在所有表中的前缀?我有200张桌子.

How can I define the prefix not to include in all tables? I have 200 tables.

推荐答案

我将研究一个类来执行一些简单的查询抽象或执行此操作的某种ORM库.一个样本就是这样.

I would look into a class to do some simple query abstraction or some kind of ORM lib that does this. A sample would be like this.

class Query {
    function from($tbl){
        return new Table($tbl);
    }
}
class Table {
    var $prefix = 'PBN_';
    var $tblname = '';

    function Table($name){
        $this->tblname = $this->prefix.$name;
    }
    function select($cols, $where = false, $order = false, $limit = false){
        $query = "SELECT {$cols} FROM {$this->tblname}";
        if($where) $query .= " WHERE ".$where; //add where
        if($order) $query .= " ORDER BY ".$order; //add order
        if($limit) $query .= " LIMIT ".$limit; //add limit
        return $query;
    }
}

$q = new Query;
$results = mysql_query($q->from('products')->select('*'));

这显然是不完整或不安全的.只是一个抽象类如何加速您的sql并为您提供前缀的示例.

This is obviously nowhere near complete or secure. Just a sample of how an abstraction class could speed up your sql and do you your prefixes for you.

这篇关于使用默认前缀从具有300个表的mysql数据库中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！