如何從 Linux 命令行執行 MySQL 查詢

如果你負責管理資料庫伺服器，你可能需要不時執行查詢並仔細檢查。雖然你可以從 MySQL/MariaDB shell 來操作，但這個技巧將允許你直接使用 Linux 命令列執行 MySQL/MariaDB 查詢，並將輸出保存到檔案以便稍後檢查（如果查詢返回大量記錄，這會特別有用）。

在我們進入更進階的查詢之前，先來看看一些從命令列直接執行 MYSQL 查詢的簡單範例。

設定範例資料庫

在我們開始探討指令之前，先建立本指南中將使用的範例資料庫，這樣你就可以跟著操作並在自己的系統上練習這些技巧。

建立 tecmintdb 資料庫

首先，讓我們建立 tecmintdb 資料庫以及 tutorials 資料表：

mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS tecmintdb;"

接下來，要在資料庫 tecmintdb 中建立名為 tutorials 的資料表，請執行以下指令：

sudo mysql -u root -p tecmintdb << 'EOF'
CREATE TABLE IF NOT EXISTS tutorials (
    tut_id INT NOT NULL AUTO_INCREMENT,
    tut_title VARCHAR(100) NOT NULL,
    tut_author VARCHAR(40) NOT NULL,
    submission_date DATE,
    PRIMARY KEY (tut_id)
);

INSERT INTO tutorials (tut_title, tut_author, submission_date) VALUES
('Getting Started with Linux', 'John Smith', '2024-01-15'),
('Advanced Bash Scripting', 'Sarah Johnson', '2024-02-20'),
('MySQL Database Administration', 'Mike Williams', '2024-03-10'),
('Apache Web Server Configuration', 'Emily Brown', '2024-04-05'),
('Python for System Administrators', 'David Lee', '2024-05-12'),
('Docker Container Basics', 'Lisa Anderson', '2024-06-18'),
('Kubernetes Orchestration', 'Robert Taylor', '2024-07-22'),
('Linux Security Hardening', 'Jennifer Martinez', '2024-08-30');
EOF

驗證資料是否已插入：

sudo mysql -u root -p -e "USE tecmintdb; SELECT * FROM tutorials;"

建立員工資料庫

現在，讓我們建立一個更複雜的 employees 資料庫，包含多個相關的資料表，這就是我們將用於進階查詢範例的資料庫：

sudo mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS employees;"

建立 employees 資料表：

sudo mysql -u root -p employees << 'EOF'
CREATE TABLE IF NOT EXISTS employees (
    emp_no INT NOT NULL AUTO_INCREMENT,
    birth_date DATE NOT NULL,
    first_name VARCHAR(14) NOT NULL,
    last_name VARCHAR(16) NOT NULL,
    gender ENUM('M','F') NOT NULL,
    hire_date DATE NOT NULL,
    PRIMARY KEY (emp_no)
);

INSERT INTO employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES
(10001, '1953-09-02', 'Georgi', 'Facello', 'M', '1984-06-02'),
(10002, '1964-06-02', 'Bezalel', 'Simmel', 'F', '1984-11-21'),
(10003, '1959-12-03', 'Parto', 'Bamford', 'M', '1984-08-28'),
(10004, '1954-05-01', 'Chirstian', 'Koblick', 'M', '1984-12-01'),
(10005, '1955-01-21', 'Kyoichi', 'Maliniak', 'M', '1984-09-15'),
(10006, '1953-04-20', 'Anneke', 'Preusig', 'F', '1985-02-18'),
(10007, '1957-05-23', 'Tzvetan', 'Zielinski', 'F', '1985-03-20'),
(10008, '1958-02-19', 'Saniya', 'Kalloufi', 'M', '1984-07-11'),
(10009, '1952-04-19', 'Sumant', 'Peac', 'F', '1985-02-18'),
(10010, '1963-06-01', 'Duangkaew', 'Piveteau', 'F', '1984-08-24');
EOF

建立 salaries 資料表：

sudo mysql -u root -p employees << 'EOF'
CREATE TABLE IF NOT EXISTS salaries (
    emp_no INT NOT NULL,
    salary INT NOT NULL,
    from_date DATE NOT NULL,
    to_date DATE NOT NULL,
    PRIMARY KEY (emp_no, from_date),
    FOREIGN KEY (emp_no) REFERENCES employees(emp_no) ON DELETE CASCADE
);

