mysql> desc test;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| one | int(11) | YES | | NULL | |
| two | int(10) unsigned | YES | | NULL | |
| three | mediumint(9) | YES | | NULL | |
| four | mediumint(8) unsigned | YES | | NULL | |
| five | smallint(6) | YES | | NULL | |
| six | smallint(5) unsigned | YES | | NULL | |
| seven | tinyint(4) | YES | | NULL | |
| eight | tinyint(3) unsigned | YES | | NULL | |
+-------+-----------------------+------+-----+---------+----------------+
9 rows in set (0.01 sec)
int
mysql> insert into test (one) values (2147483648);
ERROR 1264 (22003): Out of range value for column 'one' at row 1
mysql> insert into test (one) values (2147483647);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test (one) values (-2147483648);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test (one) values (-2147483649);
ERROR 1264 (22003): Out of range value for column 'one' at row 1
mysql> insert into test (two) values (4294967296);
ERROR 1264 (22003): Out of range value for column 'two' at row 1
mysql> insert into test (two) values (4294967295);
Query OK, 1 row affected (0.00 sec)
mysql> select one, two from test;
+-------------+------------+
| one | two |
+-------------+------------+
| 2147483647 | NULL |
| -2147483648 | NULL |
| NULL | 4294967295 |
+-------------+------------+
3 rows in set (0.00 sec)
mediumint
mysql> insert into test (three) values (8388608);
ERROR 1264 (22003): Out of range value for column 'three' at row 1
mysql> insert into test (three) values (8388607);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test (three) values (-8388608);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test (three) values (-8388609);
ERROR 1264 (22003): Out of range value for column 'three' at row 1
mysql> insert into test (four) values (16777216);
ERROR 1264 (22003): Out of range value for column 'four' at row 1
mysql> insert into test (four) values (16777215);
Query OK, 1 row affected (0.00 sec)
mysql> select three, four from test;
+----------+----------+
| three | four |
+----------+----------+
| 8388607 | NULL |
| -8388608 | NULL |
| NULL | 16777215 |
+----------+----------+
6 rows in set (0.00 sec)
smallint
mysql> insert into test (five) values (32767);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test (five) values (-32768);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test (five) values (-32769);
ERROR 1264 (22003): Out of range value for column 'five' at row 1
mysql> insert into test (six) values (65536);
ERROR 1264 (22003): Out of range value for column 'six' at row 1
mysql> insert into test (six) values (65535);
Query OK, 1 row affected (0.00 sec)
mysql> select five, six from test;
+--------+-------+
| five | six |
+--------+-------+
| 32767 | NULL |
| -32768 | NULL |
| NULL | 65535 |
+--------+-------+
9 rows in set (0.00 sec)
tinyint
mysql> insert into test (seven) values (128);
ERROR 1264 (22003): Out of range value for column 'seven' at row 1
mysql> insert into test (seven) values (127);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test (seven) values (-128);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test (seven) values (-129);
ERROR 1264 (22003): Out of range value for column 'seven' at row 1
mysql> insert into test (eight) values (256);
ERROR 1264 (22003): Out of range value for column 'eight' at row 1
mysql> insert into test (eight) values (255);
Query OK, 1 row affected (0.00 sec)
mysql> select seven, eight from test;
+-------+-------+
| seven | eight |
+-------+-------+
| 127 | NULL |
| -128 | NULL |
| NULL | 255 |
+-------+-------+
12 rows in set (0.00 sec)
总结如下:
int 四个字节 无符号 0 ~ 2^32 -1
mediumint 三个字节 无符号 0 ~ 2^24 -1
smallint 二个字节 无符号 0 ~ 2^16 -1
tinyint 一个字节 无符号 0 ~ 2^8 -1
tinyint(1) 的范围依然是0~255
mysql> desc one;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| num | tinyint(1) unsigned | YES | | NULL | |
+-------+---------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into one (num) values (0);
Query OK, 1 row affected (0.00 sec)
mysql> insert into one (num) values (255);
Query OK, 1 row affected (0.00 sec)
mysql> insert into one (num) values (256);
ERROR 1264 (22003): Out of range value for column 'num' at row 1