缓存 mysql 插入 - 保持数据完整性
我想做很多插入，但是一段时间后可以更新mysql吗？
例如，如果有一个查询，例如“
Update views_table SET views = views + 1 WHERE id = 12;
Could it not be possible to Maybe store this query until the views has gone up to 100”，然后运行以下命令，而不是运行上述 100 次查询。
Update views_table SET views = views + 100 WHERE id = 12;
现在，假设已经完成，那么数据完整性问题就来了。假设有 100 个打开的 php 文件，它们都将运行相同的查询。现在，除非存在增加缓存视图的锁定机制，否则多个文件可能具有相同的缓存视图值，因此可以说进程 1 可能有 25 个缓存视图，而 php 进程 2 可能有 25 个视图和进程3 可能有 27 个文件视图。现在假设进程 3 完成并将计数器增加到 28。然后假设 php 进程即将完成，并且它在进程 3 之后完成，这意味着计数器将回到 26。
那么你们有什么解决方案吗？速度快，但数据也安全。
谢谢
I would like to do a lot of inserts, but could it be possible to update mysql after a while.
For example if there is a query such as
Update views_table SET views = views + 1 WHERE id = 12;
Could it not be possible to maybe store this query until the views have gone up to 100 and then run the following instead of running the query from above 100 times.
Update views_table SET views = views + 100 WHERE id = 12;
Now, lets say that is done, then comes the problem of data integrity. Let's say, there are 100 php files open which are all about to run the same query. Now unless there is a locking mechanism on incrementing the cached views, there is a possibility that multiple files may have a same value of the cached view, so lets say process 1 may have 25 cached views and php process 2 may have 25 views and process 3 may have 27 views from the file. Now lets say process 3 finishes and increments the counter to 28. Then lets say php process is about finish and it finished just after process 3, which means that the counter would be brought back down to 26.
So do you guys have any solutions that are fast but are data secure as well.
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
只要您的查询使用相对值
views=views+5，就应该没有问题。仅当您将值存储在脚本中的某个位置，然后自己计算新值时，您可能会遇到麻烦。但你为什么要这样做呢？实际上，你为什么要做这一切？ :)
如果您不想使数据库过载，您可以使用 UPDATE LOW_PRIORITY table set ...，即
LOW_PRIORITY关键字会将更新操作放入队列中并等待表不再被读取或插入使用。As long as your queries use relative values
views=views+5, there should be no problems.Only if you store the value somewhere in your script, and then calculate the new value yourself,you might run into trouble. But why would you want to do this? Actually, why do you want to do all of this in the first place? :)
If you don't want to overload the database, you could use
UPDATE LOW_PRIORITY table set ..., theLOW_PRIORITYkeyword will put the update action in a queue and wait for the table to no longer be used by reads or inserts.首先：对于这些查询：无论进程何时启动，UPDATE .. SET col = col + 1 都是安全操作，因此它永远不会“减少”计数器。
关于“存储此查询直到视图达到 100，然后运行以下命令而不是运行 100 次以上的查询”：事实并非如此。您可以将计数器存储在更快的内存中（例如 memcached），并通过一个过程将其偶尔传输到数据库，或者使用 AFTER UPDATE 触发器将其存储在另一个表中，但我我真的不认为这样做有什么意义。
First of all: with these queries: regardless of when a process starts, the
UPDATE .. SET col = col + 1is a safe operation, so it will not 'decrease' the counter, ever.Regarding to 'store this query until the views have gone up to 100 and then run the following instead of running the query from above 100 times': not really. You can store a counter in faster memory (memcached comes to mind), with a process that transfers it to the database once in a while, or store it in another table with a
AFTER UPDATEtrigger, but I don't really see a point doing that.