DML Advanced: Insert, Update, Delete & Upsert

Sau khi đã có schema hoàn hảo, giờ là lúc thao tác dữ liệu. Bài này sẽ dạy bạn cách thay đổi dữ liệu một cách hiệu quả và an toàn, từ Upsert đến Bulk Operations.

1️⃣ Upsert Strategy: "Insert nếu chưa có, Update nếu đã có"

Upsert (Update + Insert) là một pattern cực kỳ phổ biến trong thực tế.

Ví dụ thực tế:

Hệ thống đồng bộ user từ bên thứ ba (Google, Facebook).
Nhập dữ liệu từ file CSV hàng ngày.
Cache invalidation: Cập nhật cache nếu có, tạo mới nếu chưa có.

PostgreSQL: `INSERT ... ON CONFLICT DO UPDATE`

PostgreSQL sử dụng ON CONFLICT để xử lý trường hợp vi phạm UNIQUE constraint.

sql

-- Ví dụ: Đồng bộ thông tin sản phẩm từ hệ thống ERP
-- product_code là UNIQUE
INSERT INTO products (product_code, name, price, updated_at)
VALUES ('SKU-001', 'iPhone 15 Pro', 29990000, NOW())
ON CONFLICT (product_code) 
DO UPDATE SET 
    name = EXCLUDED.name,
    price = EXCLUDED.price,
    updated_at = EXCLUDED.updated_at;

Giải thích:

ON CONFLICT (product_code): Khi INSERT vi phạm UNIQUE trên cột product_code.
DO UPDATE SET: Thay vì báo lỗi, hãy UPDATE các cột được chỉ định.
EXCLUDED.name: Đây là giá trị bạn đang cố INSERT. PostgreSQL dùng bảng ảo EXCLUDED để lưu trữ các giá trị này.

sql

-- Tùy chọn: Không làm gì nếu đã tồn tại (chỉ insert mới)
INSERT INTO products (product_code, name, price)
VALUES ('SKU-001', 'iPhone 15 Pro', 29990000)
ON CONFLICT (product_code) DO NOTHING;

MySQL: `INSERT ... ON DUPLICATE KEY UPDATE`

MySQL có cú pháp tương tự nhưng hơi khác.

sql

-- MySQL syntax
INSERT INTO products (product_code, name, price, updated_at)
VALUES ('SKU-001', 'iPhone 15 Pro', 29990000, NOW())
ON DUPLICATE KEY UPDATE 
    name = VALUES(name),
    price = VALUES(price),
    updated_at = VALUES(updated_at);

Lưu ý:

ON DUPLICATE KEY UPDATE kích hoạt khi vi phạm PRIMARY KEY hoặc bất kỳ UNIQUE index nào.
VALUES(column_name): Trong MySQL, dùng VALUES() để lấy giá trị đang cố INSERT (tương đương EXCLUDED của PostgreSQL).

Cảnh báo cho MySQL 8.0.20+

Từ MySQL 8.0.20, VALUES(col) trong ON DUPLICATE KEY UPDATE đã bị deprecated. Nên dùng alias:

sql

INSERT INTO products (product_code, name, price) 
VALUES ('SKU-001', 'iPhone 15 Pro', 29990000) AS new_values
ON DUPLICATE KEY UPDATE 
    name = new_values.name,
    price = new_values.price;

So sánh cú pháp Upsert

Database	Cú pháp	Trigger
PostgreSQL	`ON CONFLICT (column) DO UPDATE`	Khi vi phạm constraint được chỉ định
MySQL	`ON DUPLICATE KEY UPDATE`	Khi vi phạm PK hoặc bất kỳ UNIQUE nào
SQLite	`INSERT OR REPLACE` / `ON CONFLICT`	Tương tự PostgreSQL

2️⃣ Bulk Operations: Insert 10,000 dòng hiệu quả

Bạn cần import dữ liệu CSV với 10,000 dòng. Cách nào đúng?

❌ SAI: Loop Insert (N queries)

python

# Python pseudo-code - ĐỪNG LÀM THẾ NÀY!
for product in products_list:  # 10,000 items
    cursor.execute("""
        INSERT INTO products (name, price) VALUES (%s, %s)
    """, (product['name'], product['price']))
    connection.commit()  # 10,000 lần commit = cực chậm!

Vấn đề:

Network Round-trip: 10,000 lần gửi request đến database.
Transaction Overhead: 10,000 lần commit, mỗi lần phải flush WAL log ra đĩa.
Thời gian: Có thể mất vài phút với 10,000 dòng.

✅ ĐÚNG: Batch Insert (1 query hoặc vài query)

Cách 1: Multi-row INSERT (Đề xuất)

sql

