MySQL 数据存储详解：浮点数与定点数-CSDN博客

本文链接：https://blog.csdn.net/eandroidhu/article/details/53184841

本文介绍了MySQL中存储数据的类型，包括浮点数（float, double）和定点数（decimal）的使用。对于decimal，(10, 5)分别代表总位数和小数点后的位数。浮点数如果不指定，默认不会丢失数据；而定点数如果不指定，默认为(10, 0)。此外，还提到了位数据类型如date，以及字符类型char（会自动删除末尾空格）和varchar（不删除末尾空格）。" 51441111,1513495,理解系统架构：部署图与组件图解析,"['系统架构', 'UML', '组件设计', '软件部署', '系统规划']

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

float double（浮点数） decimal（定点数）
（10， 5）第一位表示浮点数的位数，第二位表示小数点后的位数，
浮点数不加的话默认数据不丢失，定点数不加的话默认为（10， 0）

mysql> select * from t2;
+--------------------+------+------+
| id1                | id2  | id3  |
+--------------------+------+------+
| 1.2300000190734863 | 1.23 |    1 |
| 1.2300000190734863 | 1.23 |    1 |
| 1.2300000190734863 | 1.23 |    1 |
| 1.2300000190734863 | 1.23 |    1 |
| 1.2300000190734863 | 1.23 |    1 |
+--------------------+------+------+
5 rows in set (0.00 sec)

mysql> insert into t2 values(1.234, 1.234, 1.234);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from t2;
+--------------------+-------+------+
| id1                | id2   | id3  |
+--------------------+-------+------+
| 1.2300000190734863 |  1.23 |    1 |
| 1.2300000190734863 |  1.23 |    1 |
| 1.2300000190734863 |  1.23 |    1 |
| 1.2300000190734863 |  1.23 |    1 |
| 1.2300000190734863 |  1.23 |    1 |
|              1.234 | 1.234 |    1 |
+--------------------+-------+------+
6 rows in set (0.00 sec)

mysql> insert into t2 values(1.345, 2.1345, 1.345);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+------------------------------------------+
| Level | Code | Message                                  |
+-------+------+------------------------------------------+
| Note  | 1265 | Data truncated for column 'id3' at row 1 |
+-------+------+------------------------------------------+
1 row in set (0.00 sec)

位数据类型

mysql> create table bitDemo(id bit);
Query OK, 0 rows affected (0.02 sec)

mysql> desc bitDemo;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id    | bit(1) | YES  |     | NULL    |       |
+-------+--------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into table bitDemo values(2);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table bitDemo values(2)' at line 1
mysql> insert into bitDemo values(2);
ERROR 1406 (22001): Data too long for column 'id' at row 1
mysql> insert into bitDemo values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from bitDemo;
+------+
| id   |
+------+
|     |
+------+
1 row in set (0.00 sec)

mysql> select bin(id), hex(id) from bitDemo;
+---------+---------+
| bin(id) | hex(id) |
+---------+---------+
| 1       | 1       |
+---------+---------+
1 row in set (0.00 sec)

date

mysql> create table dateDemo(d date, t time, dt datetime, tms timestamp);
Query OK, 0 rows affected (0.02 sec)

mysql> desc dateDemo;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| d     | date      | YES  |     | NULL              |                             |
| t     | time      | YES  |     | NULL              |                             |
| dt    | datetime  | YES  |     | NULL              |                             |
| tms   | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
4 rows in set (0.00 sec)

mysql> insert into dateDemo values(now(), now(), now(), now());
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select * from dateDemo;
+------------+----------+---------------------+---------------------+
| d          | t        | dt                  | tms                 |
+------------+----------+---------------------+---------------------+
| 2016-11-16 | 11:08:11 | 2016-11-16 11:08:11 | 2016-11-16 11:08:11 |
+------------+----------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> insert into dateDemo values(now(), now(), now(), 0);
ERROR 1292 (22007): Incorrect datetime value: '0' for column 'tms' at row 1
mysql> insert into dateDemo values(now(), now(), now(), null);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from dateDemo;
+------------+----------+---------------------+---------------------+
| d          | t        | dt                  | tms                 |
+------------+----------+---------------------+---------------------+
| 2016-11-16 | 11:08:11 | 2016-11-16 11:08:11 | 2016-11-16 11:08:11 |
| 2016-11-16 | 11:09:21 | 2016-11-16 11:09:21 | 2016-11-16 11:09:21 |
+------------+----------+---------------------+---------------------+
2 rows in set (0.00 sec)

mysql> alter table dateDemo add tms2 timestamp;
ERROR 1067 (42000): Invalid default value for 'tms2'
mysql> alter table dateDemo add tms2 timestamp;
ERROR 1067 (42000): Invalid default value for 'tms2'
mysql> alter table dateDemo add timestamp2 timestamp;
ERROR 1067 (42000): Invalid default value for 'timestamp2'

char 自动删除末尾空格 varchar 不删除末尾空格

mysql> insert into vc values('ab  ', 'ab');
Query OK, 1 row affected (0.00 sec)

mysql> select * from vc;
+------+------+
| vc   | c    |
+------+------+
| ab   | ab   |
+------+------+
1 row in set (0.00 sec)

mysql> select length(vc), length(c) from vc;
+------------+-----------+
| length(vc) | length(c) |
+------------+-----------+
|          4 |         2 |
+------------+-----------+
1 row in set (0.01 sec)

mysql> insert into vc values('ab +', 'ab+');
Query OK, 1 row affected (0.00 sec)

mysql> select * from vc;
+------+------+
| vc   | c    |
+------+------+
| ab   | ab   |
| ab + | ab+  |
+------+------+
2 rows in set (0.00 sec)

mysql---存储数据