POSTFRESQL系列：SQL中使用正则校验处理

需求描述

• 需要处理数据库表中某一个字段对应的数据，存储的时候是字符类型，插入数据逻辑中没有做较强的限制，导致后期业务需求中需要获取统一数据处理的时候的产生了不必要的问题。
• 以上是使用sql级别解决

SQL部分

COALESCE(NULLIF(REGEXP_REPLACE(dataTable.ziduan, '[^0-9]', '', 'g'), ''), '0')::NUMERIC < 80

SQL部分解释

1. 这个函数使用正则表达式 [ ^0-9] 匹配非数字字符，将它们替换为空字符串。

   REGEXP_REPLACE(dataTable.ziduan, '[^0-9]', '', 'g'):
   

2. 如果 dataTable.ziduan 列中全是非数字字符，替换后的结果将是空字符串。通过 NULLIF 将空字符串转换为 NULL。

       NULLIF(REGEXP_REPLACE(...), ''):
   

3. 使用 COALESCE 来处理 NULL 值，如果 NULLIF 返回 NULL，则使用默认值 ‘0’。

     COALESCE(NULLIF(...), '0'):
   

4. 将最终结果转换为 NUMERIC 类型，以便可以进行数值比较。

       ::NUMERIC:
   

5. < 80这部分是将数值与 80 进行比较。

SQL部分大量执行的效率如何？

1. REGEXP_REPLACE 函数:

   正则表达式的处理通常比直接字符串操作要慢，尤其是当需要遍历和替换整个字符串时。因此，这一步是最消耗性能的部分。如果你的数据集非常大，REGEXP_REPLACE 可能成为瓶颈。

2. NULLIF 和 COALESCE 函数:

   这两个函数的执行开销相对较小。NULLIF 只是简单地比较两个值，COALESCE 也只是在一组值中找到第一个非空值，这两者的性能通常都不会成为瓶颈。

3. 数据类型转换 (::NUMERIC):

   数据类型的转换（特别是字符串到数值的转换）可能会消耗一定的资源，特别是在大量数据的情况下。虽然这一步通常不会成为主要的性能瓶颈，但它仍然对整体性能有影响。

优化方向

1. 尽量减少正则表达式的使用:

   如果你可以确保 dataTable.ziduan 列中的数据大部分是有效的数字，并且只在极少数情况下出现非数字字符，你可以考虑预先清洗数据，避免在查询时使用正则表达式。

2. 数据库列类型的优化:

   如果可能，将dataTable.ziduan 列的类型直接存储为 NUMERIC 或者 INTEGER 而不是字符串，这样可以完全避免后续查询中的类型转换和正则表达式处理，提高查询性能。

3. 索引优化:

   如果这个查询非常频繁，可以考虑在相关列上创建索引。不过，需要注意的是，这种情况下使用 REGEXP_REPLACE 可能无法有效利用索引。

4. 缓存查询结果:

   如果你的查询经常涉及到相同的计算，可以考虑使用缓存来保存处理后的结果，避免每次查询时都进行重复计算。