沉梦听雨的编程指南
  • 数据库
  • Mysql
  • 实战篇
沉梦听雨
2024-07-13
目录

内置函数小记

# 内置函数小记

在日常开发中，MySQL 内置函数在进行逻辑判断、条件筛选以及数据处理方面发挥了关键作用。

以下是一些常用的 MySQL 内置函数，以及它们在日常开发中的应用：

# case 条件判断表达式

CASE 函数是 SQL 中用于实现条件判断和分支逻辑的关键功能之一。它提供类似编程语言中 if-else 或 switch-case 的功能。

语法：

1、简单 CASE 语法

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE default_result
END
1
2
3
4
5
6
  • expression: 一个待匹配的表达式。
  • value1, value2, ...: 可能的匹配值。
  • result1, result2, ...: 匹配对应值时的返回结果。
  • ELSE: 可选部分，当没有任何 WHEN 匹配成功时返回默认结果。如果没有 ELSE 且无匹配，则返回 NULL。

2、搜索 CASE 语法

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END
1
2
3
4
5
6
  • condition1, condition2, ...: 布尔表达式，用于定义匹配条件。
  • result1, result2, ...: 匹配对应条件时的返回结果。
  • ELSE: 可选部分，与简单 CASE 相同。

描述：

  • CASE 是 ANSI SQL 标准支持的条件表达式，几乎所有主流数据库（如 MySQL、PostgreSQL、SQL Server）都支持。
  • 支持两种模式：
    • 简单 CASE：用于匹配一个表达式的值。
    • 搜索 CASE：用于基于布尔条件进行判断。
  • 可嵌套： 可以将一个 CASE 语句嵌套在另一个 CASE 语句中，适用于复杂逻辑。

应用：

  1. 数据分类

根据字段值对数据分组：

SELECT
    product_name,
    CASE category_id
        WHEN 1 THEN 'Electronics'
        WHEN 2 THEN 'Clothing'
        WHEN 3 THEN 'Books'
        ELSE 'Others'
    END AS category_name
FROM products;
1
2
3
4
5
6
7
8
9
  1. 条件计算

在计算时添加逻辑判断：

SELECT
    order_id,
    quantity,
    price,
    CASE
        WHEN quantity >= 100 THEN price * 0.9 -- 大量购买打折
        WHEN quantity >= 50 THEN price * 0.95
        ELSE price
    END AS final_price
FROM orders;
1
2
3
4
5
6
7
8
9
10
  1. 替换空值或特殊值

将空值替换为默认值：

SELECT
    customer_id,
    CASE
        WHEN last_order_date IS NULL THEN 'No Orders'
        ELSE last_order_date
    END AS order_status
FROM customers;
1
2
3
4
5
6
7
  1. 基于日期范围的逻辑判断

对时间进行分组或分类：

SELECT
    sale_date,
    CASE
        WHEN sale_date < '2024-01-01' THEN 'Past'
        WHEN sale_date BETWEEN '2024-01-01' AND '2024-12-31' THEN 'Current Year'
        ELSE 'Future'
    END AS sale_period
FROM sales;
1
2
3
4
5
6
7
8
  1. 数据更新

在 UPDATE 语句中使用 CASE：

UPDATE employees
SET salary = 
    CASE
        WHEN performance_rating = 'A' THEN salary * 1.1
        WHEN performance_rating = 'B' THEN salary * 1.05
        ELSE salary
    END;
1
2
3
4
5
6
7
  1. 嵌套 CASE 示例

当业务逻辑复杂时，可嵌套多个 CASE：

SELECT
    employee_id,
    salary,
    CASE
    
        WHEN department = 'Sales' THEN
            CASE
                WHEN salary > 70000 THEN 'Top Sales'
                ELSE 'Regular Sales'
            END
            
        WHEN department = 'Engineering' THEN
            CASE
                WHEN salary > 90000 THEN 'Top Engineer'
                ELSE 'Regular Engineer'
            END
            
        ELSE 'Other Department'
        
    END AS category
