在 MySQL 中使用 LIMIT 1
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5924115/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using LIMIT 1 in MySQL
提问by santa
When is it appropriate to add LIMIT 1 at the end of the query in MySQL. I normally add it in DELETE but I've seen it being used with INSERT a and even UPDATE. Is it an overkill or a good practice?
什么时候在 MySQL 中的查询末尾添加 LIMIT 1 是合适的。我通常在 DELETE 中添加它，但我已经看到它与 INSERT a 甚至 UPDATE 一起使用。这是一种矫枉过正还是一种好的做法？
采纳答案by Rich Bradshaw
Well, if you use EXPLAIN you'll see that it speeds things up, as once it's found one result, it stops.
好吧，如果您使用 EXPLAIN，您会发现它加快了速度，因为一旦找到一个结果，它就会停止。
It's also a failsafe – if you know your insert of update should only ever affect one row, by specifying it, you are guaranteeing that it won't ever go wrong and mess up multiple rows.
这也是一个故障保护——如果你知道你的更新插入应该只影响一行，通过指定它，你可以保证它永远不会出错并弄乱多行。
回答by peufeu
INSERT INTO .. VALUES () LIMIT 1
Doesn't exist. Hopefully you know how many VALUES() you put in there!
不存在。希望你知道你在那里放了多少 VALUES() ！
INSERT INTO .. SELECT ... LIMIT 1
Does exist and is pretty useful, and off topic since the LIMIT is on the SELECT.
确实存在并且非常有用，而且由于 LIMIT 在 SELECT 上，因此与主题无关。
DELETE ... LIMIT 1
UPDATE ... LIMIT 1
Extremely rarely useful. Either you know your database enough to be certain that your WHERE matches a UNIQUE condition, or you don't, in which case you should spend a little more time looking at your database and learning SQL.
极少有用。要么您足够了解您的数据库以确保您的 WHERE 匹配一个 UNIQUE 条件，要么您不知道，在这种情况下，您应该多花一点时间查看您的数据库并学习 SQL。
But ...
但 ...
UPDATE jobs SET owner=me WHERE owner IS NULL ORDER BY job_submit_time LIMIT 1
Can be extremely useful! This makes a near-lockless job queue, where you can come and take a job from the queue without any waiting, locking, or conflict resolution. Quite excellent.
可以非常有用！这形成了一个近乎无锁的作业队列，您可以从队列中取出作业，而无需任何等待、锁定或冲突解决。相当优秀。
DELETE FROM cache ORDER BY last_update_time LIMIT N
Cache takes too much space ? Purge the N oldest rows...
缓存占用太多空间？清除 N 个最旧的行...
回答by Mikecito
I can't think of an instance where something that speeds up a query and makes it more secure at the same time could be called overkill. I'll put my vote in the good practice column.
我想不出一个例子，在这种情况下，可以将加快查询速度并同时使其更安全的东西称为矫枉过正。我会把我的票投到良好实践专栏中。
回答by Shawn
Extremely rarely useful. Either you know your database enough to be certain > that your WHERE matches a UNIQUE condition, or you don't, in which case you > should spend a little more time looking at your database and learning SQL
极少有用。要么您足够了解您的数据库以确保您的 WHERE 匹配一个 UNIQUE 条件，要么您不匹配，在这种情况下，您 > 应该花更多时间查看您的数据库并学习 SQL
There are instances where a limit 1 is REALLY useful. For example, a table without a unique key. If we have five of the exact same, then limit one will work.
在某些情况下，限制 1 非常有用。例如，没有唯一键的表。如果我们有五个完全相同的，那么限制一个就可以了。
回答by Blue Water
Assume you are writing Forgot Passwordcode (in PHP):
假设您正在编写忘记密码代码（在 PHP 中）：
UPDATE
`users`
SET
`pass` = PASSWORD('{$new_salted_pass}')
WHERE
`account_id` = '{$account_id}'
LIMIT 1
If your software is a Software as a Service (SaaS),
如果您的软件是软件即服务 (SaaS)，
andmultiple organizations use your software,
和多个组织使用您的软件，
andaccount_id-organization_idfields together are a unique id,
和account_id-organization_id字段一起是唯一的 ID，
andthe account_id is auto-generated in the code like so:
并且account_id 在代码中自动生成，如下所示：
JONES0001
JONES0002
JONES0003
...
JONES0001
JONES0002
JONES0003
...
andthe software engineer forgets to add AND organization_id = '{$organization_id}'to the query's WHEREcondition,
并且软件工程师忘记添加AND organization_id = '{$organization_id}'到查询的WHERE条件中，
andthe code passes QA when testing the Forgot Password functionality in QA environment,
并且代码在 QA 环境中测试忘记密码功能时通过了 QA，
andthe code is then pushed to production,
然后将代码推送到生产中，
thenthe LIMIT 1can limit the damage done when two organizations' user's share the same account_id.
那么LIMIT 1当两个组织的用户共享相同的 account_id 时，可以限制造成的损害。
