Use "LIMIT" in MySQL "INSERT"?

Question

Can I use LIMIT 2 in a MySQL INSERT query? eg.

INSERT INTO MyTable (user_name,password) VALUES (john,366543), (sam,654654) LIMIT 2 

I tried and said:

 `#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` 

Answer 1

You can do this using the INSERT ... SELECT Syntax :

 INSERT INTO MyTable (user_name, password) SELECT 'john', '366543' UNION ALL SELECT 'sam', '654654' LIMIT 2; 

Not sure why you would like ... perhaps if you had a very long list of static values ​​that you would like to easily control by setting a limit?

EDIT: As pst notes, LIMIT is actually part of SELECT and has nothing to do with INSERT itself ...

Answer 2

LIMIT 2 will only work with selected values

Answer 3

Basically, if we insert data from another table, we need to set a restriction on the insertion of certain numbers of data

  insert into cas_user (cas_name) select cas_name from users limit 1,5; 

Hope this helps.

Answer 4

If you are trying to insert huge massive data in parts using a constraint, you are working within the initial constraints set by MySQL.

Try to increase the limit values: PFB

Variables: max_allowed_packet , bulk_insert_buffer_size , key_buffer_size

Examples of requests and impressions:

 show variables like 'max_allowed_packet'; SET GLOBAL max_allowed_packet=524288000; 

Literature:
http://forums.mysql.com/read.php?20,161869

MySQL - how many rows can be inserted into a single INSERT statement?

Answer 5

I know this old post, but you can use the foreach loop to restrict the insert statement. Sort of:

  $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++; } 

Answer 6

no, you cannot use the restriction in the insert request