MySQL的数值类型

数据库小白一枚，正在不断学习积累知识，现将学习到的知识记录一下，也是将我的所得分享给大家！而今天这篇文章《MySQL的数值类型》带大家来了解一下MySQL的数值类型，希望对大家的知识积累有所帮助，从而弥补自己的不足，助力实战开发！

MySQL的数值类型

整数类型

整数类型包含

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 1

mysql> select * from test;
+----+------+------+------+------+------+
| id | a    | b    | c    | d    | e    |
+----+------+------+------+------+------+
|  3 | 0001 |    1 |    1 |    1 |    1 |
+----+------+------+------+------+------+
1 row in set (0.00 sec)

范围超出

当范围超出时则不允许存取，抛出异常。

浮点类型

浮点类型包括

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)

位类型

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的相关知识，希望对你有所帮助；关于数据库的技术知识我们会一点点深入介绍，欢迎大家关注golang学习网公众号，一起学习编程~