TINYINT -128 127 1バイト
TINYINT UNSIGNED 0 255 1バイト

以下のようなtinyintのidを持つ列に対して、データをLoopでInsertする処理をすると
tinyintの最大値の127までしかデータが入らず、128以降の番号は127にclipされる。
unsignedを指定した場合は255までの値を許容する。

mysql> INSERT INTO T1 (id) VALUES (NULL);
Query OK, 1 row affected (0.01 sec)


mysql> CREATE TABLE `T1` (
-> `id` tinyint(4) NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> desc T1;
+-------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+----------------+
| id | tinyint(4) | NO | PRI | NULL | auto_increment |
+-------+------------+------+-----+---------+----------------+
1 row in set (0.01 sec)

mysql> INSERT INTO T1 (id) VALUES (NULL);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO T1 (id) VALUES (NULL);
Query OK, 1 row affected (0.01 sec)

mysql> select * from T1;
+----+
| id |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0.00 sec)

mysql>

tinyint


6.2.1. 数値型


———————————————————————–
TINYINT -128 127 1バイト
TINYINT UNSIGNED 0 255 1バイト
———————————————————————–

mysql> CREATE TABLE `TINT` (
-> `ID` tinyint(2) NULL,
-> `TimeS` timestamp NULL DEFAULT CURRENT_TIMESTAMP
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8
-> ;
Query OK, 0 rows affected (0.15 sec)

tint_table

mysql> insert into TINT(ID) values(1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into TINT(ID) values(2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into TINT(ID) values(100);
Query OK, 1 row affected (0.00 sec)

mysql> insert into TINT(ID) values(127);
Query OK, 1 row affected (0.00 sec)

mysql> insert into TINT(ID) values(128);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'ID' at row 1 |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from TINT;
+——+———————+
| ID | TimeS |
+——+———————+
| 1 | 2009-07-06 12:45:00 |
| 2 | 2009-07-06 12:45:03 |
| 100 | 2009-07-06 12:45:11 |
| 127 | 2009-07-06 12:45:19 |
| 127 | 2009-07-06 12:45:22 | <--- 127以上の数値はTINY INTでは受け付けない +------+---------------------+ 5 rows in set (0.00 sec) mysql>

tint