mysql bigint 20 范围_MySQL中的类型：BigInt（20）vs Int（20）

据我所知，当你试图插入超出范围的值时，只有一个小的区别。

在示例中，我将使用BIGINT(20)，这是2^20(长度为39个字符)

如果系统有BIGINT(20)，则表示在内存中分配最少20位。 但是如果你要插入大于2^20的值，它将被成功存储，只有当它小于BIGINT(64) -> 9223372036854775807(或2 * BIGINT(64) -> 18446744073709551615为UNSIGNED)

例：

mysql> describe `test`;

+-------+------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+------------------+------+-----+---------+-------+

| id | int(20) unsigned | YES | | NULL | |

+-------+------------------+------+-----+---------+-------+

1 row in set (0,00 sec)

mysql> INSERT INTO `test` (`id`) VALUES (401421228216);

ERROR 1264 (22003): Out of range value for column 'id' at row 1

mysql> SET sql_mode = '';

Query OK, 0 rows affected, 1 warning (0,00 sec)

mysql> INSERT INTO `test` (`id`) VALUES (401421228216);

Query OK, 1 row affected, 1 warning (0,06 sec)

mysql> SELECT * FROM `test`;

+------------+

| id |

+------------+

| 4294967295 |

+------------+

1 row in set (0,00 sec)

如果系统有BIGINT(20)，则表示在内存中分配最少20位。 但是如果你插入的值大于2^20，它将被成功存储，如果它小于BIGINT(64) -> 9223372036854775807(或2 * BIGINT(64) -> 18446744073709551615为UNSIGNED)

例：

mysql> describe `test`;

+-------+---------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------------------+------+-----+---------+-------+

| id | bigint(20) unsigned | YES | | NULL | |

+-------+---------------------+------+-----+---------+-------+

1 row in set (0,00 sec)

mysql> INSERT INTO `test` (`id`) VALUES (401421228216);

Query OK, 1 row affected (0,04 sec)

mysql> SELECT * FROM `test`;

+--------------+

| id |

+--------------+

| 401421228216 |

+--------------+

1 row in set (0,00 sec)