Double类型精度为15-16位,如果超过该范围,数据就会失真。以下实验在MySQL、ClickHouse、TDengine 三种不同数据库中,输入数据超过16位情况下,查看其显示精度。
MySQL:不用多介绍,已经是老牌数据库(Database)了。
ClickHouse:列式数据库,主要用于数据分析(OLAP)领域。
TDengine:最近特别火的时序数据库。
声明:此实验无任何目的,也没什么意义。
Double类型说明:
MySQL
create table tb(v1 int,v2 double);
insert into tb values(10,1234567890);
insert into tb values(15,123456789012345);
insert into tb values(16,1234567890123456);
insert into tb values(17,12345678901234567);
insert into tb values(18,123456789012345678);
insert into tb values(19,1234567890123456789);
ClickHouse
create table tb(v1 int,v2 double) engine=TinyLog
insert into tb values(10,1234567890);
insert into tb values(15,123456789012345);
insert into tb values(16,1234567890123456);
insert into tb values(17,12345678901234567);
insert into tb values(18,123456789012345678);
insert into tb values(19,1234567890123456789);
TDengine
TDengine是时序数据库,建表时第一列必须是时间戳。
create table tb(ts timestamp,v1 int,v2 double);
insert into tb values(1638630000010,10,1234567890);
insert into tb values(1638630000015,15,123456789012345);
insert into tb values(1638630000016,16,1234567890123456);
insert into tb values(1638630000017,17,12345678901234567);
insert into tb values(1638630000018,18,123456789012345678);
insert into tb values(1638630000019,19,1234567890123456789);
结论
三种数据库对double数据的处理都没有问题,不过个人感觉MySQL的显示可读性更强。
补充实验:
ClickHouse
test1.dyq.com :) insert into tb values(6748392716482657358);
INSERT INTO tb VALUES
Ok.
1 rows in set. Elapsed: 0.011 sec.
test1.dyq.com :) select * from tb;
SELECT *
FROM tb
┌──────────────────v1─┐
│ 6748392716482657000 │
└─────────────────────┘
1 rows in set. Elapsed: 0.010 sec.
TDengine
taos> insert into tb values(now,6748392716482657358);
Query OK, 1 of 1 row(s) in database (0.005863s)
taos> select * from tb;
ts | v1 |
======================================================
2021-12-05 17:23:01.715 | 6748392716482657280.000000000 |
Query OK, 1 row(s) in set (0.008368s)
将数据进行二进制转换
[root@test1 ~]# echo "obase=2;6748392716482657358"|bc
101110110100111000110110110101111001110110010000011110001001110
[root@test1 ~]# echo "obase=2;6748392716482657280"|bc
101110110100111000110110110101111001110110010000011110000000000
[root@test1 ~]# echo "obase=2;6748392716482657000"|bc
101110110100111000110110110101111001110110010000011101011101000
从结果来看,貌似ClickHouse把16位以后的数直接补0处理了。