FROM employees;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

注意事项：

  1. CASE 的返回值类型：
    • 所有 THEN 和 ELSE 的返回值类型必须兼容（如不能混合返回数值和字符串）。
    • 如果返回值类型不一致，数据库可能抛出错误或自动进行隐式类型转换。
  2. ELSE 的作用：
    • 强烈建议提供 ELSE 分支，确保所有情况都有明确的处理，避免返回 NULL。
  3. 性能：
    • CASE 中的条件是按顺序检查的，一旦匹配成功，会直接返回结果，后续条件不再检查。因此，将最可能匹配的条件放在前面可以优化性能。

# if 条件判断

  • 语法：IF(condition, true_value, false_value)
  • **描述：**根据条件的真假返回不同的值。
  • **应用：**用于在查询中进行条件判断，例如在 SELECT 语句中对某个字段进行条件性赋值。
SELECT name, IF(score >= 90, 'A', 'B') AS grade FROM student_scores;
1

# coalesce 提供默认值

  • 语法： COALESCE(value1, value2, ...)
  • 描述： 返回参数列表中的第一个非 NULL 值。如果所有参数都为 NULL，则返回默认值。
  • 应用： 用于处理可能为 NULL 的字段，提供默认值或使用备选值。
SELECT name, COALESCE(score, 0) AS score FROM student_scores;
1

# concat 字符串拼接

  • 语法： CONCAT(str1, str2, ...)
  • 描述： 将多个字符串连接成一个字符串。
  • 应用： 用于拼接字段值，构造需要的字符串。
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
1

# substring 字符串截取

  • 语法： SUBSTRING(str, start, length)
  • 参数说明：
    • str：原始字符串。
    • pos：开始位置（从1开始计数）。支持负数位置，表示从字符串末尾开始计数。
    • len（可选）：提取的长度。如果不指定，从开始位置提取到字符串的末尾。
  • 描述： 返回字符串的子串。
  • 应用： 用于从字符串中提取部分内容，例如截取电话号码中的区号。

SUBSTR 和 SUBSTRING 函数实际上是同一个函数的不同名称，它们的功能完全相同。

SELECT name, SUBSTRING(phone_number, 1, 3) AS area_code FROM contacts;

-- 例如
phone_number = "1234567890"
SUBSTRING(phone_number, 1, 3) = "123"
SUBSTRING(phone_number, 3) = "34567890"

-- 从末尾开始计数
-- '2024-11-08 11:10:13'
SELECT distinct substr(create_time, -14, 5) FROM t_user_info where substr(create_time, 1, 7) = '2024-11';
-- 输出 11-08
1
2
3
4
5
6
7
8
9
10
11

# substring_index 指定分隔符截取

根据指定的分隔符截取字符串的一部分。

UPDATE t_device
SET 
    channel_id = SUBSTRING_INDEX(device_id, ';', -1),
    device_id = SUBSTRING_INDEX(device_id, ';', 1)
WHERE 
    device_id LIKE '%;%';
1
2
3
4
5
6

# cast 转换数据类型

CAST 函数用于将某种数据类型的表达式显式转换为另一种数据类型。CAST() 函数的参数是一个表达式，它包括用 AS 关键字分隔的源值和目标数据类型。

语法：

CAST (expression AS data_type)
1
  • expression：任何有效的 SQServer 表达式。
  • AS：用于分隔两个参数，在 AS 之前的是要处理的数据，在 AS 之后是要转换的数据类型。
  • data_type：目标系统所提供的数据类型，包括 bigint 和 sql_variant，不能使用用户定义的数据类型。

描述：

  • CAST 用于显式地将一个值从一种数据类型转换为另一种数据类型。
  • 它是 ANSI SQL 标准中定义的函数，因此在不同的数据库系统（如 MySQL、SQL Server、PostgreSQL 等）中具有很好的兼容性。
  • 常用于：
    • 数据类型不匹配时的强制转换。
    • 防止数据精度丢失（如整型除法变为浮点除法）。
    • 数据格式化（如将日期或数值转换为字符串）。

