在 MySQL“插入”中使用“限制”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11768127/
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
Use "LIMIT" in a MySQL "INSERT"?
提问by John Smith
Can i use LIMIT 2 on MySQL INSERT query? e.g.
我可以在 MySQL INSERT 查询上使用 LIMIT 2 吗？例如
INSERT INTO MyTable
(user_name,password)
VALUES
(john,366543),
(sam,654654)
LIMIT 2
I tried and its saying
我试过了，它说
`#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 2' at line 1`
回答by Michael Fredrickson
You could do this using the INSERT ... SELECT syntax:
您可以使用INSERT ... SELECT 语法执行此操作：
INSERT INTO MyTable (user_name, password)
SELECT 'john', '366543'
UNION ALL SELECT 'sam', '654654'
LIMIT 2;
Not sure why you would want to... maybe if you had a very long list of static values that you wanted to easily control by setting the limit?
不知道为什么你会想要......也许如果你有一个很长的静态值列表，你想通过设置限制来轻松控制？
EDIT:
As pst notes, the LIMITis actually part of the SELECT, and has nothing to do with the INSERTitself...
编辑：正如 pst 所指出的，LIMIT实际上是 的一部分SELECT，与本身无关INSERT......
回答by Ajay Gadhavana
Mostly If we are inserting data from another table that time we need to set limit to inserting specific numbers of data
大多数时候如果我们从另一个表插入数据，我们需要设置限制插入特定数量的数据
insert into cas_user (cas_name) select cas_name from users limit 1,5;
Hope this will help.
希望这会有所帮助。
回答by Vivek Vardhan
If you are trying to insert huge bulk data in parts by using limit, you are operating within the initial constraints laid down by the MySQL.
如果您试图通过使用限制来部分插入大量数据，那么您是在 MySQL 规定的初始约束内操作的。
Try increasing the values of the constraints rather : PFB
尝试增加约束的值：PFB
Variables : max_allowed_packet, bulk_insert_buffer_size, key_buffer_size
变量：max_allowed_packet的，bulk_insert_buffer_size，key_buffer_size的
Sample queries to show and set :
要显示和设置的示例查询：
show variables like 'max_allowed_packet';
SET GLOBAL max_allowed_packet=524288000;
References:
http://forums.mysql.com/read.php?20,161869
参考资料：http:
//forums.mysql.com/read.php?20,161869
MySQL - how many rows can I insert in one single INSERT statement?
回答by Ruzbeh Irani
LIMIT 2 will only work with select no values
LIMIT 2 仅适用于 select no values
回答by Robert
I know it's an old post but you can use foreach loop to limit insert statement. Something like:
我知道这是一篇旧帖子，但您可以使用 foreach 循环来限制插入语句。就像是：
$i = '1';
foreach($item as $item){
IF($i <= '2'){
IF ($stmt = $connection->prepare("INSERT IGNORE INTO `db`.`table`( `item`) VALUES (?)")) {
/* Bind our params */
$stmt->bind_param('s' , $item);
$stmt->execute();
$stmt->close();
}
}
$i++;
}
回答by Selva
no you cannot use limit in insert query
不，您不能在插入查询中使用限制
