MySQL INSERT Data

Overview

The INSERT statement is used to add new rows to a table. This chapter covers various ways to insert data, including single rows, multiple rows, and inserting from other tables.

INSERT Syntax Options

INSERT INTO table_name (columns) VALUES (values);
INSERT INTO table_name SET column=value;
INSERT INTO table_name (columns) SELECT ...;
INSERT INTO table_name (columns) VALUES (values), (values), ...;

Basic INSERT

Insert Single Row

-- Specify columns
INSERT INTO users (name, email, age) 
VALUES ('John Doe', 'john@example.com', 30);

-- Omit optional columns (they use DEFAULT or NULL)
INSERT INTO users (name, email) 
VALUES ('Jane Smith', 'jane@example.com');

-- Insert all columns
INSERT INTO users VALUES (1, 'Bob Wilson', 'bob@example.com', 25, '2024-01-01');

-- Insert with DEFAULT for optional columns
INSERT INTO users (name, email, status) 
VALUES ('Alice Brown', 'alice@example.com', DEFAULT);

Insert Multiple Rows

-- Multiple values in single statement
INSERT INTO users (name, email, age) VALUES 
    ('User 1', 'user1@example.com', 25),
    ('User 2', 'user2@example.com', 30),
    ('User 3', 'user3@example.com', 35),
    ('User 4', 'user4@example.com', 40),
    ('User 5', 'user5@example.com', 45);

-- Can insert hundreds of rows efficiently
INSERT INTO products (name, price, category) VALUES 
    ('Product A', 19.99, 'Electronics'),
    ('Product B', 29.99, 'Electronics'),
    ('Product C', 39.99, 'Clothing'),
    ('Product D', 49.99, 'Clothing'),
    ('Product E', 59.99, 'Books');

INSERT with SET

-- Using SET clause
INSERT INTO users 
SET name = 'Charlie Brown',
    email = 'charlie@example.com',
    age = 35,
    status = 'active';

-- Useful for inserting from application variables
INSERT INTO users 
SET name = :name_var,
    email = :email_var,
    age = :age_var;

INSERT with SELECT

Copy from Another Table

-- Insert all columns
INSERT INTO archive_users
SELECT * FROM users WHERE deleted_at IS NOT NULL;

-- Insert specific columns
INSERT INTO archive_users (id, name, email, deleted_at)
SELECT id, name, email, deleted_at FROM users WHERE deleted_at IS NOT NULL;

-- Insert with transformations
INSERT INTO user_summary (user_id, name, email_domain)
SELECT 
    id,
    name,
    SUBSTRING_INDEX(email, '@', -1) AS domain
FROM users;

-- Insert distinct values
INSERT INTO unique_emails (email, source)
SELECT DISTINCT email, 'users' FROM users
UNION
SELECT DISTINCT email, 'contacts' FROM contacts;

Bulk Insert from Query

-- Insert aggregated data
INSERT INTO daily_stats (stat_date, user_count, order_count)
SELECT 
    DATE(created_at) AS stat_date,
    COUNT(DISTINCT user_id),
    COUNT(*)
FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2024-02-01'
GROUP BY DATE(created_at);

-- Insert from multiple sources
INSERT INTO combined_data (id, value, source)
SELECT id, value, 'source1' FROM table1
UNION ALL
SELECT id, value, 'source2' FROM table2;

ON DUPLICATE KEY UPDATE

Basic Syntax

INSERT INTO table_name (columns) VALUES (values)
ON DUPLICATE KEY UPDATE 
    column1 = value1,
    column2 = value2;

Examples

-- Insert or update if email already exists
INSERT INTO users (name, email, login_count) 
VALUES ('John Doe', 'john@example.com', 1)
ON DUPLICATE KEY UPDATE 
    name = VALUES(name),
    login_count = login_count + 1,
    last_login = NOW();

-- Increment counter
INSERT INTO page_views (page_id, view_count) 
VALUES ('home', 1)
ON DUPLICATE KEY UPDATE 
    view_count = view_count + 1;

-- Multiple columns
INSERT INTO products (sku, name, price, stock) 
VALUES ('SKU001', 'Product Name', 29.99, 100)
ON DUPLICATE KEY UPDATE 
    name = VALUES(name),
    price = VALUES(price),
    stock = stock + VALUES(stock);

Using VALUES() Function

-- Reference values being inserted
INSERT INTO price_history (product_id, price, changed_at)
VALUES (1, 29.99, NOW())
ON DUPLICATE KEY UPDATE 
    price = VALUES(price),
    changed_at = VALUES(changed_at);

-- Use VALUES() to reference original value
INSERT INTO user_stats (user_id, login_count)
VALUES (1, 1)
ON DUPLICATE KEY UPDATE 
    login_count = VALUES(login_count) + 1;

INSERT IGNORE

Basic Usage

-- Skip rows that cause duplicate key or constraint errors
INSERT IGNORE INTO users (email) VALUES 
    ('john@example.com'),
    ('jane@example.com'),
    ('john@example.com');  -- This row is ignored if email exists

-- Handle duplicates gracefully
INSERT IGNORE INTO unique_products (sku, name) VALUES 
    ('SKU001', 'Product A'),
    ('SKU002', 'Product B'),
    ('SKU001', 'Duplicate Product');  -- Ignored

