MySQL Data Insertion

Inserting data is one of the fundamental CRUD (Create, Read, Update, Delete) operations. This guide covers all aspects of inserting data into MySQL tables with proper syntax, best practices, and Next.js integration.

1. Basic INSERT Statement Syntax

Fundamental Theory

The INSERT INTO statement is used to add new records to a table. There are two primary syntax forms:

Method 1: Specify Column Names

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Advantages:

Explicit column specification
Can omit columns with default values
Better readability and maintainability

Method 2: Implicit Column Order

INSERT INTO table_name 
VALUES (value1, value2, value3, ...);

Considerations:

Must provide values for all columns
Must follow exact table column order
Less flexible but more concise

Practical Examples

Example 1: Users Table Insertion

-- Create the users table first
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Method 1: Explicit column names (RECOMMENDED)
INSERT INTO users (
    username, 
    email, 
    password_hash, 
    first_name, 
    last_name, 
    age, 
    is_active
) VALUES (
    'john_doe', 
    'john@example.com', 
    '$2b$10$hashedpassword', 
    'John', 
    'Doe', 
    28, 
    TRUE
);

-- Method 2: Implicit columns (all values in order)
INSERT INTO users VALUES (
    NULL,  -- id (auto-generated)
    'jane_smith', 
    'jane@example.com', 
    '$2b$10$hashedpassword2', 
    'Jane', 
    'Smith', 
    32, 
    FALSE, 
    NOW(),  -- created_at
    NOW()   -- updated_at
);

Example 2: Products Table Insertion

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT DEFAULT 0,
    category VARCHAR(50),
    is_available BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert product with explicit columns
INSERT INTO products (
    name, 
    description, 
    price, 
    stock_quantity, 
    category
) VALUES (
    'Wireless Mouse', 
    'Ergonomic wireless mouse with 2.4GHz connectivity', 
    29.99, 
    50, 
    'Electronics'
);

-- Insert product using some defaults
INSERT INTO products (
    name, 
    price, 
    category
) VALUES (
    'USB Cable', 
    9.99, 
    'Accessories'
);
-- stock_quantity will be 0 (default), is_available will be TRUE (default)

Important Notes:

String values must be enclosed in single quotes
Numeric values should not have quotes
NULL should be used for missing values (without quotes)
Auto-increment columns should use NULL or be omitted
Default values are automatically used if column is omitted

2. Inserting Multiple Rows

Bulk Insertion Theory

MySQL allows inserting multiple rows in a single INSERT statement, which is more efficient than multiple individual inserts. This reduces:

Network round-trips between application and database
Query parsing overhead
Transaction management complexity

Multiple Rows Syntax

INSERT INTO table_name (column1, column2, column3, ...)
VALUES 
    (value1, value2, value3, ...),
    (value1, value2, value3, ...),
    (value1, value2, value3, ...),
    ...;

Practical Examples

Example 1: Multiple Users

-- Insert multiple users in one statement
INSERT INTO users (
    username, 
    email, 
    password_hash, 
    first_name, 
    last_name, 
    age
) VALUES 
    ('alice_wonder', 'alice@example.com', '$2b$10$hash1', 'Alice', 'Wonder', 25),
    ('bob_builder', 'bob@example.com', '$2b$10$hash2', 'Bob', 'Builder', 30),
    ('charlie_brown', 'charlie@example.com', '$2b$10$hash3', 'Charlie', 'Brown', 22),
    ('diana_prince', 'diana@example.com', '$2b$10$hash4', 'Diana', 'Prince', 28);

Example 2: Multiple Products

-- Bulk insert products
INSERT INTO products (
    name, 
    description, 
    price, 
    stock_quantity, 
    category
) VALUES 
    (
        'Mechanical Keyboard', 
        'RGB mechanical keyboard with blue switches', 
        79.99, 
        25, 
        'Electronics'
    ),
    (
        'Gaming Mouse', 
        'High-precision gaming mouse with adjustable DPI', 
        49.99, 
        30, 
        'Electronics'
    ),
    (
        'Laptop Stand', 
        'Adjustable aluminum laptop stand for ergonomic computing', 
        35.50, 
        100, 
        'Accessories'
    ),
    (
        'Webcam', 
        '1080p HD webcam with built-in microphone', 
        59.99, 
        15, 
        'Electronics'
    );

