MySQL 高级函数之 LPAD() 和 RPAD(): 字符串填充的应用
各位同学，大家好！今天我们来深入探讨 MySQL 中两个非常有用的字符串函数：LPAD() 和 RPAD()。这两个函数的主要作用是在字符串的左侧或右侧填充指定的字符，直到达到指定的长度。虽然功能简单，但在实际应用中，它们可以帮助我们解决很多格式化和数据处理的问题。
1. LPAD() 函数：左侧填充
LPAD() 函数的功能是在字符串的左侧填充指定的字符，直至达到指定的长度。其语法如下：
LPAD(str, len, padstr)str: 要进行填充的原始字符串。len: 填充后的字符串总长度。padstr: 用于填充的字符串。
工作原理：
LPAD() 函数首先检查 str 的长度。如果 str 的长度已经大于或等于 len，则函数会截断 str，使其长度等于 len，然后返回截断后的字符串。如果 str 的长度小于 len，则函数会在 str 的左侧重复填充 padstr，直到字符串的总长度达到 len。
示例 1：基本填充
假设我们有一个字符串 '123'，我们希望将其填充到长度为 5，使用 '0' 进行填充。
SELECT LPAD('123', 5, '0');结果：
00123在这个例子中，'123' 的长度为 3，小于 5。因此，LPAD() 函数在 '123' 的左侧填充了两个 '0'，使得字符串总长度达到 5。
示例 2：字符串截断
如果原始字符串的长度大于指定的长度，LPAD() 会截断字符串。
SELECT LPAD('ABCDEFG', 5, '0');结果：
ABCDE因为 'ABCDEFG' 的长度为 7，大于 5，所以 LPAD() 函数将其截断为长度为 5 的 'ABCDE'。
示例 3：使用较长的填充字符串
padstr 可以是任意长度的字符串。如果 padstr 的长度大于需要填充的长度，则只会使用 padstr 的一部分。
SELECT LPAD('1', 4, 'XYZ');结果：
XYZ1这里，'1' 的长度为 1，需要填充 3 个字符。padstr 是 'XYZ'。因此，LPAD() 函数会使用 'XYZ' 来填充 '1' 的左侧，结果为 'XYZ1'。
示例 4：处理 NULL 值
如果任何一个参数为 NULL，则 LPAD() 函数返回 NULL。
SELECT LPAD(NULL, 5, '0');
SELECT LPAD('123', NULL, '0');
SELECT LPAD('123', 5, NULL);以上三个查询都将返回 NULL。
2. RPAD() 函数：右侧填充
RPAD() 函数的功能是在字符串的右侧填充指定的字符，直至达到指定的长度。其语法如下：
RPAD(str, len, padstr)str: 要进行填充的原始字符串。len: 填充后的字符串总长度。padstr: 用于填充的字符串。
工作原理：
RPAD() 函数与 LPAD() 函数类似，但它是在字符串的右侧进行填充。如果 str 的长度已经大于或等于 len，则函数会截断 str，使其长度等于 len，然后返回截断后的字符串。如果 str 的长度小于 len，则函数会在 str 的右侧重复填充 padstr，直到字符串的总长度达到 len。
示例 1：基本填充
SELECT RPAD('123', 5, '0');结果：
12300'123' 的长度为 3，小于 5。因此，RPAD() 函数在 '123' 的右侧填充了两个 '0'，使得字符串总长度达到 5。
示例 2：字符串截断
SELECT RPAD('ABCDEFG', 5, '0');结果：
ABCDE与 LPAD() 相同，如果原始字符串的长度大于指定的长度，RPAD() 会截断字符串。
示例 3：使用较长的填充字符串
SELECT RPAD('1', 4, 'XYZ');结果：
1XYZRPAD() 函数使用 'XYZ' 来填充 '1' 的右侧，结果为 '1XYZ'。
示例 4：处理 NULL 值
与 LPAD() 相同，如果任何一个参数为 NULL，则 RPAD() 函数返回 NULL。
SELECT RPAD(NULL, 5, '0');
SELECT RPAD('123', NULL, '0');
SELECT RPAD('123', 5, NULL);以上三个查询都将返回 NULL。
3. 应用场景
LPAD() 和 RPAD() 函数在实际应用中有很多用途，下面列举一些常见的场景：
3.1 格式化数字
在很多情况下，我们需要将数字格式化为特定的长度，例如日期、编号等。LPAD() 函数可以很方便地在数字的左侧填充 '0'，使其达到指定的长度。
-- 生成 5 位数的编号
SELECT LPAD(1, 5, '0'); -- 结果：00001
SELECT LPAD(123, 5, '0'); -- 结果：001233.2 对齐文本
LPAD() 和 RPAD() 函数可以用于在输出文本时进行对齐。例如，在生成报表时，可以使用这些函数来调整文本的显示位置。
-- 假设有一个表格，包含姓名和年龄两列
-- 使用 LPAD 和 RPAD 函数对齐文本
SELECT
LPAD('Name', 10, ' ') AS Name,
RPAD('Age', 5, ' ') AS Age
UNION ALL
SELECT
LPAD('Alice', 10, ' ') AS Name,
RPAD('25', 5, ' ') AS Age
UNION ALL
SELECT
LPAD('Bob', 10, ' ') AS Name,
RPAD('30', 5, ' ') AS Age;结果：
Name Age
---------- -----
Alice 25
Bob 303.3 数据清洗
在数据清洗过程中，有时需要将不同长度的字符串统一为相同的长度。LPAD() 和 RPAD() 函数可以用于在字符串的左侧或右侧填充空格或其他字符，使其达到指定的长度。
-- 将所有字符串填充到长度为 10，右侧填充空格
SELECT RPAD(column_name, 10, ' ') FROM your_table;3.4 生成特定格式的字符串
有时，我们需要生成符合特定格式的字符串，例如文件名称、序列号等。LPAD() 和 RPAD() 函数可以用于在字符串的左侧或右侧填充字符，使其符合指定的格式。
-- 生成文件名称，格式为 "FILE_00001.txt"
SELECT CONCAT('FILE_', LPAD(1, 5, '0'), '.txt'); -- 结果：FILE_00001.txt3.5 密码存储
虽然不建议直接使用 LPAD() 或 RPAD() 进行密码存储，但可以结合其他加密算法，对密码进行一些简单的处理，例如在密码的左侧或右侧添加一些随机字符。 请务必使用安全的哈希算法进行密码存储，例如 bcrypt, Argon2 等。
-- 示例：不安全的密码处理方式，仅用于演示目的
SELECT CONCAT('SALT_', RPAD('password', 20, '*'));3.6 数据脱敏
在数据脱敏处理中，有时需要对敏感数据进行部分遮盖。LPAD() 和 RPAD() 函数可以用于在数据的左侧或右侧填充 * 或其他字符，以达到脱敏的目的。
-- 将手机号码中间四位替换为 ****
-- 假设手机号码存储在 column_phone 中
SELECT
CONCAT(
SUBSTRING(column_phone, 1, 3),
'****',
SUBSTRING(column_phone, 8, 4)
) AS masked_phone
FROM your_table;
--或者结合 LPAD/RPAD 填充 * 号
SELECT RPAD(LPAD(SUBSTRING(column_phone,1,3),7,'*'),11,'*') from your_table;
4. 性能考量
虽然 LPAD() 和 RPAD() 函数非常方便，但在处理大量数据时，也需要考虑性能问题。
-
避免在 WHERE 子句中使用函数： 如果需要在
WHERE子句中使用LPAD()或RPAD()函数，可能会导致无法使用索引，从而降低查询性能。尽量将函数操作移到WHERE子句之外，或者使用预先计算好的值进行比较。 -
尽量减少函数调用次数： 如果需要在循环中多次调用
LPAD()或RPAD()函数，可以考虑将结果缓存起来，避免重复计算。 -
选择合适的填充字符： 填充字符的选择也会影响性能。通常来说，填充单个字符比填充较长的字符串效率更高。
5. 与其他字符串函数的结合使用
LPAD() 和 RPAD() 函数可以与其他字符串函数结合使用，以实现更复杂的功能。
5.1 CONCAT() 函数
CONCAT() 函数用于连接多个字符串。可以将 LPAD() 和 RPAD() 函数与 CONCAT() 函数结合使用，生成特定格式的字符串。
-- 生成文件名称，格式为 "FILE_00001.txt"
SELECT CONCAT('FILE_', LPAD(1, 5, '0'), '.txt');5.2 SUBSTRING() 函数
SUBSTRING() 函数用于提取字符串的一部分。可以将 LPAD() 和 RPAD() 函数与 SUBSTRING() 函数结合使用，对字符串进行截取和填充。
-- 提取字符串的前 5 个字符，并在右侧填充空格，使其长度达到 10
SELECT RPAD(SUBSTRING('ABCDEFG', 1, 5), 10, ' ');5.3 REPLACE() 函数
REPLACE() 函数用于替换字符串中的一部分。可以将 LPAD() 和 RPAD() 函数与 REPLACE() 函数结合使用，对字符串进行替换和填充。
-- 将字符串中的 "A" 替换为 "X"，并在左侧填充 "0"，使其长度达到 10
SELECT LPAD(REPLACE('ABCDEFG', 'A', 'X'), 10, '0');6. 不同数据库系统的兼容性
虽然 LPAD() 和 RPAD() 函数在 MySQL 中非常常见，但在其他数据库系统中，可能使用不同的函数名称或语法来实现相同的功能。
| 数据库系统 | 左侧填充函数 | 右侧填充函数 |
|---|---|---|
| MySQL | LPAD() |
RPAD() |
| SQL Server | RIGHT(REPLICATE(padstr, len) + str, len) |
LEFT(str + REPLICATE(padstr, len), len) |
| PostgreSQL | LPAD() |
RPAD() |
| Oracle | LPAD() |
RPAD() |
在编写跨数据库系统的应用程序时，需要注意这些差异，并根据具体的数据库系统选择合适的函数。
7. 案例分析：生成订单编号
假设我们需要生成订单编号，格式为 YYYYMMDD-XXXX，其中 YYYYMMDD 为当前日期，XXXX 为 4 位数的自增序列。
-- 创建一个表来存储订单信息
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
order_number VARCHAR(20)
);
-- 创建一个存储过程来生成订单编号
DELIMITER //
CREATE PROCEDURE generate_order_number()
BEGIN
DECLARE current_date VARCHAR(8);
DECLARE next_sequence INT;
DECLARE order_number VARCHAR(20);
-- 获取当前日期
SET current_date = DATE_FORMAT(CURDATE(), '%Y%m%d');
-- 获取下一个序列号
SELECT IFNULL(MAX(CAST(SUBSTRING(order_number, 10, 4) AS UNSIGNED)), 0) + 1
INTO next_sequence
FROM orders
WHERE SUBSTRING(order_number, 1, 8) = current_date;
-- 生成订单编号
SET order_number = CONCAT(current_date, '-', LPAD(next_sequence, 4, '0'));
-- 插入订单信息
INSERT INTO orders (order_number) VALUES (order_number);
SELECT order_number;
END //
DELIMITER ;
-- 调用存储过程生成订单编号
CALL generate_order_number();在这个例子中，我们使用 DATE_FORMAT() 函数获取当前日期，并使用 LPAD() 函数将序列号填充到 4 位数。然后，我们使用 CONCAT() 函数将日期和序列号连接起来，生成订单编号。
8. FORMAT() 函数的比较
虽然 LPAD() 和 RPAD() 可以用于格式化数字，但 MySQL 还提供了一个专门用于格式化数字的函数：FORMAT()。FORMAT() 函数可以按照指定的格式将数字格式化为字符串，并可以添加千位分隔符。
SELECT FORMAT(1234567.89, 2); -- 结果：1,234,567.89在需要格式化数字时，建议使用 FORMAT() 函数，因为它更加灵活和方便。
9. 总结：格式化和对齐的利器
总而言之，LPAD() 和 RPAD() 函数是 MySQL 中非常有用的字符串函数，它们可以用于在字符串的左侧或右侧填充指定的字符，使其达到指定的长度。这些函数可以应用于格式化数字、对齐文本、数据清洗、生成特定格式的字符串等多种场景。掌握这两个函数，可以帮助我们更有效地处理字符串数据，提高开发效率。