Help us understand the problem. What is going on with this article?

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

More than 5 years have passed since last update.

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でもありません。

dkkoma
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
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  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
ユーザーは見つかりませんでした