Use Cases

-- Import from external source
INSERT IGNORE INTO users (email, name, source)
SELECT email, name, 'import_2024' 
FROM temp_import;

-- Sync data from another system
INSERT IGNORE INTO products (sku, name, price)
SELECT sku, name, price 
FROM external_products;

REPLACE

Basic Usage

-- REPLACE works like INSERT but deletes existing row first
REPLACE INTO users (id, name, email) VALUES (1, 'New Name', 'new@email.com');

-- If id=1 exists, it's deleted and new row inserted
-- If id=1 doesn't exist, new row is inserted

-- REPLACE with SELECT
REPLACE INTO archive_users 
SELECT * FROM users WHERE id = 1;

REPLACE vs INSERT ON DUPLICATE KEY

| Aspect |-------------|---------|------------------------| | Behavior | AUTO_INCREMENT | Triggers | Performance | Foreign Keys

Batch Insert Optimization

Bulk Insert

-- Insert thousands of rows efficiently
INSERT INTO large_table (col1, col2, col3) VALUES
    ('value1', 'value2', 'value3'),
    ('value4', 'value5', 'value6'),
    -- ... up to millions of rows
    ('valueN', 'valueN+1', 'valueN+2');

Programming Example

Python / Python

import mysql.connector

# Batch insert with executemany
data = [
    ('name1', 'email1@example.com', 25),
    ('name2', 'email2@example.com', 30),
    ('name3', 'email3@example.com', 35),
    # ... more rows
]

conn = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='myapp'
)

cursor = conn.cursor()

# Efficient batch insert
sql = "INSERT INTO users (name, email, age) VALUES (%s, %s, %s)"
cursor.executemany(sql, data)

conn.commit()
cursor.close()
conn.close()

PHP / PHP

<?php
$users = [
    ['name' => 'User 1', 'email' => 'user1@example.com', 'age' => 25],
    ['name' => 'User 2', 'email' => 'user2@example.com', 'age' => 30],
    // ... more users
];

$placeholders = rtrim(str_repeat('(?, ?, ?), ', count($users)), ', ');
$sql = "INSERT INTO users (name, email, age) VALUES " . $placeholders;

$stmt = $conn->prepare($sql);
$flat = [];
foreach ($users as $user) {
    $flat = array_merge($flat, array_values($user));
}
$stmt->execute($flat);
?>

Inserting Specific Values

Auto Increment

-- Insert with explicit auto-increment value
INSERT INTO users (id, name, email) VALUES (100, 'User 100', 'user100@example.com');

-- Insert without specifying auto-increment
INSERT INTO users (name, email) VALUES ('User', 'user@example.com');

-- Get last insert ID
SELECT LAST_INSERT_ID();

-- In application
-- Python: cursor.lastrowid
-- PHP: $conn->insert_id

Timestamps

-- Insert with current timestamp
INSERT INTO logs (message, created_at) VALUES ('Event occurred', NOW());

-- Insert with specific timestamp
INSERT INTO events (name, event_date) VALUES ('Conference', '2024-06-15 10:00:00');

-- Insert with NULL for timestamp
INSERT INTO users (name, last_login) VALUES ('User', NULL);

NULL Values

-- Insert NULL explicitly
INSERT INTO users (name, phone, address) VALUES ('User', NULL, NULL);

-- Insert with DEFAULT for nullable columns
INSERT INTO users (name, status) VALUES ('User', DEFAULT);

-- Check NULL after insert
SELECT * FROM users WHERE phone IS NULL;

RETURNING Clause

MySQL 8.0+ / MySQL 8.0+

-- Get inserted/updated values
INSERT INTO users (name, email) VALUES ('New User', 'new@example.com')
RETURNING id, name, created_at;

-- With ON DUPLICATE KEY
INSERT INTO users (id, name, email) VALUES (1, 'Updated', 'updated@example.com')
ON DUPLICATE KEY UPDATE name = VALUES(name)
RETURNING id, name, email, ROW_COUNT() AS affected_rows;

Troubleshooting

Common Errors

-- Duplicate entry
-- Error: Duplicate entry 'value' for key 'unique_key'
-- Solution: Use INSERT IGNORE or ON DUPLICATE KEY

-- Data too long
-- Error: Data too long for column 'name'
-- Solution: Check column size or truncate data

-- Incorrect integer value
-- Error: Incorrect integer value
-- Solution: Check data types match

-- Cannot add or update child row
-- Error: Foreign key constraint fails
-- Solution: Insert parent record first

-- Table doesn't exist
-- Error: Table 'table_name' doesn't exist
-- Solution: Create table first or check name

Debug Insert

-- Check table structure
DESCRIBE users;

-- Check data types
SHOW CREATE TABLE users;

-- Test with simple values
INSERT INTO users (name) VALUES ('Test');

-- Check auto-increment
SHOW CREATE TABLE users;

Summary

Inserting data in MySQL includes:

• Basic INSERT: Single and multiple rows
• INSERT with SELECT: Copy from other tables
• ON DUPLICATE KEY UPDATE: Insert or update
• INSERT IGNORE: Skip duplicates
• REPLACE: Replace existing rows
• Batch Operations: Efficient bulk inserts
• RETURNING: Get inserted values

---

Previous: Drop Table

Next: SELECT Data