【MySQL】函数

个人主页：Guiat
归属专栏：MySQL

正文

1. 函数概述

MySQL函数是执行特定操作并返回结果的内置例程，它们可以大大简化数据处理和转换过程。MySQL提供了丰富的内置函数，并支持创建自定义函数。

1.1 函数的作用

• 数据转换和格式化
• 执行数学计算
• 处理字符串和日期
• 条件逻辑处理
• 聚合和统计操作

1.2 MySQL函数的分类

1.3 函数的用法

2. 字符串函数

2.1 字符串连接和操作

-- 字符串连接
SELECT CONCAT('Hello ', 'World') AS greeting;  -- 'Hello World'
SELECT CONCAT_WS(', ', 'John', 'Doe', 'New York') AS address;  -- 'John, Doe, New York'

-- 字符串长度
SELECT LENGTH('MySQL') AS byte_length;  -- 5 (字节数)
SELECT CHAR_LENGTH('MySQL') AS char_length;  -- 5 (字符数)

-- 大小写转换
SELECT UPPER('hello') AS uppercase;  -- 'HELLO'
SELECT LOWER('HELLO') AS lowercase;  -- 'hello'

-- 修剪字符串
SELECT TRIM('  MySQL  ') AS trimmed;  -- 'MySQL'
SELECT LTRIM('  MySQL  ') AS left_trimmed;  -- 'MySQL  '
SELECT RTRIM('  MySQL  ') AS right_trimmed;  -- '  MySQL'
SELECT TRIM(BOTH '!' FROM '!!!MySQL!!!') AS trim_chars;  -- 'MySQL'

-- 提取子字符串
SELECT SUBSTRING('MySQL Database', 7) AS substring_from;  -- 'Database'
SELECT SUBSTRING('MySQL Database', 1, 5) AS substring_range;  -- 'MySQL'
SELECT LEFT('MySQL', 2) AS left_chars;  -- 'My'
SELECT RIGHT('MySQL', 3) AS right_chars;  -- 'SQL'

-- 替换字符串
SELECT REPLACE('MySQL Database', 'Database', 'Functions') AS replaced;  -- 'MySQL Functions'
SELECT INSERT('MySQL', 3, 2, 'NewSQL') AS inserted;  -- 'MyNewSQLL'

-- 格式化字符串
SELECT FORMAT(12345.678, 2) AS formatted_number;  -- '12,345.68'
SELECT LPAD('MySQL', 10, '*') AS left_padded;  -- '*****MySQL'
SELECT RPAD('MySQL', 10, '*') AS right_padded;  -- 'MySQL*****'

2.2 查找和比较

-- 位置查找
SELECT LOCATE('SQL', 'MySQL Database') AS position;  -- 3
SELECT POSITION('SQL' IN 'MySQL Database') AS position;  -- 3
SELECT INSTR('MySQL Database', 'SQL') AS position;  -- 3

-- 比较函数
SELECT STRCMP('MySQL', 'MySQL') AS comparison;  -- 0 (相等)
SELECT STRCMP('MySQL', 'mysql') AS comparison;  -- 0 (大小写不敏感)
SELECT STRCMP('MySQL', 'MariaDB') AS comparison;  -- 1 (第一个大于第二个)

-- 模式匹配
SELECT 'MySQL' LIKE 'My%' AS pattern_match;  -- 1 (TRUE)
SELECT 'MySQL' REGEXP '^My.*L$' AS regex_match;  -- 1 (TRUE)

2.3 高级字符串函数

-- 字符串分割
SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2) AS extract_domain;  -- 'www.mysql'
SELECT SUBSTRING_INDEX('www.mysql.com', '.', -1) AS extract_tld;  -- 'com'

-- 重复字符串
SELECT REPEAT('MySQL ', 3) AS repeated;  -- 'MySQL MySQL MySQL '

-- 反转字符串
SELECT REVERSE('MySQL') AS reversed;  -- 'LQSyM'

-- 空格函数
SELECT SPACE(5) AS spaces;  -- '     '

-- 字符串转义
SELECT QUOTE('Don\'t') AS quoted;  -- 'Don\'t'

-- 计算字符出现次数
SELECT (LENGTH('Mississippi') - LENGTH(REPLACE('Mississippi', 'i', ''))) / LENGTH('i') AS count_i;  -- 4