Example 3: Insert with SELECT (Copy Data)

-- Create an archive table
CREATE TABLE users_archive LIKE users;

-- Copy active users to archive
INSERT INTO users_archive (
    username, 
    email, 
    password_hash, 
    first_name, 
    last_name, 
    age, 
    is_active
)
SELECT 
    username, 
    email, 
    password_hash, 
    first_name, 
    last_name, 
    age, 
    is_active
FROM users 
WHERE is_active = TRUE;

-- Create a premium products table and copy expensive products
INSERT INTO premium_products 
SELECT * FROM products 
WHERE price > 100.00;

Performance Tips for Bulk Inserts:

Use INSERT ... VALUES (...), (...), ... for multiple rows
Consider using LOAD DATA INFILE for very large datasets
Wrap multiple inserts in a transaction for data consistency
Disable indexes temporarily during large bulk inserts
Use batch processing for extremely large datasets

3. Inserting Data with Relationships

Relational Database Theory

In relational databases, tables are connected through foreign key relationships. When inserting data, you must consider:

Parent-Child Relationships: Insert parent records first
Foreign Key Constraints: Values must exist in referenced tables
Referential Integrity: Maintaining consistent relationships

Complete E-commerce Example

-- Step 1: Create related tables
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    category_id INT NOT NULL,
    stock_quantity INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) NOT NULL UNIQUE,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10,2),
    status ENUM('pending', 'confirmed', 'shipped', 'delivered') DEFAULT 'pending',
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id),
    UNIQUE KEY unique_order_product (order_id, product_id)
);

-- Step 2: Insert data in correct order (parents first)
-- Insert categories (parent table)
INSERT INTO categories (name, description) VALUES 
    ('Electronics', 'Electronic devices and accessories'),
    ('Books', 'Physical and digital books'),
    ('Clothing', 'Apparel and fashion items');

-- Insert products (child of categories)
INSERT INTO products (name, description, price, category_id, stock_quantity) VALUES 
    (
        'Smartphone', 
        'Latest smartphone with advanced features', 
        699.99, 
        (SELECT id FROM categories WHERE name = 'Electronics'), 
        50
    ),
    (
        'Programming Book', 
        'Complete guide to web development', 
        39.99, 
        (SELECT id FROM categories WHERE name = 'Books'), 
        100
    ),
    (
        'T-Shirt', 
        'Cotton t-shirt with custom design', 
        19.99, 
        (SELECT id FROM categories WHERE name = 'Clothing'), 
        200
    );

-- Insert customers
INSERT INTO customers (email, first_name, last_name) VALUES 
    ('customer1@example.com', 'John', 'Doe'),
    ('customer2@example.com', 'Jane', 'Smith');

-- Insert orders (child of customers)
INSERT INTO orders (customer_id, total_amount, status) VALUES 
    (
        (SELECT id FROM customers WHERE email = 'customer1@example.com'), 
        759.98, 
        'confirmed'
    ),
    (
        (SELECT id FROM customers WHERE email = 'customer2@example.com'), 
        39.99, 
        'pending'
    );

-- Insert order_items (child of orders and products)
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES 
    (
        (SELECT id FROM orders WHERE customer_id = 
            (SELECT id FROM customers WHERE email = 'customer1@example.com') 
         LIMIT 1),
        (SELECT id FROM products WHERE name = 'Smartphone'),
        1,
        699.99
    ),
    (
        (SELECT id FROM orders WHERE customer_id = 
            (SELECT id FROM customers WHERE email = 'customer1@example.com') 
         LIMIT 1),
        (SELECT id FROM products WHERE name = 'T-Shirt'),
        3,
        19.99
    ),
    (
        (SELECT id FROM orders WHERE customer_id = 
            (SELECT id FROM customers WHERE email = 'customer2@example.com') 
         LIMIT 1),
        (SELECT id FROM products WHERE name = 'Programming Book'),
        1,
        39.99
    );

