在应用程序部分设置MySQL唯一键或检查副本？ [英] Setting MySQL unique key or checking for duplicate in application part?

问题描述

哪一个更可靠，性能更好？设置MySQL唯一密钥并使用INSERT IGNORE或首先检查数据库中是否存在数据库，并根据结果进行操作？

如果答案是第二个，有没有办法做一个单一的SQL查询而不是两个？

更新：我问，因为我工作的公司的同事认为处理这些问题应该在应用程序部分完成根据他们更可靠。

解决方案

您的应用程序不会捕获重复。

两个并发调用可以插入相同的数据，因为每个进程在您的应用程序检查唯一性时都看不到其他进程。每个进程都认为INSERT可以。

您可以强制执行某种序列化，但是您有瓶颈和性能限制。而且您将将其他客户端写入数据库，即使它只是一个发布脚本 -

这就是为什么有这样的事情一般唯一的索引和约束。外键，触发器，检查约束，NULL / NIOT NULL，数据类型约束都是为了执行数据完整性

还有一些代码猴子认为他们可以做的傲慢更好。

请参阅programmers.se：关系数据库中的约束 - 为什么不完全删除它们，而这个在应用程序代码中执行数据库约束（SO）

Which one is more reliable and has better performance? Setting MySQL unique key and using INSERT IGNORE or first checking if data exists on database and act according to the result?

If the answer is the second one, is there any way to make a single SQL query instead of two?

UPDATE: I ask because my colleagues in the company I work believe that deal with such issues should be done in application part which is more reliable according to them.

解决方案

You application won't catch duplicates.

Two concurrent calls can insert the same data, because each process doesn't see the other while your application checks for uniqueness. Each process thinks it's OK to INSERT.

You can force some kind of serialisation but then you have a bottleneck and performance limit. And you will have other clients writing to the database, even if it is just a release script-

That is why there are such things as unique indexes and constraints generally. Foreign keys, triggers, check constraints, NULL/NIOT NULL, datatype constraints are all there to enforce data integrity

There is also the arrogance of some code monkey thinking they can do better.

See programmers.se: Constraints in a relational databases - Why not remove them completely? and this Enforcing Database Constraints In Application Code (SO)

这篇关于在应用程序部分设置MySQL唯一键或检查副本？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！