日常開發，MySQL 一些常用命令

作者：撿田螺的小男孩 2025-01-15 09:00:20

數據庫 MySQL

所謂視圖，它為用戶提供了一個虛擬的表結構，這個結構是基于SQL查詢定義的。視圖本身并不存儲實際的數據，而是存儲了一個查詢語句，當用戶查詢視圖時，數據庫系統會執行這個查詢語句，并返回結果集給用戶，就好像用戶正在查詢一個實際的表一樣。

前言

大家好，我是田螺。

記得之前一位同事，分享他入職的故事。他說，剛來新公司，想查看一個表的索引，居然忘記命令啦~~ 其實一些常用的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看到的日志：

圖片

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 < import_file.sql

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.創建視圖

假設我們的表，進行了分庫分表，平時測試環境，如何查這些數據比較方便呢？

就是新建個視圖，然后查這個視圖就好啦~~

假設你有兩個相同的表 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 視圖來獲取所有相關數據啦~~

責任編輯：武曉燕來源：撿田螺的小男孩