{"id":29101,"date":"2024-04-28T10:14:51","date_gmt":"2024-04-28T09:14:51","guid":{"rendered":"https:\/\/nelkodev.com\/blog\/domina-el-insert-de-multiples-filas-en-mysql\/"},"modified":"2024-06-03T17:44:26","modified_gmt":"2024-06-03T16:44:26","slug":"domina-el-insert-de-multiples-filas-en-mysql","status":"publish","type":"post","link":"https:\/\/nelkodev.com\/en\/blog\/master-inserting-multiple-rows-in-mysql\/","title":{"rendered":"Master Multi-Row INSERT in MySQL"},"content":{"rendered":"<p>In the world of database development, inserting multiple records simultaneously can save time and improve code efficiency. In MySQL, this operation is essential not only to maintain efficiency, but also to manage large-scale data effectively. By the way, if you need to expand your knowledge in SQL or related tools, I suggest you visit <a href=\"https:\/\/nelkodev.com\/en\/\">my blog<\/a> where you will find a variety of resources and guides.<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_80 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #ffffff;color:#ffffff\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewbox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #ffffff;color:#ffffff\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewbox=\"0 0 24 24\" version=\"1.2\" baseprofile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-inserting-multiple-rows-in-mysql\/#%C2%BFPor_que_usar_insercion_de_multiples_filas\" >Why use multiple row insertion?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-inserting-multiple-rows-in-mysql\/#Sintaxis_basica_de_INSERT_para_multiples_filas_en_MySQL\" >Basic INSERT syntax for multiple rows in MySQL<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-inserting-multiple-rows-in-mysql\/#Casos_practicos_de_insercion_multiple\" >Multiple insertion use cases<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-inserting-multiple-rows-in-mysql\/#Ejemplo_1_Insertando_datos_en_una_tabla_de_usuarios\" >Example 1: Inserting data into a user table<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-inserting-multiple-rows-in-mysql\/#Ejemplo_2_Uso_de_insercion_multiple_para_llenar_una_tabla_de_pedidos\" >Example 2: Using multiple insert to populate an order table<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-inserting-multiple-rows-in-mysql\/#Consejos_para_optimizar_las_inserciones_multiples\" >Tips for optimizing multiple insertions<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-inserting-multiple-rows-in-mysql\/#1_Limite_el_numero_de_filas_por_consulta\" >1. Limit the number of rows per query<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-inserting-multiple-rows-in-mysql\/#2_Usar_transacciones\" >2. Use transactions<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-inserting-multiple-rows-in-mysql\/#3_Ajustar_la_configuracion_del_servidor_MySQL\" >3. Adjust MySQL server configuration<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/nelkodev.com\/en\/blog\/master-inserting-multiple-rows-in-mysql\/#Conclusion\" >Conclusion<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"%C2%BFPor_que_usar_insercion_de_multiples_filas\"><\/span>Why use multiple row insertion?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>When working with large volumes of data, such as in web applications or information systems, it becomes crucial to be able to insert multiple rows of data in a single operation. This approach minimizes the number of SQL queries sent to the server, reducing I\/O load and thus optimizing overall application performance.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Sintaxis_basica_de_INSERT_para_multiples_filas_en_MySQL\"><\/span>Basic INSERT syntax for multiple rows in MySQL<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The syntax for inserting multiple rows into a MySQL table is straightforward but powerful. Here I show you how it is done:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">INSERT INTO table_name (column1, column2, column3) VALUES (value1a, value2a, value3a), (value1b, value2b, value3b), (value1c, value2c, value3c);<\/code><\/pre>\n<p>In the previous example, <code>table_name<\/code> would be the name of the table in your database where you want to insert the data. <code>column1<\/code>, <code>column2<\/code>, <code>column3<\/code> are the names of the columns into which you want to insert the data. Each set of parentheses in <code>VALUES<\/code> represents a new row of data to be inserted into the table.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Casos_practicos_de_insercion_multiple\"><\/span>Multiple insertion use cases<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<h3><span class=\"ez-toc-section\" id=\"Ejemplo_1_Insertando_datos_en_una_tabla_de_usuarios\"><\/span>Example 1: Inserting data into a user table<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Imagine that you want to insert three new users into your database. The board <code>users<\/code> It has three columns: id, name and email. Here&#039;s how you would do it:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">INSERT INTO users (id, name, email) VALUES (1, &#039;Ana Torres&#039;, &#039;ana@example.com&#039;), (2, &#039;Luis Navarro&#039;, &#039;luis@example.com&#039;), ( 3, &#039;Carmen Sanz&#039;, &#039;carmen@example.com&#039;);<\/code><\/pre>\n<h3><span class=\"ez-toc-section\" id=\"Ejemplo_2_Uso_de_insercion_multiple_para_llenar_una_tabla_de_pedidos\"><\/span>Example 2: Using multiple insert to populate an order table<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Suppose you are filling out a table <code>orders<\/code> which records product purchases with columns for id, product and quantity:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">INSERT INTO orders (id, product, quantity) VALUES (101, &#039;MySQL Book&#039;, 2), (102, &#039;Python Course&#039;, 1), (103, &#039;Data Subscription&#039;, 4);<\/code><\/pre>\n<h2><span class=\"ez-toc-section\" id=\"Consejos_para_optimizar_las_inserciones_multiples\"><\/span>Tips for optimizing multiple insertions<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<h3><span class=\"ez-toc-section\" id=\"1_Limite_el_numero_de_filas_por_consulta\"><\/span>1. Limit the number of rows per query<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Although MySQL can handle large inserts, it is wise not to overload a single query with too many rows. This could make the query more susceptible to failure and could lock the table for longer than necessary.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"2_Usar_transacciones\"><\/span>2. Use transactions<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>To ensure data integrity when inserting multiple rows, especially in large-scale applications, consider wrapping your inserts in a transaction:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">START TRANSACTION; INSERT INTO table (columns) VALUES (data), (data), (data); COMMIT;<\/code><\/pre>\n<h3><span class=\"ez-toc-section\" id=\"3_Ajustar_la_configuracion_del_servidor_MySQL\"><\/span>3. Adjust MySQL server configuration<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>If you regularly need to perform bulk inserts, you might consider adjusting the <code>bulk_insert_buffer_size<\/code> to improve performance.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Conclusion\"><\/span>Conclusion<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Inserting multiple rows in MySQL is not only an essential technical skill for any database developer, but it is also a key enabler for application performance. Remember that fewer connections and fewer writes to the DB will always add up to greater efficiency. If you have any questions or want to learn more about advanced MySQL techniques, feel free to visit my page. <a href=\"https:\/\/nelkodev.com\/en\/contact\/\">contact<\/a>.<\/p>\n<p>As you progress with MySQL, continuing to explore and experiment with different methods will help you further understand its capabilities and how to apply them effectively in your projects. Happy coding!<\/p>","protected":false},"excerpt":{"rendered":"<p>In the world of database development, inserting multiple records simultaneously can save time and improve code efficiency. In MySQL, this operation is critical not only to maintain efficiency, but also to effectively manage large-scale data. By the way, if you need to expand your knowledge in SQL or [\u2026]<\/p>","protected":false},"author":1,"featured_media":29102,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[420,2185,2021],"tags":[205,2189,639,1473,2026,2137,2190,1589,352],"class_list":["post-29101","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","category-curso-mysql","category-modifying-data","tag-blog","tag-curso","tag-data","tag-domina","tag-filas","tag-insert","tag-modifying","tag-multiples","tag-mysql"],"_links":{"self":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts\/29101","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/comments?post=29101"}],"version-history":[{"count":0,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts\/29101\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/media\/29102"}],"wp:attachment":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/media?parent=29101"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/categories?post=29101"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/tags?post=29101"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}