MySQL - ORDER BY 子句

MySQL：使用 ORDER BY 对查询结果进行排序

ORDER BY 子句用于根据一个或多个列对结果集中的行进行排序。默认情况下，结果以任意顺序返回，这可能不可预测。使用 ORDER BY 对于以有意义的方式呈现数据至关重要，无论是按字母顺序排列的名称列表、按时间顺序排列的事件列表，还是按销售额排名的产品列表。

基本语法

SELECT column_list
FROM table_name
WHERE condition
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

• ASC：升序排序（A-Z，0-9）。这是默认值，因此可以省略。
• DESC：降序排序（Z-A，9-0）。

让我们使用一个示例 employees 表进行演示。

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    hire_date DATE NOT NULL,
    salary DECIMAL(10, 2)
);

INSERT INTO employees (first_name, last_name, department, hire_date, salary) VALUES
('Aisha', 'Khan', 'Engineering', '2022-08-15', 95000.00),
('Carlos', 'Garcia', 'Sales', '2021-02-20', 80000.00),
('Mei', 'Lin', 'Engineering', '2021-07-01', 110000.00),
('David', 'Smith', 'Sales', '2022-08-15', 78000.00),
('Zoe', 'Washington', 'Marketing', '2023-01-10', 85000.00),
('Brian', 'O''Connell', 'Engineering', '2023-01-10', 92000.00);

单列排序

要获取按姓氏字母顺序排序的员工列表：

SELECT first_name, last_name, salary
FROM employees
ORDER BY last_name; -- 默认是升序 (ASC)

要查看谁的薪水最高：

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC;

多列排序

你可以按多个列进行排序。结果集首先按第一列排序，然后对于第一列中具有相同值的所有行，再按第二列排序，依此类推。这对于分组和子排序非常有用。

让我们按部门对员工进行排序，并在每个部门内部按入职日期排序，以查看资历。

SELECT department, hire_date, first_name, last_name
FROM employees
ORDER BY department ASC, hire_date ASC;

此查询首先将所有 ‘Engineering’（工程部）员工分组，然后是 ‘Marketing’（市场部），再是 ‘Sales’（销售部）。在 ‘Engineering’ 组内，它会按入职时间先后排序。

高级排序技巧

按表达式排序

你可以根据函数或表达式的结果进行排序。例如，按姓名的长度排序：

SELECT first_name, last_name
FROM employees
ORDER BY LENGTH(first_name) DESC;

处理 NULL 值

默认情况下，MySQL 将 NULL 值视为低于任何非 NULL 值。这意味着在 ASC（升序）排序时 NULL 值会首先出现，在 DESC（降序）排序时会最后出现。你可以通过一个简单的技巧改变这种行为。

-- 让我们添加一个薪水为 NULL 的员工
INSERT INTO employees (first_name, last_name, department, hire_date, salary)
VALUES ('Unknown', 'Intern', 'Engineering', '2023-06-01', NULL);

-- 按薪水排序，但将 NULL 值放在最后
-- ISNULL(salary) 如果 salary 为 NULL 则返回 1，否则返回 0。
-- 所以我们首先按此排序（0 在 1 之前），然后按薪水本身排序。
SELECT first_name, last_name, salary
FROM employees
ORDER BY ISNULL(salary) ASC, salary DESC;

使用 FIELD() 进行自定义排序

有时你需要按特定的、非字母数字顺序进行排序。FIELD() 函数对此非常适用。它返回列表中某个值的索引。

让我们按照特定的部门层级对员工进行排序：首先是工程部，然后是市场部，最后是销售部。

SELECT first_name, last_name, department
FROM employees
ORDER BY FIELD(department, 'Engineering', 'Marketing', 'Sales');

实际应用：在应用程序中获取排序数据

获取排序数据是后端应用程序中的常见任务。下面是一个使用 mysql-connector-python 库的 Python 示例。

使用 mysql-connector-python 的 Python 示例

# 设置：pip install mysql-connector-python python-dotenv
# 创建一个 .env 文件，包含你的数据库凭据

import os
import mysql.connector
from mysql.connector import errorcode
from dotenv import load_dotenv

load_dotenv()

def get_employees_sorted_by(sort_column, sort_order='ASC'):
    """以动态但安全的方式获取员工数据。"""

    # 将允许排序的列加入白名单，以防止 SQL 注入
    allowed_columns = ['first_name', 'last_name', 'hire_date', 'salary']
    if sort_column not in allowed_columns:
        raise ValueError(f'Invalid sort column: {sort_column}')

    # 将排序顺序加入白名单
    if sort_order.upper() not in ['ASC', 'DESC']:
        raise ValueError(f'Invalid sort order: {sort_order}')

    try:
        conn = mysql.connector.connect(
            host=os.getenv('DB_HOST'),
            user=os.getenv('DB_USER'),
            password=os.getenv('DB_PASSWORD'),
            database=os.getenv('DB_NAME')
        )
        cursor = conn.cursor(dictionary=True)

        # 安全地构建查询
        query = f"SELECT first_name, last_name, salary FROM employees ORDER BY {sort_column} {sort_order}"

        print(f'正在执行查询: {query}')
        cursor.execute(query)

        for row in cursor.fetchall():
            print(f"{row['first_name']} {row['last_name']} - 薪水: {row['salary']}")

    except mysql.connector.Error as err:
        print(f'错误: {err}')
    finally:
        if 'conn' in locals() and conn.is_connected():
            cursor.close()
            conn.close()

# --- 用法 ---
print('--- 按薪水降序排序 ---')
get_employees_sorted_by('salary', 'DESC')

print('\n--- 按姓氏升序排序 ---')
get_employees_sorted_by('last_name')

安全警告：切勿直接将用户输入注入到 ORDER BY 子句中。这会导致 SQL 注入。务必在应用程序代码中验证并白名单（whitelist）列名和排序方向，如 Python 示例所示。