★ FEATURED ARTICLE 先说明一点写这个题目不是想给你甩一份“背完就忘”的命令对照表。网上这类资源一大把但大多是按字母排序的字典查起来费劲而且很多命令单独看根本不知道什么时候该用。我写这篇“mysql命令大全”是把日常开发和运维里真正高频、真正会踩坑、真正需要组合使用的命令挑出来按使用场景重新组织一遍。你读完可以直接照着敲也知道为什么这么敲。下文涉及的操作我默认你的环境是MySQL 5.7或8.0版本终端能正常连上服务。1. 先把环境摸清楚连接、信息查询与基础维护命令很多新手上来就急着建库建表结果连当前用的什么版本、字符集是什么、有哪些用户都说不清楚。这就像你拿到一台新电脑先得看看系统配置再装软件数据库也是一样。基础的信息查询命令是整个操作链条的第一步。1.1 连接数据库的正确姿势与参数解读MySQL自带命令行客户端mysql语法不复杂mysql -h 127.0.0.1 -P 3306 -u root -p参数含义拆开说-h指定主机地址。不写默认是localhost但在很多生产环境的网络配置下localhost走的是socket连接如果你明确要用TCP/IP协议就得写-h 127.0.0.1。两者的区别在于socket连接本地速度快但无法跨机器TCP连接可以远程访问。-P端口号。注意是大写P小写p是用来输入密码的。这个大小写问题我见过不止一个同事在这里栽跟头。-u用户名。-p密码参数。直接加在命令里比如-p123456虽然可以但不推荐因为会留在shell历史记录里。一般建议只写-p回车后再输入密码不会被记录。连接成功后会进入mysql交互环境。判断连接状态可以用SELECT VERSION(); SELECT CURRENT_USER(); SELECT DATABASE();VERSION()返回服务器版本号CURRENT_USER()返回当前登录账号DATABASE()返回当前默认操作的库。如果还没选库DATABASE()会返回NULL这是正常现象不用慌。1.2 查看库、表、列的各类信息命令管理数据库的第一步是知道这个实例里有什么。高频命令如下-- 查看所有数据库 SHOW DATABASES; -- 切换到指定数据库 USE db_name; -- 查看当前库所有表 SHOW TABLES; -- 查看表的字段结构 DESC table_name; -- 或者 SHOW COLUMNS FROM table_name; -- 查看建表语句这个非常实用能完整还原一张表 SHOW CREATE TABLE table_name; -- 查看表的索引信息 SHOW INDEX FROM table_name;SHOW CREATE TABLE是我最常用的命令之一。它会把表结构、字段类型、默认值、注释、索引、约束全部展示出来。在需要从测试库往生产库同步表结构的时候直接拷贝这条命令的输出是最稳妥的比手工重写建表语句少很多坑。还有一个实用技巧SHOW TABLE STATUS可以查看表的大致信息包含行数、数据长度、索引长度、创建时间等。注意InnoDB引擎下表里的行数是一个估算值不是精确值需要准确行数还得用COUNT(*)。提示指标名称区分大小写要看操作系统。字段名不区分但表名在Linux系统下默认区分大小写Windows下不区分。跨平台迁移时建议统一用一个小写表名规则。2. 库表操作从创建到删除的完整生命周期掌握了信息查询后就进入实际的库表操作。这里不只是简单的CREATE TABLE还涉及字符集、存储引擎、字段类型这些容易埋坑的细节。2.1 创建库/表的关键参数与字符集陷阱创建数据库CREATE DATABASE IF NOT EXISTS mydb DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;字符集必须指定utf8mb4为什么不用utf8因为MySQL里的utf8是一个有历史包袱的字符集它最多支持3字节编码存不了emoji表情和一些生僻汉字而utf8mb4是真正的4字节UTF-8完全兼容。有人接手旧库发现表情符号存进去变成问号十有八九就是字符集用了老的utf8。COLLATE是排序规则默认utf8mb4_general_ci不区分大小写适合大多数业务如果字段需要区分大小写可以单独指定utf8mb4_bin。创建表给一个实战中的例子CREATE TABLE user ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 主键, username varchar(64) NOT NULL COMMENT 用户名, email varchar(128) DEFAULT NULL COMMENT 邮箱, age int(11) DEFAULT 0 COMMENT 年龄, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_username (username) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表;几点解释bigint(20)里的20只是显示宽度不代表存储长度范围bigint本身就固定占8字节。CURRENT_TIMESTAMP在MySQL 5.6之后才支持作为datetime的默认值如果你的版本更老只能用TIMESTAMP类型。ON UPDATE CURRENT_TIMESTAMP的意思是这一行数据更新时字段值自动刷新成当前时间。这是常见的“更新时间”字段的标准写法不用业务代码手动维护。2.2 修改表结构ALTER TABLE 的多种应用场景表结构不是一成不变的业务一迭代就要加字段、改字段。ALTER TABLE是这里的主角。-- 新增字段 ALTER TABLE user ADD COLUMN phone varchar(20) DEFAULT NULL COMMENT 手机号 AFTER email; -- 修改字段类型 ALTER TABLE user MODIFY COLUMN age tinyint(4) NOT NULL DEFAULT 0 COMMENT 年龄; -- 修改字段名和类型 ALTER TABLE user CHANGE COLUMN phone mobile varchar(20) DEFAULT NULL COMMENT 手机号; -- 删除字段 ALTER TABLE user DROP COLUMN mobile; -- 新增普通索引 ALTER TABLE user ADD INDEX idx_email (email); -- 删除索引 ALTER TABLE user DROP INDEX idx_email;MODIFY和CHANGE的区别很多人分不清MODIFY只能改字段的属性定义不能改字段名CHANGE既可以改名也可以改属性改名时必须写全旧名 新名 类型。注意大数据量表执行ALTER TABLE会导致锁表业务高峰尽量减少这种操作。MySQL 8.0支持了在线DDL比老版本友好很多但建议还是放在低峰期操作。删除表和库-- 删除表 DROP TABLE IF EXISTS table_name; -- 清空表数据保留表结构 TRUNCATE TABLE table_name; -- 删除整个库这条命令要极其谨慎 DROP DATABASE IF EXISTS mydb;TRUNCATE和DELETE的区别TRUNCATE是直接清空所有行并且重置自增ID速度快但不走事务DELETE是一行一行删可以加WHERE条件走事务但ID不会重置。少数情况下误操作DROP/TRUNCATE靠日志恢复非常麻烦。3. 日常数据操作增删改查的标准用法与细节偏门命令不算多但高频的INSERT、SELECT、UPDATE、DELETE组合起来变化非常多。这一节讲的命令不追求冷门而是追求实际业务里最常用的各种形态。3.1 插入数据单条、批量、忽略冲突的写法-- 单条插入 INSERT INTO user (username, email, age) VALUES (zhangsan, zhangsanexample.com, 25); -- 批量插入 INSERT INTO user (username, email, age) VALUES (lisi, lisiexample.com, 30), (wangwu, wangwuexample.com, 28); -- 冲突时忽略唯一键冲突直接跳过 INSERT IGNORE INTO user (username, email, age) VALUES (zhangsan, zhangsanexample.com, 26); -- 冲突时更新唯一键冲突则执行更新 INSERT INTO user (username, email, age) VALUES (zhangsan, zhangsan2example.com, 26) ON DUPLICATE KEY UPDATE email VALUES(email), age VALUES(age);批量插入的性能远高于逐条插入因为减少了客户端和服务器之间的网络往返次数。我见过有人循环几千次单条INSERT导数据跑了半小时改成批量一万条一次十几秒就搞定了。实测下来建议单次批量控制在几百到几千条之间太大容易超过max_allowed_packet限制。INSERT IGNORE和ON DUPLICATE KEY UPDATE都依赖唯一索引或主键冲突来判断两者区别显而易见一个直接忽略一个执行更新。具体选哪个看业务需求比如用户注册场景重复时直接提示已存在用IGNORE更合适导入数据时要幂等更新用ON DUPLICATE KEY UPDATE。3.2 查询数据的核心语法过滤、排序、分组、分页SELECT命令是MySQL里使用频率最高、也最能体现水平的命令。最常见形态-- 条件过滤 SELECT id, username, email FROM user WHERE age 18 AND email IS NOT NULL; -- 模糊查询 SELECT * FROM user WHERE username LIKE zhang%; -- 范围查询 SELECT * FROM user WHERE age BETWEEN 20 AND 30; -- IN查询 SELECT * FROM user WHERE age IN (20, 25, 30); -- 排序 SELECT * FROM user ORDER BY age DESC, id ASC; -- 分组统计 SELECT age, COUNT(*) AS cnt FROM user GROUP BY age HAVING cnt 1; -- 分页 SELECT * FROM user ORDER BY id LIMIT 10 OFFSET 20; -- 等价写法 SELECT * FROM user ORDER BY id LIMIT 20, 10;分组统计里有一个经典陷阱WHERE是在分组前过滤行HAVING是在分组后过滤组。比如你要统计每个年龄的人数但只关心年龄大于20的那条件放在WHERE如果只要出现次数大于1的年龄组那条件放在HAVING两者不能混用。LIKE查询里%匹配任意多字符_匹配单个字符。但注意LIKE zhang%这样的写法无法利用普通索引前缀模糊LIKE %zhang更是全表扫描。数据量大的表做模糊查询要谨慎尽量用全文索引或用ES这类外部搜索组件。分页深分页一个常见性能问题LIMIT 100000, 20MySQL会先查到100020行然后丢弃前10万行代价很大。常规优化方式是记录上一页最后一条ID用WHERE id last_id LIMIT 20来翻页。3.3 更新与删除注意WHERE条件防止灾难-- 普通更新 UPDATE user SET age 26 WHERE username zhangsan; -- 多字段更新 UPDATE user SET age age 1, updated_at NOW() WHERE id 100; -- 删除 DELETE FROM user WHERE id 100;说到UPDATE和DELETE必须强调一个老生常谈但依然在不断发生的事故点WHERE条件忘记写或者写错了范围。没有WHERE条件的UPDATE会全表更新DELETE会全表清空。实际操作中我自己的习惯是先写一条SELECT语句确认要影响的数据条数再改成UPDATE或者DELETE执行这也算是最稳妥的防御式操作。UPDATE操作有时候要看影响行数如果发现明明数据存在但显示0 rows affected很可能你设置的值和原值一样MySQL认为这种更新没变化所以不计数这不算错误。4. 用户与权限管理安全的边界控制权限管理是很多人的知识盲区平时不用觉得陌生一旦遇到安全问题、或者新同事入职要开账号这些问题就会摊到你面前。MySQL的权限体系用“用户权限”双层模型控制力度很细。4.1 创建用户与授权-- 创建用户MySQL 8.0语法 CREATE USER app_userlocalhost IDENTIFIED BY StrongPssw0rd; -- 授权 GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO app_userlocalhost; -- 授予所有权限谨慎使用 GRANT ALL PRIVILEGES ON mydb.* TO app_userlocalhost; -- 授予跨库查询权限 GRANT SELECT ON *.* TO app_userlocalhost; -- 刷新权限 FLUSH PRIVILEGES;必须说明一点在MySQL 8.0之前GRANT语句可以顺带创建用户但8.0之后必须先用CREATE USER创建再GRANT授权这个顺序不能反。app_userlocalhost这部分里localhost是允许登录的主机地址可以换成%表示允许任何主机但为了安全生产库上一般不用%只开放固定的应用服务器IP能降低账号泄露后造成的外网访问风险。4.2 查看权限、修改密码与撤销权限-- 查看当前用户权限 SHOW GRANTS FOR app_userlocalhost; -- 修改密码MySQL 8.0 ALTER USER app_userlocalhost IDENTIFIED BY NewPassw0rd; -- 撤销权限 REVOKE DELETE ON mydb.* FROM app_userlocalhost; -- 删除用户 DROP USER app_userlocalhost;修改密码的语法在不同版本里差异较大。5.7版本常见的是SET PASSWORD FOR app_userlocalhost PASSWORD(xxx)PASSWORD()函数在8.0里已经被移除这种写法会直接报错。接手老项目时要注意版本差异。还有一个常被忽略的安全习惯不需要用完的临时账号要及时删除应用账号权限坚持最小化原则只给业务需要的增删改查绝不轻易给ALL PRIVILEGES。日常开发说“帮我开通一下数据库权限”有些操作人员图省事直接给了整个实例的所有权限这给后面埋下相当大的隐患。5. 数据备份与恢复用命令守住最后一道防线备份恢复这组命令平时用不到但一旦真出事就是你最后的机会。这里讲两条主线逻辑备份用的mysqldump物理备份用的xtrabackup工具命令层面属于备份工具不是MySQL内置命令但极其常用。5.1 使用mysqldump导出数据# 导出单库含建表语句和数据 mysqldump -h 127.0.0.1 -u root -p mydb mydb_backup.sql # 导出单库只导表结构不导数据 mysqldump -h 127.0.0.1 -u root -p --no-data mydb mydb_schema.sql # 导出单库只导数据不导表结构 mysqldump -h 127.0.0.1 -u root -p --no-create-info mydb mydb_data.sql # 导出多库 mysqldump -h 127.0.0.1 -u root -p --databases mydb1 mydb2 multi_db.sql # 导出所有库 mysqldump -h 127.0.0.1 -u root -p --all-databases all_db.sql常用参数里--single-transaction很重要它会在InnoDB引擎下基于事务导出导出的过程中不锁表适合在线备份。注意它依赖REPEATABLE READ事务隔离级别如果表用的MyISAM引擎该参数不会生效依然会锁表。还需要--routines导出存储过程和函数--triggers导出触发器默认情况触发器不导出容易在恢复时出问题。5.2 恢复数据# 恢复整库 mysql -h 127.0.0.1 -u root -p mydb_backup.sql # 进入mysql后指定库恢复 mysql USE mydb; mysql SOURCE /tmp/mydb_backup.sql;恢复之前要确保目标库已经存在如果备份SQL里没有CREATE DATABASE语句就要先手动建库再导入。--databases导出的SQL里包含建库语句恢复时不用先建库。--all-databases恢复时需要服务器系统库目录可写一般用于整机迁移。数据量很大时直接导入速度可能会很慢可以配合--max_allowed_packet128M这种参数提高客户端单次传输上限。5.3 定时备份的工程化思路命令本身只是工具关键在于形成一套备份策略。我在生产环境中常用一种组合方案每天凌晨用mysqldump --single-transaction --routines --triggers做一次逻辑备份到备份服务器。每周做一次全量物理备份用xtrabackup。日志层面开启binlog并设置合理的保留周期配合全量备份可以做时间点恢复。逻辑备份适合中小数据量几十GB以内和跨版本迁移物理备份适合大数据量下的快速恢复。两条腿走路才能保证数据安全。注意备份文件一定要定期演练恢复。只备份不复原跟没备份没有本质区别真到灾难发生时发现文件损坏或参数漏了就晚了。我建议每季度挑一个闲置环境真实执行一次恢复脚本。6. 性能与状态诊断让慢查询和死锁无处遁形MySQL日常运行中最影响体验的就是性能问题。命令层面的性能诊断主要围绕状态变量、慢查询日志、processlist和explain。6.1 查看系统状态与连接信息-- 查看全局状态包含各项计数器 SHOW GLOBAL STATUS; -- 查看当前线程连接情况 SHOW PROCESSLIST; -- 或者用 SHOW FULL PROCESSLIST;SHOW PROCESSLIST是排查线上问题的首选命令。执行后能看到每个连接的ID、用户、主机、数据库、命令类型、耗时和状态。如果发现大量Sleep状态的连接堆积说明应用连接池有泄漏如果大量Query状态且Time值很大说明有慢查询正在执行最直接的方法就是记录ID然后执行KILL id;把这个查询杀掉让业务先恢复再慢慢查sql。SHOW GLOBAL STATUS里值得关注几个指标Threads_connected当前连接数、Threads_running正在运行的线程数、Slow_queries慢查询累计数、Uptime。连接数接近max_connections上限时数据库会拒绝新连接应用端就报“Too many connections”要提前设置监控。6.2 分析慢查询日志慢查询日志需要提前开启不是默认开着的。-- 查看慢查询日志是否开启 SHOW VARIABLES LIKE slow_query_log; -- 开启慢查询日志全局生效重启后失效 SET GLOBAL slow_query_log ON; -- 设置阈值超过2秒记录 SET GLOBAL long_query_time 2;持久化配置在my.cnf里加[mysqld] slow_query_log 1 slow_query_log_file /var/log/mysql/slow.log long_query_time 2 log_queries_not_using_indexes 1long_query_time单位是秒支持小数比如0.5表示500毫秒。慢查询日志文件是文本形式可以直接用mysqldumpslow这个工具分析它把内容按执行次数、耗时排序聚合mysqldumpslow -t 10 /var/log/mysql/slow.log-t 10表示显示最慢的10条。实际使用中慢查询日志不会直接告诉你SQL该怎么优化它是定位问题的起点。拿到慢SQL之后下一步用EXPLAIN分析执行计划。6.3 EXPLAIN读懂MySQL的执行计划EXPLAIN SELECT u.username, o.order_no FROM user u LEFT JOIN order o ON u.id o.user_id WHERE u.age 20;结果里几个核心字段type访问类型。从好到差依次是system const eq_ref ref range index ALL。ALL就是全表扫描基本是性能瓶颈。看到ALL优先考虑加索引。key实际使用的索引。如果为NULL说明没走索引。rows预估扫描行数。这个值越小越好。Extra额外信息出现Using filesort说明排序没走索引数据量大的时候相当费性能出现Using temporary说明用了临时表也要关注。EXPLAIN不能仅凭字段名臆断我见过不少开发看到type为ALL立刻加索引结果加了还是不生效。比较典型的原因是查询条件里对索引列做了函数操作比如WHERE DATE(created_at) 2024-01-01这种情况即使created_at上有索引也用不上必须改写为created_at 2024-01-01 AND created_at 2024-01-02。6.4 索引命令的实战建议创建索引的语法上文提过这里再补两个实用技巧-- 复合索引的前缀原则 ALTER TABLE user ADD INDEX idx_union (age, username);复合索引中最左侧的字段才能独立走索引。比如(age, username)这个组合索引查age能走索引查username走不了。所以设计索引时要先把区分度最高、查询最多、作为等值条件的字段放在最左边。-- 删除重复和冗余索引 SHOW INDEX FROM table_name;一个表里索引数量不是越多越好索引本身也有存储成本和写入维护成本。写多读少的表索引一定要克制读多写少的大表索引值得好好设计。建议用SHOW INDEX FROM先看一下现有状态删除掉重复的比如已经有了ALTER TABLE ... ADD INDEX idx_email又建了idx_email_2两索引结构相同属于冗余。7. 常用高频命令速查表前面每一节都展开了讲最后整理一个浓缩的速查表方便背记和快速查找。这个表格只是把高频命令做个横向对照具体使用时还要结合你实际的表名、字段名与版本差异。场景命令示例备注连接数据库mysql -h 127.0.0.1 -P 3306 -u root -p小写p是密码大写P是端口查看版本SELECT VERSION();确认版本差异查看所有库SHOW DATABASES;加\G可用行显示切换库USE db_name;查看所有表SHOW TABLES;查看表结构DESC table_name;或SHOW CREATE TABLE table_name查看索引SHOW INDEX FROM table_name;创建库CREATE DATABASE db_name DEFAULT CHARSET utf8mb4;字符集特别注意创建表CREATE TABLE table_name (...);新增字段ALTER TABLE t ADD COLUMN c varchar(20);修改字段ALTER TABLE t MODIFY COLUMN c varchar(50);改名改类型ALTER TABLE t CHANGE COLUMN old_c new_c int;删除字段ALTER TABLE t DROP COLUMN c;插入数据INSERT INTO t (c1, c2) VALUES (v1, v2);批量插入INSERT INTO t (c1, c2) VALUES (v1,v2), (v3,v4);更新数据UPDATE t SET c1x WHERE id1;务必写WHERE删除数据DELETE FROM t WHERE id1;务必写WHERE清空表TRUNCATE TABLE t;重置自增ID查询SELECT c1, c2 FROM t WHERE c1x ORDER BY id;分页SELECT * FROM t ORDER BY id LIMIT 10 OFFSET 20;深分页用ID模糊查询SELECT * FROM t WHERE name LIKE 张%;前缀可用索引分组统计SELECT age, COUNT(*) FROM t GROUP BY age HAVING cnt1;WHERE与HAVING区分创建用户CREATE USER ulocalhost IDENTIFIED BY p;8.0先建后授权授权GRANT SELECT, INSERT ON db.* TO ulocalhost;查看权限SHOW GRANTS FOR ulocalhost;改密码ALTER USER ulocalhost IDENTIFIED BY newp;刷新权限FLUSH PRIVILEGES;备份库mysqldump -u root -p db db.sql;加--single-transaction恢复库mysql -u root -p db db.sql;查看连接SHOW PROCESSLIST;排查阻塞杀掉慢查询KILL id;紧急救火分析SQLEXPLAIN SELECT ...;看type、key、rows一个小习惯命令末尾加\G代替分号结果会按纵向列显示。字段很多的表在终端里横向显示很乱用\G一眼就能看全尤其是SHOW CREATE TABLE和SHOW PROCESSLIST两个场景效果好得多。我个人在实际操作中最深刻的体会是MySQL命令不是背会的是“用”会的。搜一遍记一遍远不如去一个真实环境里敲一遍、踩一遍坑。也别迷信某一版本的命令大全因为MySQL版本迭代带来的语法变化远比手册上写的多。真正处理生产问题的时候手里有一套自己能熟练执行的命令组合比什么都重要。希望这份整理能给你搭建一套实用框架剩下的就是在你自己的环境里多练、多验证。
阅读完成 · 觉得有帮助？
