Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationEventAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
7
Help us understand the problem. What are the problem?

More than 5 years have passed since last update.

@dkkoma

MySQLの数値型における桁数指定

ZEROFILLしない場合は意味ないけど、なんとなくつけてたので
改めて意味ないってことの自戒をこめて。

MySQL-5.5

まずは以下のDDLでテーブルを作成します。

CREATE TABLE TEST_TABLE
(
    ID INT PRIMARY KEY AUTO_INCREMENT
    , tiny TINYINT(1) ZEROFILL NOT NULL
    , bool BOOLEAN NOT NULL
    , num0z INT(0) ZEROFILL NOT NULL 
    , num0 INT(0) NOT NULL 
    , num1 INT(1) ZEROFILL NOT NULL 
    , num11 INT(11) ZEROFILL NOT NULL 
    , numz INT ZEROFILL NOT NULL
    , num INT NOT NULL
)
ENGINE=InnoDB
DEFAULT CHARSET=ujis;
mysql> desc TEST_TABLE;
+-------+------------------------------+------+-----+---------+----------------+
| Field | Type                         | Null | Key | Default | Extra          |
+-------+------------------------------+------+-----+---------+----------------+
| ID    | int(11)                      | NO   | PRI | NULL    | auto_increment |
| tiny  | tinyint(1) unsigned zerofill | NO   |     | NULL    |                |
| bool  | tinyint(1)                   | NO   |     | NULL    |                |
| num0z | int(10) unsigned zerofill    | NO   |     | NULL    |                |
| num0  | int(11)                      | NO   |     | NULL    |                |
| num1  | int(1) unsigned zerofill     | NO   |     | NULL    |                |
| num11 | int(11) unsigned zerofill    | NO   |     | NULL    |                |
| numz  | int(10) unsigned zerofill    | NO   |     | NULL    |                |
| num   | int(11)                      | NO   |     | NULL    |                |
+-------+------------------------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

こんな感じのテーブルになる。
個人的にはZEROFILLなんて1度も使ったことのない幻の機能なのでどうでもいいのだけど、
int zerofillint(10) unsigned zerofillになるのはよくわからない。

insert into TEST_TABLE(tiny, bool, num0z, num0, num1, num11, numz, num) values
(0, 0, 0, 0, 0, 0, 0, 0),
(100, 100, 100, 100, 100, 100, 100, 100),
(1, 1, 1, 1, 1, 1, 1, 1);

データを突っ込んでselectしてみるとこんな感じ。
tinyint(1)にもint(1)にも100が入りますね。これは常識。

mysql> select * from TEST_TABLE;
+----+------+------+------------+------+------+-------------+------------+-----+
| ID | tiny | bool | num0z      | num0 | num1 | num11       | numz       | num |
+----+------+------+------------+------+------+-------------+------------+-----+
|  1 |    0 |    0 | 0000000000 |    0 |    0 | 00000000000 | 0000000000 |   0 |
|  2 |  100 |  100 | 0000000100 |  100 |  100 | 00000000100 | 0000000100 | 100 |
|  3 |    1 |    1 | 0000000001 |    1 |    1 | 00000000001 | 0000000001 |   1 |
+----+------+------+------------+------+------+-------------+------------+-----+
3 rows in set (0.00 sec)

http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html
によると、TRUE、FALSEはそれぞれ単に1, 0のエイリアスであるため、bool列に格納された100はTRUEでもFALSEでもありません。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
7
Help us understand the problem. What are the problem?