SQL插入SELECT并返回SELECT数据以创建行视图计数
因此，我正在创建一个系统，它将一次从一个表中提取50-150条记录，并将其显示给用户，并试图为每条记录保留一个视图计数。
我认为最有效的方法是创建一个内存表，使用INSERT INTO将行的ID提取到其中，然后有一个cron函数定期运行，以聚合视图ID计数并清除内存表，用最新的视图计数更新原始表。这避免了不断更新可能被访问最多的表，因此我不会每次使用每个查询锁定150行(如果使用MyISAM，则不会锁定整个表)。
基本上，该方法解释了这里。
但是，我当然希望在提取记录信息以供查看的同时执行此操作，并且我希望避免运行第二次单独的查询，只为了获取相同的数据集进行计数。
是否有任何方法选择数据集，返回该数据集，同时从该数据集中插入单个列到另一个表中？
看起来PostgreSQL可能与我想要的返回关键字类似，但我使用的是MySQL。
回答 2
Stack Overflow用户
发布于 2015-09-04 10:33:32
首先，我不会向Main表中添加计数器列。我将创建一个单独的Audit表，该表将保存来自Main表的项的ID，以及请求ID时的至少时间戳。本质上，Audit表将存储请求的历史记录。在这种方法中，您可以轻松地生成更有趣的报告。您始终可以计算每个项目的总计，也可以按日、周、月等计算每个项目或跨越所有项目的汇总。根据数据量的不同，您可以定期删除超过某个阈值(一个月、一年等)的审计条目。
此外，您还可以根据需要在Audit表中存储更多信息，例如，用户ID来计算每个用户的统计数据。
要“自动”填充Audit表，我将创建一个存储过程。客户端代码将调用这个存储过程，而不是执行原始的SELECT。存储过程将返回与原始SELECT完全相同的结果，但也会向客户端代码透明地向Audit表添加必要的详细信息。
因此，让我们假设Audit表如下所示：
CREATE TABLE AuditTable
(
ID int
IDENTITY -- SQL Server
SERIAL -- Postgres
AUTO_INCREMENT -- MySQL
NOT NULL,
ItemID int NOT NULL,
RequestDateTime datetime NOT NULL
)您的主SELECT如下所示：
SELECT ItemID, Col1, Col2, ...
FROM MainTable
WHERE <complex criteria>要在Server中的一个语句中同时执行INSERT和SELECT，我需要在Postgres - RETURNING子句中使用OUTPUT子句，在MySQL - ?？中使用？？我不认为它有这样的东西。因此，MySQL过程将有几个单独的语句。
MySQL
首先执行SELECT并将结果插入到暂时性 (可能是内存)表中。然后将项ID从临时表复制到Audit表中。然后将临时表中的SELECT返回给客户端。
CREATE TEMPORARY TABLE TempTable
(
ItemID int NOT NULL,
Col1 ...,
Col2 ...,
...
)
ENGINE = MEMORY
SELECT ItemID, Col1, Col2, ...
FROM MainTable
WHERE <complex criteria>
;
INSERT INTO AuditTable (ItemID, RequestDateTime)
SELECT ItemID, NOW()
FROM TempTable;
SELECT ItemID, Col1, Col2, ...
FROM TempTable
ORDER BY ...; Server (只是为了逗您开心)。这一条语句同时执行INSERT和SELECT)
MERGE INTO AuditTable
USING
(
SELECT ItemID, Col1, Col2, ...
FROM MainTable
WHERE <complex criteria>
) AS Src
ON 1 = 0
WHEN NOT MATCHED BY TARGET THEN
INSERT
(ItemID, RequestDateTime)
VALUES
(Src.ItemID, GETDATE())
OUTPUT
Src.ItemID, Src.Col1, Src.Col2, ...
;您可以保持Audit表的原样，也可以设置cron定期对其进行总结。这真的取决于数据量。在我们的系统中，我们将单个行存储一周，另外，我们每小时汇总统计数据，并将其保存6周，再加上每天进行18个月的汇总。但是，重要的是，所有这些摘要都是单独的Audit表，我们不把审计信息保存在Main表中，所以我们不需要更新它。
Joe在SQL风格习惯:斜态的攻击中解释得很好
现在转到任何SQL文本，搜索帖子。您将在DDL中找到数以千计的帖子，其中包括名为
createdby、createddate、modifiedby和modifieddate的列，其中包含行声明末尾的特定元数据。它是用一种新语言编写的旧的mag磁带头标签！德加·武！ 头记录只出现在磁带上一次。但是这些元数据值在表中的每一行上都会反复出现。使用数据库(不仅仅是SQL)的主要原因之一是从数据中删除冗余；这只会增加更多的冗余。但是现在想想，当一行被删除时，审计跟踪会发生什么？当行被更新时，审计跟踪会发生什么？痕迹被摧毁了。审计数据应与架构分离。您会把日志文件放在与数据库相同的磁盘驱动器上吗？会计会让同一人批准并收到付款吗？
Stack Overflow用户
发布于 2015-09-04 06:14:32
您会问MySQL是否支持SELECT触发器。不需要这样做。您需要作为两个查询来完成这个任务，但是可以将这些查询放在存储过程中--然后您可以传递正在获取的范围，让它返回结果并将其插入到另一个表中。
使用存储过程的框架示例更新答案：
DELIMITER $$
CREATE PROCEDURE `FetchRows`(IN StartID INT, IN EndID INT)
BEGIN
UPDATE Blah SET ViewCount = ViewCount+1 WHERE id >= StartID AND id <= EndID;
# ^ Assumes counts are stored in the same table. If they're in a seperate table, do an INSERT INTO ... ON DUPLICATE KEY UPDATE ViewCount = ViewCount+1 instead.
SELECT * FROM Blah WHERE id >= StartID AND id <= EndID;
END$$
DELIMITER ;https://stackoverflow.com/questions/32281469
复制相似问题
腾讯云开发者
