Master MySQL for Ops: DML/DQL Tricks, Permissions & Transaction Pitfalls

1. DML Operations

Data Manipulation Language (DML) is used for inserting, updating and deleting business data. Follow strict conventions to avoid production accidents.

Insert (INSERT)

One‑sentence summary: Use INSERT to persist new records such as user registrations, orders or system logs.

Key syntax:

-- Single‑row insert (explicit column list recommended)
INSERT INTO user (id, username, email, city, created_at)
VALUES (1, 'zhangsan', '[email protected]', 'Beijing', NOW());

-- Batch insert (reduces network I/O and disk writes)
INSERT INTO system_log (log_level, message, create_time)
VALUES
  ('INFO',  'User login successful', NOW()),
  ('ERROR', 'Payment gateway timeout', NOW()),
  ('WARN',  'Stock below warning level', NOW());

Ops pitfall guide:

Never omit column names – schema changes will break the statement.

Control batch size (e.g., LIMIT 1000) to avoid exhausting connections.

Validate data (email format, phone length, etc.) before insertion.

Update and Delete (UPDATE / DELETE)

One‑sentence summary: Modify business status (e.g., order refund, user disable) or clean up expired data with precise WHERE conditions.

Key syntax:

-- Update inside a transaction
START TRANSACTION;
UPDATE orders SET status = 'REFUNDED', update_at = NOW()
WHERE id = 1001;
COMMIT;

-- Delete old logs in batches to avoid long locks
DELETE FROM system_log
WHERE create_time < NOW() - INTERVAL 90 DAY
LIMIT 1000;

Ops pitfall guide:

Always include a WHERE clause; verify affected rows with a preceding SELECT ... WHERE.

For large tables, delete in batches (e.g., 1000 rows per batch) to prevent table locks.

Schedule heavy DML during off‑peak windows.

Wrap critical changes in a transaction and use ROLLBACK on error.

2. DQL Queries

Basic Queries and Filtering

One‑sentence summary: Retrieve specific columns, apply precise filters, and eliminate duplicates for list displays.

Key syntax:

-- Select specific columns (avoid SELECT *)
SELECT id, username, city, last_login_time FROM user;

-- Filter active users in Beijing
SELECT id, username FROM user
WHERE city = 'Beijing' AND status = 1;

-- Fuzzy search (username contains "li")
SELECT * FROM user WHERE username LIKE '%li%';

-- Distinct city list
SELECT DISTINCT city FROM user;

Ops pitfall guide:

Never use SELECT * on large tables – it forces a full row read and increases I/O.

Test NULL with IS NULL / IS NOT NULL, not with = NULL.

Prefix matching ( LIKE 'li%') can use an index; leading wildcard ( LIKE '%li%') disables index usage.

Aggregation and Grouping

One‑sentence summary: Generate business reports (e.g., city‑wise user counts, average salary, monthly sales) by grouping data.

Key syntax:

-- User count per city
SELECT city, COUNT(*) AS user_count FROM user
WHERE status = 1
GROUP BY city;

-- Average salary per department (filter avg > 8000)
SELECT dept_id, AVG(salary) AS avg_salary FROM employee
GROUP BY dept_id
HAVING avg_salary > 8000;

-- Monthly order total
SELECT DATE_FORMAT(create_time, '%Y-%m') AS month,
       SUM(amount) AS total_amount
FROM orders
GROUP BY month;

Ops pitfall guide:

Index the GROUP BY columns; otherwise MySQL performs a file sort on large tables.

Use HAVING for filtering aggregated results; WHERE cannot reference aggregate functions.

Sorting and Pagination

One‑sentence summary: Implement ordered business lists and paginate results to avoid loading massive datasets at once.

Key syntax:

-- Page 3, 10 rows per page
SELECT id, title, create_time FROM article
WHERE status = 1
ORDER BY create_time DESC
LIMIT 10 OFFSET 20;

-- Cursor pagination (efficient for deep pages)
SELECT id, title, create_time FROM article
WHERE id < 1000 AND status = 1
ORDER BY id DESC
LIMIT 10;

Ops pitfall guide:

For deep pagination, avoid large OFFSET; use cursor pagination based on an indexed column.

