四、数据类型
1.数据类型分类
分类 | 数据类型 | 说明 |
数值类型 | BIT(M) | 位类型。指定位数,默认值1,范围1-64 |
TINYINT [UNSIGNED] | 带符号的范围-128127,无符号范围0255.默认有符号 | |
BOOL | 使用0和1表示真和假 | |
SMALLINT [UNSIGNED] | 带符号是-2^15次方到2^15-1,无符号是2^16-1 | |
INT [UNSINGED] | 带符号是-2^31次方到2^31-1,无符号是2^32-1 | |
BIGINT [UNSIGNED] | 带符号是-2^63次方到2^63-1,无符号是2^64-1 | |
FLOAT [(M,D)] [UNSIGNED] | M指定显示长度,d指定小数位数,占用4字节 | |
DOUBLE [(M,D)] [UNSIGNED] | 表示比f1oat精度更大的小数,占用空间8字节 | |
DECIMAL [(M,D)] [UNSIGNED] | 定点数指定长度,D表示小数点的位数 | |
文本、 二进制类型 | CHAR(size) | 固定长度字符串,最大255 |
VARCHAR(SIZE) | 可变长度字符串,最大长度65535 | |
BLOB | 二进制数据 | |
TEXT | 大文本,不支持全文索引,不支持默以值 | |
时间日期 | DATE/DATETIME/TIMESTAMP | 日期类型(yyyy-mm-dd) (yyyy-mm-dd hh:mm:ss) timestamp时间戳 |
String类型 | ENUM类型 | EUM是一个字符串对象,其值来自表创建时在列规定中显示枚举的一列值 |
SET类型 | SET是一个字符串对象,可以有零或多个值,其值来自表创建时规定的允许的一列值。指定包括多个set成员的set列值时各成员之间用逗号间隔开。这样set成员值本身不能包含逗号。 |
2.数值类型
类型 | 字节 | 带符号的 | 无符号的 | ||
最大值 | 最小值 | 最大值 | 最小值 | ||
TINYINT | 1 | -128 | 127 | 0 | 255 |
SMALLINT | 2 | -32768 | 32767 | 0 | 65535 |
MEDIUMINT | 3 | -8388608 | 8388607 | 0 | 16777215 |
INT | 4 | -2147483648 | 2147483647 | 0 | 4294967295 |
BIGINT | 8 | -9223372036854775808 | 9223372036854775807 | 0 | 18446744073709551615 |
①tinyint类型
mysql> create table t1(
-> num tinyint
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc t1;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| num | tinyint(4) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| t1 |
+-------------------+
1 row in set (0.00 sec)
mysql> show create table t1;
+-------+------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`num` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`num` tinyint(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> insert into t1 values (-128);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values (127);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values (0);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1 values (-1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t1 values (1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+
| num |
+------+
| -128 |
| 127 |
| 0 |
| -1 |
| 1 |
+------+
5 rows in set (0.00 sec)
mysql> insert into t1 values (-129);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into t1 values (128);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
在MySQL中,整型可以指定是有符号的和无符号的,默认是有符号的。
可以通过UNSIGNED来说明某个字段是无符号的
注意:尽量不使用unsigned
,对于int
类型可能存放不下的数据,int unsigned
同样可能存放不下,与其如此,还不如设计时,将int类型提升为bigint类型。
mysql> create table t2(
-> num tinyint unsigned
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| t1 |
| t2 |
+-------------------+
2 rows in set (0.00 sec)
mysql> desc t2;
+-------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| num | tinyint(3) unsigned | YES | | NULL | |
+-------+---------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into t2 values (255);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 values (0);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t2 values (100);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 values (-1);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> insert into t2 values (256);
ERROR 1264 (22003): Out of range value for column 'num' at row 1
mysql> select * from t2;
+------+
| num |
+------+
| 255 |
| 0 |
| 100 |
+------+
3 rows in set (0.00 sec)
如果我们向mysql特定的类型中插入不合法的数据,MySQL一般都是直接拦截我们的操作,如果我们的数据成功插入到mysql中了,一定插入的时候是合法的。所以在mysql中,一般而言,数据类型本身也就是一种约束(约束使用者,保证数据插入的合法性),这样就能保证数据库中的数据是可预期的、完整的
但是在C/C++中,如果较大的数存入范围较小的数据类型中(如char中存入1234567),这种情况下一般不会报错,顶多会警告,这时候存入了数据,但是数据会被截断或发生了隐式类型转换
②bit类型
基本语法:bit[(M)]
:位字段类型,M表示每个值的位数,范围从1到64,如果M被忽略,默认为1
- "位数" 指的是字段可以存储的位的数量。在这种情况下,
BIT(1)
意味着字段只能存储一个位。如果需要存储更多的位,比如 8 位,您可以使用BIT(8)
,这将允许存储 8 位的二进制数,范围从 00000000 到 11111111。 - bit字段在显示时,是按照ASC码对应的值显示。
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| t1 |
| t2 |
+-------------------+
2 rows in set (0.00 sec)
-- 创建表t3,一行存储id,一行存储在线状态online
mysql> create table t3(
-> id int,
-> online bit(1)
-> );
Query OK, 0 rows affected (0.02 sec)
-- BIT(1) 表示这个字段可以存储一个位(bit),即 0 或 1
mysql> desc t3;
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| online | bit(1) | YES | | NULL | |
+--------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into t3 (id,online) values (123,0);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t3 (id,online) values (124,1);
Query OK, 1 row affected (0.00 sec)
-- 超出了 BIT(1) 字段所能存储的范围
mysql> insert into t3 (id,online) values (123,3);
ERROR 1406 (22001): Data too long for column 'online' at row 1
mysql> insert into t3 (id,online) values (123,5);
ERROR 1406 (22001): Data too long for column 'online' at row 1
mysql> insert into t3 (id,online) values (123,2);
ERROR 1406 (22001): Data too long for column 'online' at row 1
-- 这里发现online的数据没有出现,因为bit字段显示时,是按照ASCII码的值显示
mysql> select * from t3;
+------+--------+
| id | online |
+------+--------+
| 123 | |
| 124 | |
+------+--------+
2 rows in set (0.00 sec)
-- 以十进制显示
mysql> select id,hex(online) from t3;
+------+-------------+
| id | hex(online) |
+------+-------------+
| 123 | 0 |
| 124 | 1 |
+------+-------------+
2 rows in set (0.00 sec)
-- 更改bit的位数
mysql> alter table t3 modify online bit(10);
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> desc t3;
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| online | bit(10) | YES | | NULL | |
+--------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from t3;
+------+--------+
| id | online |
+------+--------+
| 123 | |
| 124 | |
+------+--------+
2 rows in set (0.00 sec)
这次插入之前不能插入的数据可以插入了
mysql> insert into t3 (id,online) values (123,3);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t3 (id,online) values (123,5);
Query OK, 1 row affected (0.00 sec)
mysql> select id,hex(online) from t3;
+------+-------------+
| id | hex(online) |
+------+-------------+
| 123 | 0 |
| 124 | 1 |
| 123 | 3 |
| 123 | 5 |
+------+-------------+
4 rows in set (0.00 sec)
-- 且可以插入字符,但其实这里的字符是以97的形式存储
mysql> insert into t3 (id,online) values (123,'a');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t3;
+------+--------+
| id | online |
+------+--------+
| 123 | |
| 124 | |
| 123 | |
| 123 | |
| 123 | a |
+------+--------+
5 rows in set (0.00 sec)
-- 如果插入97,显示的时候也是字符a
mysql> insert into t3 (id,online) values (123,97);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t3;
+------+--------+
| id | online |
+------+--------+
| 123 | |
| 124 | |
| 123 | |
| 123 | |
| 123 | a |
| 123 | a |
+------+--------+
6 rows in set (0.00 sec)
-- 创建表t4,给他指定位数为65,发现创建失败,因为bit的位数范围为1~64
mysql> create table t4(
-> id int,
-> online bit(65)
-> );
ERROR 1439 (42000): Display width out of range for column 'online' (max = 64)
-- 表示online的最大范围为64
mysql> create table t4( id int, online bit(64) );
Query OK, 0 rows affected (0.01 sec)
mysql> desc t4;
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| online | bit(64) | YES | | NULL | |
+--------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
-- 删除掉表t4重新创建,这次不指定位数
mysql> drop table t4;
Query OK, 0 rows affected (0.01 sec)
mysql> create table t4( id int, online bit ); // 如果M被忽略,默认为1
Query OK, 0 rows affected (0.01 sec)
mysql> desc t4;
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| online | bit(1) | YES | | NULL | |
+--------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
③小数类型
1>float
基本语法:float[(m, d)] [unsigned]
: M指定显示长度(整数位+小数位),d指定小数位数,占用空间4个字节
- 小数:float(4,2)表示的范围是
-99.99 ~ 99.99
,MySQL在保存值时会进行四舍五入。
mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| t1 |
| t2 |
| t3 |
| t4 |
+-------------------+
4 rows in set (0.01 sec)
-- 创建表t5,一行存储int,一行存储浮点数,总长度为4,小数位位数为2
mysql> create table t5(
-> id int,
-> salary float(4,2)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc t5;
+--------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| salary | float(4,2) | YES | | NULL | |
+--------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> show create table t5\G
*************************** 1. row ***************************
Table: t5
Create Table: CREATE TABLE `t5` (
`id` int(11) DEFAULT NULL,
`salary` float(4,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
-- 向表中插入数据
mysql> insert into t5 (id,salary) values (1,99.99);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t5 (id,salary) values (1,-99.99);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t5 (id,salary) values (1,12.34);
Query OK, 1 row affected (0.00 sec)
-- 都OK
mysql> select * from t5;
+------+--------+
| id | salary |
+------+--------+
| 1 | 99.99 |
| 1 | -99.99 |
| 1 | 12.34 |
+------+--------+
3 rows in set (0.00 sec)
-- 插入的数据要符合定义的属性
-- 99.999四舍五入后是100.000,不符合
mysql> insert into t5 (id,salary) values (1,99.999);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
-- 100.00不是四位,不符合
mysql> insert into t5 (id,salary) values (1,100.00);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
-- 当插入 100.0 时,MySQL 会自动将其视为 100.00
-- 因为 MySQL 总是会确保小数部分符合 D 的要求
mysql> insert into t5 (id,salary) values (1,100.0);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
-- 符合要求,这里存储10.0实际上存储的是10.00
mysql> insert into t5 (id,salary) values (1,10.0);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t5;
+------+--------+
| id | salary |
+------+--------+
| 1 | 99.99 |
| 1 | -99.99 |
| 1 | 12.34 |
| 1 | 10.00 |
+------+--------+
4 rows in set (0.00 sec)
mysql> insert into t5 (id,salary) values (1,23.45);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t5;
+------+--------+
| id | salary |
+------+--------+
| 1 | 99.99 |
| 1 | -99.99 |
| 1 | 12.34 |
| 1 | 10.00 |
| 1 | 23.45 |
+------+--------+
5 rows in set (0.00 sec)
-- 当插入小数位数超过定义的小数位数(例如 FLOAT(4,2))的数据时
-- MySQL 会对数据进行四舍五入处理
mysql> insert into t5 (id,salary) values (1,23.456); -- 保存为了23.46
Query OK, 1 row affected (0.00 sec)
mysql> insert into t5 (id,salary) values (1,23.454); -- 保存为了23.45
Query OK, 1 row affected (0.00 sec)
mysql> select * from t5;
+------+--------+
| id | salary |
+------+--------+
| 1 | 99.99 |
| 1 | -99.99 |
| 1 | 12.34 |
| 1 | 10.00 |
| 1 | 23.45 |
| 1 | 23.46 |
| 1 | 23.45 |
+------+--------+
7 rows in set (0.00 sec)
-- 都保存为了99.99
mysql> insert into t5 (id,salary) values (1,99.994);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t5 (id,salary) values (1,99.993);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t5 (id,salary) values (1,99.991);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t5;
+------+--------+
| id | salary |
+------+--------+
| 1 | 99.99 |
| 1 | -99.99 |
| 1 | 12.34 |
| 1 | 10.00 |
| 1 | 23.45 |
| 1 | 23.46 |
| 1 | 23.45 |
| 1 | 99.99 |
| 1 | 99.99 |
| 1 | 99.99 |
+------+--------+
10 rows in set (0.00 sec)
-- 这几个在四舍五入的时候超过了规定的范围
mysql> insert into t5 (id,salary) values (1,99.995);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> insert into t5 (id,salary) values (1,99.996);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> insert into t5 (id,salary) values (1,99.997);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> desc t5;
+--------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| salary | float(4,2) | YES | | NULL | |
+--------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> create table t6(
-> id bigint,
-> salary float(4,2) unsigned
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc t6;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| id | bigint(20) | YES | | NULL | |
| salary | float(4,2) unsigned | YES | | NULL | |
+--------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into t6(id,salary) values (1,0);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t6;
+------+--------+
| id | salary |
+------+--------+
| 1 | 0.00 |
+------+--------+
1 row in set (0.00 sec)
mysql> insert into t6(id,salary) values (1,99.99);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t6(id,salary) values (1,99.996);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> insert into t6(id,salary) values (1,99.999);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> insert into t6(id,salary) values (1,99.99);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t6(id,salary) values (1,-99.99);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> insert into t6(id,salary) values (1,-0.99);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> insert into t6(id,salary) values (1,-0.01);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> insert into t6(id,salary) values (1,34.567);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t6;
+------+--------+
| id | salary |
+------+--------+
| 1 | 0.00 |
| 1 | 99.99 |
| 1 | 99.99 |
| 1 | 34.57 |
+------+--------+
4 rows in set (0.00 sec)
float可能会出现丢失精度的情况,例如:
mysql> desc t5;
+--------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| salary | float | YES | | NULL | |
+--------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
-- 这里插入了35434663.23但是保存的是35434700
mysql> insert into t5(id,salary) values (1,35434663.23);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t5;
+------+----------+
| id | salary |
+------+----------+
| 1 | 99.99 |
| 1 | -99.99 |
| 1 | 12.34 |
| 1 | 10 |
| 1 | 23.45 |
| 1 | 23.46 |
| 1 | 23.45 |
| 1 | 99.99 |
| 1 | 99.99 |
| 1 | 99.99 |
| 1 | 3456540 |
| 1 | 3456540 |
| 1 | 543.244 |
| 1 | 3543.24 |
| 1 | 3543460 |
| 1 | 35434700 |
+------+----------+
16 rows in set (0.00 sec)
salary
列在实际的表结构中是一个普通的 FLOAT
类型,允许插入大数值,但由于浮点数的精度限制,部分大数值在存储和检索时可能会出现精度丢失,显示为不同的数值。要避免这种精度问题,应该使用 DECIMAL
类型来代替 FLOAT
,尤其是当你需要精确存储货币或其他精度敏感的数据时。
2>decimal
基本语法:decimal(m, d) [unsigned]
: 定点数m指定长度,d表示小数点的位数
mysql> create table t7(
-> f1 float(10,8),
-> f2 decimal(4,2)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> desc t7;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| f1 | float(10,8) | YES | | NULL | |
| f2 | decimal(4,2) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
-- 插入数据
mysql> insert into t7(f1,f2) values (10.0,99.99);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t7(f1,f2) values (10.0,-99.99);
Query OK, 1 row affected (0.00 sec)
-- 数据不符合小数位2位的要求
mysql> insert into t7(f1,f2) values (10.0,-99.999);
ERROR 1264 (22003): Out of range value for column 'f2' at row 1
mysql> insert into t7(f1,f2) values (10.0,99.999);
ERROR 1264 (22003): Out of range value for column 'f2' at row 1
mysql> insert into t7(f1,f2) values (10.0,0.1);
Query OK, 1 row affected (0.00 sec)
-- 同样会四舍五入
mysql> insert into t7(f1,f2) values (10.0,99.994);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> insert into t7(f1,f2) values (10.0,99.935);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from t7;
+-------------+--------+
| f1 | f2 |
+-------------+--------+
| 10.00000000 | 99.99 |
| 10.00000000 | -99.99 |
| 10.00000000 | 0.10 |
| 10.00000000 | 99.99 |
| 10.00000000 | 99.94 |
+-------------+--------+
5 rows in set (0.00 sec)
-- 改变decimal的属性,变得和float一样
mysql> alter table t7 modify f2 decimal(10,8);
Query OK, 5 rows affected (0.04 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> desc t7;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| f1 | float(10,8) | YES | | NULL | |
| f2 | decimal(10,8) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
-- 再次插入数据,且float和decimal数据一致
mysql> insert into t7 (f1,f2) values (23.12345678,23.12345678);
Query OK, 1 row affected (0.01 sec)
-- 一个是23.12345695,另一个是23.12345678,精度差距很大
mysql> select * from t7;
+-------------+--------------+
| f1 | f2 |
+-------------+--------------+
| 10.00000000 | 99.99000000 |
| 10.00000000 | -99.99000000 |
| 10.00000000 | 0.10000000 |
| 10.00000000 | 99.99000000 |
| 10.00000000 | 99.94000000 |
| 23.12345695 | 23.12345678 |
+-------------+--------------+
6 rows in set (0.00 sec)
3>float和decimal比较
1. 精度 vs. 性能
DECIMAL(定点数)
:
①高精度:DECIMAL 类型在内部以字符串或整数的形式存储数值,能够精确地表示小数点后的位数,因此特别适合用于对精度要求高的场景,例如金融、货币、科学计算等。
②性能较低:由于 DECIMAL 以定点形式存储,需要进行较多的计算处理,尤其是在涉及大量计算的情况下,性能可能低于 FLOAT。
FLOAT 和 DOUBLE(浮点数)
:
①高性能:浮点数使用科学计数法存储,在硬件层面支持的情况下,计算速度非常快,适合于需要处理大量浮点数的场景,如图形计算、物理模拟、工程计算等。
②精度较低:由于浮点数的存储方式,它们只能近似表示某些数值,可能在非常大的或非常小的数值时产生精度丢失。这在要求精度的场景下可能导致问题。
2.存储空间
DECIMAL
:
- 由于其精度,它通常占用更多的存储空间,尤其是在处理大范围的数值时。
FLOAT 和 DOUBLE
:
- 这些类型相对于 DECIMAL 占用的存储空间较小,特别是在存储范围大的数值时。FLOAT 通常使用 4 字节,而 DOUBLE 使用 8 字节。