INSERT INTO salaries (emp_no, salary, from_date, to_date) VALUES
(10001, 60117, '1984-06-02', '1985-06-02'),
(10001, 62102, '1985-06-02', '1986-06-02'),
(10001, 66074, '1986-06-02', '9999-01-01'),
(10002, 65828, '1984-11-21', '1985-11-21'),
(10002, 65909, '1985-11-21', '9999-01-01'),
(10003, 40006, '1984-08-28', '1985-08-28'),
(10003, 43616, '1985-08-28', '9999-01-01'),
(10004, 40054, '1984-12-01', '1985-12-01'),
(10004, 42283, '1985-12-01', '9999-01-01'),
(10005, 78228, '1984-09-15', '1985-09-15'),
(10005, 82507, '1985-09-15', '9999-01-01'),
(10006, 40000, '1985-02-18', '1986-02-18'),
(10006, 43548, '1986-02-18', '9999-01-01'),
(10007, 56724, '1985-03-20', '1986-03-20'),
(10007, 60605, '1986-03-20', '9999-01-01'),
(10008, 46671, '1984-07-11', '1985-07-11'),
(10008, 48584, '1985-07-11', '9999-01-01'),
(10009, 60929, '1985-02-18', '1986-02-18'),
(10009, 64604, '1986-02-18', '9999-01-01'),
(10010, 72488, '1984-08-24', '1985-08-24'),
(10010, 74057, '1985-08-24', '9999-01-01');
EOF

建立 departments 表以進行更複雜的聯接：

sudo mysql -u root -p employees << 'EOF'
CREATE TABLE IF NOT EXISTS departments (
    dept_no CHAR(4) NOT NULL,
    dept_name VARCHAR(40) NOT NULL,
    PRIMARY KEY (dept_no),
    UNIQUE KEY (dept_name)
);

INSERT INTO departments (dept_no, dept_name) VALUES
('d001', 'Marketing'),
('d002', 'Finance'),
('d003', 'Human Resources'),
('d004', 'Production'),
('d005', 'Development'),
('d006', 'Quality Management');
EOF

建立 dept_emp 資料表以將 employees 與 departments 連結:

sudo mysql -u root -p employees << 'EOF'
CREATE TABLE IF NOT EXISTS dept_emp (
    emp_no INT NOT NULL,
    dept_no CHAR(4) NOT NULL,
    from_date DATE NOT NULL,
    to_date DATE NOT NULL,
    PRIMARY KEY (emp_no, dept_no),
    FOREIGN KEY (emp_no) REFERENCES employees(emp_no) ON DELETE CASCADE,
    FOREIGN KEY (dept_no) REFERENCES departments(dept_no) ON DELETE CASCADE
);

INSERT INTO dept_emp (emp_no, dept_no, from_date, to_date) VALUES
(10001, 'd005', '1984-06-02', '9999-01-01'),
(10002, 'd005', '1984-11-21', '9999-01-01'),
(10003, 'd004', '1984-08-28', '9999-01-01'),
(10004, 'd004', '1984-12-01', '9999-01-01'),
(10005, 'd003', '1984-09-15', '9999-01-01'),
(10006, 'd005', '1985-02-18', '9999-01-01'),
(10007, 'd004', '1985-03-20', '9999-01-01'),
(10008, 'd005', '1984-07-11', '9999-01-01'),
(10009, 'd006', '1985-02-18', '9999-01-01'),
(10010, 'd006', '1984-08-24', '9999-01-01');
EOF

確認所有設定正確無誤：

sudo mysql -u root -p -e "USE employees; SHOW TABLES;"

現在你已經用範例資料設置好了兩個資料庫，你可以跟著本指南中的所有範例操作。tecmintdb 資料庫非常適合簡單查詢，而 employees 資料庫則讓你練習更複雜的操作，例如連接查詢和聚合操作。

基本查詢執行

要查看伺服器上的所有資料庫，您可以使用以下指令：

sudo mysql -u root -p -e "show databases;"

接下來，要在資料庫 tecmintdb 中建立名為 tutorials 的資料表，請執行以下指令：

sudo mysql -u root -p -e "USE tecmintdb; CREATE TABLE tutorials(tut_id INT NOT NULL AUTO_INCREMENT, tut_title VARCHAR(100) NOT NULL, tut_author VARCHAR(40) NOT NULL, submissoin_date DATE, PRIMARY KEY (tut_id));"

將 MySQL 查詢結果保存到檔案

我們將使用以下命令，並將輸出透過管道傳送到 tee 命令，接著指定我們想要存儲輸出的檔案名稱。

作為示範，我們將使用一個名為 employees 的資料庫，以及 employees 與 salaries 資料表之間的一個簡單連接。在你自己的情況中，只需在引號內輸入 SQL 查詢並按下 Enter 鍵。