Ensure the sorting column is indexed; otherwise MySQL must perform a filesort.

Multi‑Table Joins and Subqueries

One‑sentence summary: Join multiple tables (e.g., user‑order, role‑permission) to satisfy complex business data needs.

Key syntax:

-- Inner join: users with orders after 2025‑12‑01
SELECT u.id, u.username, o.id AS order_id, o.amount
FROM user u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.create_time > '2025-12-01';

-- Left join: all users with optional orders
SELECT u.id, u.username, o.id AS order_id
FROM user u
LEFT JOIN orders o ON u.id = o.user_id;

-- Subquery: orders larger than average amount
SELECT * FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

-- Exists: users who have login logs
SELECT id, username FROM user u
WHERE EXISTS (SELECT 1 FROM login_log l WHERE l.user_id = u.id);

Ops pitfall guide:

Always provide an ON clause for joins; missing it creates a Cartesian product.

Prefer JOIN over correlated subqueries for performance.

For LEFT JOIN, keep join conditions in ON, not in WHERE, to preserve left‑side rows.

Alias tables in self‑joins to avoid column name conflicts.

3. DCL Permission Management

User Management

One‑sentence summary: Create separate accounts for development, testing and production environments, isolating login sources.

Core commands:

-- Read‑only production user (local only)
CREATE USER 'prod_read'@'localhost' IDENTIFIED BY 'Prod@Read123';

-- Development user (remote access allowed)
CREATE USER 'dev_user'@'%' IDENTIFIED BY 'Dev@User456';

-- Change password regularly
ALTER USER 'prod_read'@'localhost' IDENTIFIED BY 'NewProd@Read789';

-- Drop unused account
DROP USER 'test_user'@'%';

Ops pitfall guide:

The host part defines where the user can connect; 'user'@'localhost' limits to local, 'user'@'%' allows any IP.

Never use weak passwords such as 123456 or root.

Regularly clean up stale accounts from departed staff.

Permission Control

One‑sentence summary: Assign the minimal necessary privileges to each user, limiting access to databases and tables.

Core commands:

-- Grant read‑only access to production DB
GRANT SELECT ON prod_db.* TO 'prod_read'@'localhost';

-- Grant full DML to development DB
GRANT SELECT, INSERT, UPDATE, DELETE ON dev_db.* TO 'dev_user'@'%';

-- Revoke UPDATE from a user
REVOKE UPDATE ON dev_db.user FROM 'dev_user'@'%';

-- Verify privileges
SHOW GRANTS FOR 'prod_read'@'localhost';

Ops pitfall guide:

Never grant ALL PRIVILEGES to non‑admin accounts; it is equivalent to root.

Assign permissions based on role: read‑only, business, or admin.

MySQL 8.0+ automatically refreshes privileges after GRANT / REVOKE; no manual FLUSH PRIVILEGES needed.

Audit permissions monthly and remove unnecessary rights.

4. Functions & Constraints

Common Functions

String functions: CONCAT(), CHAR_LENGTH(), REPLACE() – use CHAR_LENGTH() for Chinese character count ( LENGTH() returns bytes).

Date functions: DATE_ADD(), DATE_FORMAT(), TIMESTAMPDIFF() – keep server and session time zones consistent; prefer NOW() over SYSDATE().

Numeric functions: ROUND(), CEIL(), FLOOR() – store money as DECIMAL(10,2) instead of FLOAT/DOUBLE to avoid precision loss.

Flow functions: CASE WHEN, COALESCE() – use CASE sparingly; complex logic is often better handled in application code.

Data Constraints

PRIMARY KEY: Unique, non‑null row identifier. Use auto‑increment integer; UUIDs degrade index performance.

UNIQUE: Guarantees column uniqueness (e.g., email, phone). Adds write overhead – use only when business requires uniqueness.

NOT NULL: Disallows NULL values. Prefer sensible defaults (empty string, 0) to improve query speed.

FOREIGN KEY: Enforces referential integrity between parent and child tables. In high‑concurrency systems, foreign keys can slow writes; sometimes replace with application‑level checks and proper indexes.

Practical Examples

Concatenate user info handling NULL:

SELECT CONCAT(username, '(', COALESCE(nickname, '未设置昵称'), ')') AS user_info FROM user;

