创建视图还是使用innerjoins? [英] Create view or use innerjoins?

问题描述

我有一个规范化的数据库，外键/主键为多个数据库提供了一个.

I have a normalized database, with foreign keys/primary keys giving one to many databases.

我计划使用PHP访问此数据库以进行基本的前端/后端显示.现在，我的问题来自以下两个示例查询:

I plan to access this database with PHP for the basic frontend/backend display. Now, my question comes from these two exampled queries:

CREATE VIEW `view` AS
  SELECT
    functiondetails.Detail,
    functionnames.ID,
    functionnames.FunctionName,
    functionnames.Catogory
  FROM functiondetails
    INNER JOIN functionnames ON functiondetails.AsscID = functionnames.ID

或

SELECT
  functiondetails.Detail,
  functionnames.ID,
  functionnames.FunctionName,
  functionnames.Catogory
FROM functiondetails
  INNER JOIN functionnames ON functiondetails.AsscID = functionnames.ID

在查询中没有错误，因为我都运行了两次，但是我的总体问题是:

There is no error within the query as i've ran both without fail, but my overall question is this:

如果我打算不断地从数据库中引用很多信息.创建一个视图会更容易，该视图随后将使用新添加的信息进行所有更新，或者在我的实际php上进行第二次查询是更好的做法.例如:

if I plan to constantly reference alot of information from my database. Wouldn't it be easier to create a view, which will then update all the time with the newly added information, or would it be in better practice to have the second query on my actual php.. Example:

$Query = $MySQli->prepare("
  SELECT
    functiondetails.Detail,
    functionnames.ID,
    functionnames.FunctionName,
    functionnames.Catogory
  FROM functiondetails
    INNER JOIN functionnames ON functiondetails.AsscID = functionnames.ID
")
$Query->execute();
$Results = $Query->fetch_results();
$Array = $Results->fetch_array(MYSQLI_ASSOC);

还是要从我的视图中选择?

Or to select from my view?

$Query = $MySQLi->prepare("SELECT * FROM `view`");
$Query->execute();
$Results = $Query->fetch_results();
$Array = $Results->fetch_array(MYSQLI_ASSOC);

那么哪一种是查询我的数据库的更好方法?

So which one would be a better method to use for querying my database?

推荐答案

抽象层，创建抽象层的通常原因是为您提供一种使您的生活更轻松的工具.

Views are an abstraction layer and the usual reason for creating an abstraction layer is to give you a tool to make your life easier.

使用视图的一些主要优点包括:

Some of the big advantages to using views include:

1. 安全性
   您可以控制谁有权查看，而无需授予他们对基础表的访问权.

1. Security
   You can control who has access to view without granting them access to the underlying tables.

说明
通常，列标题的描述性不足.视图使您可以使返回的数据更加清晰.

Clarification
Often times, column headers aren't as descriptive as they can be. Views allow you to add clarity to the data being returned.

性能
在性能方面，视图不会对您造成负面影响.但是，由于MySQL不支持物化视图，因此您不会通过使用视图来获得性能提升. .

Performance
Performance wise, views do not negatively hurt you. You will not, however, see a performance gain by using views either as MySQL does not support materialized views.

易于编码
视图可用于重用复杂的查询，而用户错误的空间较小.

Ease in Coding
Views can be used to reuse complex queries with less room for user error.

易于管理
只要您的表架构发生更改，它就使您的工作变得更轻松.

例如，假设您有一个表，其中包含要出售的房屋homes_for_sale，但后来您决定让该表处理您曾经拥有过的待售房屋/已出售的所有房屋，all_homes.显然，新表的模式将与第一个表大不相同.

如果您要从homes_for_sale中提取大量查询，那么现在您必须遍历所有代码并更新所有查询.这使您面临用户错误和管理方面的噩梦.

解决更改的更好方法是用相同名称的视图替换表.该视图将返回与原始表完全相同的架构，即使实际的架构已更改.然后，您可以根据需要按自己的进度浏览代码，并更新查询调用.

Ease of Management
It makes your life easier whenever your table schema changes.

For example, say you have a table that contains homes you have for sale, homes_for_sale, but later on you decide you want that table to handle all homes you've ever had for sale/have for sale currently, all_homes. Obviously, the schema of the new table would be much different than the first.

If you have a ton of queries pulling from homes_for_sale, now you have to go through all your code and update all the queries. This opens you up to user error and a management nightmare.

The better way to address the change is replace the table with a view of the same name. The view would return the exact same schema as the original table, even though the actual schema has changed. Then you can go through your code at your own pace, if needed at all, and update your query calls.

这篇关于创建视图还是使用innerjoins?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！