3. 数值函数

3.1 基本数学运算

-- 基本运算
SELECT ABS(-15) AS absolute_value;  -- 15
SELECT SIGN(-15) AS sign_value;  -- -1 (负数)
SELECT SIGN(0) AS sign_value;  -- 0
SELECT SIGN(15) AS sign_value;  -- 1 (正数)
SELECT MOD(15, 4) AS modulo;  -- 3 (余数)
SELECT 15 % 4 AS modulo;  -- 3 (与MOD相同)
SELECT POW(2, 3) AS power;  -- 8 (2的3次方)
SELECT SQRT(16) AS square_root;  -- 4

-- 舍入函数
SELECT ROUND(15.7) AS rounded;  -- 16
SELECT ROUND(15.2) AS rounded;  -- 15
SELECT ROUND(15.678, 2) AS rounded_decimal;  -- 15.68
SELECT FLOOR(15.7) AS floor_value;  -- 15 (向下取整)
SELECT CEILING(15.2) AS ceiling_value;  -- 16 (向上取整)
SELECT TRUNCATE(15.678, 2) AS truncated;  -- 15.67 (截断到指定小数位)

3.2 高级数学函数

-- 三角函数
SELECT SIN(PI()/2) AS sine;  -- 1
SELECT COS(PI()) AS cosine;  -- -1
SELECT TAN(PI()/4) AS tangent;  -- 1

-- 对数函数
SELECT LOG(10) AS natural_log;  -- 2.302585... (自然对数)
SELECT LOG10(100) AS log10;  -- 2
SELECT LOG(2, 8) AS log_base_2;  -- 3

-- 指数函数
SELECT EXP(1) AS e_value;  -- 2.718281... (自然常数e)

-- 常数
SELECT PI() AS pi_value;  -- 3.141592...

3.3 随机数和类型转换

-- 随机数
SELECT RAND() AS random_number;  -- 0到1之间的随机数
SELECT FLOOR(RAND() * 10) AS random_int;  -- 0到9之间的随机整数
SELECT FLOOR(1 + RAND() * 10) AS random_1_to_10;  -- 1到10之间的随机整数

-- 数值类型转换
SELECT CAST('15.7' AS DECIMAL(10,2)) AS decimal_value;  -- 15.70
SELECT CAST('2023-01-15' AS DATE) AS date_value;  -- '2023-01-15'
SELECT CONVERT('15.7', DECIMAL(10,2)) AS decimal_value;  -- 15.70

4. 日期和时间函数

4.1 日期获取和格式化

-- 获取当前日期和时间
SELECT NOW() AS current_datetime;  -- '2023-12-01 12:34:56'
SELECT CURRENT_TIMESTAMP() AS current_datetime;  -- '2023-12-01 12:34:56'
SELECT CURDATE() AS current_date;  -- '2023-12-01'
SELECT CURRENT_DATE() AS current_date;  -- '2023-12-01'
SELECT CURTIME() AS current_time;  -- '12:34:56'
SELECT CURRENT_TIME() AS current_time;  -- '12:34:56'

-- 日期和时间格式化
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') AS formatted_date;  -- '2023-12-01'
SELECT DATE_FORMAT(NOW(), '%b %d, %Y') AS formatted_date;  -- 'Dec 01, 2023'
SELECT DATE_FORMAT(NOW(), '%W, %M %e, %Y') AS formatted_date;  -- 'Friday, December 1, 2023'
SELECT TIME_FORMAT(CURTIME(), '%h:%i %p') AS formatted_time;  -- '12:34 PM'

4.2 日期时间提取和计算

-- 日期部分提取
SELECT YEAR('2023-12-01') AS year_part;  -- 2023
SELECT MONTH('2023-12-01') AS month_part;  -- 12
SELECT DAY('2023-12-01') AS day_part;  -- 1
SELECT DAYNAME('2023-12-01') AS day_name;  -- 'Friday'
SELECT MONTHNAME('2023-12-01') AS month_name;  -- 'December'
SELECT DAYOFWEEK('2023-12-01') AS day_of_week;  -- 6 (1=Sunday, 2=Monday, ...)
SELECT WEEKDAY('2023-12-01') AS weekday;  -- 4 (0=Monday, 1=Tuesday, ...)
SELECT DAYOFYEAR('2023-12-01') AS day_of_year;  -- 335
SELECT WEEK('2023-12-01') AS week_of_year;  -- 48
SELECT QUARTER('2023-12-01') AS quarter;  -- 4

