前言
记得之前一位同事，分享他入职的故事。他说，刚来新公司，想查看一个表的索引，居然忘记命令啦~~ 其实一些常用的mysql命令，虽然网上也是很快能查到，但还是都记住比较好~ 这样会显得你基础很扎实~~
本文总结了我日常工作，常用的mysql命令。小伙伴们收藏起来，慢慢看哈~
1. 连接mysql的命令
我们经常需要连接mysql数据库，用以下命令：
mysql -u username -p -h host_name -P port_number
有些时候，我们要远程连接 MySQL，也是同样道理：
mysql -u username -p -h remote_host_ip -P 3306
- remote_host_ip：远程 MySQL 服务器的 IP 地址。
- 3306：MySQL 默认端口（如果是其他端口，修改为相应端口）。
2. 查看当前 MySQL 正在运行的所有线程及其状态
show processlist;
SHOW PROCESSLIST 命令返回一个包含当前活动的连接线程的列表，每个连接线程的状态、运行的查询等信息。它对于诊断性能问题、查看阻塞查询、监控数据库健康状态非常有用。
3. 查看系统变量
很多时候，我们需要查看mysql的一些变量。比如，你要查看是否开启了慢查询日志：
show variables like 'slow_query_log';
而有些伙伴可能会这样查，加了个GLOBAL：
show global variables like 'slow_query_log';
- show variables like 'slow_query_log'; 默认查询的是当前会话（连接）或实例的变量。
- show global variables 显式地查询的是 全局变量，即当前整个 MySQL 实例的配置。
其实除了慢查询日期是否开启，还有很多配置变量查询（大家如果要查其他变量，类似这样就好），如下:
show global variables like 'sync_binlog';
sync_binlog 的作用：用于设置 MySQL 在写入二进制日志时的同步策略。
- 如果设置为 1，表示 每次写操作后都强制将二进制日志刷写到磁盘，以确保数据持久性。
- 如果设置为0，表示不强制每次写操作后刷新二进制日志，而是通过操作系统的缓存来控制。这种设置通常会带来更好的性能，但在崩溃恢复时可能会丢失一部分数据。
4.查看加锁信息
有些时候，我们看某个SQL加了什么锁，可以这样（MySQL 8.0+版本）：
SELECT * FROM performance_schema.data_locks\G;
它用于查询MySQL数据库中当前持有的和请求的数据锁信息。这些信息包括锁的类型、状态、持有者等
5. 查看和设置隔离级别
有些时候，我们需要查看数据库的隔离级别、或者设置隔离级别。
select @@tx_isolation; -- 查看当前会话的事务隔离级别
select @@global.tx_isolation; -- 查看全局的事务隔离级别
设置数据库隔离级别：
set global TRANSACTION ISOLATION level read COMMITTED;
6. 操作索引（查看、新增、删除）
查看某个表的索引有多种方法。
最简单的就是直接：show index from table_name;
mysql> show index from user_tab;
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+--------------------------------------------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+--------------------------------------------------+---------+------------+
| user_tab | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | | YES | NULL |
| user_tab | 0 | email | 1 | email | A | 4 | NULL | NULL | | BTREE | | | YES | NULL |
| user_tab | 0 | unique_username | 1 | username | A | 4 | NULL | NULL | | BTREE | | | YES | NULL |
| user_tab | 0 | idx_user_id | 1 | user_id | A | 4 | NULL | NULL | YES | BTREE | | user_id字段的唯一索引，确保user_id在整个表中唯一 | YES | NULL |
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+--------------------------------------------------+---------+------------+
也可以直接查看表结构，也可以看到索引：
mysql> show create table user_tab;
如果是新增索引：
ALTER TABLE table_name
ADD INDEX index_name (column1, column2, ...);
删除索引：
ALTER TABLE table_name DROP INDEX index_name;
7. 查看死锁日志
我在排查死锁日志的时候，经常用到
show engine innodb status
这个mysql命令，用于显示 InnoDB 存储引擎的当前状态信息。
主要包括这些：
- 锁信息：包括当前持有的锁、等待的锁以及死锁的历史记录。
- 缓冲池信息：InnoDB 缓冲池的使用情况、脏页的数量、缓冲池中的读写操作等。
- 日志信息：重做日志（redo log）和回滚日志（undo log）的状态。
这是是我之前排查死锁问题，用show engine innodb status看到的日志：
大家如果不知道如何排查mysql死锁问题，可以看我的这篇文章哈：
数据库死锁排查思路分享
8. 查看有哪些数据库、哪些表
如果没有图形界面，我们查看数据库，需要这样的命令：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sys |
| test_db |
| test_db_00 |
| test_db_01 |
| world |
| xxl_job |
+--------------------+
选择某个库，查看它的所有表：
mysql> use test_db;
Database changed
mysql> show tables;
+------------------------+
| Tables_in_test_db |
+------------------------+
| user_info_tab |
| user_score |
| user_score_tab |
| user_tab |
| users |
+------------------------+
9. 查看未提交的事务
SELECT * FROM information_schema.innodb_trx;
这条 SQL 语句用于查看当前 InnoDB 存储引擎中未提交的事务。information_schema.innodb_trx 表提供了关于当前活跃事务的信息，这对于诊断长时间运行的事务、死锁问题或了解事务的当前状态非常有用。
10.查看存储引擎支持情况
有些时候，我们要查看当前数据库服务器支持的存储引擎，可以用这两个命令：
SHOW ENGINES; -- 会列出所有可用的存储引擎以及它们是否默认启用
SELECT * FROM information_schema.ENGINES; --information_schema 数据库包含了关于 MySQL 服务器实例的元数据。你可以查询 ENGINES 表来获取存储引擎的信息。
mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| ndbinfo | NO | MySQL Cluster system information storage engine | NULL | NULL | NULL |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| ndbcluster | NO | Clustered, fault-tolerant tables | NULL | NULL | NULL |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11.查看数据库字符集与排序规则
查询当前数据库的字符集：
SHOW VARIABLES LIKE 'character_set_database';
查询当前数据库的排序规则：
SHOW VARIABLES LIKE 'collation_database';
还可以用这个：
SELECT * FROM information_schema.schemata;
mysql> SELECT * FROM information_schema.schemata;
+--------------+--------------------+----------------------------+------------------------+----------+--------------------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | DEFAULT_ENCRYPTION |
+--------------+--------------------+----------------------------+------------------------+----------+--------------------+
| def | mysql | utf8mb4 | utf8mb4_0900_ai_ci | NULL | NO |
| def | information_schema | utf8mb3 | utf8mb3_general_ci | NULL | NO |
| def | performance_schema | utf8mb4 | utf8mb4_0900_ai_ci | NULL | NO |
| def | sys | utf8mb4 | utf8mb4_0900_ai_ci | NULL | NO |
| def | sakila | utf8mb4 | utf8mb4_0900_ai_ci | NULL | NO |
| def | world | utf8mb4 | utf8mb4_0900_ai_ci | NULL | NO |
| def | test_db | utf8mb4 | utf8mb4_0900_ai_ci | NULL | NO |
| def | test_db_00 | utf8mb4 | utf8mb4_0900_ai_ci | NULL | NO |
| def | test_db_01 | utf8mb4 | utf8mb4_0900_ai_ci | NULL | NO |
| def | xxl_job | utf8mb4 | utf8mb4_unicode_ci | NULL | NO |
+--------------+--------------------+----------------------------+------------------------+----------+--------------------+
12. SQL 导入导出
导出特定的表：
mysqldump -u your_username -p your_database_name table1 table2 > export_file.sql
导出数据库结构而不包含数据：
mysqldump -u your_username -p --no-data your_database_name > structure_only.sql
导入整个 SQL 文件：
mysql -u your_username -p your_database_name
13. 创建表、新增列、在某个字段后新增列
创建表的语句：
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL
);
使用 ALTER TABLE 语句来向表中添加新的列：
ALTER TABLE users ADD COLUMN age INT;
有些时候，我们想把新字段加在某个字段之后，从 MySQL 8.0.19 开始，可以直接使用 AFTER column_name 语法来指定列的位置。例如：
ALTER TABLE users ADD COLUMN age INT AFTER email;
14. 存储过程，插入大量数据
有些时候，我们为了做测试，或者验证，需要往一个表插入很多数据，可以用存储过程~：
假设先有个用户表：
CREATE TABLE user_tab (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) NOT NULL
);
接下来，创建一个存储过程来批量插入数据:
DELIMITER //
CREATE PROCEDURE BatchInsertUsers(IN numUsers INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= numUsers DO
INSERT INTO user_tab (username)
VALUES (CONCAT('user', i));
SET i = i + 1;
END WHILE;
END //
DELIMITER ;
调用这个存储过程并插入数据:
CALL BatchInsertUsers(100);
15.创建视图
所谓视图，它为用户提供了一个虚拟的表结构，这个结构是基于SQL查询定义的。视图本身并不存储实际的数据，而是存储了一个查询语句，当用户查询视图时，数据库系统会执行这个查询语句，并返回结果集给用户，就好像用户正在查询一个实际的表一样。
假设我们的表，进行了分库分表，平时测试环境，如何查这些数据比较方便呢？
就是新建个视图，然后查这个视图就好啦~~
假设你有两个相同的表 user_table_1 和 user_table_2，它们分别存储在不同的分片中，且都有 id 和 username 字段。你可以创建一个视图来查询这两个表中的所有数据：
CREATE VIEW all_users AS
SELECT id, username FROM user_table_1
UNION ALL
SELECT id, username FROM user_table_2;
有了视图，我们通过简单地查询 all_users 视图来获取所有相关数据啦~~
👇🏻 点击下方阅读原文，获取鱼皮的编程学习路线、原创项目教程、求职面试宝典、编程交流圈子。
