MySQL Zerofill詳解

一、什麼是MySQL Zerofill

MySQL Zerofill是MySQL中一種填充類型(Fill Type)，用於在數值類型的欄位左側填充零，使欄位具有固定的位數。Zerofill填充類型僅適用於整數類型，其主要作用是方便對齊和數據處理。

二、如何使用MySQL Zerofill

使用MySQL Zerofill很簡單，只需要在定義欄位時在數值類型後加上Zerofill關鍵字即可。例如，定義一個固定長度為5位的整數類型num，可以使用以下語句:

CREATE TABLE test (
    id INT ZEROFILL,
    num INT(5) ZEROFILL
);

在插入數據時，如果數據不足指定長度，則會在左側填充零，例如：

INSERT INTO test (id, num) VALUES (23, 123);
INSERT INTO test (id, num) VALUES (56, 9);

插入後，id和num的值分別為：

+----+-------+
| id | num   |
+----+-------+
| 023 | 00123 |
| 056 | 00009 |
+----+-------+

三、MySQL Zerofill的應用場景

MySQL Zerofill可以應用於很多場景，以下列舉幾個典型的例子：

1、日期類型

在使用日期類型存儲年月日時，如果需要保證長度固定，可以使用MySQL Zerofill。例如，定義一個存儲日期的表：

CREATE TABLE date_test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    year INT(4) ZEROFILL,
    month INT(2) ZEROFILL,
    day INT(2) ZEROFILL
);

插入數據時，需要注意月份和日期的長度，如果不足兩位，則需要在左側補零，例如：

INSERT INTO date_test (year, month, day) VALUES (2022, 1, 5);
INSERT INTO date_test (year, month, day) VALUES (2022, 12, 31);

插入後，表中數據如下：

+----+------+-------+-------+
| id | year | month | day   |
+----+------+-------+-------+
|  1 | 2022 | 01    | 05    |
|  2 | 2022 | 12    | 31    |
+----+------+-------+-------+

2、訂單編號

在一些電商網站中，訂單編號可能需要符合一定的規則，例如年月日+流水號等。為了保證訂單編號長度固定，可以使用MySQL Zerofill。例如，定義一個存儲訂單的表：

CREATE TABLE order_test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_num INT(6) UNSIGNED ZEROFILL,
    order_time DATETIME DEFAULT NULL
);

在插入訂單時，需要先生成訂單號，然後將訂單號插入表中：

SET @order_num = CONCAT(YEAR(NOW()), MONTH(NOW()), DAY(NOW()), LPAD(@id, 4, '0'));
INSERT INTO order_test (order_num) VALUES (@order_num);

生成的訂單號為YYYYMMDD0001（當天生成的第一筆訂單），插入後，表中數據如下：

+----+-----------+---------------------+
| id | order_num | order_time          |
+----+-----------+---------------------+
|  1 | 202206090001 | 2022-06-09 12:34:56 |
+----+-----------+---------------------+

3、版本號

在一些軟體中，版本號需要遵循一定的規則，例如主版本號.次版本號.修訂版本號。為了保證版本號長度固定，可以使用MySQL Zerofill。例如，定義一個存儲版本號的表：

CREATE TABLE version_test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    major_version INT(2) UNSIGNED ZEROFILL DEFAULT NULL,
    minor_version INT(2) UNSIGNED ZEROFILL DEFAULT NULL,
    revision_version INT(3) UNSIGNED ZEROFILL DEFAULT NULL
);

在插入版本號時，需要將版本號拆分為主版本號、次版本號和修訂版本號插入表中：

SET @version = '1.2.003';
SET @major_version = SUBSTRING_INDEX(@version, '.', 1);
SET @minor_version = SUBSTR(SUBSTRING_INDEX(@version, '.', 2), LENGTH(@major_version) + 2);
SET @revision_version = SUBSTR(SUBSTRING_INDEX(@version, '.', 3), LENGTH(@major_version) + LENGTH(@minor_version) + 3);
INSERT INTO version_test (major_version, minor_version, revision_version) VALUES (@major_version, @minor_version, @revision_version);

插入後，表中數據如下：

+----+---------------+----------------+------------------+
| id | major_version | minor_version | revision_version |
+----+---------------+----------------+------------------+
|  1 | 01            | 02            | 003            |
+----+---------------+----------------+------------------+

四、總結

MySQL Zerofill是一種非常實用的填充類型，在很多場景中都可以發揮重要作用。使用Zerofill可以方便對齊和數據處理，提高了數據的可讀性和穩定性。在實際應用中，需要根據具體需求選擇合適的Zerofill長度和類型，確保數據的準確性和規範性。