Handling Auto-Increment IDs

-- Using LAST_INSERT_ID() to get the auto-generated ID
INSERT INTO customers (email, first_name, last_name) 
VALUES ('newcustomer@example.com', 'New', 'Customer');

SET @new_customer_id = LAST_INSERT_ID();

INSERT INTO orders (customer_id, total_amount) 
VALUES (@new_customer_id, 99.99);

SET @new_order_id = LAST_INSERT_ID();

INSERT INTO order_items (order_id, product_id, quantity, unit_price) 
VALUES 
    (@new_order_id, 1, 2, 29.99),
    (@new_order_id, 2, 1, 39.99);

Relationship Insertion Strategies:

Top-Down Approach: Insert parent records before children
Use SELECT Subqueries: To get foreign key values dynamically
LAST_INSERT_ID(): To get auto-generated IDs for relationships
Transactions: Use for atomic operations across multiple tables
Error Handling: Check for foreign key constraint violations

4. Data Insertion with Next.js

Application-Level Data Insertion

In Next.js applications, we handle data insertion through API routes to maintain separation of concerns and security. Key considerations:

Parameterized Queries: Prevent SQL injection attacks
Input Validation: Validate data before insertion
Error Handling: Graceful error management
API Design: RESTful endpoints for CRUD operations

Database Configuration

// lib/db.js
import mysql from 'mysql2/promise';

const dbConfig = {
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  database: process.env.DB_NAME || 'my_nextjs_app',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
};

const pool = mysql.createPool(dbConfig);

export async function query(sql, params) {
  try {
    const [rows] = await pool.execute(sql, params);
    return rows;
  } catch (error) {
    console.error('Database query error:', error);
    throw error;
  }
}

export async function executeTransaction(operations) {
  const connection = await pool.getConnection();
  await connection.beginTransaction();
  
  try {
    for (const operation of operations) {
      await connection.execute(operation.sql, operation.params);
    }
    await connection.commit();
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }
}

export { pool };

API Route for Single Insert

// pages/api/users/create.js
import { query } from '../../../lib/db';