-- Một câu lệnh INSERT nhiều dòng
INSERT INTO products (name, price) VALUES
    ('Product 1', 100000),
    ('Product 2', 200000),
    ('Product 3', 150000),
    -- ... thêm 9,997 dòng nữa
    ('Product 10000', 500000);

python

# Python với psycopg2 - executemany hoặc execute_values
from psycopg2.extras import execute_values

data = [
    ('Product 1', 100000),
    ('Product 2', 200000),
    # ... 10,000 tuples
]

execute_values(cursor, """
    INSERT INTO products (name, price) VALUES %s
""", data)

connection.commit()  # CHỈ 1 LẦN COMMIT

Cách 2: COPY Command (PostgreSQL - Nhanh nhất)

COPY là lệnh tối ưu nhất của PostgreSQL để import dữ liệu hàng loạt. Nó bypass nhiều overhead của INSERT thông thường.

sql

-- Import từ file CSV
COPY products (name, price) 
FROM '/path/to/products.csv' 
WITH (FORMAT csv, HEADER true);

python

# Python - copy_from với StringIO
import io

# Tạo file-like object từ data
buffer = io.StringIO()
for product in products_list:
    buffer.write(f"{product['name']}\t{product['price']}\n")

buffer.seek(0)
cursor.copy_from(buffer, 'products', columns=('name', 'price'))
connection.commit()

Benchmark so sánh (10,000 dòng)

Phương pháp	Thời gian ước tính	Số queries
Loop INSERT + Commit from each	30-60 giây	10,000
Loop INSERT + 1 Commit cuối	5-10 giây	10,000
Multi-row INSERT (batch 1000)	0.5-2 giây	10
COPY (PostgreSQL)	0.1-0.5 giây	1

💡 HPN Pro Tip: Batch Size tối ưu

Đừng cố INSERT 10,000 dòng trong 1 câu lệnh duy nhất. Query sẽ quá dài và có thể bị giới hạn bởi max_allowed_packet (MySQL) hoặc memory.
Sweet spot: Batch 500-2000 dòng mỗi query.
Đừng quên wrap trong Transaction: Nếu batch thứ 5 fail, bạn muốn rollback tất cả, không chỉ batch đó.

3️⃣ RETURNING Clause: Lấy ID ngay sau khi INSERT

Một pattern cực kỳ phổ biến: Bạn INSERT một dòng mới và muốn lấy id của nó ngay lập tức để sử dụng tiếp (ví dụ: tạo order, rồi cần order_id để tạo order_items).

❌ SAI: Query 2 lần (Anti-pattern)

sql

-- Bước 1: Insert
INSERT INTO orders (user_id, total_amount) VALUES (1, 500000);

-- Bước 2: Lấy ID vừa tạo (KHÔNG AN TOÀN!)
SELECT id FROM orders ORDER BY id DESC LIMIT 1;
-- Hoặc
SELECT LAST_INSERT_ID(); -- MySQL
SELECT currval('orders_id_seq'); -- PostgreSQL

Vấn đề:

Race Condition: Nếu có 2 request INSERT cùng lúc, bạn có thể lấy nhầm ID của request khác.
Không atomic: Giữa lúc INSERT và SELECT, có thể có thay đổi khác.

✅ ĐÚNG: Dùng `RETURNING` (PostgreSQL)

PostgreSQL cho phép bạn lấy giá trị của dòng vừa INSERT/UPDATE/DELETE ngay trong cùng câu lệnh.

sql

-- INSERT và lấy ID ngay lập tức
INSERT INTO orders (user_id, total_amount, status)
VALUES (1, 500000, 'pending')
RETURNING id;

-- Kết quả:
--  id 
-- ----
--  42

sql

-- Có thể RETURNING nhiều cột
INSERT INTO orders (user_id, total_amount, status)
VALUES (1, 500000, 'pending')
RETURNING id, created_at, order_code;

-- Kết quả:
--  id |         created_at         | order_code
-- ----+----------------------------+------------
--  42 | 2024-01-15 10:30:00.000+07 | ORD-0042

sql

-- RETURNING hoạt động với UPDATE/DELETE luôn!
UPDATE orders SET status = 'completed' WHERE id = 42
RETURNING id, status, updated_at;

DELETE FROM orders WHERE id = 42
RETURNING *;  -- Trả về toàn bộ dòng bị xóa

MySQL: Giải pháp thay thế

MySQL không hỗ trợ RETURNING. Phải dùng LAST_INSERT_ID().

sql

-- MySQL
INSERT INTO orders (user_id, total_amount) VALUES (1, 500000);
SELECT LAST_INSERT_ID() AS new_order_id;

python

# Python với MySQL Connector
cursor.execute("""
    INSERT INTO orders (user_id, total_amount) VALUES (%s, %s)
""", (1, 500000))
cursor.execute("SELECT LAST_INSERT_ID()")
new_id = cursor.fetchone()[0]

