日常開(kāi)發(fā)，MySQL 一些常用命令
前言
大家好，我是田螺。
記得之前一位同事，分享他入職的故事。他說(shuō)，剛來(lái)新公司，想查看一個(gè)表的索引，居然忘記命令啦~~ 其實(shí)一些常用的mysql命令，雖然網(wǎng)上也是很快能查到，但還是都記住比較好~ 這樣會(huì)顯得你基礎(chǔ)很扎實(shí)！
本文總結(jié)了我日常工作，常用的mysql命令。小伙伴們收藏起來(lái)，慢慢看哈！
1. 連接mysql的命令
我們經(jīng)常需要連接mysql數(shù)據(jù)庫(kù)，用以下命令：
mysql -u username -p -h host_name -P port_number有些時(shí)候，我們要遠(yuǎn)程連接 MySQL，也是同樣道理：
mysql -u username -p -h remote_host_ip -P 3306- remote_host_ip：遠(yuǎn)程 MySQL 服務(wù)器的 IP 地址。
- 3306：MySQL 默認(rèn)端口（如果是其他端口，修改為相應(yīng)端口）。
2. 查看當(dāng)前 MySQL 正在運(yùn)行的所有線程及其狀態(tài)
show processlist;SHOW PROCESSLIST 命令返回一個(gè)包含當(dāng)前活動(dòng)的連接線程的列表，每個(gè)連接線程的狀態(tài)、運(yùn)行的查詢等信息。它對(duì)于診斷性能問(wèn)題、查看阻塞查詢、監(jiān)控?cái)?shù)據(jù)庫(kù)健康狀態(tài)非常有用。
3. 查看系統(tǒng)變量
很多時(shí)候，我們需要查看mysql的一些變量。比如，你要查看是否開(kāi)啟了慢查詢?nèi)罩荆?/p>
show variables like 'slow_query_log';而有些伙伴可能會(huì)這樣查，加了個(gè)GLOBAL：
show global variables like 'slow_query_log';- show variables like 'slow_query_log'; 默認(rèn)查詢的是當(dāng)前會(huì)話（連接）或?qū)嵗淖兞俊?/li>
- show global variables 顯式地查詢的是 全局變量，即當(dāng)前整個(gè) MySQL 實(shí)例的配置。
其實(shí)除了慢查詢?nèi)掌谑欠耖_(kāi)啟，還有很多配置變量查詢（大家如果要查其他變量，類似這樣就好），如下:
show global variables like 'sync_binlog';
圖片
sync_binlog 的作用：用于設(shè)置 MySQL 在寫入二進(jìn)制日志時(shí)的同步策略。
- 如果設(shè)置為 1，表示 每次寫操作后都強(qiáng)制將二進(jìn)制日志刷寫到磁盤，以確保數(shù)據(jù)持久性。
- 如果設(shè)置為0，表示不強(qiáng)制每次寫操作后刷新二進(jìn)制日志，而是通過(guò)操作系統(tǒng)的緩存來(lái)控制。這種設(shè)置通常會(huì)帶來(lái)更好的性能，但在崩潰恢復(fù)時(shí)可能會(huì)丟失一部分?jǐn)?shù)據(jù)。
4.查看加鎖信息
有些時(shí)候，我們看某個(gè)SQL加了什么鎖，可以這樣（MySQL 8.0+版本）：
SELECT * FROM performance_schema.data_locks\G;它用于查詢MySQL數(shù)據(jù)庫(kù)中當(dāng)前持有的和請(qǐng)求的數(shù)據(jù)鎖信息。這些信息包括鎖的類型、狀態(tài)、持有者等
圖片
5. 查看和設(shè)置隔離級(jí)別
有些時(shí)候，我們需要查看數(shù)據(jù)庫(kù)的隔離級(jí)別、或者設(shè)置隔離級(jí)別。
select @@tx_isolation; -- 查看當(dāng)前會(huì)話的事務(wù)隔離級(jí)別
select @@global.tx_isolation; -- 查看全局的事務(wù)隔離級(jí)別設(shè)置數(shù)據(jù)庫(kù)隔離級(jí)別：
set global TRANSACTION ISOLATION level read COMMITTED;6. 操作索引（查看、新增、刪除）
查看某個(gè)表的索引有多種方法。
最簡(jiǎn)單的就是直接：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在整個(gè)表中唯一 | YES | NULL |
+----------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+--------------------------------------------------+---------+------------+也可以直接查看表結(jié)構(gòu)，也可以看到索引：
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. 查看死鎖日志
我在排查死鎖日志的時(shí)候，經(jīng)常用到
show engine innodb status這個(gè)mysql命令，用于顯示 InnoDB 存儲(chǔ)引擎的當(dāng)前狀態(tài)信息。
主要包括這些：
- 鎖信息：包括當(dāng)前持有的鎖、等待的鎖以及死鎖的歷史記錄。
- 事務(wù)信息：當(dāng)前活躍的事務(wù)、事務(wù)的等待狀態(tài)等。
- 緩沖池信息：InnoDB 緩沖池的使用情況、臟頁(yè)的數(shù)量、緩沖池中的讀寫操作等。
- 日志信息：重做日志（redo log）和回滾日志（undo log）的狀態(tài)。
- 行操作統(tǒng)計(jì)：比如每秒插入、更新、刪除的行數(shù)。
這是是我之前排查死鎖問(wèn)題，用show engine innodb status看到的日志：
圖片
8. 查看有哪些數(shù)據(jù)庫(kù)、哪些表
如果沒(méi)有圖形界面，我們查看數(shù)據(jù)庫(kù)，需要這樣的命令：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sys |
| test_db |
| test_db_00 |
| test_db_01 |
| world |
| xxl_job |
+--------------------+選擇某個(gè)庫(kù)，查看它的所有表：
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. 查看未提交的事務(wù)
SELECT * FROM information_schema.innodb_trx;這條 SQL 語(yǔ)句用于查看當(dāng)前 InnoDB 存儲(chǔ)引擎中未提交的事務(wù)。information_schema.innodb_trx 表提供了關(guān)于當(dāng)前活躍事務(wù)的信息，這對(duì)于診斷長(zhǎng)時(shí)間運(yùn)行的事務(wù)、死鎖問(wèn)題或了解事務(wù)的當(dāng)前狀態(tài)非常有用。
10.查看存儲(chǔ)引擎支持情況
有些時(shí)候，我們要查看當(dāng)前數(shù)據(jù)庫(kù)服務(wù)器支持的存儲(chǔ)引擎，可以用這兩個(gè)命令：
SHOW ENGINES; -- 會(huì)列出所有可用的存儲(chǔ)引擎以及它們是否默認(rèn)啟用
SELECT * FROM information_schema.ENGINES; --information_schema 數(shù)據(jù)庫(kù)包含了關(guān)于 MySQL 服務(wù)器實(shí)例的元數(shù)據(jù)。你可以查詢 ENGINES 表來(lái)獲取存儲(chǔ)引擎的信息。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.查看數(shù)據(jù)庫(kù)字符集與排序規(guī)則
查詢當(dāng)前數(shù)據(jù)庫(kù)的字符集：
SHOW VARIABLES LIKE 'character_set_database';查詢當(dāng)前數(shù)據(jù)庫(kù)的排序規(guī)則：
SHOW VARIABLES LIKE 'collation_database';還可以用這個(gè)：
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 導(dǎo)入導(dǎo)出
導(dǎo)出特定的表：
mysqldump -u your_username -p your_database_name table1 table2 > export_file.sql導(dǎo)出數(shù)據(jù)庫(kù)結(jié)構(gòu)而不包含數(shù)據(jù)：
mysqldump -u your_username -p --no-data your_database_name > structure_only.sql導(dǎo)入整個(gè) SQL 文件：
mysql -u your_username -p your_database_name < import_file.sql13. 創(chuàng)建表、新增列、在某個(gè)字段后新增列
創(chuàng)建表的語(yǔ)句：
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL
);使用 ALTER TABLE 語(yǔ)句來(lái)向表中添加新的列：
ALTER TABLE users ADD COLUMN age INT;有些時(shí)候，我們想把新字段加在某個(gè)字段之后，從 MySQL 8.0.19 開(kāi)始，可以直接使用 AFTER column_name 語(yǔ)法來(lái)指定列的位置。例如：
ALTER TABLE users ADD COLUMN age INT AFTER email;14. 存儲(chǔ)過(guò)程，插入大量數(shù)據(jù)
有些時(shí)候，我們?yōu)榱俗鰷y(cè)試，或者驗(yàn)證，需要往一個(gè)表插入很多數(shù)據(jù)，可以用存儲(chǔ)過(guò)程~：
假設(shè)先有個(gè)用戶表：
CREATE TABLE user_tab (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) NOT NULL
);接下來(lái)，創(chuàng)建一個(gè)存儲(chǔ)過(guò)程來(lái)批量插入數(shù)據(jù):
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 ;調(diào)用這個(gè)存儲(chǔ)過(guò)程并插入數(shù)據(jù):
CALL BatchInsertUsers(100);15.創(chuàng)建視圖
所謂視圖，它為用戶提供了一個(gè)虛擬的表結(jié)構(gòu)，這個(gè)結(jié)構(gòu)是基于SQL查詢定義的。視圖本身并不存儲(chǔ)實(shí)際的數(shù)據(jù)，而是存儲(chǔ)了一個(gè)查詢語(yǔ)句，當(dāng)用戶查詢視圖時(shí)，數(shù)據(jù)庫(kù)系統(tǒng)會(huì)執(zhí)行這個(gè)查詢語(yǔ)句，并返回結(jié)果集給用戶，就好像用戶正在查詢一個(gè)實(shí)際的表一樣。
假設(shè)我們的表，進(jìn)行了分庫(kù)分表，平時(shí)測(cè)試環(huán)境，如何查這些數(shù)據(jù)比較方便呢？
就是新建個(gè)視圖，然后查這個(gè)視圖就好啦~~
假設(shè)你有兩個(gè)相同的表 user_table_1 和 user_table_2，它們分別存儲(chǔ)在不同的分片中，且都有 id 和 username 字段。你可以創(chuàng)建一個(gè)視圖來(lái)查詢這兩個(gè)表中的所有數(shù)據(jù)：
CREATE VIEW all_users AS
SELECT id, username FROM user_table_1
UNION ALL
SELECT id, username FROM user_table_2;有了視圖，我們通過(guò)簡(jiǎn)單地查詢 all_users 視圖來(lái)獲取所有相關(guān)數(shù)據(jù)啦~~
