Cara Insert, Update, dan Delete Data di MySQL: DML Operations Lengkap

Operasi DML (Data Manipulation Language) adalah inti dari daily database operations. Setelah mengoptimasi DML operations untuk high-traffic applications, saya akan berbagi teknik-teknik yang efisien dan safe.

INSERT Operations

1. Single Row Insert

-- Insert satu row
INSERT INTO users (username, email, created_at)
VALUES ('john_doe', '[email protected]', NOW());
-- Insert dengan auto-increment
INSERT INTO products (name, price, category)
VALUES ('Laptop Dell', 999.99, 'electronics');
-- Dapatkan ID yang baru di-insert
SELECT LAST_INSERT_ID();

2. Multiple Row Insert

-- Insert multiple rows (lebih efisien!)
INSERT INTO users (username, email)
VALUES 
    ('alice', '[email protected]'),
    ('bob', '[email protected]'),
    ('charlie', '[email protected]');
-- Biasanya bisa handle hingga 1000 rows per statement

3. INSERT dengan SELECT

-- Copy data dari table lain
INSERT INTO users_archive (username, email, created_at)
SELECT username, email, created_at
FROM users
WHERE status = 'inactive';
-- Insert dengan transformasi
INSERT INTO order_summary (order_month, total_sales)
SELECT 
DATE_FORMAT(order_date, '%Y-%m') as order_month,
SUM(total_amount) as total_sales
FROM orders
GROUP BY order_month;

4. INSERT IGNORE

-- Insert tapi ignore duplicate key errors
INSERT IGNORE INTO users (username, email)
VALUES ('john_doe', '[email protected]');
-- Row dengan duplicate key akan di-skip tanpa error

5. INSERT ON DUPLICATE KEY UPDATE

-- Update jika key sudah exist, insert jika belum
INSERT INTO product_inventory (product_id, quantity)
VALUES (101, 50)
ON DUPLICATE KEY UPDATE
    quantity = quantity + VALUES(quantity),
    last_updated = NOW();
-- Berguna untuk upsert operations

UPDATE Operations

1. Basic Update

-- Update single column
UPDATE users
SET status = 'active'
WHERE id = 1;
-- Update multiple columns
UPDATE users
SET 
status = 'verified',
verified_at = NOW(),
updated_at = NOW()
WHERE id = 1;

2. Update dengan Calculations

-- Update dengan arithmetic
UPDATE products
SET price = price * 1.1  -- Increase 10%
WHERE category = 'electronics';
-- Update dengan nilai dari kolom lain
UPDATE order_items
SET total_price = quantity * unit_price
WHERE total_price IS NULL;

3. Update dengan JOIN

-- Update dengan informasi dari table lain
UPDATE users u
INNER JOIN orders o ON u.id = o.user_id
SET u.total_orders = u.total_orders + 1
WHERE o.status = 'completed';
-- Update dengan subquery
UPDATE products
SET stock = stock - (
SELECT SUM(quantity)
FROM order_items
WHERE product_id = products.id
AND order_id IN (SELECT id FROM orders WHERE status = 'pending')
)
WHERE id IN (SELECT product_id FROM order_items);

4. Safe Update Practices

-- SELALU gunakan WHERE clause
-- DANGEROUS:
-- UPDATE users SET status = 'inactive';  -- Updates ALL users!
-- SAFE:
UPDATE users SET status = 'inactive' WHERE id = 123;
-- Gunakan LIMIT untuk batch updates
UPDATE large_table
SET processed = 1
WHERE processed = 0
LIMIT 1000;

DELETE Operations

1. Basic Delete

-- Delete single row
DELETE FROM users WHERE id = 123;
-- Delete dengan multiple conditions
DELETE FROM orders 
WHERE status = 'cancelled' 
AND created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

2. DELETE dengan JOIN

-- Delete dengan referensi ke table lain
DELETE o FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.status = 'banned';

3. Safe Delete Practices

-- Verify sebelum delete dengan SELECT
SELECT * FROM users WHERE id = 123;
-- Jika benar, baru delete
DELETE FROM users WHERE id = 123;
-- Gunakan soft delete daripada hard delete
-- Tambahkan column:
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP NULL;
-- Soft delete:
UPDATE users SET deleted_at = NOW() WHERE id = 123;
-- Query hanya active users:
SELECT * FROM users WHERE deleted_at IS NULL;

4. Batch Delete

-- Delete dalam chunks untuk large tables
DELETE FROM logs
WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY)
LIMIT 1000;
-- Ulangi sampai tidak ada rows yang terhapus

REPLACE Statement

1. REPLACE INTO

-- Delete existing row jika key exist, kemudian insert
REPLACE INTO users (id, username, email)
VALUES (1, 'new_username', '[email protected]');
-- Berguna untuk simple upsert tapi lebih berbahaya daripada INSERT...ON DUPLICATE

Batch Operations Best Practices

1. Transactions untuk Batch Operations

-- Gunakan transactions untuk data consistency
START TRANSACTION;
INSERT INTO orders (user_id, total_amount) VALUES (1, 100.00);
SET @order_id = LAST_INSERT_ID();
INSERT INTO order_items (order_id, product_id, quantity)
VALUES 
(@order_id, 1, 2),
(@order_id, 2, 1);
UPDATE products 
SET stock = stock - 2 WHERE id = 1;
UPDATE products 
SET stock = stock - 1 WHERE id = 2;
COMMIT;
-- Atau ROLLBACK jika ada error

2. Batching Large Inserts

# Python example untuk batch insert
import mysql.connector
def batch_insert(data_list, batch_size=1000):
conn = mysql.connector.connect(host='localhost', database='mydb', user='user', password='pass')
cursor = conn.cursor()
query = "INSERT INTO users (username, email) VALUES (%s, %s)"

for i in range(0, len(data_list), batch_size):
    batch = data_list[i:i+batch_size]
    cursor.executemany(query, batch)
    conn.commit()
    print(f"Inserted batch {i//batch_size + 1}")

cursor.close()
conn.close()
Kesimpulan
DML operations adalah foundation database manipulation:
– INSERT: Tambahkan data baru
– UPDATE: Modifikasi existing data
– DELETE: Remove data (gunakan soft delete!)
– REPLACE: Upsert dengan caution
– Gunakan transactions untuk consistency
Selalu test di development sebelum production operations!