MySql DB

MySQLI INSERT INTO

Last modified on: December 11, 2024

<div class="flex-shrink-0 flex flex-col relative items-end">
<div>
<div class="pt-0">
<div class="gizmo-bot-avatar flex h-8 w-8 items-center justify-center overflow-hidden rounded-full">&nbsp;</div>
</div>
</div>
</div>
<div class="group/conversation-turn relative flex w-full min-w-0 flex-col agent-turn">
<div class="flex-col gap-1 md:gap-3">
<div class="flex max-w-full flex-col flex-grow">
<div class="min-h-8 text-message flex w-full flex-col items-end gap-2 whitespace-normal break-words text-start [.text-message+&amp;]:mt-5" dir="auto" data-message-author-role="assistant" data-message-id="557edeec-ffdf-4246-b5a0-dae82364b54c" data-message-model-slug="gpt-4o">
<div class="flex w-full flex-col gap-1 empty:hidden first:pt-[3px]">
<div class="markdown prose w-full break-words dark:prose-invert light">
<h3>MySQL INSERT INTO Statement: Add Data to Tables</h3>
<p>The <strong><code>INSERT INTO</code></strong> statement in MySQL is used to insert new rows into a table. This operation is fundamental for adding data to a database.</p>
<hr />
<h3>Basic Syntax</h3>
<h4>1. <strong>Insert Specific Columns</strong></h4>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>INSERT INTO table_name (column1, column2, ...) 
VALUES (value1, value2, ...);
</code></pre>
</div>
</div>
</div>
</div>
<ul>
<li><strong><code>table_name</code></strong>: The name of the table where data will be inserted.</li>
<li><strong><code>column1, column2, ...</code></strong>: The columns that will receive the values.</li>
<li><strong><code>value1, value2, ...</code></strong>: The actual values to insert into the columns.</li>
</ul>
<h4>2. <strong>Insert All Columns</strong></h4>
<p>If you want to insert values for all columns, you can omit the column names:</p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>INSERT INTO table_name 
VALUES (value1, value2, ...);
</code></pre>
</div>
</div>
</div>
</div>
<hr />
<h3>Examples of INSERT INTO Usage</h3>
<h4>1. <strong>Insert a Single Row</strong></h4>
<p>Add a new user to the <code>users</code> table:</p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>INSERT INTO users (name, email, role) 
VALUES ('John Doe', 'john.doe@example.com', 'admin');
</code></pre>
</div>
</div>
</div>
</div>
<h4>2. <strong>Insert Multiple Rows</strong></h4>
<p>Insert multiple rows in a single query:</p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>INSERT INTO users (name, email, role) 
VALUES 
    ('Alice', 'alice@example.com', 'editor'),
    ('Bob', 'bob@example.com', 'viewer');
</code></pre>
</div>
</div>
</div>
</div>
<h4>3. <strong>Insert Data with Default Values</strong></h4>
<p>Omit columns that have default values:</p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>INSERT INTO users (name, email) 
VALUES ('Charlie', 'charlie@example.com');
</code></pre>
</div>
</div>
</div>
</div>
<p>If the <code>role</code> column has a default value (e.g., <code>viewer</code>), it will automatically be assigned.</p>
<h4>4. <strong>Insert Data with NULL Values</strong></h4>
<p>Explicitly insert a <code>NULL</code> value:</p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>INSERT INTO users (name, email, role) 
VALUES ('Daisy', NULL, 'editor');
</code></pre>
</div>
</div>
</div>
</div>
<h4>5. <strong>Using Subqueries to Insert Data</strong></h4>
<p>Insert data into a table based on a query from another table:</p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>INSERT INTO archive_users (name, email, role) 
SELECT name, email, role 
FROM users 
WHERE active = 0;
</code></pre>
</div>
</div>
</div>
</div>
<hr />
<h3>Handling Duplicate Keys with INSERT</h3>
<h4>1. <strong>Using ON DUPLICATE KEY UPDATE</strong></h4>
<p>If a duplicate key violation occurs, update the existing row instead:</p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>INSERT INTO users (id, name, email) 
VALUES (1, 'Eve', 'eve@example.com') 
ON DUPLICATE KEY UPDATE email = 'eve@example.com';
</code></pre>
</div>
</div>
</div>
</div>
<h4>2. <strong>INSERT IGNORE</strong></h4>
<p>Ignore rows that would cause duplicate key violations:</p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>INSERT IGNORE INTO users (id, name, email) 
VALUES (1, 'Eve', 'eve@example.com');
</code></pre>
</div>
</div>
</div>
</div>
<hr />
<h3>Practical Example</h3>
<p>Insert a new order into the <code>orders</code> table with specific details:</p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>INSERT INTO orders (order_id, user_id, total_amount, status) 
VALUES (101, 1, 250.75, 'pending');
</code></pre>
</div>
</div>
</div>
</div>
<hr />
<h3>Performance Tips for INSERT</h3>
<ol>
<li><strong>Batch Inserts</strong>: Insert multiple rows in one query to improve performance.
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>INSERT INTO table_name (column1, column2) 
VALUES 
    (value1, value2), 
    (value3, value4);
</code></pre>
</div>
</div>
</div>
</div>
</li>
<li><strong>Indexes</strong>: Minimize index overhead during bulk inserts by temporarily disabling indexes.</li>
<li><strong>Transactions</strong>: Use transactions to ensure data integrity when inserting multiple rows.</li>
</ol>
<hr />
<h3>&nbsp;</h3>
</div>
</div>
</div>
</div>
</div>
</div>

Web Technology View More

MySQLI INSERT INTO

Learn Ansible

Learn HTML

Learn Angular 3

Learn PHP

Learn Angular 2.0

Learn Python

Learn C# Sharp

Learn CSS

Learn Node Js

Learn JQuery Framework

Learn SQL Tutorial

Learn MySql

Learn PHP 7.1

Learn Angular 3

Learn Angular 2

Learn HTML5

Learn Angular 2

Learn Angular 3.0

Learn Bootstrap 3

Learn Bootstrap 4

Learn Bootstrap 5

Learn JQuery Tutorials