整型

mysql> create table test1( tinyint_test tinyint , int_test int );

mysql> desc test1;
+--------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------+------+-----+---------+-------+
| tinyint_test | tinyint(4) | YES | | NULL | | ## 不指定宽度的话，系统会自动设置一个合理的值，确保 int 能够显示完整。
| int_test | int(11) | YES | | NULL | |
+--------------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> insert into test1 values (111,111);
Query OK, 1 row affected (0.09 sec)

mysql> insert into test1 (tinyint_test) values(128);
ERROR 1264 (22003): Out of range value for column 'tinyint_test' at row 1

mysql> insert into test1(int_test) values(2147483647);
Query OK, 1 row affected (0.05 sec)

mysql> insert into test1(int_test) values(2147483648);
ERROR 1264 (22003): Out of range value for column 'int_test' at row 1

//测试结果，默认有符号，超过存储范围出错。

====无符号整形测试=============== ## 业务背景。

mysql> create table test2(
-> tinyint_test tinyint unsigned, //约束条件unsigned限定只能存正值（无符号）
-> int_test int unsigned
-> );
Query OK, 0 rows affected (0.00 sec)

mysql> desc test2;
+--------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| tinyint_test | tinyint(3) unsigned | YES | | NULL | |
| int_test | int(10) unsigned | YES | | NULL | |
+--------------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into test2(tinyint_test) values(255);
Query OK, 1 row affected (0.06 sec)

mysql> insert into test2(int_test) values(2147483648);
Query OK, 1 row affected (1.87 sec)

mysql> insert into test2 values(-20,-20);
ERROR 1264 (22003): Out of range value for column 'tinyint_test' at row 1

测试整数类型的显示宽度

mysql> create table t1 (
-> id1 int,
-> id2 int(6)
-> );

mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id1 | int(11) | YES | | NULL | |
| id2 | int(6) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+

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

mysql> select * from t1;
+------+------+
| id1 | id2 |
+------+------+
| 1 | 1 |
+------+------+
1 row in set (0.00 sec)

mysql> insert into t1 values(3,22223333); //插入大于宽度限制的值，仍然可以存储
Query OK, 1 row affected (0.03 sec)

mysql> select * from t1;
+------------+---------+
| id1 | id2 |
+------------+---------+
| 3 | 222233 |
+------------+---------+
2 rows in set (0.00 sec)

结论：整形的宽度仅为显示宽度，不是限制。因此建议整形无须指定宽度，由系统自行调整。