prestosql报错_那些年坑过我们的SQL

SQL中的NULL值、空值和类型转换

我们平常写SQL中遇到过滤空值或者NULL值的处理&＃xff0c;尤其是在非关系型数据库中没有唯一键和默认值的约束要求&＃xff0c;很容易出现NULL值或者空值。很多时候我们总是会出现困扰&＃xff0c;到底是该使用&＃61;&＃39; &＃39; 还是用 is NULL呢&＃xff1f;

另外我们一个多表JOIN的查询在这个SQL上跑的好好的&＃xff0c;一旦换了一个SQL执行引擎就提示我们JOIN两边的数据类型不一致&＃xff0c;这些都是为什么呢&＃xff1f;

今天就带大家一起领略一下各类SQL中空值以及类型转换中的那些坑。

一&＃xff1a;空值和NULL值

• 底层字段类型为字符型(string,varchar,char等):

  hive:如果底层存储的是NULL值&＃xff0c;我们必须要用 is NULL来判断&＃xff0c;如果底层存储的是&＃39;  &＃39;&＃xff0c;则需要使用 &＃61;&＃39; &＃39;来判断。在hive中NULL默认存储为&＃39;\N&＃39;,但是&＃39; &＃39;存储还是&＃39; &＃39;。

  presto:与hive保持一致。

  spark:与hive保持一致。

  impala:与hive保持一致。

  mysql:使用上与hive使用保持一致。

• 底层字段是数值型(int,bigint等)

  hive:由于int类型的数据在hive中默认存储为&＃39;\N&＃39;,所以查询的时候必须要使用is NULL才能取出空的值。

  presto:与hive中保持一致&＃xff0c;不能使用&＃61;&＃39;&＃39;,会报类型错误。

  spark:与hive中保持一致。

  impala:与presto保持一致&＃xff0c;只能使用is NULL,使用&＃61;&＃39;&＃39;由于后面是字符类型&＃xff0c;会报错。

  mysql:与presto保持一致&＃xff0c;使用is NULL,不过在mysql中数值类型一般会采用默认值0来填充。

这里需要特别注意的地方是&＃xff1a;NULL值是不支持比较的。什么意思呢&＃xff0c;比如说有一列ID的值是 NULL&＃xff0c;如果使用ID<>&＃39;123&＃39;是无法把这条记录取出来的&＃xff0c;只能使用ID is NULL。当然NULL和NULL比较也是没有意义的。另外空值也不支持与具体的值来比较&＃xff0c;使用的时候也要注意&＃xff0c;这部分数据需要特殊处理&＃xff0c;如&＃xff1a;先单独过滤掉或者单独取出来。

二&＃xff1a;SQL中的字段类型转换

隐式类型转换&＃xff0c;又叫自动类型转换&＃xff0c;意思就是当要比较的两个对象(字段)类型不一致是&＃xff0c;程序自动调整为一致的情况。通常是支持又小向大(兼容)的类型转换&＃xff0c;如从int到bigint或者到string。不过这里并不是绝对的&＃xff0c;一般程序会分析两个对象之间的运算符&＃xff0c;如果是数值运算符(&＃43;-*/)的话再分析对象里面的具体的值&＃xff0c;如果可以转为数字计算就会自动转为数字计算。否则的话会SQL可以运行但是结果会是空值。

支持隐式类型转换的SQL语言有&＃xff1a;HIVE,SPARK&＃xff0c;MYSQL,其中MYSQL如果两边不支持运算的话不一定返回空值&＃xff0c;要看具体的情况&＃xff0c;例如如果是&＃39;张三&＃39;&＃43;1 &＃xff0c;返回结果是1。

显式类型转换&＃xff0c;又叫强制类型转换&＃xff0c;就是说程序不会自动帮你把两个对象的类型转为一致&＃xff0c;需要自己手动显示声明。常用的显示声明有cast(col as type),convert(type col)&＃xff0c;一般对cast较为熟悉&＃xff0c;各种SQL基本通用。这里需要说明的是理论上有了强制类型转换在所有数据类型之间是可以互转的。但是转换的时候要确保底层数据转换后是有意义的&＃xff0c;否则即使可以转&＃xff0c;结果也是没有意思甚至是不正确的。

必须使用显式类型转换的SQL&＃xff1a;PRESTO,IMPALA。另外需要注意的是mysql在使用强制类型转换的时候如果是把字符串转为数字AS后的类型只支持DECIMAL&＃xff0c;SIGNED&＃xff0c;UNSIGNED&＃xff0c;而不支持其他类型。

通过以上空值的分析&＃xff0c;相信大家对SQL中的NULL、空值有了进一步的了解&＃xff0c;下次针对具体的数据采用上面的办法就可以确保取出的数据是正确的。通过类型转换的分析&＃xff0c;大家在写SQL的时候首先要明确使用的是那种类型的SQL&＃xff0c;JOIN的时候两个字段的类型是否一致&＃xff0c;这样使用起来才能游刃有余。

备注&＃xff1a;以上是本人根据日常工作中及网上相关资料的总结&＃xff0c;虽然经过验证但不免某些地方存在瑕疵&＃xff0c;如有发现也请大家积极指正&＃xff0c;互相学习。