MySQL ０埋めの AUTO_INCREMENT

MySQL で id など AUTO_INCREMENT を用意するとき、
０埋めで作成されるようにするには、桁数指定で、UNSIGNED ZEROFILL　を付ける

例）4桁

CREATE TABLE branches (
  id           INT(4) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT
, branch_name  VARCHAR(60) NOT NULL
, PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

サンプル

mysql> TRUNCATE TABLE branches;
mysql> INSERT INTO branches (bname)VALUES('aaa'),('bbb');
mysql> commit;
mysql> SELECT * FROM branches;

+------+-------+
| id   | bname |
+------+-------+
| 0001 | aaa   |
| 0002 | bbb   |
+------+-------+
2 rows in set (0.00 sec)

mysql> select * from branches where id = 2;
+------+-------+
| id   | bname |
+------+-------+
| 0002 | bbb   |
+------+-------+
1 row in set (0.00 sec)

mysql> select * from branches where id = '0002';
+------+-------+
| id   | bname |
+------+-------+
| 0002 | bbb   |
+------+-------+
1 row in set (0.00 sec)

mysql> select * from branches where id = 0002;
+------+-------+
| id   | bname |
+------+-------+
| 0002 | bbb   |
+------+-------+
1 row in set (0.00 sec)

mysql> select * from branches where id = 02;
+------+-------+
| id   | bname |
+------+-------+
| 0002 | bbb   |
+------+-------+
1 row in set (0.00 sec)

mysql>