MySQL

这页只放日常开发和表结构维护最常复制的 SQL / 命令。

登录和执行脚本

# 登录
mysql -h127.0.0.1 -P3306 -uroot -p

# 执行 SQL 文件
mysql -h127.0.0.1 -P3306 -uroot -p --default-character-set=utf8mb4 demo < ./init.sql

# 推荐把密码写到单独配置文件，避免进入历史命令
mysql --defaults-extra-file=./mysql-client.cnf demo < ./init.sql

[client]
host=127.0.0.1
port=3306
user=root
password=123456
default-character-set=utf8mb4

建库和账号模板

create database if not exists demo
  default character set utf8mb4
  collate utf8mb4_0900_ai_ci;

create user if not exists 'app_user'@'%' identified by 'change_me';
grant select, insert, update, delete on demo.* to 'app_user'@'%';
flush privileges;

show grants for 'app_user'@'%';

建表模板

drop table if exists user_account;

create table user_account (
  id bigint unsigned not null auto_increment comment '主键',
  user_no varchar(64) not null comment '用户编号',
  user_name varchar(128) not null default '' comment '用户名称',
  mobile varchar(32) default null comment '手机号',
  amount decimal(18, 2) not null default 0.00 comment '金额',
  status tinyint not null default 1 comment '状态：1-启用，0-禁用',
  extra json default null comment '扩展信息',
  deleted tinyint not null default 0 comment '是否删除：0-否，1-是',
  create_time datetime(3) not null default current_timestamp(3) comment '创建时间',
  update_time datetime(3) not null default current_timestamp(3) on update current_timestamp(3) comment '更新时间',
  primary key (id),
  unique key uk_user_no (user_no),
  key idx_mobile (mobile),
  key idx_status_create_time (status, create_time)
) engine=InnoDB
  default charset=utf8mb4
  collate=utf8mb4_0900_ai_ci
  comment='用户账户表';

DDL 变更模板

-- 新增列
alter table user_account
  add column email varchar(128) default null comment '邮箱' after mobile;

-- 修改列
alter table user_account
  modify column user_name varchar(256) not null default '' comment '用户名称';

-- 修改列名
alter table user_account
  change column user_no account_no varchar(64) not null comment '账户编号';

-- 新增索引
alter table user_account
  add key idx_create_time (create_time);

-- 删除索引
alter table user_account
  drop index idx_create_time;

分页查询模板

-- 查列表
select id, user_no, user_name, mobile, status, create_time
from user_account
where deleted = 0
  and status = 1
order by id desc
limit 20 offset 0;

-- 查总数
select count(1)
from user_account
where deleted = 0
  and status = 1;

-- 深分页优化：先查 id，再回表
select u.*
from user_account u
join (
  select id
  from user_account
  where deleted = 0
  order by id desc
  limit 100000, 20
) t on t.id = u.id
order by u.id desc;

CRUD 和 Upsert 模板

-- 插入
insert into user_account (user_no, user_name, mobile, amount, status)
values ('U1001', '张三', '13800000000', 100.00, 1);

-- 批量插入
insert into user_account (user_no, user_name, mobile, amount, status)
values
  ('U1001', '张三', '13800000000', 100.00, 1),
  ('U1002', '李四', '13900000000', 200.00, 1);

-- Upsert
insert into user_account (user_no, user_name, mobile, amount, status)
values ('U1001', '张三', '13800000000', 300.00, 1)
on duplicate key update
  user_name = values(user_name),
  mobile = values(mobile),
  amount = values(amount),
  status = values(status),
  update_time = now(3);

-- 软删除
update user_account
set deleted = 1,
    update_time = now(3)
where id = 1001
  and deleted = 0;

批量更新模板

update user_account
set status = case id
  when 1001 then 1
  when 1002 then 0
  when 1003 then 1
end,
update_time = now(3)
where id in (1001, 1002, 1003);

统计和去重模板

-- 分组统计
select status, count(*) as total, sum(amount) as amount_sum
from user_account
where deleted = 0
group by status
having count(*) > 0;

-- 查重复手机号
select mobile, count(*) as total
from user_account
where deleted = 0
group by mobile
having count(*) > 1;

-- MySQL 8 删除重复数据，保留每组最早的一条
delete u
from user_account u
join (
  select id
  from (
    select id, row_number() over (partition by mobile order by id asc) as rn
    from user_account
    where mobile is not null
  ) x
  where x.rn > 1
) d on d.id = u.id;

JSON 字段模板

-- 取字段
select extra ->> '$.source' as source
from user_account;

-- 更新字段
update user_account
set extra = json_set(coalesce(extra, json_object()), '$.source', 'import')
where id = 1001;

时间模板

-- 当天数据，推荐左闭右开
select *
from user_account
where create_time >= curdate()
  and create_time < date_add(curdate(), interval 1 day);

-- 最近 7 天
select *
from user_account
where create_time >= date_sub(now(), interval 7 day);

排障模板

-- 查看执行计划
explain format=tree
select *
from user_account
where mobile = '13800000000';

-- 分析真实执行耗时（MySQL 8.0.18+）
explain analyze
select *
from user_account
where mobile = '13800000000';

-- 当前连接
show full processlist;

-- 事务和锁等待
select * from information_schema.innodb_trx\G
select * from performance_schema.data_lock_waits\G

备份恢复模板

# 备份单库
mysqldump -h127.0.0.1 -uroot -p --single-transaction --routines --triggers --events demo > demo.sql

# 备份单表
mysqldump -h127.0.0.1 -uroot -p --single-transaction demo user_account > user_account.sql

# 恢复
mysql -h127.0.0.1 -uroot -p demo < demo.sql