应用：

可以转换的类型是有限制的。这个类型可以是以下值其中的一个：

  1. 二进制，同带 binary 前缀的效果: BINARY
  2. 字符型，可带参数：CHAR()
  3. 日期：DATE
  4. 时间: TIME
  5. 日期时间型：DATETIME
  6. 浮点数：DECIMAL
    • DECIMAL(m, n)，其中 m 和 n 是 DECIMAL 类型的小数位数和总位数。
  7. 整数：INT
  8. 无符号整数：UNSIGNED

代码示例

  1. 避免整数除法的精度丢失
SELECT CAST(10 AS FLOAT) / 3; -- 返回 3.333333
SELECT 10 / 3;               -- 返回 3 (整数除法)
1
2
  1. 类型兼容

将字符型转换为日期型，便于执行日期计算：

SELECT CAST('2025-01-06' AS DATE) + INTERVAL '1 DAY'; -- 返回 2025-01-07
1
  1. 数据格式转换

将浮点数转换为整数，截断小数部分：

SELECT CAST(123.456 AS INT); -- 返回 123
1
  1. 用于显示

将非字符串类型转换为字符串，以便拼接或显示：

SELECT 'The result is ' + CAST(123 AS VARCHAR); -- 返回 "The result is 123"
1
  1. 将整数转换为浮点数：
SELECT CAST(123 AS DECIMAL(5, 2));
1

这将把整数 123 转换为浮点数 123.00，保留两位小数。

# round 对数值进行四舍五入操作

语法：

ROUND(expression, length)
1
  • expression: 要处理的数值或表达式。
  • length: 保留的小数位数。为正数时，表示保留小数点右侧的位数；为负数时，表示保留小数点左侧的位数。

描述：

  • ROUND 用于对数值进行四舍五入操作。
  • 不同数据库系统可能对超过精度限制的情况有不同的处理方式：
    • SQL Server：允许 length 为负值，表示对整数部分的位数进行舍入。
    • MySQL：length 默认为 0，表示舍入到整数。
    • PostgreSQL：类似 SQL Server。
  • 常用于：
    • 结果的格式化，保留指定的小数位。
    • 金额、比率等敏感数据的精度控制。
    • 舍入操作以避免数据波动。

应用：

  1. 保留指定的小数位
SELECT ROUND(123.456, 2); -- 返回 123.46
SELECT ROUND(123.451, 2); -- 返回 123.45
1
2
  1. 舍入到整数
SELECT ROUND(123.456, 0); -- 返回 123
SELECT ROUND(123.789, 0); -- 返回 124
1
2
  1. 对整数部分舍入（负数长度）
SELECT ROUND(12345.678, -2); -- 返回 12300 (舍入到百位)
SELECT ROUND(98765, -3);     -- 返回 99000 (舍入到千位)
1
2
  1. 用于计算后的格式化

计算百分比并保留两位小数：

SELECT ROUND((CAST(45 AS FLOAT) / 50) * 100, 2); -- 返回 90.00
1
  1. 控制财务数据精度
SELECT ROUND(123.45678, 2) AS TotalAmount; -- 确保金额只有两位小数
1
  1. 结合应用

经常配合 CAST 函数使用，先通过 CAST 处理数据类型，再通过 ROUND 格式化结果：

SELECT ROUND(CAST(45 AS FLOAT) / 7, 2); -- 结果为 6.43
1

通过这样的组合，可以应对数据类型转换与结果精度控制的双重需求，在数据处理和报表展示中应用广泛。

# 函数大全

好文分享：

  1. 齐全且实用的MySQL函数使用大全_mysql函数大全以及举例-CSDN博客 (opens new window)
  2. MySQL函数大全，MySQL常用函数汇总 (biancheng.net) (opens new window)
上次更新: 2025/1/6 17:59:10
执行计划解析
JSON字段处理

← JSON字段处理→

Theme by Vdoing | Copyright © 2023-2025 沉梦听雨 | MIT License