在SQLite数据库使用临时表 [英] Use of temp tables in SQLite databases

问题描述

我是通过源$ C ​​$ C在浏览的Mendeley为Android由马丁·夏娃，看到临时表通过调用替换与_id表与主表一起创建的。例如在集合表

I was browsing through the source code for Mendeley for Android by Martin Eve and saw that Temp tables are created along with the main tables by calling replace on the tables with _id. For example on the Collections table

db.execSQL(COLLECTIONS_CREATE.replace(" (_id", "_TEMP (_id"));

我猜它创建一个新的临时表。任何对此的进一步解释将是巨大的。另外在数据首先插入临时表，后来搬到主表。

I guess it creates a new temporary table. Any more explanation on this would be great. Further on the data is first inserted to temp tables and later moved to main tables.

我搜索过SO和整个<少时href=\"http://stackoverflow.com/questions/4976585/what-are-the-use-cases-for-temporary-tables-in-sql-database-systems\">What是用例在SQL数据库系统临时表？并看到临时表用于处理复杂查询，排序和提高性能。任何人都可以解释它如何帮助在这种情况呢？

I searched through SO and came across What are the use cases for temporary tables in SQL database systems? and saw that temp tables are used for handling complex queries, sorting and for increasing performance. Can anyone explain how it helps in this situation?

推荐答案

临时表使得通过让程序员打破了单一的复杂查询到多个相对简单的查询，事情变得更容易为程序员，也暂时让程序员存储结果因此它们可以咨询该程序的过程中，不同的目的多次，而无需每次都重新实例。后者还使事情变得更容易为计算机。磁盘子系统和CPU可以稍微休息一下，可以这么说。

Temp tables make things easier for the programmer by letting the programmer break up a single complex query into multiple relatively simpler queries, and also by letting the programmer store results temporarily so they can be consulted multiple times for different purposes during the course of the program without having to be reinstantiated each time. The latter also makes things easier for the computer. The disk subsystem and CPU can take a little rest, so to speak.

前者的一个例子：假设您想获得的所有记录其中：

An example of the former: let say you wanted to get all records where:

                    the sale was in the eastern division
                    and involved one of the several new gizmos introduced last quarter
                    and occurred during the special 5-day bonanza sale

                    or

                    the sale was made by the boss's daughter
                    who floats from division to division
                    and the sale occurred at any time during the month of May

你的程序，然后生成电子邮件称赞为销售营业员，用抄送给部门经理。

Your program will then generate an email praising the salesperson for the sale, with a cc to the division manager.

这是获取满足其中任何一个条件集的记录单上面的查询可能会有点笨拙 - 只为一个人的腐坏的大脑或疲惫的眼睛有点难以对付的那种废话一个漫长的一天后，办理在生活中最阶层来处理。这是一个简单的例子，当然，;在生产系统中的条件往往比上述更复杂，涉及到计算和空值测试和其他各种繁琐的事情，可能会导致查询语句长长，变成纠结的线团。

The single query that fetches records that satisfy either of those sets of conditions above might get a little unwieldy--just a little difficult for one's addled brain or weary eyes to handle after a long day of dealing with the sort of crap one has to deal with in most walks of life. It's a trivial example, of course; in a production system the conditions are often more complex than those above, involving calculations and tests for null values and all sorts of other tedious things that can cause a query statement to grow long and turn into a ball of tangled yarn.

因此​​，如果您创建了一个临时表，你可以填充满足第一组条件行的临时表，然后写，抓住满足第二组条件的行第二次查询，并将其插入到临时表也万岁 - 。您的临时表中包含你需要的工作，在两个小步骤的所有行

So if you created a temp table, you could populate the temp table with the rows that satisfy the first set of conditions, and then write the second query that grabs the rows that satisfy the second set of conditions, and insert them into the temp table too, and voilà -- your temp table contains all the rows you need to work with, in two baby steps.

这篇关于在SQLite数据库使用临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！