export default async function handler(req, res) {
  if (req.method !== 'POST') {
    return res.status(405).json({ message: 'Method not allowed' });
  }

  try {
    const { username, email, password_hash, first_name, last_name, age } = req.body;

    // Input validation
    if (!username || !email || !password_hash) {
      return res.status(400).json({ 
        message: 'Username, email, and password are required' 
      });
    }

    if (username.length < 3 || username.length > 50) {
      return res.status(400).json({ 
        message: 'Username must be between 3 and 50 characters' 
      });
    }

    // Insert user with parameterized query (prevents SQL injection)
    const result = await query(
      `INSERT INTO users (
        username, email, password_hash, first_name, last_name, age, is_active
      ) VALUES (?, ?, ?, ?, ?, ?, TRUE)`,
      [username, email, password_hash, first_name, last_name, age]
    );

    // Return the inserted user data
    res.status(201).json({
      message: 'User created successfully',
      userId: result.insertId,
      user: {
        id: result.insertId,
        username,
        email,
        first_name,
        last_name,
        age
      }
    });

  } catch (error) {
    console.error('User creation error:', error);
    
    // Handle specific MySQL errors
    if (error.code === 'ER_DUP_ENTRY') {
      return res.status(409).json({ 
        message: 'Username or email already exists' 
      });
    }
    
    res.status(500).json({ 
      message: 'Internal server error', 
      error: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
}

API Route for Bulk Insert

// pages/api/products/bulk-create.js
import { query } from '../../../lib/db';

export default async function handler(req, res) {
  if (req.method !== 'POST') {
    return res.status(405).json({ message: 'Method not allowed' });
  }

  try {
    const { products } = req.body;

    if (!products || !Array.isArray(products) || products.length === 0) {
      return res.status(400).json({ 
        message: 'Products array is required and must not be empty' 
      });
    }

    // Validate each product
    for (const product of products) {
      if (!product.name || !product.price) {
        return res.status(400).json({ 
          message: 'Each product must have name and price' 
        });
      }
    }

    // Build bulk insert query
    const placeholders = products.map(() => '(?, ?, ?, ?, ?)').join(', ');
    const values = products.flatMap(p => [
      p.name,
      p.description || null,
      p.price,
      p.category_id,
      p.stock_quantity || 0
    ]);

    const result = await query(
      `INSERT INTO products (name, description, price, category_id, stock_quantity) 
       VALUES ${placeholders}`,
      values
    );

    res.status(201).json({
      message: `${products.length} products created successfully`,
      insertedCount: result.affectedRows,
      productIds: Array.from({ length: products.length }, (_, i) => result.insertId + i)
    });

  } catch (error) {
    console.error('Bulk product creation error:', error);
    res.status(500).json({ 
      message: 'Error creating products', 
      error: process.env.NODE_ENV === 'development' ? error.message : undefined
    });
  }
}

Frontend Component for Data Insertion

// components/UserRegistrationForm.js
import { useState } from 'react';

export default function UserRegistrationForm() {
  const [formData, setFormData] = useState({
    username: '',
    email: '',
    password: '',
    confirmPassword: '',
    firstName: '',
    lastName: '',
    age: ''
  });
  const [message, setMessage] = useState('');
  const [loading, setLoading] = useState(false);

  const handleChange = (e) => {
    setFormData({
      ...formData,
      [e.target.name]: e.target.value
    });
  };

  const handleSubmit = async (e) => {
    e.preventDefault();
    setLoading(true);
    setMessage('');

    // Client-side validation
    if (formData.password !== formData.confirmPassword) {
      setMessage('Passwords do not match');
      setLoading(false);
      return;
    }

    if (formData.age && (formData.age < 0 || formData.age > 150)) {
      setMessage('Please enter a valid age');
      setLoading(false);
      return;
    }

    try {
      const response = await fetch('/api/users/create', {
        method: 'POST',
        headers: {
          'Content-Type': 'application/json',
        },
        body: JSON.stringify({
          username: formData.username,
          email: formData.email,
          password_hash: formData.password, // In real app, hash this on server
          first_name: formData.firstName,
          last_name: formData.lastName,
          age: formData.age ? parseInt(formData.age) : null
        }),
      });

      const data = await response.json();

      if (response.ok) {
        setMessage(`✅ ${data.message} - User ID: ${data.userId}`);
        // Reset form
        setFormData({
          username: '',
          email: '',
          password: '',
          confirmPassword: '',
          firstName: '',
          lastName: '',
          age: ''
        });
      } else {
        setMessage(`❌ ${data.message}`);
      }
    } catch (error) {
      setMessage(`❌ Error: ${error.message}`);
    } finally {
      setLoading(false);
    }
  };

  return (
    <div className="card">
      <div className="card-body">
        <h5 className="card-title">Register New User</h5>
        <form onSubmit={handleSubmit}>
          <div className="row">
            <div className="col-md-6">
              <div className="mb-3">
                <label className="form-label">Username *</label>
                <input
                  type="text"
                  className="form-control"
                  name="username"
                  value={formData.username}
                  onChange={handleChange}
                  required
                />
              </div>
            </div>
            <div className="col-md-6">
              <div className="mb-3">
                <label className="form-label">Email *</label>
                <input
                  type="email"
                  className="form-control"
                  name="email"
                  value={formData.email}
                  onChange={handleChange}
                  required
                />
              </div>
            </div>
          </div>

          <div className="row">
            <div className="col-md-6">
              <div className="mb-3">
                <label className="form-label">Password *</label>
                <input
                  type="password"
                  className="form-control"
                  name="password"
                  value={formData.password}
                  onChange={handleChange}
                  required
                />
              </div>
            </div>
            <div className="col-md-6">
              <div className="mb-3">
                <label className="form-label">Confirm Password *</label>
                <input
                  type="password"
                  className="form-control"
                  name="confirmPassword"
                  value={formData.confirmPassword}
                  onChange={handleChange}
                  required
                />
              </div>
            </div>
          </div>

          <div className="row">
            <div className="col-md-6">
              <div className="mb-3">
                <label className="form-label">First Name</label>
                <input
                  type="text"
                  className="form-control"
                  name="firstName"
                  value={formData.firstName}
                  onChange={handleChange}
                />
              </div>
            </div>
            <div className="col-md-6">
              <div className="mb-3">
                <label className="form-label">Last Name</label>
                <input
                  type="text"
                  className="form-control"
                  name="lastName"
                  value={formData.lastName}
                  onChange={handleChange}
                />
              </div>
            </div>
          </div>

          <div className="mb-3">
            <label className="form-label">Age</label>
            <input
              type="number"
              className="form-control"
              name="age"
              value={formData.age}
              onChange={handleChange}
              min="0"
              max="150"
            />
          </div>

          <button 
            type="submit" 
            className="btn btn-primary"
            disabled={loading}
          >
            {loading ? 'Creating User...' : 'Create User'}
          </button>
        </form>

        {message && (
          <div className="mt-3 alert alert-info">{message}</div>
        )}
      </div>
    </div>
  );
}

Data Insertion Best Practices

Security

Always use parameterized queries to prevent SQL injection
Validate and sanitize all user inputs
Use environment variables for database credentials
Implement proper authentication and authorization
Hash passwords before storage

Performance

Use bulk inserts for multiple records
Consider transactions for related operations
Use appropriate indexes on frequently searched columns
Batch large insert operations
Monitor query performance

Data Integrity

Use database constraints (NOT NULL, UNIQUE, FOREIGN KEY)
Implement application-level validation
Use proper data types for each column
Set appropriate default values
Handle errors gracefully

Error Handling

Handle duplicate key errors gracefully
Check for foreign key constraint violations
Validate data types and formats
Provide meaningful error messages to users
Log errors for debugging

Common Insert Errors and Solutions

Error	Cause	Solution
`ER_DUP_ENTRY`	Duplicate value for unique column	Check for existing records or use INSERT IGNORE
`ER_NO_REFERENCED_ROW`	Foreign key constraint fails	Insert parent record first or check foreign key value
`ER_BAD_NULL_ERROR`	NULL value for NOT NULL column	Provide value for required column
`ER_DATA_TOO_LONG`	Data exceeds column length	Truncate data or modify column size
`ER_TRUNCATED_WRONG_VALUE`	Incorrect data type	Ensure data matches column type

Next Steps

After mastering data insertion, continue with:

Reading Data: SELECT statements with WHERE, JOIN, GROUP BY
Updating Data: UPDATE statements with proper conditions
Deleting Data: DELETE operations with transactions
Advanced Queries: Subqueries, stored procedures, views
Database Optimization: Indexing, query optimization, caching

← Create Table Select Query →

MySQL Tutorial

MySQL Data Insertion

1. Basic INSERT Statement Syntax

Fundamental Theory

Method 1: Specify Column Names

Method 2: Implicit Column Order

Practical Examples

Example 1: Users Table Insertion

Example 2: Products Table Insertion

Important Notes:

2. Inserting Multiple Rows

Bulk Insertion Theory

Multiple Rows Syntax

Practical Examples

Example 1: Multiple Users

Example 2: Multiple Products

Example 3: Insert with SELECT (Copy Data)

Performance Tips for Bulk Inserts:

3. Inserting Data with Relationships

Relational Database Theory

Complete E-commerce Example

Handling Auto-Increment IDs

Relationship Insertion Strategies:

4. Data Insertion with Next.js

Application-Level Data Insertion

Database Configuration

API Route for Single Insert

API Route for Bulk Insert

Frontend Component for Data Insertion

Data Insertion Best Practices

Security

Performance

Data Integrity

Error Handling

Common Insert Errors and Solutions

Next Steps

Follow Us

Our Tools

Our Company

Special Tools