MySQL的數值類型

整數類型

整數類型包含TINYINT、SMALLINT、MEDIUMINT、INT、 BIGINT等。

存取范圍類型存儲大小默認顯示寬度(個)范圍(有符號)范圍(無符號)用途TINYINT(m)1Bytem：4-128 - 1270 - 255小整數值

SMALLINT(m)2Bytem：6-32768 - 327670 - 65535大整數值

MEDIUMINT(m)3Bytem：9-8388608 - 83886070 - 16777215大整數值

INT\INTEGER(m)4Bytem：11-2147483648 - 21474836470 - 4294967295大整數值

BIGINT(m)8Bytem：20-9233372036854775808 - 92233720368547758070 - 18446744073709551615極大整數值

m為其顯示寬度，在為字段設置 zerofill約束條件時有效，否則將不會填充滿整個顯示寬度。

可選約束

unsigned：使用無符號存儲。

zerofill：顯示寬度不夠時使用0進行填充。

顯示寬度

使用一切數值類型時，指定其寬度均是為其指定顯示寬度，并非存入的限制寬度。

以下示例將演示為TINYINT類型設置設置了顯示寬度后，當寬度不夠時將以指定字符進行填充。mysql> CREATE TABLE `test` (

-> `id` int(11) NOT NULL AUTO_INCREMENT,

-> `a` tinyint(4) unsigned zerofill DEFAULT NULL,

-> `b` smallint(6) unsigned DEFAULT NULL,

-> `c` mediumint(9) DEFAULT NULL,

-> `d` int(11) DEFAULT NULL,

-> `e` bigint(20) DEFAULT NULL,

-> PRIMARY KEY (`id`)

-> ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Query OK, 0 rows affected, 9 warnings (0.05 sec)mysql> desc test;

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

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

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

| id | int | NO | PRI | NULL | auto_increment |

| a | tinyint(4) unsigned zerofill | YES | | NULL | |

| b | smallint unsigned | YES | | NULL | |

| c | mediumint | YES | | NULL | |

| d | int | YES | | NULL | |

| e | bigint | YES | | NULL | |

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

6 rows in set (0.00 sec)mysql> INSERT INTO `test` (`a`, `b`, `c`, `d`, `e`) VALUES ('1', '1', '1', '1', '1');

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `test` (`id`, `a`, `b`, `c`, `d`, `e`) VALUES ('3', '-1', '-1', '1', '1', '1');

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

mysql> INSERT INTO `test` (`a`, `b`, `c`, `d`, `e`) VALUES ('333', '333', '333', '333', '333');

ERROR 1264 (22003): Out of range value for column 'a' at row 1mysql> select * from test;

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

| id | a | b | c | d | e |

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

| 3 | 0001 | 1 | 1 | 1 | 1 |

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

1 row in set (0.00 sec)

范圍超出

當范圍超出時則不允許存取，拋出異常。

浮點類型

浮點類型包括FLOAT、DOUBLE、DECIMAL。

存取范圍類型存儲大小最大顯示寬度(個)范圍(有符號)范圍(無符號)精確度FLOAT(m[,d])4Bytesm：255，d：30(-3.402 823 466 E+38，-1.175 494 351 E-38) - 00 - (1.175 494 351 E-38，3.402 823 466 E+38)點七位以內

DOUBLE(m[,d])8Bytesm：255，d：30(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308) - 00 - (2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)點十五位以內

DECIMAL(m[,d])m+2(如果m

m為其整數部分顯示個數，n為其小數部分顯示個數。

DECIMAL底層由字符串進行存儲，故精度不會出現偏差，也被稱為定點類型。

精度問題mysql> CREATE TABLE `test` (

-> `id` INT NOT NULL,

-> `a` FLOAT NULL,

-> `b` DOUBLE NULL,

-> `c` DECIMAL NULL,

-> PRIMARY KEY (`id`));

Query OK, 0 rows affected (0.02 sec)mysql> desc test;

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

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

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

| id | int | NO | PRI | NULL | |

| a | float | YES | | NULL | |

| b | double | YES | | NULL | |

| c | decimal(10,0) | YES | | NULL | |

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

4 rows in set (0.01 sec)INSERT INTO `test` (`id`, `a`, `b`,`c`) VALUES ('1', '3.1415', '3.14159','3.14159');

INSERT INTO `test` (`id`, `a`, `b`,`c`) VALUES ('2', '1.1111111111111111', '1.1111111111111111','1.1111111111111111');mysql> select * from test;

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

| id | a | b | c |

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

| 1 | 3.1415 | 3.14159 | 3 |

| 2 | 1.11111 | 1.1111111111111112 | 1 |

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

2 rows in set (0.01 sec)mysql> alter table test modify `c` DECIMAL(65,30) NULL;

Query OK, 2 rows affected (0.13 sec)

Records: 2 Duplicates: 0 Warnings: 0

mysql> desc test;

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

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

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

| id | int | NO | PRI | NULL | |

| a | float | YES | | NULL | |

| b | double | YES | | NULL | |

| c | decimal(65,30) | YES | | NULL | |

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

4 rows in set (0.00 sec)

mysql> select * from test;

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

| id | a | b | c |

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

| 1 | 3.1415 | 3.14159 | 3.000000000000000000000000000000 |

| 2 | 1.11111 | 1.1111111111111112 | 1.000000000000000000000000000000 |

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

2 rows in set (0.00 sec)

mysql> INSERT INTO `test` (`id`, `a`, `b`,`c`) VALUES ('3', '1.1111111111111111', '1.1111111111111111','1.1111111111111111');

Query OK, 1 row affected (0.00 sec)

mysql> select * from test;

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

| id | a | b | c |

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

| 1 | 3.1415 | 3.14159 | 3.000000000000000000000000000000 |

| 2 | 1.11111 | 1.1111111111111112 | 1.000000000000000000000000000000 |

| 3 | 1.11111 | 1.1111111111111112 | 1.111111111111111100000000000000 |

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

3 rows in set (0.00 sec)

位類型

BIT(M)可以用來存放多位二進制數，M范圍從1~64，如果不寫默認為1位。注意：對于位字段需要使用函數讀取

bin()顯示為二進制

hex()顯示為十六進制mysql> create table `test`(num bit);

Query OK, 0 rows affected (0.03 sec)

mysql> desc test;

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

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

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

| num | bit(1) | YES | | NULL | |

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

1 row in set (0.01 sec)

mysql> insert into `test`(num) values (1);

Query OK, 1 row affected (0.01 sec)

mysql> select * from test;

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

| num |

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

| 0x01 |

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

1 row in set (0.00 sec)

mysql> select bin(num),hex(num) from test;

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

| bin(num) | hex(num) |

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

| 1 | 1 |

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

1 row in set (0.00 sec)

mysql> alter table `test` modify num bit(5);

Query OK, 1 row affected (0.10 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> insert into `test`(num) values (8);

Query OK, 1 row affected (0.00 sec)

mysql> select * from test;

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

| num |

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

| 0x01 |

| 0x08 |

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

2 rows in set (0.00 sec)

mysql> select bin(num),hex(num) from test;

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

| bin(num) | hex(num) |

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

| 1 | 1 |

| 1000 | 8 |

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

2 rows in set (0.00 sec)

總結

以上是生活随笔為你收集整理的mysql可以存储整数数值的是_MySQL的数值类型的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。