【LightDB】mysql字符和数值比较兼容

mysql字符和数值比较

mysql在进行字符和数值比较时，会将字符类型转换为数值类型进行比较。例如：

select * from test where 1 = '1';
select * from test where 2 > '12';
select * from test where '2' > '12'; -- 结果和示例3不同
select * from test where 2 > 12; -- 结果同示例3

通过上述sql验证了结论

mysql字符和数值转换规则

select * from test where 1 = '1a'; -- 1= '1a'-> true
select * from est where 1 = ''; -- 1= ''-> false
select * from est where 0 = ''; -- 0= ''-> true

通过以上测试可以看出，mysql模式下字符串转数字时，会忽略尾部不合法字符。

lightdb兼容mysql的字符和数字比较

lightdb在21.3版本已支持字符和数值的比较。和mysql一样，也是将字符转换成数值。但当字符中出现不合法字符时，会报错。23.4版本提供了这一支持。支持后效果如下：

lightdb@mysql=# select * from dual where 1 = '1a'; 
 dummy 
-------
 X
(1 row)

lightdb@mysql=# select * from dual where 1 = ''; 
 dummy 
-------
(0 rows)

lightdb@mysql=# select * from dual where 0 = ''; 
 dummy 
-------
 X
(1 row)
lightdb@mysql=# create TABLE INT2_NUMERIC_TBL(f1 smallint, f2 int, f3 bigint, f4 numeric(20,10),id serial);
CREATE TABLE
lightdb@mysql=# INSERT INTO INT2_NUMERIC_TBL VALUES ('34.5'::varchar,'34.5'::varchar,'34.5'::varchar,'34.5'::varchar);
INSERT 0 1
lightdb@mysql=# INSERT INTO INT2_NUMERIC_TBL VALUES ('2e2'::varchar,'2e2'::varchar,'2e2'::varchar,'2e2'::varchar);
INSERT 0 1
lightdb@mysql=# INSERT INTO INT2_NUMERIC_TBL VALUES ('2e2a'::varchar,'2e2a'::varchar,'2e2a'::varchar,'2e2a'::varchar); 
INSERT 0 1
lightdb@mysql=# INSERT INTO INT2_NUMERIC_TBL VALUES ('34.5','34.5','34.5','34.5');
INSERT 0 1
lightdb@mysql=# INSERT INTO INT2_NUMERIC_TBL VALUES ('2e2','2e2','2e2','2e2');
INSERT 0 1
lightdb@mysql=# select * from INT2_NUMERIC_TBL;
 f1  | f2  | f3  |       f4       | id 
-----+-----+-----+----------------+----
  35 |  35 |  35 |  34.5000000000 |  1
 200 | 200 | 200 | 200.0000000000 |  2
 200 | 200 | 200 | 200.0000000000 |  3
  34 |  34 |  34 |  34.5000000000 |  4
   2 |   2 |   2 | 200.0000000000 |  5
(5 rows)

lightdb@mysql=# select * from INT2_NUMERIC_TBL where f1 = '';
 f1 | f2 | f3 | f4 | id 
----+----+----+----+----
(0 rows)

lightdb@mysql=# select * from INT2_NUMERIC_TBL where f2 = '';
 f1 | f2 | f3 | f4 | id 
----+----+----+----+----
(0 rows)

lightdb@mysql=# select * from INT2_NUMERIC_TBL where f3 = '';
 f1 | f2 | f3 | f4 | id 
----+----+----+----+----
(0 rows)

lightdb@mysql=# select * from INT2_NUMERIC_TBL where f4 = '';
 f1 | f2 | f3 | f4 | id 
----+----+----+----+----
(0 rows)

lightdb@mysql=# 
lightdb@mysql=# select * from INT2_NUMERIC_TBL where f1 = '1a';
 f1 | f2 | f3 | f4 | id 
----+----+----+----+----
(0 rows)

lightdb@mysql=# select * from INT2_NUMERIC_TBL where f2 = '1a';
 f1 | f2 | f3 | f4 | id 
----+----+----+----+----
(0 rows)

lightdb@mysql=# select * from INT2_NUMERIC_TBL where f3 = '1a';
 f1 | f2 | f3 | f4 | id 
----+----+----+----+----
(0 rows)

lightdb@mysql=# select * from INT2_NUMERIC_TBL where f4 = '1a';
 f1 | f2 | f3 | f4 | id 
----+----+----+----+----
(0 rows)

lightdb@mysql=# 
lightdb@mysql=# select * from INT2_NUMERIC_TBL where f1 = '1e2';
 f1 | f2 | f3 | f4 | id 
----+----+----+----+----
(0 rows)

lightdb@mysql=# select * from INT2_NUMERIC_TBL where f2 = '1e2';
 f1 | f2 | f3 | f4 | id 
----+----+----+----+----
(0 rows)

lightdb@mysql=# select * from INT2_NUMERIC_TBL where f3 = '1e2';
 f1 | f2 | f3 | f4 | id 
----+----+----+----+----
(0 rows)

lightdb@mysql=# select * from INT2_NUMERIC_TBL where f4 = '1e2';
 f1 | f2 | f3 | f4 | id 
----+----+----+----+----
(0 rows)

lightdb@mysql=# select * from INT2_NUMERIC_TBL where f1 = '1e2a';
 f1 | f2 | f3 | f4 | id 
----+----+----+----+----
(0 rows)

lightdb@mysql=# select * from INT2_NUMERIC_TBL where f2 = '1e2a';
 f1 | f2 | f3 | f4 | id 
----+----+----+----+----
(0 rows)

lightdb@mysql=# select * from INT2_NUMERIC_TBL where f3 = '1e2a';
 f1 | f2 | f3 | f4 | id 
----+----+----+----+----
(0 rows)

lightdb@mysql=# select * from INT2_NUMERIC_TBL where f4 = '1e2a';
 f1 | f2 | f3 | f4 | id 
----+----+----+----+----
(0 rows)