請注意，系統會提示您輸入資料庫使用者的密碼：

sudo mysql -u root -p -e "USE employees; SELECT DISTINCT A.first_name, A.last_name FROM employees A JOIN salaries B ON A.emp_no = B.emp_no WHERE hire_date < '1985-01-31';" | tee queryresults.txt

使用 cat 指令查看查詢結果。

cat queryresults.txt

從命令列執行 MySQL/MariaDB 查詢

將查詢結果存成純文字檔後，你可以更輕鬆地使用其他指令列工具來處理這些資料。既然你已經了解了基礎知識，讓我們來探討一些更進階的技巧，讓你的指令列資料庫操作更加高效。

格式化輸出以提升可讀性

預設的表格格式非常適合在終端機中查看，但有時你需要不同的格式。你可以以垂直格式輸出結果，這在處理有很多欄位的表格時特別有用：

sudo mysql -u root -p -e "USE employees; SELECT * FROM employees LIMIT 1\G"

結尾的 \G 將每一列垂直顯示，而不是以表格形式呈現，因此你看到的不是擁擠的水平表格，而是類似於這樣的方式：

*************************** 1. row ***************************
    emp_no: 10001
birth_date: 1953-09-02
first_name: Georgi
 last_name: Facello
    gender: M
 hire_date: 1984-06-02

匯出到 CSV 格式

當你需要將查詢結果匯入試算表應用程式或其他工具時，CSV 格式是你的最佳選擇：

sudo mysql -u root -p -e "USE employees; SELECT first_name, last_name, hire_date FROM employees WHERE hire_date < '1985-01-31';" | sed 's/\t/,/g' > employees.csv

這會將輸出通過 sed 將製表符替換為逗號，從而創建一個可以在 Excel、LibreOffice Calc 或任何其他試算表軟體中乾淨打開的正確 CSV 檔案。

無需密碼提示即可執行查詢

如果你正在使用 cron 任務或腳本自動化資料庫操作，你不想每次都手動輸入密碼，這時 MySQL 配置文件就派上用場了。

在 ~/.my.cnf 建立一個檔案，並輸入您的憑證：

[client]
user=root
password=your_password_here

然後將其保護起來，只有你能閱讀：

chmod 600 ~/.my.cnf

現在您可以在不使用 -p 標誌且不被提示的情況下執行查詢：

mysql -e "SHOW DATABASES;"

請記住，將密碼儲存在純文字檔案中具有安全風險，因此只應在您能控制存取的伺服器上使用此方法，並且在生產環境中應考慮使用 MySQL 更安全的驗證方法。

執行複雜的多行查詢

有時候你的查詢過於複雜，無法用單一命令行完成，尤其是在處理多重連接、子查詢或複雜條件時。

你可以將你的 SQL 放在一個檔案中並執行它：

cat > complex_query.sql << 'EOF'
USE employees;
SELECT 
    e.first_name,
    e.last_name,
    d.dept_name,
    s.salary
FROM employees e
INNER JOIN dept_emp de ON e.emp_no = de.emp_no
INNER JOIN departments d ON de.dept_no = d.dept_no
INNER JOIN salaries s ON e.emp_no = s.emp_no
WHERE e.hire_date BETWEEN '1985-01-01' AND '1985-12-31'
    AND s.from_date = (
        SELECT MAX(from_date) 
        FROM salaries 
        WHERE emp_no = e.emp_no
    )
ORDER BY s.salary DESC
LIMIT 10;
EOF

現在執行它。

sudo mysql -u root -p < complex_query.sql > top_earners_1985.txt

這種方法可以讓你的查詢保持有序且可重複使用，而且你可以像管理其他程式碼一樣，用 git 來進行版本控制。

批次處理多個查詢

如果你需要執行多個相關查詢並分別保存每個結果，你可以編寫腳本來做：

#!/bin/bash

QUERIES=(
    "SELECT COUNT(*) as total_employees FROM employees"
    "SELECT dept_name, COUNT(*) as employee_count FROM dept_emp de JOIN departments d ON de.dept_no = d.dept_no GROUP BY dept_name"
    "SELECT YEAR(hire_date) as year, COUNT(*) as hires FROM employees GROUP BY YEAR(hire_date) ORDER BY year"
)

FILENAMES=(
    "total_count.txt"
    "dept_distribution.txt"
    "yearly_hires.txt"
)

for i in "${!QUERIES[@]}"; do
    echo "Running query $((i+1))..."
    mysql -u root -p -e "USE employees; ${QUERIES[$i]}" > "${FILENAMES[$i]}"
    echo "Results saved to ${FILENAMES[$i]}"
