2017-01-11 23:03
浏览 34

正确处理并行插入PHP / MySQL

I have a PHP app which receives information from an external source, and saves data to MySQL.

The algorithm is very simple. The data being sent is a string, and it works this way:

  1. Receive the data in $data. Ex: $data = "info"
  2. Checks in database if "info" exists. Ex: select count(*) from table where data="info"
  3. If "info" exists, then exit
  4. Else insert "info" in database and exit.

When there are several parallel requests to the page, there are some duplicates. The data can be sent twice of more times, but we need to save it just once.

The reason is that there's a time lapse between the steps 2 and 3, where the data is still not inserted, so two instances can insert it at the same time.

What's the correct way to handle this in PHP?

(The idea is not to add a unique index to MySQL, I know it'll work but will throw an error. Also the data being sent is very long, and indexes have a limit.)

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

我有一个PHP应用程序,它从外部源接收信息,并将数据保存到MySQL。 \ n

算法非常简单。 发送的数据是一个字符串,它以这种方式工作:

  1. 接收$ data中的数据。 例如:$ data =“info”
  2. 检查数据库中是否存在“info”。 例如:从表中选择count(*)data =“info”
  3. 如果“info”存在,则退出
  4. 否则在数据库中插入“info”并退出 。

    当页面有多个并行请求时,会有一些重复的请求。 数据可以发送两次以上,但我们只需保存一次。

    原因是步骤2和3之间有一段时间间隔,数据仍然存在 没有插入,所以两个实例可以同时插入它。


    (我的想法不是向MySQL添加一个唯一的索引,我知道它会工作但会引发错误。而且发送的数据也很长,索引有限制 。)

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

1条回答 默认 最新

  • doupao1978 2017-01-11 23:10

    You can do the test and INSERT in a single query:

    INSERT INTO yourTable (columnName)
    SELECT :data
    WHERE NOT EXISTS (SELECT 1 FROM yourTable WHERE columnName = :data)
    打赏 评论

相关推荐 更多相似问题