如何在 Linux 命令行中运行 MySQL 查询

如果你负责管理数据库服务器，你可能需要不时运行查询并仔细检查结果。虽然你可以在MySQL/MariaDB 终端中执行查询，但这个技巧可以让你直接使用 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

安排自动报告

结合我们所讲的内容，使用以下 bash 脚本并借助 cron 创建自动化的每日报告。

#!/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 命令行或使用繁重的图形界面工具来操作数据库；命令行能够提供速度、自动化能力，并且可以将数据库操作集成到你现有的脚本和工作流程中。

你还有其他想与社区分享的建议吗？如果有，请通过下面的评论表单分享。 :