mysql 的int类型,彻底了解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；