目录
一、创建测试数据
二、创建SQL游标
三、验证转换规则
1、integer 转成 numeric
2、integer 转成 real
3、numeric 转成 real
四、结论
KingbaseES 数值类型有整型和浮点类型,如:integer , bigint 就是整型类型的,而numeric , real 则是浮点类型的数据。整型和浮点类型数据在进行比较时,需要有将其中一边数据类型进行转换,不恰当的类型转换会导致索引无法使用,影响SQL的执行性能。
作为DBA,应当熟悉转换规则,主动采取转换,避免隐式转换。以下以例子的形式向大家展示KingbaseES 数据库 integer , numeric (decimal) , real (double) 三中类型数据相互比较时的类型转换规则。
一、创建测试数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 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游标
1 2 3 4 5 6 7 8 | 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
1 2 3 4 5 6 7 8 9 10 11 | 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 )<br><br>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
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 | 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 ) |
四、结论
integer , numeric , real 三种类型的数据在比较时,转换规则 integer -> numeric -> real 。