MySQL函数：`LPAD()`和`RPAD()`用特定字符填充字符串到指定长度。

MySQL字符串填充艺术：LPAD()与RPAD() 的深度剖析

大家好，今天我们来深入探讨MySQL中两个非常有用的字符串函数：LPAD() 和 RPAD()。它们的作用是在字符串的左侧或右侧填充指定的字符，直到字符串达到指定的长度。 虽然概念简单，但熟练运用它们可以解决很多实际问题，例如数据格式化、对齐输出等等。

LPAD()：左侧填充

LPAD() 函数用于在字符串的左侧填充指定的字符，使其达到指定的长度。其语法如下：

LPAD(str, len, padstr)

• str: 要填充的字符串。
• len: 填充后的字符串的总长度。
• padstr: 用于填充的字符串。

工作原理：

LPAD()函数会检查 str 的长度。如果 str 的长度小于 len，它会在 str 的左侧重复 padstr，直到字符串的总长度达到 len。 如果 str 的长度大于或等于 len，它会截断 str 到 len 的长度。

示例 1：基本用法

SELECT LPAD('MySQL', 10, '*');

输出：

******MySQL

在这个例子中，'MySQL' 的长度为 5， len 为 10， padstr 为 '*'. 因此，LPAD() 在 'MySQL' 的左侧填充了 5 个 '*'，使字符串的总长度达到 10。

示例 2：填充多个字符

SELECT LPAD('123', 8, '00');

输出：

00000123

这里，padstr 是 '00'。 LPAD() 重复 '00' 直到可以填充到 len 为 8 的长度。

示例 3：字符串长度大于指定长度

SELECT LPAD('HelloWorld', 5, '*');

输出：

Hello

在这种情况下，'HelloWorld' 的长度是 10，大于 len (5)。 因此，LPAD() 截断了 'HelloWorld' 到前 5 个字符。

示例 4：与数字类型结合

在数据库中，我们经常需要格式化数字。 LPAD() 可以方便地实现这个功能。

SELECT LPAD(123, 5, '0');

输出：

00123

注意，MySQL会自动将数字类型转换为字符串类型，然后再进行填充操作。

示例 5：结合 UPDATE 语句使用

假设我们有一个 products 表，其中有一个 product_id 字段是整数类型。 我们希望将 product_id 格式化为 5 位数字，不足 5 位的用 ‘0’ 填充。

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255)
);

INSERT INTO products (product_id, product_name) VALUES
(1, 'Apple'),
(12, 'Banana'),
(123, 'Orange'),
(1234, 'Grape'),
(12345, 'Watermelon');

ALTER TABLE products ADD COLUMN formatted_product_id VARCHAR(5);

UPDATE products SET formatted_product_id = LPAD(product_id, 5, '0');

SELECT * FROM products;

执行以上 SQL 语句后， products 表的内容如下：

product_id	product_name	formatted_product_id
1	Apple	00001
12	Banana	00012
123	Orange	00123
1234	Grape	01234
12345	Watermelon	12345

示例 6：处理 NULL 值

如果 str 是 NULL，LPAD() 会返回 NULL。

SELECT LPAD(NULL, 5, '0');

输出：

NULL

在实际应用中，需要注意处理 NULL 值，避免出现意料之外的结果。 可以使用 IFNULL() 或 COALESCE() 函数来处理 NULL 值。

SELECT LPAD(IFNULL(NULL, ''), 5, '0');

输出：

00000

RPAD()：右侧填充

RPAD() 函数与 LPAD() 函数类似，不同之处在于它是在字符串的右侧填充指定的字符，使其达到指定的长度。 其语法如下：

RPAD(str, len, padstr)

• str: 要填充的字符串。
• len: 填充后的字符串的总长度。
• padstr: 用于填充的字符串。

工作原理：

RPAD()函数会检查 str 的长度。如果 str 的长度小于 len，它会在 str 的右侧重复 padstr，直到字符串的总长度达到 len。 如果 str 的长度大于或等于 len，它会截断 str 到 len 的长度。

示例 1：基本用法

SELECT RPAD('MySQL', 10, '*');

输出：

MySQL******