done

將此保存為腳本，使用 chmod +x 使其可執行，這樣你就有了一個可重複使用的批量查詢工具。

監控長時間執行的查詢

當你執行可能需要一段時間的查詢時，你希望能看到進度，或者至少知道它們仍在運行。

將查詢與狀態輸出結合：

(sudo mysql -u root -p -e "USE employees; SELECT COUNT(*) FROM large_table WHERE complex_condition;" && echo "Query completed at $(date)") | tee query_log.txt

對於更長的查詢，請在後台執行它們，並監控 MySQL 的進程列表：

sudo mysql -u root -p -e "USE employees; SELECT * FROM massive_table;" > output.txt &
sudo watch -n 5 'mysql -u root -p -e "SHOW PROCESSLIST\G" | grep -A 5 "SELECT"'

這會在背景中執行你的查詢，同時每隔 5 秒顯示一次進程列表，這樣你可以看到它仍在運行以及已經完成了多少進度。

篩選與處理結果

一旦你將查詢結果保存到文本文件中，就可以使用標準的 Linux 工具進一步處理它們。以下是一些有用的範例：

計算結果列的數量（不包括標題行）：

tail -n +2 queryresults.txt | wc -l

使用 awk 提取特定欄位：

awk '{print $1, $3}' queryresults.txt

在結果中搜尋特定模式：

grep -i "engineering" dept_distribution.txt

按數字欄位排序結果：

tail -n +2 queryresults.txt | sort -k3 -n

處理特殊字符與大型數據集

當你的資料包含特殊字元、分頁或換行時，預設的輸出可能會變得混亂，因此使用 --batch 和 --raw 選項可以獲得更乾淨的輸出：

sudo mysql -u root -p --batch --raw -e "SELECT description FROM products WHERE category='electronics';" > products.txt

對於返回數百萬行的查詢，你可能會遇到記憶體問題，與其將所有資料一次載入記憶體，不如分批串流結果：

sudo mysql -u root -p --quick -e "SELECT * FROM huge_table;" | gzip > huge_results.txt.gz

--quick 選項告訴 MySQL 一次檢索一行，而不是緩衝整個結果集，並且通過 gzip 管道即時壓縮輸出，可以節省磁碟空間。

建立快速資料庫備份

雖然這在技術上並不是執行查詢，但你可以使用類似的命令列技巧，透過 mysqldump 指令快速建立資料庫備份。

sudo mysqldump -u root -p employees | gzip > employees_backup_$(date +%Y%m%d).sql.gz

或者只備份特定的表格：

sudo mysqldump -u root -p employees employees salaries | gzip > critical_tables_$(date +%Y%m%d).sql.gz

排程自動報告

結合我們所學的所有內容，使用 cron 並借助以下 bash 腳本來建立自動化的每日報告。

#!/bin/bash
REPORT_DATE=$(date +%Y-%m-%d)
REPORT_FILE="/var/reports/daily_stats_${REPORT_DATE}.txt"

{
    echo "Database Statistics Report - ${REPORT_DATE}"
    echo "=========================================="
    echo
    
    echo "Total Employees:"
    mysql -e "USE employees; SELECT COUNT(*) FROM employees;"
    echo
    
    echo "New Hires This Month:"
    mysql -e "USE employees; SELECT COUNT(*) FROM employees WHERE MONTH(hire_date) = MONTH(CURRENT_DATE()) AND YEAR(hire_date) = YEAR(CURRENT_DATE());"
    echo
    
    echo "Department Distribution:"
    mysql -e "USE employees; SELECT d.dept_name, COUNT(*) as count FROM dept_emp de JOIN departments d ON de.dept_no = d.dept_no WHERE de.to_date = '9999-01-01' GROUP BY d.dept_name ORDER BY count DESC;"
    
} > "$REPORT_FILE"

echo "Report generated: $REPORT_FILE"

將其新增到 cron 中，每天早上 6 點運行：

0 6 * * * /usr/local/bin/generate_db_report.sh

摘要

我們已經分享了幾個 Linux 技巧，作為系統管理員的你，在自動化日常 Linux 任務或更輕鬆地執行這些任務時，可能會覺得很有用。

這裡的重點是，你並不總是需要啟動 MySQL 命令行介面或使用繁重的圖形化工具來操作資料庫；使用命令行可以提供速度、自動化功能，並讓你能將資料庫操作整合到現有的 Shell 腳本和工作流程中。

你還有其他想與社群分享的建議嗎？如果有，請使用下方的評論表單分享。 :