⚠️ LAST_INSERT_ID() và Connection Scope

LAST_INSERT_ID() trả về ID của lần INSERT cuối cùng TRÊN CONNECTION HIỆN TẠI. Nó an toàn với đa luồng vì mỗi connection có giá trị riêng. Nhưng hãy chắc chắn bạn không dùng connection pooling với auto-commit giữa 2 câu lệnh.

4️⃣ Production Pattern: Upsert + Returning

Kết hợp tất cả lại thành một pattern mạnh mẽ cho production:

sql

-- PostgreSQL: Đồng bộ user từ OAuth Provider
-- Nếu user mới -> INSERT
-- Nếu user cũ -> UPDATE thông tin mới nhất
-- Trả về ID và trạng thái để biết đã INSERT hay UPDATE

INSERT INTO users (oauth_provider, oauth_id, email, full_name, avatar_url, updated_at)
VALUES ('google', '123456789', 'hpn@example.com', 'HPN Dev', 'https://...', NOW())
ON CONFLICT (oauth_provider, oauth_id) 
DO UPDATE SET 
    email = EXCLUDED.email,
    full_name = EXCLUDED.full_name,
    avatar_url = EXCLUDED.avatar_url,
    updated_at = NOW()
RETURNING 
    id, 
    (xmax = 0) AS is_new_user;  -- Trick: xmax = 0 nghĩa là INSERT, khác 0 là UPDATE

-- Kết quả:
--  id | is_new_user
-- ----+-------------
--  42 | false        <-- User đã tồn tại, được UPDATE
)

Tổng kết

Technique	Khi nào dùng	Database
`ON CONFLICT DO UPDATE`	Upsert pattern	PostgreSQL
`ON DUPLICATE KEY UPDATE`	Upsert pattern	MySQL
Multi-row INSERT	Bulk import 100-2000 dòng/batch	Tất cả
`COPY FROM`	Bulk import >10,000 dòng	PostgreSQL
`LOAD DATA INFILE`	Bulk import >10,000 dòng	MySQL
`RETURNING`	Lấy ID/data sau INSERT	PostgreSQL, SQLite 3.35+
`LAST_INSERT_ID()`	Lấy ID sau INSERT	MySQL

💡 HPN Pro Tip: ORM và Raw SQL

Hầu hết ORM (SQLAlchemy, TypeORM, Prisma) đều hỗ trợ các pattern trên. Nhưng với Bulk Operations (>1000 dòng), hãy luôn dùng raw SQL hoặc driver-specific methods (executemany, copy_from). ORM thường generate query không tối ưu cho bulk.

DML Advanced: Insert, Update, Delete & Upsert ​

1️⃣ Upsert Strategy: "Insert nếu chưa có, Update nếu đã có" ​

PostgreSQL: INSERT ... ON CONFLICT DO UPDATE ​

MySQL: INSERT ... ON DUPLICATE KEY UPDATE ​

So sánh cú pháp Upsert ​

2️⃣ Bulk Operations: Insert 10,000 dòng hiệu quả ​

❌ SAI: Loop Insert (N queries) ​

✅ ĐÚNG: Batch Insert (1 query hoặc vài query) ​

Cách 1: Multi-row INSERT (Đề xuất) ​

Cách 2: COPY Command (PostgreSQL - Nhanh nhất) ​

Benchmark so sánh (10,000 dòng) ​

3️⃣ RETURNING Clause: Lấy ID ngay sau khi INSERT ​

❌ SAI: Query 2 lần (Anti-pattern) ​

✅ ĐÚNG: Dùng RETURNING (PostgreSQL) ​

MySQL: Giải pháp thay thế ​

4️⃣ Production Pattern: Upsert + Returning ​

Tổng kết ​

DML Advanced: Insert, Update, Delete & Upsert

1️⃣ Upsert Strategy: "Insert nếu chưa có, Update nếu đã có"

PostgreSQL: `INSERT ... ON CONFLICT DO UPDATE`

MySQL: `INSERT ... ON DUPLICATE KEY UPDATE`

So sánh cú pháp Upsert

2️⃣ Bulk Operations: Insert 10,000 dòng hiệu quả

❌ SAI: Loop Insert (N queries)

✅ ĐÚNG: Batch Insert (1 query hoặc vài query)

Cách 1: Multi-row INSERT (Đề xuất)

Cách 2: COPY Command (PostgreSQL - Nhanh nhất)

Benchmark so sánh (10,000 dòng)

3️⃣ RETURNING Clause: Lấy ID ngay sau khi INSERT

❌ SAI: Query 2 lần (Anti-pattern)

✅ ĐÚNG: Dùng `RETURNING` (PostgreSQL)

MySQL: Giải pháp thay thế

4️⃣ Production Pattern: Upsert + Returning

Tổng kết