在这个例子中，'MySQL' 的长度为 5， len 为 10， padstr 为 '*'. 因此，RPAD() 在 'MySQL' 的右侧填充了 5 个 '*'，使字符串的总长度达到 10。

示例 2：填充多个字符

SELECT RPAD('123', 8, '00');

输出：

12300000

这里，padstr 是 '00'。 RPAD() 重复 '00' 直到可以填充到 len 为 8 的长度。

示例 3：字符串长度大于指定长度

SELECT RPAD('HelloWorld', 5, '*');

输出：

Hello

在这种情况下，'HelloWorld' 的长度是 10，大于 len (5)。 因此，RPAD() 截断了 'HelloWorld' 到前 5 个字符。

示例 4：格式化输出

RPAD() 常用于格式化输出，例如在命令行界面中对齐文本。

SELECT RPAD('Name:', 15, ' ') || RPAD('Age:', 5, ' ') || 'City:' ;

输出：

Name:          Age:  City:

这里，我们使用 RPAD() 将 'Name:' 填充到 15 个字符的长度，将 'Age:' 填充到 5 个字符的长度，然后将它们与 'City:' 连接起来，实现了简单的文本对齐。

示例 5：结合 SELECT 语句使用

假设我们有一个 employees 表，包含 employee_id 和 employee_name 字段。 我们希望将 employee_name 字段填充到 20 个字符的长度，不足 20 位的用空格填充，并在查询结果中显示。

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(255)
);

INSERT INTO employees (employee_id, employee_name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie'),
(4, 'David');

SELECT employee_id, RPAD(employee_name, 20, ' ') AS formatted_name FROM employees;

执行以上 SQL 语句后，查询结果如下：

employee_id	formatted_name
1	Alice
2	Bob
3	Charlie
4	David

示例 6：处理 NULL 值

与 LPAD() 类似，如果 str 是 NULL，RPAD() 会返回 NULL。

SELECT RPAD(NULL, 5, '0');

输出：

NULL

同样，需要注意处理 NULL 值，可以使用 IFNULL() 或 COALESCE() 函数来处理 NULL 值。

SELECT RPAD(IFNULL(NULL, ''), 5, '0');

输出：

00000

LPAD() vs RPAD()：选择哪个？

LPAD() 和 RPAD() 的区别在于填充的方向。 LPAD() 从左侧填充，而 RPAD() 从右侧填充。 选择哪个函数取决于具体的应用场景。

• 数字格式化： 通常使用 LPAD()，例如将数字格式化为固定长度的字符串，并在左侧填充 0。
• 文本对齐： RPAD() 常用于文本对齐，例如在命令行界面中对齐文本。
• 其他场景： 根据需要在字符串的左侧或右侧填充字符，选择相应的函数。

函数	描述	典型应用
LPAD(str, len, padstr)	在字符串 str 的左侧填充 padstr，使其达到 len 的长度	数字格式化，例如将数字格式化为固定长度的字符串并在左侧填充 0
RPAD(str, len, padstr)	在字符串 str 的右侧填充 padstr，使其达到 len 的长度	文本对齐，例如在命令行界面中对齐文本

性能考虑

虽然 LPAD() 和 RPAD() 函数非常方便，但在处理大量数据时，需要注意性能问题。 频繁调用这些函数可能会影响查询性能。

优化建议：

• 避免在循环中使用： 尽量避免在循环中使用 LPAD() 和 RPAD() 函数。 如果需要在循环中格式化字符串，可以考虑在应用程序代码中进行格式化。
• 索引： 如果需要在 WHERE 子句中使用 LPAD() 或 RPAD() 函数，可能会导致索引失效。 可以考虑创建函数索引来优化查询性能。
• 预先计算： 如果填充的字符串是固定的，可以预先计算好填充后的字符串，然后直接使用，避免每次都调用 LPAD() 或 RPAD() 函数。

总结 LPAD 和 RPAD 的应用

LPAD() 和 RPAD() 是两个功能强大的字符串函数，可以用于格式化字符串、对齐文本等。掌握它们的使用方法，可以提高 SQL 开发效率。在实际应用中，需要根据具体场景选择合适的函数，并注意性能问题。