彻底了解mysql int类型

int类型占4个字节，32位

无符号范围为0-4,294,967,295

有符号范围为-2147483648 ----2147483647

mysql> create table t1(id int(40) unsigned zerofill);

Query OK, 0 rows affected (0.01 sec)


mysql> insert into t1 set id= 4294967295;
Query OK, 1 row affected (0.00 sec)


mysql> commit;
Query OK, 0 rows affected (0.01 sec)


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


mysql> select length(id) from t1;
+------------+
| length(id) |
+------------+
|         40 |
+------------+

1 row in set (0.00 sec)

----------------int (N)  zerofill  就是该列长度为N个字节，数字长度不足N，以0补齐

默认创建的int类型字段是有符号的

mysql> create table t001 (id int);
Query OK, 0 rows affected (0.01 sec)


mysql> insert into t001 set id= -43;
Query OK, 1 row affected (0.01 sec)


mysql> commit;
Query OK, 0 rows affected (0.00 sec)


mysql> desc t001;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

创建表时指明为有符号signed + zerofill  则zerofill情况下转为unsigned，以便前面补充0

mysql> create table t001 (id int signed zerofill);
Query OK, 0 rows affected (0.00 sec)


mysql> insert into t001 set id= -43;
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> desc t001;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type                      | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| id    | int(10) unsigned zerofill | YES  |     | NULL    |       |
+-------+---------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> create table t001 (id int(2555));
ERROR 1439 (42000): Display width out of range for column 'id' (max = 255)

显示最大宽度为255，前面能填充0

mysql> create table t001 (id int);
Query OK, 0 rows affected (0.01 sec)


mysql> insert into t001 set id=4294967295;
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> insert into t001 set id=2147483647;
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

int 默认有符号 范围从 -2147483648 ----2147483647;


mysql> create table t001 (id int unsigned);
Query OK, 0 rows affected (0.01 sec)


mysql> insert into t001 set id=4294967295;
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t001 set id=-1;
ERROR 1264 (22003): Out of range value for column 'id' at row 1

mysql> desc t001;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id    | int(10) unsigned | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
创建表时，指明int unsigned时的范围为 0------4294967295；