xingliuhuaMySQL-03 SQL 语法大全
目录
MySQL SQL 语法大全
目录
- DDL 数据定义语言
- DML 数据操纵语言
- DQL 查询语句
- JOIN 连接查询
- 子查询与 CTE
- 窗口函数（MySQL 8.0+）
- 聚合函数与 GROUP BY
- 常用内置函数
- JSON 操作（MySQL 5.7+）
- DCL 数据控制语言
- TCL 事务控制语言
- Go 中的 SQL 实践
1. DDL 数据定义语言
1.1 数据库
-- 创建
CREATE DATABASE IF NOT EXISTS myapp
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
-- 修改字符集
ALTER DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 删除
DROP DATABASE IF EXISTS myapp;
-- 查看建库语句
SHOW CREATE DATABASE myapp;
1.2 表操作
-- 创建表
CREATE TABLE IF NOT EXISTS users (
id BIGINT UNSIGNED AUTO_INCREMENT COMMENT '用户 ID',
name VARCHAR(64) NOT NULL COMMENT '用户名',
email VARCHAR(128) NOT NULL COMMENT '邮箱',
phone CHAR(11) COMMENT '手机号',
age TINYINT UNSIGNED NOT NULL DEFAULT 0,
gender ENUM('M', 'F', 'Unknown') NOT NULL DEFAULT 'Unknown',
balance DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
extra JSON,
status TINYINT NOT NULL DEFAULT 1 COMMENT '1:正常 0:禁用',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at DATETIME COMMENT '软删除时间',
PRIMARY KEY (id),
UNIQUE KEY uk_email (email),
KEY idx_phone (phone),
KEY idx_status_created (status, created_at)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_unicode_ci
COMMENT = '用户表';
-- 查看建表语句
SHOW CREATE TABLE users\G
-- 查看表结构
DESC users;
SHOW COLUMNS FROM users;
1.3 ALTER TABLE（Online DDL）
-- 添加列（MySQL 5.6+ 支持 Online DDL，不锁表）
ALTER TABLE users ADD COLUMN avatar VARCHAR(256) AFTER name, ALGORITHM=INPLACE, LOCK=NONE;
-- 修改列类型（可能需要锁表，谨慎！）
ALTER TABLE users MODIFY COLUMN name VARCHAR(128) NOT NULL;
-- 重命名列（MySQL 8.0+）
ALTER TABLE users RENAME COLUMN old_name TO new_name;
-- 删除列
ALTER TABLE users DROP COLUMN avatar;
-- 添加索引（Online DDL）
ALTER TABLE users ADD INDEX idx_age (age), ALGORITHM=INPLACE, LOCK=NONE;
-- 删除索引
ALTER TABLE users DROP INDEX idx_age;
-- 重命名表
RENAME TABLE users TO user_accounts;
ALTER TABLE users RENAME TO user_accounts;
-- 删除表
DROP TABLE IF EXISTS temp_users;
-- 清空表（比 DELETE 快，不记录行级日志，不可回滚）
TRUNCATE TABLE temp_logs;
MySQL 特有：
ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE实现在线 DDL，生产环境大表变更推荐使用 gh-ost 或 pt-online-schema-change。
1.4 分区表
-- RANGE 分区（按年份）
CREATE TABLE orders_partitioned (
id BIGINT UNSIGNED AUTO_INCREMENT,
user_id BIGINT UNSIGNED NOT NULL,
amount DECIMAL(10, 2),
created_at DATETIME NOT NULL,
PRIMARY KEY (id, created_at)
) ENGINE = InnoDB
PARTITION BY RANGE (YEAR(created_at)) (
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p_future VALUES LESS THAN MAXVALUE
);
-- 查看分区信息
SELECT PARTITION_NAME, TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_NAME = 'orders_partitioned';
2. DML 数据操纵语言
2.1 INSERT
-- 普通插入
INSERT INTO users (name, email, age) VALUES ('Alice', 'alice@example.com', 25);
-- 批量插入（比多次单行插入快 10-50 倍）
INSERT INTO users (name, email, age) VALUES
('Bob', 'bob@example.com', 30),
('Carol', 'carol@example.com', 28),
('Dave', 'dave@example.com', 22);
-- INSERT IGNORE：忽略唯一键冲突（不插入，不报错）
INSERT IGNORE INTO users (name, email) VALUES ('Alice', 'alice@example.com');
-- INSERT ... ON DUPLICATE KEY UPDATE（MySQL 特有！）
-- 存在则更新，不存在则插入
INSERT INTO users (email, name, login_count)
VALUES ('alice@example.com', 'Alice', 1)
ON DUPLICATE KEY UPDATE
login_count = login_count + 1,
updated_at = NOW();
-- REPLACE INTO（存在则先删后插，不推荐用于有外键的场景）
REPLACE INTO users (id, name, email) VALUES (1, 'Alice New', 'alice_new@example.com');
-- INSERT ... SELECT
INSERT INTO users_backup SELECT * FROM users WHERE created_at < '2023-01-01';
2.2 UPDATE
-- 单表更新
UPDATE users SET status = 0, updated_at = NOW()
WHERE id = 1;
-- 多表关联更新（MySQL 特有！）
UPDATE orders o
JOIN users u ON o.user_id = u.id
SET o.user_name = u.name
WHERE o.status = 1;
-- 限制更新行数（避免误操作大量数据）
UPDATE users SET status = 0 WHERE status = 1 LIMIT 100;
-- 带子查询更新
UPDATE products p
SET p.stock = p.stock - 1
WHERE p.id IN (SELECT product_id FROM order_items WHERE order_id = 100);
注意：UPDATE 不加 WHERE 会更新全表！生产建议开启
sql_safe_updates = ON。
2.3 DELETE
-- 单表删除
DELETE FROM users WHERE id = 1;
-- 多表关联删除（MySQL 特有！）
DELETE o, oi
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.user_id = 100 AND o.status = 0;
-- 删除 JOIN 结果
DELETE u FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL; -- 删除没有订单的用户
-- 限制删除行数（分批删除大量数据，避免长事务）
DELETE FROM logs WHERE created_at < '2023-01-01' LIMIT 1000;
-- 循环执行直到 affected rows = 0
3. DQL 查询语句
3.1 SELECT 基础
-- 完整的 SELECT 语句结构
SELECT [DISTINCT] column_list
FROM table_name
[JOIN other_table ON condition]
[WHERE condition]
[GROUP BY columns]
[HAVING condition]
[ORDER BY columns [ASC|DESC]]
[LIMIT offset, count]
[FOR UPDATE | LOCK IN SHARE MODE]
;
执行顺序（与书写顺序不同）：
FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT
3.2 WHERE 条件
-- 比较运算符
WHERE age > 18
WHERE age BETWEEN 18 AND 65
WHERE status IN (1, 2, 3)
WHERE email IS NULL
WHERE email IS NOT NULL
-- 字符串匹配
WHERE name LIKE 'Ali%' -- 前缀匹配（可走索引）
WHERE name LIKE '%Ali%' -- 全模糊（不走索引）
WHERE name REGEXP '^Ali' -- 正则（不走索引）
-- 多条件
WHERE age > 18 AND status = 1
WHERE age < 18 OR status = 0
-- EXISTS
WHERE EXISTS (
SELECT 1 FROM orders WHERE orders.user_id = users.id
)
3.3 ORDER BY 与 LIMIT
-- 排序
ORDER BY created_at DESC
ORDER BY status ASC, created_at DESC -- 多列排序
ORDER BY FIELD(status, 2, 1, 0) -- MySQL 特有：自定义排序顺序
-- 分页
LIMIT 20 -- 前 20 条
LIMIT 40, 20 -- 跳过 40 条，取 20 条（第 3 页）
LIMIT 20 OFFSET 40 -- 等价写法
-- 深分页问题（LIMIT 1000000, 20 很慢）
-- 优化：游标分页
SELECT * FROM orders
WHERE id > :last_id -- 上次查询最后一个 id
ORDER BY id ASC
LIMIT 20;
4. JOIN 连接查询
4.1 JOIN 类型
-- INNER JOIN（内连接，两表都有才返回）
SELECT u.name, o.id, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- LEFT JOIN（左连接，左表全返回，右表无则 NULL）
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;
-- RIGHT JOIN（右连接，右表全返回）
-- 通常改为 LEFT JOIN + 交换表顺序
-- 查找"左表有但右表没有"的记录（反连接）
SELECT u.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;
-- CROSS JOIN（笛卡尔积，通常不用）
SELECT * FROM colors CROSS JOIN sizes;
4.2 JOIN 执行原理
MySQL 使用 Nested Loop Join（嵌套循环连接）：
for each row in 驱动表:
for each row in 被驱动表:
if join_condition matched:
output row
优化原则：
- 小表驱动大表：驱动表数据量小，被驱动表有索引
- 被驱动表必须有索引：否则每次扫全表，O(n×m)
- MySQL 会自动优化 JOIN 顺序，但可用
STRAIGHT_JOIN强制顺序
-- 查看 JOIN Buffer 大小（当被驱动表无索引时使用）
SHOW VARIABLES LIKE 'join_buffer_size';
-- MySQL 8.0.18+ 支持 Hash Join（等值连接且无索引时自动使用）
4.3 多表 JOIN
-- 三表 JOIN
SELECT
u.name AS user_name,
o.id AS order_id,
p.name AS product_name,
oi.qty,
oi.price
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.status = 1
AND o.created_at >= '2024-01-01'
ORDER BY o.created_at DESC
LIMIT 20;
5. 子查询与 CTE
5.1 子查询
-- 标量子查询（返回单值）
SELECT name, (SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_cnt
FROM users u;
-- IN 子查询
SELECT * FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders WHERE amount > 1000);
-- EXISTS 子查询（通常比 IN 快）
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 1000);
-- FROM 子查询（派生表）
SELECT avg_data.dept, avg_data.avg_salary
FROM (
SELECT dept, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept
) AS avg_data
WHERE avg_data.avg_salary > 10000;
5.2 CTE（公共表表达式，MySQL 8.0+）
-- 基本 CTE（等价于派生表，但更可读）
WITH user_orders AS (
SELECT user_id, COUNT(*) AS cnt, SUM(amount) AS total
FROM orders
WHERE status = 1
GROUP BY user_id
)
SELECT u.name, uo.cnt, uo.total
FROM users u
JOIN user_orders uo ON u.id = uo.user_id
WHERE uo.total > 10000;
-- 多个 CTE
WITH
active_users AS (
SELECT id FROM users WHERE status = 1
),
recent_orders AS (
SELECT user_id, SUM(amount) AS total
FROM orders
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY user_id
)
SELECT u.id, u.name, COALESCE(ro.total, 0) AS recent_total
FROM active_users au
JOIN users u ON au.id = u.id
LEFT JOIN recent_orders ro ON au.id = ro.user_id;
-- 递归 CTE（MySQL 8.0+，处理树形/层级数据）
WITH RECURSIVE category_tree AS (
-- 锚点：根节点
SELECT id, name, parent_id, 0 AS level, CAST(name AS CHAR(1000)) AS path
FROM categories
WHERE parent_id IS NULL
UNION ALL
-- 递归：子节点
SELECT c.id, c.name, c.parent_id, ct.level + 1, CONCAT(ct.path, ' > ', c.name)
FROM categories c
JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY path;
6. 窗口函数（MySQL 8.0+）
窗口函数在不改变行数的情况下，基于一组相关行（窗口）计算值。
6.1 语法结构
function_name(args) OVER (
[PARTITION BY partition_cols]
[ORDER BY order_cols]
[ROWS|RANGE BETWEEN ... AND ...]
)
6.2 排名函数
SELECT
id,
name,
score,
RANK() OVER (ORDER BY score DESC) AS rank_with_gap, -- 1,2,2,4
DENSE_RANK() OVER (ORDER BY score DESC) AS rank_no_gap, -- 1,2,2,3
ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num, -- 1,2,3,4
NTILE(4) OVER (ORDER BY score DESC) AS quartile -- 分4组
FROM students;
-- 分组排名
SELECT
dept,
name,
salary,
RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_rank
FROM employees;
6.3 偏移函数
SELECT
date,
revenue,
LAG(revenue, 1) OVER (ORDER BY date) AS prev_revenue, -- 前一行
LEAD(revenue, 1) OVER (ORDER BY date) AS next_revenue, -- 后一行
FIRST_VALUE(revenue) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS first_rev,
LAST_VALUE(revenue) OVER (ORDER BY date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS last_rev
FROM daily_revenue;
-- 计算环比增长率
SELECT
date,
revenue,
LAG(revenue) OVER (ORDER BY date) AS prev,
ROUND((revenue - LAG(revenue) OVER (ORDER BY date)) / LAG(revenue) OVER (ORDER BY date) * 100, 2) AS growth_pct
FROM daily_revenue;
6.4 聚合窗口函数
-- 累计求和（Running Total）
SELECT
date,
revenue,
SUM(revenue) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative
FROM daily_revenue;
-- 移动平均（7 天移动平均）
SELECT
date,
revenue,
AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
FROM daily_revenue;
7. 聚合函数与 GROUP BY
-- 标准聚合函数
SELECT
dept,
COUNT(*) AS cnt,
COUNT(DISTINCT employee_id) AS unique_emp,
SUM(salary) AS total_salary,
AVG(salary) AS avg_salary,
MAX(salary) AS max_salary,
MIN(salary) AS min_salary,
GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS names -- MySQL 特有！
FROM employees
GROUP BY dept
HAVING avg_salary > 8000
ORDER BY total_salary DESC;
-- ROLLUP（按层级汇总，MySQL 特有增强）
SELECT dept, job_title, SUM(salary)
FROM employees
GROUP BY dept, job_title WITH ROLLUP;
-- 会额外输出 dept 汇总行和总汇总行
-- JSON_ARRAYAGG / JSON_OBJECTAGG（MySQL 5.7.22+）
SELECT
user_id,
JSON_ARRAYAGG(product_id) AS product_ids,
JSON_OBJECTAGG(product_id, qty) AS product_qty_map
FROM order_items
GROUP BY user_id;
8. 常用内置函数
8.1 字符串函数
CONCAT('Hello', ' ', 'World') -- 'Hello World'
CONCAT_WS('-', '2024', '01', '15') -- '2024-01-15'（分隔符连接）
LENGTH('Hello') -- 5（字节长度）
CHAR_LENGTH('你好') -- 2（字符长度）
UPPER('hello') / LOWER('HELLO') -- 大小写转换
TRIM(' hello ') -- 'hello'
LTRIM / RTRIM -- 左/右去空格
SUBSTRING('Hello World', 1, 5) -- 'Hello'（1-based）
LEFT('Hello', 3) -- 'Hel'
RIGHT('Hello', 3) -- 'llo'
REPLACE('Hello World', 'World', 'MySQL') -- 'Hello MySQL'
INSTR('Hello', 'ell') -- 2（位置）
LPAD('5', 3, '0') -- '005'（左填充）
RPAD('5', 3, '0') -- '500'
FORMAT(1234567.89, 2) -- '1,234,567.89'
HEX('A') -- '41'
UNHEX('41') -- 'A'
MD5('password') -- MD5 哈希
SHA2('password', 256) -- SHA256 哈希
UUID() -- 生成 UUID
8.2 日期时间函数
NOW() -- 当前日期时间（事务期间不变）
SYSDATE() -- 当前日期时间（实时）
CURRENT_DATE() / CURDATE() -- 当前日期
CURRENT_TIME() / CURTIME() -- 当前时间
DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S') -- '2024-01-15 10:30:00'
STR_TO_DATE('2024-01-15', '%Y-%m-%d') -- 字符串转日期
UNIX_TIMESTAMP() -- Unix 时间戳（秒）
FROM_UNIXTIME(1705286400) -- 时间戳转 DATETIME
DATE_ADD(NOW(), INTERVAL 7 DAY) -- 加 7 天
DATE_SUB(NOW(), INTERVAL 1 MONTH) -- 减 1 个月
DATEDIFF('2024-12-31', '2024-01-01') -- 365（天数差）
TIMESTAMPDIFF(HOUR, start, end) -- 时间差（小时）
YEAR(created_at) -- 提取年份
MONTH(created_at) -- 提取月份
DAY(created_at) -- 提取日
WEEKDAY(created_at) -- 0=周一 ... 6=周日
LAST_DAY('2024-02-01') -- '2024-02-29'（月末）
8.3 数值函数
ROUND(3.14159, 2) -- 3.14
CEIL(3.1) -- 4（向上取整）
FLOOR(3.9) -- 3（向下取整）
TRUNCATE(3.14159, 2) -- 3.14（截断，不四舍五入）
ABS(-5) -- 5
MOD(10, 3) -- 1（取余）
POWER(2, 10) -- 1024
SQRT(16) -- 4
RAND() -- 0~1 随机浮点数
RAND(42) -- 带种子的随机数
GREATEST(1, 5, 3) -- 5
LEAST(1, 5, 3) -- 1
8.4 条件函数
-- IF
IF(age >= 18, '成年', '未成年')
-- IFNULL
IFNULL(phone, '未填写') -- phone 为 NULL 时返回'未填写'
-- NULLIF
NULLIF(score, 0) -- score=0 时返回 NULL
-- COALESCE（返回第一个非 NULL 值）
COALESCE(phone, email, '无联系方式')
-- CASE WHEN
CASE status
WHEN 0 THEN '待付款'
WHEN 1 THEN '已付款'
WHEN 2 THEN '已发货'
ELSE '未知'
END
-- CASE 搜索式
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
WHEN score >= 70 THEN 'C'
ELSE 'D'
END
9. JSON 操作（MySQL 5.7+）
9.1 JSON 列定义与查询
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100),
attrs JSON -- 存储灵活的属性
);
INSERT INTO products VALUES (1, 'iPhone', '{"color": "black", "storage": 256, "features": ["5G", "Face ID"]}');
-- 提取 JSON 值（-> 是 JSON_EXTRACT 的简写）
SELECT
id,
name,
attrs -> '$.color' AS color, -- 返回带引号的字符串
attrs ->> '$.color' AS color_text, -- 返回不带引号（MySQL 5.7.13+）
attrs -> '$.storage' AS storage,
attrs -> '$.features[0]' AS first_feature
FROM products;
-- JSON_EXTRACT
SELECT JSON_EXTRACT(attrs, '$.color') FROM products WHERE id = 1;
9.2 JSON 修改
-- 更新 JSON 字段
UPDATE products
SET attrs = JSON_SET(attrs, '$.color', 'white', '$.storage', 512)
WHERE id = 1;
-- JSON_INSERT（只在路径不存在时插入）
UPDATE products SET attrs = JSON_INSERT(attrs, '$.weight', '172g') WHERE id = 1;
-- JSON_REPLACE（只在路径存在时替换）
UPDATE products SET attrs = JSON_REPLACE(attrs, '$.color', 'gold') WHERE id = 1;
-- JSON_REMOVE（删除某个键）
UPDATE products SET attrs = JSON_REMOVE(attrs, '$.weight') WHERE id = 1;
-- 数组追加
UPDATE products SET attrs = JSON_ARRAY_APPEND(attrs, '$.features', 'USB-C') WHERE id = 1;
9.3 JSON 索引（虚拟列 + 索引）
-- MySQL 不支持直接对 JSON 列建索引，但可以通过虚拟列建索引
ALTER TABLE products
ADD COLUMN color VARCHAR(50)
GENERATED ALWAYS AS (attrs ->> '$.color') VIRTUAL,
ADD INDEX idx_color (color);
-- 查询自动利用虚拟列索引
SELECT * FROM products WHERE attrs ->> '$.color' = 'black';
-- EXPLAIN 会显示使用了 idx_color
9.4 JSON 函数汇总
JSON_OBJECT('key1', val1, 'key2', val2) -- 构造 JSON 对象
JSON_ARRAY(val1, val2, val3) -- 构造 JSON 数组
JSON_CONTAINS(json, val, path) -- 是否包含值
JSON_CONTAINS_PATH(json, 'one', path) -- 路径是否存在
JSON_KEYS(json) -- 返回顶层 key 数组
JSON_LENGTH(json) -- 返回长度
JSON_TYPE(json) -- 返回类型
JSON_VALID(str) -- 是否合法 JSON
JSON_PRETTY(json) -- 格式化输出（MySQL 5.7.22+）
10. DCL 数据控制语言
-- 创建用户
CREATE USER 'appuser'@'%' IDENTIFIED BY 'SecurePass123!';
CREATE USER 'readonly'@'10.0.0.%' IDENTIFIED BY 'ReadPass123!';
-- 授权
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'appuser'@'%';
GRANT SELECT ON myapp.* TO 'readonly'@'10.0.0.%';
GRANT ALL PRIVILEGES ON myapp.* TO 'appuser'@'%';
-- 角色（MySQL 8.0+）
CREATE ROLE 'app_role', 'read_role';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app_role';
GRANT SELECT ON myapp.* TO 'read_role';
GRANT 'app_role' TO 'appuser'@'%';
-- 查看权限
SHOW GRANTS FOR 'appuser'@'%';
-- 撤销
REVOKE DELETE ON myapp.* FROM 'appuser'@'%';
-- 刷新权限缓存
FLUSH PRIVILEGES;
11. TCL 事务控制语言
-- 开启事务
START TRANSACTION;
-- 或 BEGIN;
-- 执行操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 提交
COMMIT;
-- 回滚
ROLLBACK;
-- 保存点（部分回滚）
START TRANSACTION;
INSERT INTO orders VALUES (...);
SAVEPOINT sp1;
INSERT INTO order_items VALUES (...);
-- 如果 order_items 操作出错，只回滚到 sp1
ROLLBACK TO SAVEPOINT sp1;
COMMIT;
-- 自动提交
SHOW VARIABLES LIKE 'autocommit'; -- 默认 ON
SET autocommit = 0; -- 关闭后需要手动 COMMIT
-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
12. Go 中的 SQL 实践
12.1 事务处理
func Transfer(db *sql.DB, fromID, toID int64, amount float64) error {
tx, err := db.Begin()
if err != nil {
return fmt.Errorf("begin tx: %w", err)
}
// defer 保证最终状态
defer func() {
if p := recover(); p != nil {
tx.Rollback()
panic(p)
}
}()
// 扣款
_, err = tx.Exec(
"UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?",
amount, fromID, amount,
)
if err != nil {
tx.Rollback()
return fmt.Errorf("deduct: %w", err)
}
// 检查影响行数
// result.RowsAffected() == 0 说明余额不足
// 入账
_, err = tx.Exec(
"UPDATE accounts SET balance = balance + ? WHERE id = ?",
amount, toID,
)
if err != nil {
tx.Rollback()
return fmt.Errorf("deposit: %w", err)
}
return tx.Commit()
}
12.2 批量插入
func BatchInsert(db *sql.DB, users []User) error {
if len(users) == 0 {
return nil
}
// 构建批量插入 SQL
valueStrings := make([]string, 0, len(users))
valueArgs := make([]interface{}, 0, len(users)*3)
for _, u := range users {
valueStrings = append(valueStrings, "(?, ?, ?)")
valueArgs = append(valueArgs, u.Name, u.Email, u.Age)
}
stmt := fmt.Sprintf(
"INSERT INTO users (name, email, age) VALUES %s",
strings.Join(valueStrings, ","),
)
_, err := db.Exec(stmt, valueArgs...)
return err
}
// 使用 sqlx 的 NamedExec + BulkInsert
func BatchInsertSQLX(db *sqlx.DB, users []User) error {
_, err := db.NamedExec(
`INSERT INTO users (name, email, age) VALUES (:name, :email, :age)`,
users,
)
return err
}
12.3 动态查询构建
// 避免手动拼接 SQL，使用 squirrel 库
import sq "github.com/Masterminds/squirrel"
func QueryUsers(db *sql.DB, name string, minAge, maxAge int, status *int) ([]User, error) {
q := sq.Select("id", "name", "email", "age").
From("users").
Where(sq.Eq{"deleted_at": nil})
if name != "" {
q = q.Where(sq.Like{"name": name + "%"})
}
if minAge > 0 {
q = q.Where(sq.GtOrEq{"age": minAge})
}
if maxAge > 0 {
q = q.Where(sq.LtOrEq{"age": maxAge})
}
if status != nil {
q = q.Where(sq.Eq{"status": *status})
}
sqlStr, args, err := q.ToSql()
if err != nil {
return nil, err
}
rows, err := db.Query(sqlStr, args...)
// ... 处理 rows
}
12.4 JSON 列处理
import "encoding/json"
type ProductAttrs struct {
Color string `json:"color"`
Storage int `json:"storage"`
Features []string `json:"features"`
}
type Product struct {
ID int64
Name string
Attrs ProductAttrs
}
// 扫描 JSON 列
func scanProduct(rows *sql.Rows) (*Product, error) {
var p Product
var attrsJSON []byte
err := rows.Scan(&p.ID, &p.Name, &attrsJSON)
if err != nil {
return nil, err
}
if err = json.Unmarshal(attrsJSON, &p.Attrs); err != nil {
return nil, err
}
return &p, nil
}
// GORM 的 JSON 列处理
import "gorm.io/datatypes"
type ProductGORM struct {
ID uint
Name string
Attrs datatypes.JSON // gorm.io/datatypes 提供
}
小结
- MySQL 特有语法：
ON DUPLICATE KEY UPDATE、多表 UPDATE/DELETE、GROUP_CONCAT、WITH ROLLUP、STRAIGHT_JOIN - MySQL 8.0 新增：窗口函数、CTE（含递归）、
JSON_TABLE、原子 DDL - JSON 类型：灵活存储，但需通过虚拟列建索引，不适合频繁更新的字段
- Go 注意：使用占位符防 SQL 注入，批量操作减少往返，事务用 defer + Rollback 保底