MySQLを利用して、IDを０埋めでデータベースに登録したい

Question

初めて質問させていただきます。
SQLを勉強しています。
宜しくお願いします。

前提・実現したいこと

データベースに、０埋めの状態でIDを登録したい。

発生している問題・エラーメッセージ

０埋めでIDを登録できず、「000008」を期待しても「8」と登録されてしまう。

該当のソースコード

下記のコードを使用して、user_idを6桁の連番（6桁に満たない場合は０埋め）で登録したいのですが上手くいきません。
現在、連番で登録することはできます。０埋めができません。

ソースコード
INSERT INTO userinfo VALUES (LPAD(user_id, 6, '0'), "mailaddress", "name");

テーブル名：userinfo
カラム情報：
user_id INT(6) not null primary key AUTO_INCREMENT
mail_address VARCHAR(50)not null
user_name VARCHAR(30) not null

※SELECT LPAD(user_id, 6, '0') FROM userinfo;
下記のSQL文を実行すると、すでに登録されているIDが「000001」「000002」のように出力できました

ソースコード
INSERT INTO userinfo VALUES (LPAD(user_id, 6, '0'), "mailaddress", "name");

試したこと

INSERT INTO userinfo VALUES (LPAD(user_id, 6, 0), "mailaddress", "name");

INSERT INTO userinfo (user_id, mail_address, user_name) VALUES (LPAD(user_id, 6, '0'), "mailaddress", "name");

補足情報（FW/ツールのバージョンなど）

MySQL、STS-3.9.9

Comments

[m.ts10806]

なぜそうするのでしょうか。

Answer 1

user_id INT(6) という定義なので、ゼロ埋めしてINSERTしてもただの数字としてINSERTされます。
どうしてもゼロ埋めしてDBに保存したいのであれば、テーブル定義を変更して user_id varchar(6) のように文字列として保存する型に変更する必要があります。

Comments

[退会済みユーザー]

お返事下さり、ありがとうございます。
INT型で定義しているので、DBには「1」という数値として登録されてしまうのですね、、
定義を「varchar」に変更して対応します。ありがとうございました。

[退会済みユーザー]

> 定義を「varchar」に変更して対応します。

主キーとして使う、かつ、AUTO_INCREMENT したいのではないのですか？　であれば、対策としてそれは違うのではないですか？

Answer 2

たぶん連番とかに使いたいのではないかと想像していますが・・・

user_id INT(6) not null primary key AUTO_INCREMENT

そのような ID はテーブルの中でレコードを一意に識別するだけに使うもので、それ以外の目的で使うものではないと認識するのか良さそうです。

連番が必要なら別の手段を使った方が良いと思います。レコードを削除したり、INSERT に失敗してロールバックされたりすると欠番となってしまいますから。

Comments

[退会済みユーザー]

アドバイスを下さり、ありがとうございます。
とても勉強になりました。

Answer 3

user_id INT(6) not null primary key AUTO_INCREMENT ZEROFILLとしてはどうでしょうか。

Comments

[退会済みユーザー]

お返事下さり、ありがとうございます。

Answer 4

CREATE TABLE `userinfo` (
  `id` int(6) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `mail_address` VARCHAR(50)not null,
  `user_name` VARCHAR(30) not null ,
  PRIMARY KEY (`id`)
);

こんな感じで、id に zerofill をつけると、ゼロ埋めになります。

Comments

[退会済みユーザー]

お返事して下さり、ありがとうございます。

ご教示頂いコードでテーブルを作成し直したのですが、
やはりinsertすると、IDが「000001」ではなく「1」と登録されてしまいます

[phper.k]

> やはりinsertすると、IDが「000001」ではなく「1」と登録されてしまいます
やり方間違っているのでは？

[退会済みユーザー]

お返事下さりありがとうございます。
zerofillにをつけると０埋め出来るのですね！ありがとうございます

Answer 5

数値でよければこうします

create table tbl(id int(6) unsigned zerofill,val varchar(10));
insert into tbl values
(1,'aaa'),(200,'bbb'),(1234567,'ccc');

Comments

[yambejp]

サンプルのように6桁してのところに7桁の数字をいれると
桁あふれしますが見てくれが悪い以外実害はありまぜん

[退会済みユーザー]

お返事下さり、ありがとうございます。
zerofillの使い方を知れて勉強になりました。ありがとうございます。

[yambejp]

みんな同じ回答になっているので、別案として仮想生成列を
お伝えしておきます（桁あふれしたときに下6桁取る）

create table tbl(id int(6) ,val varchar(10),vid varchar(6) as (right(CAST(1000000+id as char),6)));
insert into tbl(id,val) values
(1,'aaa'),(200,'bbb'),(1234567,'ccc');