douzhi7661
2014-02-13 16:26
浏览 133
已采纳

使用TRIGGER在mySQL INSERT上进行计时问题

For an iPad app I have created a web service which creates a new table row in my teams table when the user starts the app. The PHP generates a unique ID and fills some other fields as well.

Additionally I have created a trigger on the teams table in mySQL on insert where a teamnumber is generated automatically depending on the already inserted rows for that specific day, project and group.

Unfortunately I seem to have a timing issue when inserting new rows to my table sometimes. If two apps create a team in the same second (milisecond?) the result of the mySQL trigger will be the same teamnumber for both apps. So instead of having teamnumber 1 and 2 both apps have teamnumber 1.

My teamtable looks something like this:

TeamID | pProject | group | teamnumber | languagecode | created_at

And the trigger for mySQL:

CREATE TRIGGER after_team_insert BEFORE INSERT ON teams 

FOR EACH ROW
BEGIN

SELECT COUNT( * ) INTO @counter
FROM teams
WHERE DATE( created_at ) = DATE( NOW( ) )
AND teams.group = NEW.group
AND teams.pProject = NEW.pProject;

SET NEW.teamnumber = CAST(@counter AS UNSIGNED) + 1;

END;

I think my problem has something to do with parallel threads that insert rows into the database and therefor get a wrong count when the trigger is working.

Does anyone have a solution for this problem or do I have to use some kind of queue in PHP to prevent this doubling of teamnumbers?

Thanks in advance, Carsten.

图片转代码服务由CSDN问答提供 功能建议

对于iPad应用程序,我创建了一个Web服务,在用户启动时在我的团队表中创建一个新的表行 该应用程序。 PHP生成一个唯一的ID并填充其他一些字段。

此外,我在插入的mySQL中的teams表上创建了一个触发器,其中根据已插入的内容自动生成一个teamnumber 该特定日期,项目和组的行。

不幸的是,有时我在向表中插入新行时似乎有时间问题。 如果两个应用程序在同一秒内创建一个团队(毫秒?),mySQL触发器的结果将是两个应用程序的相同团队编号。 因此,不是拥有团队编号1和2,而是两个应用都拥有团队编号1.

我的团队表看起来像这样:

  TeamID |  pProject | 小组|  teamnumber | 语言代码|  created_at 
   
 
 

mySQL的触发器:

 创建TRIGGER after_team_insert在插入团队之前
 
每个 ROW 
BEGIN 
 
SELECT COUNT(*)INTO @counter 
FROM团队
WHERE DATE(created_at)= DATE(NOW())
AND teams.group = NEW.group 
AND teams.pProject = NEW.pProject; \  n 
SET NEW.teamnumber = CAST(@counter AS UNSIGNED)+ 1; 
 
END; 
   
 
 

我认为我的问题与并行线程有关 将行插入数据库并因此在触发器工作时得到错误的计数。

有没有人有这个问题的解决方案,或者我必须在PHP中使用某种类型的队列 防止团队数量增加一倍?

提前致谢, Carsten。

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dqy012345 2014-02-13 16:31
    已采纳

    I guess you can use a lock table ! http://dev.mysql.com/doc/refman/5.0/fr/lock-tables.html

    已采纳该答案
    打赏 评论
  • duandangqin0559 2014-02-14 09:57

    If, for any reason, you can't lock your table you can try to handle the problem differently:

    • Add a unique constraint on the columns group / pProject / teamnumber.
    • Handle any constraint error in your code, check if the teamnumber is the problem, and then try to reevaluate it.
    • Ensure you're teamnumber has been set. If not, try again.

    Not that clean, a bit tricky, but it should do the work if you don't mind team number 3 was inserted after team number 4.

    打赏 评论

相关推荐 更多相似问题