mysql 前导0处理|极客笔记

mysql 前导0处理

在数据库中，有时我们需要在数字类型的字段中补全前导零，以确保数据的一致性和规范性。比如我们需要将数字类型的字段存储为固定位数的字符，而不足的部分需要用0进行填充。MySQL提供了一些函数和方法来实现这一功能，让我们深入了解一下。

使用 LPAD 函数

MySQL中的 LPAD 函数可以在一个字符串的左侧插入指定的字符，直至字符串达到指定的长度。我们可以使用该函数来在数字类型的字段前添加前导0。

语法如下：

LPAD(str, length, pad);

str：要填充的字符串或字段名
length：最终字符串的长度
pad：用来填充的字符

接下来我们通过一个示例来演示如何使用 LPAD 函数来给数字类型的字段添加前导0。

假设我们有一个表 students，其中有一个字段 student_id 是用来存储学生的学号。我们希望将学号格式化为5位数字，不足的部分用0进行填充。

首先我们创建一个示例表 students：

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    student_id INT
);

INSERT INTO students (name, student_id) VALUES
('Alice', 123),
('Bob', 4567),
('Charlie', 89012);

现在我们想要查询所有学生的格式化后的学号，可以使用以下 SQL 语句：

SELECT id, name, LPAD(student_id, 5, '0') AS formatted_student_id
FROM students;

运行以上 SQL 语句，我们可以看到如下结果：

+----+---------+---------------------+
| id | name    | formatted_student_id |
+----+---------+---------------------+
|  1 | Alice   | 00123               |
|  2 | Bob     | 04567               |
|  3 | Charlie | 89012               |
+----+---------+---------------------+

从结果可以看出，学生学号已经按照我们的要求格式化为了5位数字，不足的部分用0进行填充。

使用 CONCAT 和 LPAD 函数结合

除了直接使用 LPAD 函数外，我们还可以结合 CONCAT 函数来实现对数字类型字段的前导0处理。这种方法在一些特定的场景中可能更方便一些。

通过 CONCAT 函数，我们可以先将0和数字类型字段拼接在一起，然后再使用 LPAD 函数来指定最终字符串的长度。

下面我们来看一个示例，使用 CONCAT 和 LPAD 函数结合来处理前导0。

假设我们仍然有一个表 students，我们可以使用以下 SQL 语句来查询格式化后的学号：

SELECT id, name, LPAD(CONCAT('0', student_id), 5, '0') AS formatted_student_id
FROM students;

运行以上 SQL 语句，可以得到如下结果：

+----+---------+---------------------+
| id | name    | formatted_student_id |
+----+---------+---------------------+
|  1 | Alice   | 00123               |
|  2 | Bob     | 04567               |
|  3 | Charlie | 89012               |
+----+---------+---------------------+

与之前的方法相比，这种结合使用 CONCAT 和 LPAD 函数的方法也可以实现对数字类型字段的前导0处理。