标签(空格分隔): MYSQL
<numeric data type> ::=
<integer data type> |
<decimal data type> |
<float data type> |
<bit data type>
<integer data type> ::=
BOOLEAN |
TINYINT,INT1 [ ( <presentation width> ) ] |
SMALLINT,INT2 [ ( <presentation width> ) ] |
MEDIUMINT,INT3,MIDDLEINT [ ( <presentation width> ) ] |
INTEGER,INT,INT4 [ ( <presentation width> ) ] |
BIGINT,INT8 [ ( <presentation width> ) ] |
INTEGER数据类型的字段可以存储整数,大部分数据表的主键都是INTEGER类型。
MYSQL 支持多种INTEGER数据类型的变体,这些变体的区别主要在于所支持的值的范围大小。下表列出了不同整形的取之范围
整数类型 | 同义词 | 取值范围 |
---|---|---|
TINYINT | INT1 | ( –2^7, +2^7 –1 ] |
SMALLINT | INT2 | ( –2^15, +2^15 –1 ] |
MEDIUMINT | INT3, MIDDLEINT | ( –2^23, +2^23 –1 ] |
INTEGER | INT, INT4 | ( –2^31, +2^31 –1 ] |
BIGINT | INT8 | ( –2^63, +2^63 –1 ] |
对于每一个整数类型,我们都可以指定一个显示宽度,例如 INTEGER(11)。然而这个宽度与能存储的值的范围无关,只是为了向用户友好的显示存储的值。
创建一个表,并且添加行
mysql> CREATE TABLE tb_width(id INTEGER(4));
Query OK, 0 rows affected (0.22 sec)
mysql> INSERT INTO tb_width VALUES (1);
Query OK, 1 row affected (0.07 sec)
mysql> SELECT * FROM tb_width;
+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
mysql> INSERT INTO tb_width VALUES (11);
Query OK, 1 row affected (0.07 sec)
mysql> SELECT * FROM tb_width;
+------+
| id |
+------+
| 1 |
| 11 |
+------+
2 rows in set (0.00 sec)
mysql> INSERT INTO tb_width VALUES (111);
Query OK, 1 row affected (0.05 sec)
mysql> SELECT * FROM tb_width;
+------+
| id |
+------+
| 1 |
| 11 |
| 111 |
+------+
3 rows in set (0.00 sec)
mysql> INSERT INTO tb_width VALUES (1111);
Query OK, 1 row affected (0.09 sec)
mysql> SELECT * FROM tb_width;
+------+
| id |
+------+
| 1 |
| 11 |
| 111 |
| 1111 |
+------+
4 rows in set (0.00 sec)
mysql> INSERT INTO tb_width VALUES (11111);
Query OK, 1 row affected (0.06 sec)
mysql> SELECT * FROM tb_width;
+-------+
| id |
+-------+
| 1 |
| 11 |
| 111 |
| 1111 |
| 11111 |
+-------+
5 rows in set (0.00 sec)
mysql> INSERT INTO tb_width VALUES (111111);
Query OK, 1 row affected (0.09 sec)
mysql> SELECT * FROM tb_width;
+--------+
| id |
+--------+
| 1 |
| 11 |
| 111 |
| 1111 |
| 11111 |
| 111111 |
+--------+
6 rows in set (0.00 sec)
如果插入的值小于4位,则在数字前面用空格补足4位,如果插入的值大于4位,则会保留更多的位。指定的宽度只被看作最小宽度,因此,宽度和存储值的范围无关,只和显示有关