表达式和函数

更新: 9/4/2025字数: 0 字 时长: 0 分钟

以下是 MySQL 中常用的 表达式 和 函数 分类详解，涵盖数值、字符串、日期、条件判断等高频操作：

---

一、表达式类型

1. 算术表达式

SELECT
  10 + 5 AS 加法,          -- 15
  10 - 5 AS 减法,          -- 5
  10 * 5 AS 乘法,          -- 50
  10 / 3 AS 除法,          -- 3.3333
  10 DIV 3 AS 整除,        -- 3 (取整)
  10 % 3 AS 取模,          -- 1
  POW(2, 3) AS 幂运算;     -- 8

2. 比较表达式

SELECT
  5 = 5 AS 等于,           -- 1 (TRUE)
  5 <> 5 AS 不等于,        -- 0 (FALSE)
  5 > 3 AS 大于,           -- 1
  5 <= 5 AS 小于等于,      -- 1
  'a' <=> NULL AS 安全等于; -- 0 (NULL安全比较)

3. 逻辑表达式

SELECT
  (5 > 3) AND (2 < 4) AS 与,   -- 1
  (5 > 3) OR (2 > 4) AS 或,    -- 1
  NOT (5 > 3) AS 非;           -- 0

---

二、核心函数

1. 字符串函数

SELECT
  CONCAT('Hello', ' ', 'World') AS 拼接,      -- 'Hello World'
  LENGTH('MySQL') AS 字节长度,                -- 5
  CHAR_LENGTH('MySQL') AS 字符长度,           -- 5
  UPPER('mysql') AS 大写,                    -- 'MYSQL'
  LOWER('MySQL') AS 小写,                    -- 'mysql'
  SUBSTRING('MySQL', 2, 3) AS 子串,          -- 'ySQ'
  TRIM('  MySQL  ') AS 去空格,               -- 'MySQL'
  REPLACE('abc', 'b', 'x') AS 替换,          -- 'axc'
  REVERSE('123') AS 反转,                    -- '321'
  LOCATE('y', 'MySQL') AS 查找位置;           -- 2

2. 数值函数

SELECT
  ABS(-5) AS 绝对值,                -- 5
  ROUND(3.1415, 2) AS 四舍五入,     -- 3.14
  CEIL(3.2) AS 向上取整,            -- 4
  FLOOR(3.9) AS 向下取整,           -- 3
  RAND() AS 随机数,                 -- [0,1)随机值
  FORMAT(1234567.89, 2) AS 千分位;  -- '1,234,567.89'

3. 日期时间函数

SELECT
  NOW() AS 当前时间,                  -- '2023-10-25 14:30:00'
  CURDATE() AS 当前日期,              -- '2023-10-25'
  CURTIME() AS 当前时间,              -- '14:30:00'
  DATE_FORMAT(NOW(), '%Y-%m-%d') AS 格式化日期, -- '2023-10-25'
  DATEDIFF('2023-10-31', NOW()) AS 日期差,     -- 6 (天数差)
  DATE_ADD(NOW(), INTERVAL 1 MONTH) AS 加1个月,
  EXTRACT(YEAR FROM NOW()) AS 提取年份;        -- 2023

4. 条件函数

-- CASE WHEN 表达式
SELECT
  name,
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    ELSE 'C'
  END AS grade
FROM students;

-- IF 函数
SELECT
  name,
  IF(score >= 60, '及格', '不及格') AS result
FROM students;

-- NULL 处理
SELECT
  IFNULL(NULL, '默认值') AS 替换NULL,   -- '默认值'
  COALESCE(NULL, NULL, '第一个非NULL值') AS 多参数处理; -- '第一个非NULL值'

5. 聚合函数

SELECT
  COUNT(*) AS 总行数,
  AVG(score) AS 平均分,
  MAX(score) AS 最高分,
  MIN(score) AS 最低分,
  SUM(score) AS 总分,
  GROUP_CONCAT(name SEPARATOR ', ') AS 名字合并
FROM students
GROUP BY class_id;

6. 窗口函数 (MySQL 8.0+)

SELECT
  name,
  score,
  RANK() OVER (ORDER BY score DESC) AS 排名,
  AVG(score) OVER (PARTITION BY class_id) AS 班级平均分
FROM students;

---

三、高级表达式

1. 正则表达式

SELECT 'abc123' REGEXP '^[a-z]+[0-9]+$' AS 是否匹配; -- 1 (TRUE)

2. JSON 处理 (MySQL 5.7+)

SELECT
  JSON_EXTRACT('{"name": "John", "age": 30}', '$.name') AS 提取值, -- '"John"'
  JSON_SET('{"name": "John"}', '$.age', 30) AS 设置值;             -- '{"name": "John", "age": 30}'

---

四、性能注意

1. 避免在 WHERE 条件中对字段使用函数（会导致索引失效）：

   -- ❌ 错误用法（索引失效）
   SELECT * FROM users WHERE YEAR(create_time) = 2023;
   
   -- ✅ 正确用法
   SELECT * FROM users WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';

2. 复杂表达式建议用 EXPLAIN 分析执行计划。