限制MySQL表中每个键的最大记录数 [英] Limiting maximum records per key in MySQL table

问题描述

我有一个用于按照以下结构存储产品的表格...

I've a table for storing products as per the following structure...

id shop_id product_id product_title

id shop_id product_id product_title

每个商店都选择一个计划，因此它可以在此表中存储N个产品.每个商店的N都不一样.

Every shop selects a plan, and accordingly it can stored N products in this tables. N is different for every shop.

问题陈述:在表中执行插入操作时，每个shop_id的条目总数不能超过N.

Problem Statement: While doing insert operation in the table, total number of entries per shop_id can't be more than N.

我可以在每次插入操作之前对#products进行计数，然后决定是将新条目放入表中还是将其忽略.这些操作是由收到的事件触发的，可能有数以百万计的事件.因此，它似乎并不高效.性能是关键.

I can count the #products before every insert operation, and then decide whether new entry should go in the table or be ignored. The operations are triggered by events received, and it may be in millions. So it doesn't seem to efficient. Performance is the key.

有更好的方法吗?

推荐答案

我认为您应该使用存储过程，以便可以将验证委托给MySql，而不委托给PHP，这是您可能需要的示例，确保正确替换表名和列名.

I Think you should use a stored procedure so you can delegate the validations to MySql and not to PHP, here is an example of what you might need, just be sure to replace the table names and columns names properly.

如果您担心性能，则应检查表的索引以获得更好的性能.

If you are worried about the performance you should check the indexes of the tables for better performance.

程序

DELIMITER //
DROP PROCEDURE IF EXISTS storeProduct//
CREATE PROCEDURE storeProduct(IN shopId INT, IN productId INT, IN productTitle VARCHAR(255))
LANGUAGE SQL MODIFIES SQL DATA SQL SECURITY INVOKER
BEGIN
    /*Here we get the plan for the shop*/
    SET @N = (SELECT plan FROM planTable WHERE shop_id = shopId);

    /*NOW WE COUNT THE PRODUCTS THAT ARE STORED WITH THE shop_id*/
    SET @COUNT = (SELECT COUNT(id) FROM storing_products WHERE shop_id = shopId);

    /*NOW WE CHECK IF WE CAN STORE THE PRODUCTS OR NOT*/
    IF @COUNT < @N THEN
        /*YES WE CAN INSERT*/
        INSERT INTO storing_products(shop_id, product_id, product_title)
        VALUES (shopId, productId, productTitle);
        /*1 means that the insert acording to the plan is ok*/
        SELECT 1 AS 'RESULT';
    ELSE
        /*NO WE CAN NOT INSERT*/
        /*0 means that the insert acording to the plan is not ok*/
        SELECT 0 AS 'RESULT';
    END IF;
END//
DELIMITER ;

现在您可以像从PHP一样调用它

Now you can call it from PHP just like

<?php
    //....
    $result = $link->query("CALL storeProduct($shop_id, $product_id, $product_title);");
    //....
?>

或您所做的任何事情

答案就像

+--------+
| RESULT |
+--------+
|      1 |
+--------+

如果还可以，或者

+--------+
| RESULT |
+--------+
|      0 |
+--------+

如果不是

我希望它将对您有帮助

问候

这篇关于限制MySQL表中每个键的最大记录数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！