Calculate days since registration:

SELECT username, TIMESTAMPDIFF(DAY, create_time, NOW()) AS register_days FROM user;

Round monetary amount to two decimals:

SELECT order_id, ROUND(amount, 2) AS final_amount FROM orders;

5. Transactions & Isolation Levels

ACID Properties

Atomicity: All statements succeed or all are rolled back.

Consistency: Constraints remain intact after the transaction.

Isolation: Concurrent transactions do not interfere with each other.

Durability: Once committed, data is persisted to disk.

Transaction Example

-- Transfer $100 from account 1 to account 2
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;

Isolation Levels

READ UNCOMMITTED: Allows dirty reads. Highest performance, rarely used.

READ COMMITTED: Prevents dirty reads. Good default for many applications.

REPEATABLE READ: Prevents dirty and non‑repeatable reads. MySQL default (uses MVCC to avoid phantom reads).

SERIALIZABLE: Eliminates all three problems. Lowest performance; used in financial systems requiring strict consistency.

Transaction Pitfalls

MySQL’s default autocommit=1 commits each statement; start a transaction explicitly with START TRANSACTION or BEGIN.

Only InnoDB supports transactions; avoid MyISAM for critical tables.

Use indexed columns with SELECT ... FOR UPDATE to acquire row locks; otherwise a table lock may be taken.

Higher isolation levels increase locking overhead; READ COMMITTED is sufficient for most workloads.

6. Experiment Data (DDL & Sample Data)

-- Create user table
CREATE TABLE user (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
  username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
  email VARCHAR(100) UNIQUE COMMENT '邮箱',
  city VARCHAR(30) COMMENT '城市',
  status TINYINT DEFAULT 1 COMMENT '状态：1-活跃，0-禁用',
  create_time DATETIME DEFAULT NOW() COMMENT '创建时间',
  last_login_time DATETIME COMMENT '最后登录时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户表';

-- Create orders table
CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '订单ID',
  user_id INT COMMENT '用户ID',
  amount DECIMAL(10,2) NOT NULL COMMENT '订单金额',
  status VARCHAR(20) DEFAULT 'UNPAID' COMMENT '状态：UNPAID-未支付，PAID-已支付，REFUNDED-已退款',
  create_time DATETIME DEFAULT NOW() COMMENT '创建时间',
  update_time DATETIME COMMENT '更新时间',
  FOREIGN KEY (user_id) REFERENCES user(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '订单表';

-- Create system_log table
CREATE TABLE system_log (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '日志ID',
  log_level VARCHAR(10) NOT NULL COMMENT '日志级别：INFO/WARN/ERROR',
  message VARCHAR(500) NOT NULL COMMENT '日志内容',
  create_time DATETIME DEFAULT NOW() COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '系统日志表';

-- Insert sample users
INSERT INTO user (username, email, city, last_login_time) VALUES
('zhangsan', '[email protected]', 'Beijing', '2025-12-01 10:00:00'),
('lisi', '[email protected]', 'Shanghai', '2025-12-02 11:00:00'),
('wangwu', '[email protected]', 'Guangzhou', NULL);

-- Insert sample orders
INSERT INTO orders (user_id, amount, status) VALUES
(1, 299.99, 'PAID'),
(2, 159.00, 'UNPAID'),
(1, 99.00, 'REFUNDED');

-- Insert sample logs
INSERT INTO system_log (log_level, message) VALUES
('INFO', '系统启动成功'),
('ERROR', '数据库连接失败'),
('WARN', '内存使用率超过80%');

7. Operations Takeaways

Query optimization three‑step rule: Avoid SELECT *, add proper indexes, and use EXPLAIN to analyze execution plans.

Data manipulation three‑rule: Always include a WHERE clause, limit affected rows with LIMIT, and run heavy DML during off‑peak hours.

Permission management three‑points: Grant minimal privileges, audit regularly, and clean up stale accounts.

Performance tuning core: Optimize indexes, keep transactions short, and consider sharding for very large tables.

Fault diagnosis three‑steps: Review the slow‑query log, examine the query plan, and check index usage.

Practicing the provided DDL/DML scripts, analyzing slow queries, and documenting recurring pitfalls will steadily improve MySQL operational competence.