-- 时间部分提取
SELECT HOUR('12:34:56') AS hour_part;  -- 12
SELECT MINUTE('12:34:56') AS minute_part;  -- 34
SELECT SECOND('12:34:56') AS second_part;  -- 56

-- 统一提取函数
SELECT EXTRACT(YEAR FROM '2023-12-01') AS year_part;  -- 2023
SELECT EXTRACT(MONTH FROM '2023-12-01') AS month_part;  -- 12
SELECT EXTRACT(DAY FROM '2023-12-01') AS day_part;  -- 1
SELECT EXTRACT(HOUR FROM '12:34:56') AS hour_part;  -- 12

-- 日期计算
SELECT DATE_ADD('2023-12-01', INTERVAL 7 DAY) AS date_plus_7_days;  -- '2023-12-08'
SELECT DATE_SUB('2023-12-01', INTERVAL 1 MONTH) AS date_minus_1_month;  -- '2023-11-01'
SELECT ADDDATE('2023-12-01', 7) AS date_plus_7_days;  -- '2023-12-08'
SELECT SUBDATE('2023-12-01', 7) AS date_minus_7_days;  -- '2023-11-24'
SELECT ADDTIME('12:34:56', '1:2:3') AS time_plus;  -- '13:36:59'
SELECT SUBTIME('12:34:56', '1:2:3') AS time_minus;  -- '11:32:53'

-- 日期时间差值
SELECT DATEDIFF('2023-12-15', '2023-12-01') AS days_difference;  -- 14
SELECT TIMESTAMPDIFF(HOUR, '2023-12-01 12:00:00', '2023-12-02 14:00:00') AS hours_difference;  -- 26
SELECT TIMESTAMPDIFF(MINUTE, '12:34:56', '14:45:37') AS minutes_difference;  -- 131 (如果是当天)

4.3 日期时间格式化选项

DATE_FORMAT常用格式说明符：

-- 格式化示例
SELECT DATE_FORMAT('2023-12-01 13:45:30', '%W, %M %e, %Y at %h:%i %p') AS formatted;
-- 'Friday, December 1, 2023 at 01:45 PM'

5. 条件函数

5.1 IF和CASE表达式

-- IF函数
SELECT IF(10 > 5, 'Greater', 'Less') AS result;  -- 'Greater'
SELECT IF(stock > 0, 'In Stock', 'Out of Stock') AS status
FROM products;

-- CASE表达式 (简单CASE)
SELECT 
    product_name,
    CASE category_id
        WHEN 1 THEN 'Electronics'
        WHEN 2 THEN 'Clothing'
        WHEN 3 THEN 'Books'
        ELSE 'Other'
    END AS category_name
FROM products;

-- CASE表达式 (搜索CASE)
SELECT 
    product_name,
    price,
    CASE
        WHEN price < 10 THEN 'Budget'
        WHEN price BETWEEN 10 AND 50 THEN 'Mid-range'
        WHEN price > 50 THEN 'Premium'
        ELSE 'Unknown'
    END AS price_range
FROM products;

5.2 NULL值处理

-- IFNULL函数
SELECT IFNULL(middle_name, '') AS middle_name
FROM customers;  -- 如果middle_name为NULL，返回空字符串

-- COALESCE函数 (返回第一个非NULL值)
SELECT COALESCE(phone, mobile, email, 'No contact') AS contact
FROM customers;

-- NULLIF函数 (如果两个表达式相等，返回NULL)
SELECT NULLIF(10, 10) AS result;  -- NULL
SELECT NULLIF(10, 20) AS result;  -- 10

-- ISNULL函数
SELECT ISNULL(NULL) AS result;  -- 1 (TRUE)
SELECT ISNULL(123) AS result;  -- 0 (FALSE)

5.3 最值函数

-- GREATEST函数 (返回最大值)
SELECT GREATEST(10, 5, 20, 15) AS max_value;  -- 20
SELECT GREATEST('apple', 'orange', 'banana') AS max_string;  -- 'orange' (字母顺序最大)

