Mysql创建SQL子查询ALIAS

发布于 2024-12-14 15:49:55 字数 540 浏览 0 评论 0原文

基本上，我有多个这样的查询：

SELECT a, b, c FROM (LONG QUERY) X WHERE ...

问题是，我非常频繁地使用这个长查询。我希望给这个子查询一个别名，主要是：

Shorten &简化查询（还减少错误和代码重复）
可能优化性能。（我相信这是默认由mysql查询缓存完成的）

到目前为止，我一直都是这样存储的：

variable = LONG QUERY;
Query("SELECT a, b, c FROM ("+variable+") X WHERE ...");

这还不错。我正在寻找一种在内部使用 mysql 执行此操作的方法。

是否有可能创建一个简单的只读视图，不会产生任何开销，这样我就可以在任何地方做？我相信这样更合适&可读的方式来做到这一点。

SELECT a, b, c FROM myTable WHERE ...

原文

Basically, I have multiple queries like this:

SELECT a, b, c FROM (LONG QUERY) X WHERE ...

The thing is, I am using this LONG QUERY really frequently. I am looking to give this subquery an alias which would primarily:

Shorten & simplify queries (also reduce errors and code duplication)
Possibly optimize performance. (I believe this is done by default by mysql query caching)

Till now, I have been doing it this way to store:

variable = LONG QUERY;
Query("SELECT a, b, c FROM ("+variable+") X WHERE ...");

Which is not bad. I am looking for a way to do this with mysql internally.

Is it possible to create a simple, read-only view that would generate NO overhead, so I could do everywhere? I believe this is more propper & readable way of doing it.

SELECT a, b, c FROM myTable WHERE ...

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

沉睡月亮 2024-12-21 15:49:55

通常这些被称为视图。例如：

CREATE VIEW vMyLongQuery  
AS 
    SELECT a, b, c FROM (LONG QUERY) X WHERE ...

然后可以像这样引用：

SELECT a, b, c FROM vMyLongQuery

参见 http:// /dev.mysql.com/doc/refman/5.0/en/create-view.html 有关语法的更多信息。

就性能而言，最好的情况性能将与您现在所做的完全一样，最坏的情况将杀死您的应用程序。这取决于您对视图的处理方式以及 MySQL 如何处理它们。

MySQL 以merge 和temptable 两种方式实现视图。 merge 选项几乎正是您现在所做的，您的视图作为子查询合并到您的查询中。使用临时表，它实际上会将所有数据假脱机到一个临时表中，然后选择/加入到该临时表。当数据连接到临时表时，您也会失去索引的好处。

请注意，merge 查询计划不支持您认为的以下任何内容。

聚合函数（SUM()、MIN()、MAX()、COUNT() 等）
DISTINCT
GROUP BY
HAVING
LIMIT
UNION 或 UNION ALL
选择列表中的子查询
引用没有基础表的文字

因此，如果您的子查询使用这些，你可能会损害性能。

另外，请仔细听取 OMG Ponies 的建议，视图与基类不同。视图在数据库中占有一席之地，但很容易被滥用。当一名具有 OO 背景的工程师接触数据库时，视图似乎是促进代码继承和可重用性的便捷方法。通常，人们最终会发现自己的嵌套视图与嵌套视图的嵌套视图相连。 SQL 处理嵌套视图时，本质上是获取每个单独视图的定义，并将其扩展为一个会让 DBA 哭泣的查询。

另外，您在示例中遵循了出色的实践，我鼓励您继续这样做。您单独指定了所有列，切勿使用 SELECT * 来指定视图的结果。它最终会毁掉你的一天。

Typically these are called views. For example:

CREATE VIEW vMyLongQuery  
AS 
    SELECT a, b, c FROM (LONG QUERY) X WHERE ...

Which can then be referenced like this:

SELECT a, b, c FROM vMyLongQuery

See http://dev.mysql.com/doc/refman/5.0/en/create-view.html for more info on syntax.

As far as performance goes, best case performance will be near enough exactly the same as what you're doing now and worst case it will kill your app. It depends on what you do with the views and how MySQL processes them.

MySQL implements views two ways merge and temptable. The merge option is pretty much exactly what you're doing now, your view is merged into your query as a subquery. With a temptable it will actually spool all the data into a temptable and then select/join to that temptable. You also lose index benefits when data is joined to the temptable.

As a heads up, the merge query plan doesn't support any of the following in your view.

Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)
DISTINCT
GROUP BY
HAVING
LIMIT
UNION or UNION ALL
Subquery in the select list
Reference to literals without an underlying table

So if your subquery uses these, you're likely going to hurt performance.

Also, heed OMG Ponies' advice carefully, a view is NOT the same as a base class. Views have their place in a DB but can easily be misused. When an engineer comes to the database from an OO background, views seem like a convenient way to promote inheritance and reusability of code. Often people eventually find themselves in a position where they have nested views joined to nested views of nested views. SQL processes nested views by essentially taking the definition of each individual view and expanding that into a beast of a query that will make your DBA cry.

Also, you followed excellent practice in your example and I encourage you to continue this. You specified all your columns individually, never ever use SELECT * to specify the results of your view. It will, eventually, ruin your day.

回复收藏 0 原文