MySQL 常用函数及应用案例

以下是 MySQL 常用函数的及使用场景

一、字符串函数

1. CONCAT()
   字符串拼接：SELECT CONCAT('Hello', ' ', 'World'); → Hello World
   结合时间：SELECT CONCAT('当前日期：', CURDATE()); → 当前日期：2025-02-27

2. SUBSTRING()
   截取字符串：SELECT SUBSTRING('MySQL Functions', 6, 5); → Funct

3. LENGTH() / CHAR_LENGTH()
   计算字节长度/字符长度：
   SELECT LENGTH('数据库'), CHAR_LENGTH('数据库'); → 9（UTF-8编码）和 3

4. REPLACE()
   替换文本：SELECT REPLACE('2023-01-01', '2023', '2025'); → 2025-01-01

二、数值函数

1. ROUND()
   四舍五入：SELECT ROUND(3.1415, 2); → 3.14

2. CEIL() / FLOOR()
   向上/向下取整：SELECT CEIL(2.3), FLOOR(2.7); → 3, 2

3. RAND()
   生成随机数：SELECT FLOOR(RAND() * 100); → 0~99的整数

三、日期时间函数

1. NOW() / CURDATE()
   当前时间戳：SELECT NOW(); → 2025-02-27 13:49:00
   当前日期：SELECT CURDATE(); → 2025-02-27

2. DATE_FORMAT()
   格式化日期：

   SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H:%i:%s');  
   → `2025年02月27日 13:49:00`
   

3. DATEDIFF() / TIMESTAMPDIFF()
   计算日期差：

   SELECT DATEDIFF('2025-03-01', CURDATE());  -- 2天
   SELECT TIMESTAMPDIFF(MONTH, '2025-01-01', CURDATE());  -- 1个月
   

四、聚合函数

1. SUM() / AVG()
   求和与平均值：

   SELECT SUM(salary), AVG(age) FROM employees;
   

2. COUNT()
   统计行数：

   SELECT COUNT(*) FROM orders WHERE order_date >= CURDATE();
   

五、控制流函数

1. IF()
   条件判断：

   SELECT IF(DATEDIFF(CURDATE(), '2025-02-20') > 7, '过期', '有效');
   

2. CASE WHEN
   多条件分支：

   SELECT 
     CASE 
       WHEN temperature > 30 THEN '高温'
       WHEN temperature < 10 THEN '低温'
       ELSE '适宜'
     END AS weather_status
   FROM sensors;
   

3. COALESCE()
   处理 NULL 值：SELECT COALESCE(NULL, '默认值'); → 默认值

六、其他实用函数

1. CAST()
   类型转换：SELECT CAST('123' AS SIGNED); → 123

2. JSON_EXTRACT()
   解析 JSON 数据：

   SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name'); → "John"
   

3. 加密函数
   MD5('data'), SHA1('data') 用于生成哈希值。

注意事项

1. 函数兼容性：不同 MySQL 版本可能支持不同函数（如 JSON 函数需 5.7+）
2. 性能优化：聚合函数在大数据量时可能影响查询速度，需结合索引使用
3. 时区处理：日期函数受 time_zone 系统变量影响
4. NULL 处理：多数函数遇到 NULL 参数会返回 NULL，需用 IFNULL() 等函数容错

可根据具体需求组合使用这些函数，例如生成带时间戳的唯一标识：

SELECT CONCAT('ID_', DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), '_', FLOOR(RAND()*1000));

典型应用案例

以下结合MySQL函数特性，提供6类典型应用案例，涵盖数据处理、业务逻辑实现等场景：

一、动态生成业务标识（字符串/日期函数）

场景：电商订单号生成
案例：

SELECT 
  CONCAT(
    'ORD', 
    DATE_FORMAT(NOW(), '%Y%m%d%H%i'),  -- 格式化为202502271351 
    '_', 
    SUBSTRING(MD5(RAND()), 1, 6)       -- 附加6位随机哈希 
  ) AS order_no;

输出：ORD202502271351_a3f8e7
效果：唯一性保障，包含时间戳和随机码，避免重复。

二、用户行为时间分析（日期函数）

场景：计算用户注册至今活跃天数
案例：

SELECT 
  user_id,
  DATEDIFF(CURDATE(), registration_date) AS active_days,
  IF(DATEDIFF(CURDATE(), last_login) <= 7, '活跃', '沉默') AS status 
FROM users;

输出：

user_id | active_days | status  
---------------------------------
101     | 438         | 活跃 
102     | 720         | 沉默 

效果：识别用户活跃周期，支持精细化运营。

三、动态定价策略（控制流/数值函数）

场景：酒店房间节假日溢价
案例：

SELECT 
  room_id,
  base_price,
  CASE 
    WHEN CURDATE() BETWEEN '2025-02-28' AND '2025-03-02' THEN base_price * 1.5  -- 春节假期溢价50%
    WHEN DAYOFWEEK(CURDATE()) IN (6,7) THEN base_price * 1.2                    -- 周末溢价20%
    ELSE base_price 
  END AS final_price 
FROM rooms;

效果：实现节假日/周末自动调价，提升收益。

四、数据清洗与转换（字符串/类型转换）

场景：导入外部文本数据到数值字段
案例：

-- 清理非数字字符并转为整数 
UPDATE raw_data 
SET cleaned_value = CAST(REPLACE(REPLACE(dirty_value, '$', ''), ',', '') AS UNSIGNED)
WHERE dirty_value REGEXP '^[0-9$,]+$';

示例转换："$1,234" → 1234
效果：解决数据格式污染问题，保障后续统计准确性。

五、实时数据看板（聚合函数）

场景：当日销售业绩统计
案例：

SELECT 
  COUNT(*) AS order_count,        -- 订单总数 
  SUM(amount) AS total_sales,     -- 销售总额 
  AVG(delivery_time) AS avg_delivery_hours 
FROM orders 
WHERE order_date >= CURDATE()     -- 统计2025-02-27当日数据 
  AND status = 'completed';

输出：订单数: 356 | 销售额: ¥128,500 | 平均配送耗时: 2.3小时
效果：实时监控核心业务指标。

六、配置文件动态解析（JSON函数）

场景：读取用户个性化设置
案例：

SELECT 
  user_id,
  JSON_UNQUOTE(JSON_EXTRACT(settings, '$.theme')) AS theme,       -- 解析主题配置 
  JSON_EXTRACT(settings, '$.notification.email_enabled') AS email_notify 
FROM user_profiles 
WHERE JSON_CONTAINS_PATH(settings, 'one', '$.notification');      -- 过滤含通知配置的用户 

输出：

user_id | theme    | email_notify  
-------------------------------
205     | dark     | true 
302     | light    | false 

效果：灵活处理半结构化数据，支持个性化服务。

PS:

1. 函数组合：如 CONCAT(DATE_FORMAT(), RAND()) 生成高唯一性业务ID
2. 时区敏感场景：使用 CONVERT_TZ(NOW(), 'UTC', 'Asia/Shanghai') 避免时间误差
3. 性能优化：对 WHERE 条件中的函数计算字段（如 YEAR(order_date)）建立虚拟列索引
4. 防御性编程：用 COALESCE(SUM(amount), 0) 避免空值导致逻辑异常

通过灵活组合函数，可覆盖90%以上的数据处理需求，同时保持代码简洁高效。

————————————————

​最后我们放松一下眼睛