动态SQL查询在代码中可能吗？ [英] Dynamic SQL queries in code possible?

问题描述

而不是硬编码sql查询像 select *从user_id = 220202 的用户可以使这些动态像 Select * from $ users其中$原因我要求是当需要更改表/列名称，我可以只是在一个地方更新它，并且don（）$。$ input $ <$ p $> $

不得不要求开发人员逐行查找所有引用更新。这是非常耗时。我不喜欢在代码中暴露数据库内容的想法。

我的主要关注的是载入时间。与动态页面一样，数据库必须获取页面内容，同样的方式，如果查询是动态的，第一个系统必须查找引用然后执行查询，那么它会影响加载时间吗？

我使用codeignitor PHP。

如果可能的话，下一个问题是在哪里存储所有的引用？在应用程序中，在文件中，在数据库中，以及如何？

--- EDIT：

更好： SQL查询本身是否动态？我可以只引用$ sqlA而不是整个查询？这种方式如果我必须重写查询，我只能更新1个文件。

解决方案

使用 Codeigniter ，我建议您使用 Active记录类

活动记录类使您​​能够以步骤动态地构建查询，从而使您能够逻辑地构建查询。所以，让你的例子使用活动记录...

（这可以用更少的代码，我只是试图说明活动记录）

  $ this-> db-> select（'*'）; 
 
 $ this-> db-> from（$ table）; 
 
 $ this-> db->其中（$ user_id，$ input）; 
  

所以为了显示我的逻辑构建查询的意思，你可以构建任何逻辑INTO查询构建过程。假设你有一个$ limit变量，你设置如果你想限制你得到的结果数量。但如果没有设置（或 NULL ），则不想设置limit子句。

  if（$ isset（$ limit））{
 $ this-> db-> limit（$ limit）; 
} 
  

现在可以执行您的查询

  $ query = $ this-> db-> get（） 
  

然后只需处理 $ query 数据库类，就像您使用任何其他CodeIgniter查询对象一样。

Instead of hard coding sql queries like Select * from users where user_id =220202 can these be made dynamic like Select * from $users where $user_id = $input.

Reason i ask is when changes are needed to table/column names i can just update it in one place and don't have to ask developers to go line by line to find all references to update. It is very time consuming. And I do not like the idea of exposing database stuff in the code.

My major concern is load time. Like with dynamic pages, the database has to fetch the page content, same way if queries are dynamic first system has to lookup the references then execute the queries, so does it impact load times?

I am using codeignitor PHP.

If it is possible then the next question is where to store all the references? In the app, in a file, in the DB, and how?

---EDIT:
Even better: Can the SQL query itself be made dynamic? I can just reference $sqlA instead of the whole query? This way if I have to re-write the query I can just update 1 file.

解决方案

Because you are using Codeigniter, I would reccomend utilizing the Active Record Class to accomplish what you are trying to do.

The active record class enables you to build queries dynamically in steps allowing you to build them logically. So to take your example using active record...

( this could be accomplished with less code, I'm just trying to illustrate Active Record )

$this->db->select('*');

$this->db->from($table);

$this->db->where($user_id, $input);

and so to show what I mean about building the query logically, you can build whatever logic you want INTO the query building process. Lets say you have a $limit variable that you set if you want to limit the number of results you get. BUT if it isn't set (or NULL) you don't want to set the limit clause.

if ( $isset($limit) ) {
    $this->db->limit($limit);
}

and now to execute your query now that it has been built

$query = $this->db->get();

Then just deal with $query with your database class just like you would any other CodeIgniter query object.

这篇关于动态SQL查询在代码中可能吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！