仅当未达到限制时才插入MySQL [英] Mysql insert only if limit has not been reached

问题描述

我希望我的标题正确，因为我真的不知道该怎么说！！

I hope I have got the title right as I don't really know how to word this!!

这里是背景...

我有一个将数据插入数据库的应用程序。该数据库包含一个日期字段和数据字段（还有其他字段，但为简单起见，仅需要这两个字段）。同一日期只能有8个条目，不能再有其他条目。在正常操作中，这并不是真正的问题，但是每个月两次数据库在一天快要结束时遭受重创。

I have a app inserting data to a db. The db holds a date field and data field (there are others but for simplicity these two are the only ones needed). There can only be 8 entries on the same date, no more. In normal operation this is not really an issue but twice a month the database gets hit hard towards the end of the day.

我目前的操作方式是查询一个日期有多少条记录。如果少于9，则插入数据。

The way I currently do it, is to query how many records there are for a date. If that's less than 9 I insert the data.

它尚未发生，但我担心的是请求A进入并检查了数据库，发现有7条记录。单击确定。但是在A可以插入之前，请求B进入并仅找到7条记录。单击确定。但这将在一个日期输入9条记录。这不可能发生。

It has never happened yet but my concern is that request A comes in and checks the DB and finds there are 7 records. OK to insert. But before A can insert, request B comes in and finds only 7 records. OK to insert. But that would then enter 9 records on one date. This can't happen.

我认为有两种解决方法，但是想知道A，如果我完全正确的话！或B有更好的解决方案。

I think there are two solutions to this but would like to know A, if I'm right at all! or B is there a better solution.

我认为...

A）我可以这样做交易。但是，我仍然不会遇到相同的问题吗？据我所知，只要事务中没有查询失败就可以运行。

A) I could do it in a transaction. However, would I still no encounter the same problem? As far as I am aware as long as no queries in a transaction fail then it runs anyway.

或

B）使用存储过程先检查然后插入。我对存储过程的经验很少，所以我必须承认我不知道我在说什么！

B) Use a stored procedure to check first then insert. I have had very little experience with stored procedures so I must admit I have no idea what I'm talking about here!

或者，有没有办法获得一个查询以首先检查是否少于9个条目？

Alternatively, is there a way to get a single query to check first if there is less than 9 entries??

非常感谢，

Gavin

推荐答案

您担心有人会在另一条记录插入之前先插入新记录，甚至在一百万秒内插入它吗？

You are afraid of that someone would insert a new record before other one insert it even in the million second?

我也有这个问题，所以我只是为您搜索了一些内容，您可以通过两种方式进行操作：

I have this question too, so I just searched something for you, you can do this with two ways:

例如如果您有一个名为 date_table 的表，并且它看起来像：

For example if you have a table which named date_table and it looks like:

date_field
----------
1998-07-13
1992-03-23
1998-09-17
1994-02-30

---

A）计算行数，然后在8行以下时将其插入。 （使用PHP和MySQL）

首先，通过此操作获得多少行：

First, get how many rows are there by this:

SELECT COUNT(*) AS Num FROM date_table WHERE date_field = '1998-07-13'

，因此您将获得一个 Num 字段，该字段告诉您 1998-07-13 ，

so you'll get a Num field which told you how many rows were 1998-07-13,

然后使用PHP防止用户在等于8时插入相同的日期，方法是：

then using PHP to prevent user insert the same date when it's equal 8 by:

<?php if($DB['Num'] >= 8) exit('Sorry, no more same value'); ?>

否则插入行。

或者您不信任PHP，或者您认为有人会在一百万秒之前插入行

Or you don't trust PHP, or you think someone would insert the row more earlier than 1 million second

B）仅在8行以下且只有一个查询时插入它：

INSERT INTO date_table (date_field) 
     SELECT '1998-07-13' 
     FROM dual 
     WHERE (SELECT COUNT(*) FROM date_table WHERE date_field = '1998-07-13') < 9;

注意： dual 是一个mysql系统表，因此您无需创建该表

BEWARE: dual is a mysql system table, so you don't need to create it

如果您不了解上述查询的工作原理，则：

If you don't understand how the above query works:

insert into TABLE_NAME (TABLE_FIELD) 
     select VALUE_YOU_WANT_TO_INSERT
     from dual 
     where (select count(*) 
            from TABLE_NAME 
            where THE_FIELD_YOU_WANT_TO_CHECK = '1998-07-13'
           ) < LESS_THEN_HOW_MUCH;

---

编辑：添加更多字段会更改以下行：

---

EDIT: add more fields change the following lines:

INSERT INTO date_table (field1, field2, field3) 
     SELECT value1, value2, value3

这篇关于仅当未达到限制时才插入MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！