> ## Content Index
> Fetch the complete content index at: https://huizhou92.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# 10 MySQL Tricks I Actually Use in Production
- URL: https://huizhou92.com/10-mysql-tricks-i-actually-use-in-production/
- Published: 2025-10-27T06:39:23.000Z
- Updated: 2025-10-27T06:39:23.000Z
- Description: 10 MySQL Tricks I Actually Use in Production. As someone who works with MySQL daily, I’ve noticed that many developers still rely on basic CRUD operations。
- Author: huizhou92
- Tags: #Migrated-1788833207488, #Import 2026-09-08 02:07

As someone who works with MySQL daily, I’ve noticed that many developers still rely on basic CRUD operations. Here are 10 practical techniques I frequently use that improve both development efficiency and query performance.

### 1\. JSON Columns for Flexible Schema

**Use case**: When you need dynamic fields (like user preferences) without bloating your schema.

```sql
-- Create user preferences table with JSON column 
CREATE TABLE user_preferences ( 
  id INT PRIMARY KEY, 
  user_id INT, 
  preferences JSON 
); 
-- Insert test data 
INSERT INTO user_preferences VALUES 
(1, 1, '{"theme": "dark", "notifications": true, "fontSize": 14}'), 
(2, 2, '{"theme": "light", "notifications": false, "fontSize": 16}'), 
(3, 3, '{"theme": "dark", "notifications": true, "fontSize": 12}'); 
-- Extract JSON value with -> operator 
SELECT preferences->'$.theme' AS theme  
FROM user_preferences  
WHERE user_id = 1; 
-- Returns: "dark"
```

### 2\. WITH ROLLUP for Aggregation Totals

**Use case**: Generate reports with subtotals and grand totals in a single query.

```sql
-- Get employee count and salary sum by department, plus totals 
SELECT 
  department, 
  COUNT(*) AS employee_count, 
  SUM(salary) AS total_salary 
FROM employees 
GROUP BY department WITH ROLLUP; 
-- Results: 
-- department | employee_count | total_salary 
-- Tech       | 3              | 50000.00 
-- Marketing  | 2              | 25000.00 
-- HR         | 2              | 21000.00 
-- NULL       | 7              | 96000.00  -- Grand total
```

### 3\. CASE WHEN for Conditional Aggregation

**Use case**: Aggregate multiple conditions in a single query without multiple passes.

```sql
-- Count active/inactive users in one query 
SELECT 
  SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_users, 
  SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) AS inactive_users 
FROM users; 
-- Results: 
-- active_users | inactive_users 
-- 3            | 2
```

---

### 4\. INSERT IGNORE to Skip Duplicates

**Use case**: Bulk imports where you want to skip existing records instead of erroring out.

```sql
-- Insert data, skipping duplicates based on unique key 
INSERT IGNORE INTO users (id, name, email) VALUES 
(1, 'John', 'john@example.com'),  -- Skipped if exists 
(3, 'Jane', 'jane@example.com');  -- Inserted if new
```

---

### 5\. ON DUPLICATE KEY UPDATE for Upserts

**Use case**: Insert or update based on unique key — no need for separate SELECT/INSERT/UPDATE logic.

```sql
-- Upsert based on id (unique key) 
INSERT INTO users (id, name, email) VALUES 
(1, 'John', 'john_new@example.com'),  -- Updates if exists 
(4, 'Bob', 'bob@example.com')         -- Inserts if new 
ON DUPLICATE KEY UPDATE 
  name = VALUES(name), 
  email = VALUES(email);
```

---

### 6\. FIND\_IN\_SET for Comma-Separated Lookups

**Use case**: Query records where a field contains a specific value in a comma-separated list.

```sql
-- Find products in category 1 
SELECT * FROM products  
WHERE FIND_IN_SET('1', category_ids); 
-- Matches: Product A (1,2,3), Product C (1,4), Product E (1,5,6)
```

---

### 7\. GROUP\_CONCAT to Merge Rows

**Use case**: Combine multiple rows into a single delimited string (e.g., employee lists, tags).

```sql
-- Concatenate employee names by department 
SELECT 
  department, 
  GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS employees 
FROM employees 
GROUP BY department; 
-- Results: 
-- Tech | Alice, Bob, Charlie
```

---

### 8\. EXISTS for Faster Subqueries

**Use case**: Replace inefficient `IN` subqueries, especially with large datasets.

```sql
-- Find orders containing items over $100 
SELECT * FROM orders o 
WHERE EXISTS ( 
  SELECT 1 FROM order_items oi 
  WHERE oi.order_id = o.id AND oi.price > 100 
);
```

---

### 9\. ROW\_NUMBER() for Pagination

**Use case**: MySQL 8.0+ window functions provide cleaner, more predictable pagination.

```sql
-- Get rows 1-10 of newest articles 
SELECT * FROM ( 
  SELECT *, ROW_NUMBER() OVER (ORDER BY created_at DESC) AS row_num 
  FROM articles 
) t 
WHERE row_num BETWEEN 1 AND 10;
```

---

### 10\. WITH Clause (CTEs) for Complex Queries

**Use case**: Break down complex queries into readable, maintainable chunks.

```sql
-- Calculate user order stats, then join with users 
WITH user_stats AS ( 
  SELECT user_id, COUNT(*) AS order_count, SUM(amount) AS total_amount 
  FROM orders 
  GROUP BY user_id 
) 
SELECT u.name, us.order_count, us.total_amount 
FROM users u 
JOIN user_stats us ON u.id = us.user_id;
```

### Final Notes

These techniques come from real production scenarios. A few caveats:

- JSON columns work well for infrequent queries of dynamic data. For high-frequency queries, normalize into separate tables.
- Window functions and CTEs require MySQL 8.0+. Check your version compatibility.
- Always use `EXPLAIN` to analyze query performance for complex queries.

Got other useful MySQL tricks? Drop them in the comments.