電話番号カラムのデータ型とその扱い[PHP][MYSQL][データ型][int型][integer][桁あふれ][telカラム]

今日はPHPとMysqlのトピックです。WebサービスではUsersテーブルにユーザーの個人情報を格納しておくのはよくある事。ユーザーの名前、パス、年齢、性別、住所などですね。今回はその中で「電話番号」のカラムを作っていて１０分ほどハマったこととその解決法をご紹介します。

まず、Userテーブルにユーザーの個人情報を格納するカラム群をつくります。データ型を設定してこの段階では特に落とし穴も見当たらないように思えます。user_nameはvarchar(255)くらいでidをAUTO_INCREMENTにして、私も常識的に考えて作ってました。ですが電話番号カラム「tel」に排水溝程度の落とし穴がありました。

問題は「tel」カラムのデータ型にありました。int(11)にして電話番号１１桁いれたら

Mysql「そんなにデカイの入るか(ﾟдﾟ)ﾊﾞｰｶ（Warning: #1264 Out of range value for column 'カラム名' at row 1）」

って言われました。無知とはなんとも恐ろしいもの、つい癖で整数のカラムを全部int型にしてました。int型は2147483647までしか入らないということを知りませんでした。でも、Mysqlさんも整数系の他のデータ型にしろって教えてくれればいいのにね。ちょっと調べてみるとtelカラムのデータ型をchar(11)にするなどという邪道が載っていました。そんな小技ではなく、あくまで王道を行きたいのが人情というもの。そこでもっと大きな桁が入る整数系のデータ型がないか調べましたらありました。

DBonline: http://www.dbonline.jp/mysql/type/

TINYINT
-128から127 (符号無しの場合0から255)

SMALLINT
-32768から32767 (符号無しの場合0から65535)

MEDIUMINT
-8388608から8388607 (符号無しの場合0から16777215)

INT
-2147483648から2147483647 (符号無しの場合0から4294967295)
別名：INTEGER

BIGINT
-9223372036854775808から9223372036854775807
(符号無しの場合0から18446744073709551615)

だそうです。それで telカラムをBIGINT(11)に変えたらMysqlさんもちゃんとデータを入れてくれました。

Mysql「おっと、整数型だから先頭の0は消えちゃうんだぜ」
でもまだこれで終わりではありませんでした。たいていの電話番号は先頭が０です。整数型ですので先頭の０が消されてしまいます。

MYSQLマニュアル
(5.0.3 より前の) 古いバージョンの MySQL とは異なり、MySQL 5.1 内の DECIMAL カラムは、先頭の + キャラクタや - キャラクタ、あるいは先頭の 0 の桁を格納しません。 DECIMAL(5,1) カラムの中に +0003.1 を挿入すると、それは 3.1 と格納されます。負の数では、リテラルの - キャラクタは格納されません。古い性能に依存するアプリケーションはこの変更を考慮するように改良を施さなければなりません。

で、解決法を例によって探しました。ありました。

CUI版

DBonline: http://www.dbonline.jp/mysql/type/
データ型にZEROFILLを付けるとデータ型の桁数分だけ0で埋められます。

phpadmin版

Webコーダーの手帳：　http://web-coder.info/2009/07/phpmyadmin-new-field.html
属性
BINARY：数値型以外
UNSIGNED：正の数値
UNSIGNED ZEROFILL：「長さ/値」で指定した数まで数値の桁が足りない場合は、0を入れる
on update CURRENT_TIMESTAMP：そのデータが更新されるたびに、その時点でのタイプスタンプを値にします

ようやく出来ました

でもvarcharでやったほうがシンプルでいいかも