整型与浮点类型数据比较隐式转换规则

最新推荐文章于 2022-08-10 16:38:57 发布

Kingbase 研究院

最新推荐文章于 2022-08-10 16:38:57 发布

阅读量1.1k

点赞数

CC 4.0 BY-SA版权

文章标签： postgresql 数据分析 filter 数据仓库 oracle

本文链接：https://blog.csdn.net/lyu1026/article/details/124465098

本文详细介绍了KingbaseES数据库中integer, numeric和real数据类型比较时的类型转换规则，以及不当转换对SQL性能的影响，通过实例演示了不同数据类型之间的转换过程和优化建议。

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

KingbaseES 数值类型有整型和浮点类型，如：integer , bigint 就是整型类型的，而numeric , real 则是浮点类型的数据。整型和浮点类型数据在进行比较时，需要有将其中一边数据类型进行转换，不恰当的类型转换会导致索引无法使用，影响SQL的执行性能。作为DBA，应当熟悉转换规则，主动采取转换，避免隐式转换。以下以例子的形式向大家展示KingbaseES 数据库 integer , numeric (decimal) , real (double) 三中类型数据相互比较时的类型转换规则。

一、创建测试数据

create table test_integer(id integer,desc_info text);
create table test_numeric(id numeric,desc_info text);
create table test_real(id real,desc_info text);

insert into test_integer select generate_series(10001,20000),repeat('a',500);
insert into test_numeric select generate_series(10001,20000),repeat('a',500);
insert into test_real select generate_series(10001,20000),repeat('a',500);

create index ind_test_integer on test_integer(id);
create index ind_test_numeric on test_numeric(id);
create index ind_test_real on test_real(id);

analyze test_integer;
analyze test_numeric;
analyze test_real;

二、创建SQL游标

prepare test_integer_bind_numeric(numeric) as select * from test_integer where id=$1;
prepare test_integer_bind_real(real) as select * from test_integer where id=$1;

prepare test_numeric_bind_integer(integer) as select * from test_numeric where id=$1;
prepare test_numeric_bind_real(real) as select * from test_numeric where id=$1;

prepare test_real_bind_numeric(numeric) as select * from test_real where id=$1;
prepare test_real_bind_integer(integer) as select * from test_real where id=$1;

三、验证转换规则

1、integer 转成 numeric

testdb=# explain execute test_integer_bind_numeric(12345);
                           QUERY PLAN                            
-----------------------------------------------------------------
 Seq Scan on test_integer  (cost=0.00..817.00 rows=50 width=508)
   Filter: ((id)::numeric = '12345'::numeric)
(2 rows)

testdb=# explain execute test_numeric_bind_integer(12345);
                                      QUERY PLAN                                       
---------------------------------------------------------------------------------------
 Index Scan using ind_test_numeric on test_numeric  (cost=0.29..8.30 rows=1 width=510)
   Index Cond: (id = '12345'::numeric)
(2 rows)

2、integer 转成 real

testdb=# explain execute test_integer_bind_real(12345);
                           QUERY PLAN                            
-----------------------------------------------------------------
 Seq Scan on test_integer  (cost=0.00..817.00 rows=50 width=508)
   Filter: ((id)::double precision = '12345'::real)
(2 rows)

testdb=# explain execute test_real_bind_integer(12345);
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Index Scan using ind_test_real on test_real  (cost=0.29..8.30 rows=1 width=508)
   Index Cond: (id = '12345'::double precision)
(2 rows)

3、numeric 转成 real

testdb=# explain execute test_numeric_bind_real(12345);
                           QUERY PLAN                            
-----------------------------------------------------------------
 Seq Scan on test_numeric  (cost=0.00..817.00 rows=50 width=510)
   Filter: ((id)::double precision = '12345'::real)
(2 rows)

testdb=# explain execute test_real_bind_numeric(12345);
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Index Scan using ind_test_real on test_real  (cost=0.29..8.30 rows=1 width=508)
   Index Cond: (id = '12345'::double precision)
(2 rows)