SQL初心者でも安心！MySQLで列の先頭にゼロを挿入する3つのテクニック
MySQLで列の値の先頭にゼロを追加する方法
LPAD()関数を使用する
LPAD()関数は、文字列を指定した長さに左パディングする関数です。以下のクエリを使用して、列 col の値の先頭にゼロを追加できます。
SELECT LPAD(col, 4, '0') AS padded_col FROM your_table;
このクエリは、col 列の値を4文字の長さに左パディングし、新しい列 padded_col に格納します。パディング文字としてゼロが使用されます。
SUBSTRING()関数とCONCAT()関数を使用する
SUBSTRING()関数とCONCAT()関数を使用して、列の値の先頭にゼロを追加することもできます。以下のクエリは、col 列の値の先頭にゼロを追加し、新しい列 padded_col に格納します。
SELECT CONCAT(
SUBSTRING(REPEAT('0', 4 - LENGTH(col)), 1, LENGTH(col)),
col
) AS padded_col
FROM your_table;
このクエリは、以下の手順を実行します。
REPEAT('0', 4 - LENGTH(col))を使用して、col列の値の長さに達するまでゼロを繰り返します。SUBSTRING()関数を使用して、繰り返されたゼロ文字列の先頭からcol列の値の長さの部分文字列を取得します。CONCAT()関数を使用して、取得した部分文字列とcol列の値を連結します。
ZEROFILL属性を使用する
MySQL 5.7以降では、ZEROFILL属性を使用して、列の値を自動的にゼロパディングできます。以下のクエリを使用して、col 列を作成し、値を自動的にゼロパディングします。
CREATE TABLE your_table (
col INT ZEROFILL
);
このクエリは、col という名前の列を作成し、その列の値を自動的にゼロパディングします。
どの方法を使用するか
使用する方法は、状況によって異なります。
- 少数の行に対してゼロパディングする場合は、
LPAD()関数を使用するのが最も簡単です。 - 多くの行に対してゼロパディングする場合は、
SUBSTRING()関数とCONCAT()関数を使用すると、パフォーマンスが向上する場合があります。 - 列の値を常にゼロパディングする場合は、
ZEROFILL属性を使用すると、クエリを記述する必要がなくなります。
- 上記のクエリは、列
colが数値型であることを前提としています。列が文字列型の場合は、適切な型変換関数を使用する必要があります。 - ゼロパディングする桁数は、必要に応じて変更できます。
このコードは、以下のことを行います。
customersテーブルからすべてのレコードを選択します。id列の値の先頭にゼロを追加し、新しい列padded_idを作成します。- 結果を
id、padded_id、およびnameの列を含む新しいテーブルに格納します。
SELECT
id,
LPAD(id, 4, '0') AS padded_id,
name
FROM customers
INTO new_table;
このコードを実行すると、以下の結果が得られます。
| id | padded_id | name |
|---|---|---|
| 1 | 0001 | John Doe |
| 2 | 0002 | Jane Doe |
| 3 | 0003 | Peter Jones |
このコードは、SUBSTRING()関数とCONCAT()関数を使用して、列の値の先頭にゼロを追加する方法を示すバリエーションとして変更することもできます。
SELECT
id,
CONCAT(
SUBSTRING(REPEAT('0', 4 - LENGTH(id)), 1, LENGTH(id)),
id
) AS padded_id,
name
FROM customers
INTO new_table;
このコードは、ZEROFILL属性を使用して、列の値を自動的にゼロパディングする方法を示すバリエーションとして変更することもできます。
CREATE TABLE new_table (
id INT ZEROFILL,
name VARCHAR(255)
);
INSERT INTO new_table (id, name)
SELECT id, name FROM customers;
このバリエーションでは、CREATE TABLE ステートメントを使用して、new_table という名前の新しいテーブルを作成します。id 列は INT 型で、ZEROFILL 属性が指定されています。これにより、id 列の値は自動的にゼロパディングされます。
次に、INSERT INTO ステートメントを使用して、customers テーブルからすべてのレコードを new_table テーブルに挿入します。
UPDATE クエリを使用して、既存の列の値を更新し、先頭にゼロを追加することができます。以下のクエリは、customers テーブルの id 列の値の先頭にゼロを追加します。
UPDATE customers
SET id = LPAD(id, 4, '0');
このクエリは、customers テーブルのすべてのレコードを更新し、id 列の値の先頭にゼロを追加します。
ビューを使用する
ビューを使用して、列の値の先頭にゼロを追加したように見える列を作成することもできます。以下のクエリは、customers テーブルに基づいて、padded_id という名前の新しいビューを作成します。このビューには、id 列の値の先頭にゼロが表示されます。
CREATE VIEW padded_customers AS
SELECT
id,
LPAD(id, 4, '0') AS padded_id,
name
FROM customers;
このクエリを実行すると、padded_customers という名前の新しいビューが作成されます。このビューには、id 列の値の先頭にゼロが表示されます。
トリガーを使用する
トリガーを使用して、列の値が変更されるたびに、その値の先頭にゼロを追加することができます。以下のトリガーは、customers テーブルの id 列の値が変更されるたびに、その値の先頭にゼロを追加します。
CREATE TRIGGER pad_id BEFORE UPDATE ON customers
FOR EACH ROW
BEGIN
SET NEW.id = LPAD(NEW.id, 4, '0');
END;
このトリガーを作成すると、customers テーブルの id 列の値が更新されるたびに、その値の先頭に自動的にゼロが追加されます。
- 既存のデータを一度だけゼロパディングする場合は、
UPDATEクエリを使用するのが最も簡単です。 - 既存のデータと新しいデータを常にゼロパディングする場合は、ビューを使用すると便利です。
- 列の値が更新されるたびにゼロパディングする必要がある場合は、トリガーを使用するのが最善の方法です。
mysql sql database