-- LEAST函数 (返回最小值)
SELECT LEAST(10, 5, 20, 15) AS min_value;  -- 5
SELECT LEAST('apple', 'orange', 'banana') AS min_string;  -- 'apple' (字母顺序最小)

6. 聚合函数

6.1 基本聚合

-- 计数
SELECT COUNT(*) AS total_rows FROM products;  -- 所有行数
SELECT COUNT(product_id) AS total_products FROM products;  -- 非NULL product_id行数
SELECT COUNT(DISTINCT category_id) AS category_count FROM products;  -- 不同category_id的数量

-- 求和
SELECT SUM(price) AS total_price FROM products;
SELECT SUM(price * quantity) AS inventory_value FROM products;

-- 平均值
SELECT AVG(price) AS average_price FROM products;
SELECT AVG(IFNULL(discount, 0)) AS average_discount FROM products;  -- NULL值替换为0再计算平均值

-- 最大值和最小值
SELECT MAX(price) AS highest_price FROM products;
SELECT MIN(price) AS lowest_price FROM products;
SELECT MAX(order_date) AS latest_order FROM orders;  -- 最近订单日期

6.2 统计聚合

-- 标准差和方差
SELECT STD(price) AS price_std FROM products;  -- 样本标准差
SELECT STDDEV(price) AS price_std FROM products;  -- 同上
SELECT STDDEV_POP(price) AS price_std_pop FROM products;  -- 总体标准差
SELECT VARIANCE(price) AS price_variance FROM products;  -- 样本方差
SELECT VAR_POP(price) AS price_var_pop FROM products;  -- 总体方差

-- 组连接
SELECT 
    category_id,
    GROUP_CONCAT(product_name) AS products
FROM products
GROUP BY category_id;

-- 带选项的组连接
SELECT 
    category_id,
    GROUP_CONCAT(
        product_name 
        ORDER BY price DESC 
        SEPARATOR ' | '
    ) AS products
FROM products
GROUP BY category_id;

6.3 窗口聚合函数

-- 行号
SELECT 
    product_id,
    product_name,
    category_id,
    price,
    ROW_NUMBER() OVER (ORDER BY price DESC) AS price_rank
FROM products;

-- 按分类分组排序
SELECT 
    product_id,
    product_name,
    category_id,
    price,
    ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS category_price_rank
FROM products;

-- 累计总和
SELECT 
    order_id,
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

-- 移动平均
SELECT 
    order_date,
    amount,
    AVG(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_average_3day
FROM orders;

7. 加密与安全函数

7.1 哈希和加密函数

-- 哈希函数
SELECT MD5('password') AS md5_hash;  -- 128位哈希值
SELECT SHA1('password') AS sha1_hash;  -- 160位哈希值
SELECT SHA2('password', 256) AS sha256_hash;  -- 256位SHA2哈希

-- 对称加密函数
SELECT AES_ENCRYPT('sensitive data', 'encryption_key') AS encrypted_data;
SELECT AES_DECRYPT(encrypted_column, 'encryption_key') AS decrypted_data
FROM secure_table;

-- 编码函数
SELECT TO_BASE64('MySQL') AS base64_encoded;  -- 'TXlTUUw='
SELECT FROM_BASE64('TXlTUUw=') AS base64_decoded;  -- 'MySQL'

-- 压缩函数
SELECT COMPRESS('long text data') AS compressed_data;
SELECT UNCOMPRESS(compressed_column) AS uncompressed_data
FROM compressed_table;

7.2 安全实践示例

-- 密码存储最佳实践
-- 注意: 实际应用中应使用专门的密码哈希库而不是MD5
CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(64) NOT NULL,
    salt VARCHAR(32) NOT NULL
);

-- 创建密码哈希和盐
SET @salt = MD5(RAND());
INSERT INTO users (username, password_hash, salt)
VALUES ('john_doe', SHA2(CONCAT('user_password', @salt), 256), @salt);

-- 验证密码
SELECT user_id FROM users 
WHERE username = 'john_doe' 
AND password_hash = SHA2(CONCAT('entered_password', salt), 256);

结语
感谢您的阅读！期待您的一键三连！欢迎指正！