针对MySQL特有函数的梧桐替代解决方法

MySQL 8.0 特有函数清单包含了一些重要的JSON函数、正则表达式函数和地理空间函数，我们针对这些内容，梳理了基于梧桐6.0版本的替代解决方法。以方便用户在平时的国产化项目及其它工作中更加方便的对照及参考。

MySQL 8.0 特有函数清单（扩展）

JSON 函数：

1. JSON_ARRAYAGG(): 将某列的值聚合成一个JSON数组
2. JSON_OBJECTAGG(): 将键值对聚合成一个JSON对象
3. JSON_PRETTY(): 格式化输出JSON字符串，使其更易读
4. JSON_TABLE(): 将JSON数组转换为关系表
5. JSON_MERGE_PATCH(): 将两个JSON对象合并，后者的键值覆盖前者的键值
6. JSON_STORAGE_SIZE(): 计算JSON对象的存储大小
7. JSON_KEYS(): 获取JSON对象中的所有键
8. JSON_DEPTH(): 计算JSON对象的嵌套深度
9. JSON_LENGTH(): 计算JSON对象的长度
10. JSON_CONTAINS(): 检查一个JSON对象是否包含另一个JSON对象
11. JSON_CONTAINS_PATH(): 检查JSON对象是否包含指定路径
12. JSON_EXTRACT(): 从JSON对象中提取指定路径的值
13. JSON_REMOVE(): 从JSON对象中删除指定路径的值
14. JSON_INSERT(): 向JSON对象中插入值
15. JSON_REPLACE(): 替换JSON对象中的指定路径的值
16. JSON_SET(): 设置JSON对象中的指定路径的值
17. JSON_UNQUOTE(): 去掉JSON字符串的引号
18. JSON_VALID(): 检查字符串是否为有效的JSON

正则表达式函数：

1. REGEXP_LIKE(): 检查字符串是否匹配正则表达式
2. REGEXP_INSTR(): 返回正则表达式匹配的位置
3. REGEXP_SUBSTR(): 返回匹配正则表达式的子字符串
4. REGEXP_REPLACE(): 用正则表达式替换字符串中的匹配部分

地理空间函数：

1. ST_DISTANCE_SPHERE(): 计算球面上两点之间的距离
2. ST_MAKEENVELOPE(): 创建一个矩形包络
3. ST_MAKEPOINT(): 创建一个点
4. ST_TRANSFORM(): 变换几何对象的坐标系
5. ST_GEOMFROMGEOJSON(): 从GeoJSON创建几何对象
6. ST_ASGEOJSON(): 将几何对象转换为GeoJSON
7. ST_ISVALID(): 检查几何对象是否有效
8. ST_ASTEXT(): 将几何对象转换为文本
9. ST_SRID(): 返回几何对象的空间参考系统标识符
10. ST_SETSRID(): 设置几何对象的空间参考系统标识符
11. ST_DISTANCE(): 计算两几何对象之间的距离
12. ST_INTERSECTS(): 检查两几何对象是否相交

详细说明及替代方案

JSON 函数：

1. JSON_ARRAYAGG(): 将某列的值聚合成一个JSON数组

   • MySQL 8.0:

     -- 将某列的值聚合成一个JSON数组
     SELECT JSON_ARRAYAGG(column) AS json_array FROM table_name;
     

   • 梧桐 6.0 替代方案:

     -- 将某列的值聚合成一个JSON数组
     SELECT json_agg(column) AS json_array FROM table_name;
     

2. JSON_OBJECTAGG(): 将键值对聚合成一个JSON对象

   • MySQL 8.0:

     -- 将键值对聚合成一个JSON对象
     SELECT JSON_OBJECTAGG(key, value) AS json_object FROM table_name;
     

   • 梧桐 6.0 替代方案:

     -- 将键值对聚合成一个JSON对象
     SELECT jsonb_object_agg(key, value) AS json_object FROM table_name;
     

3. JSON_PRETTY(): 格式化输出JSON字符串，使其更易读

   • MySQL 8.0:

     -- 格式化输出JSON字符串
     SELECT JSON_PRETTY('{"key": "value"}');
     

   • 梧桐 6.0 替代方案:

     -- 格式化输出JSON字符串
     SELECT jsonb_pretty('{"key": "value"}'::jsonb);
     

4. JSON_TABLE(): 将JSON数组转换为关系表

   • MySQL 8.0:

     -- 将JSON数组转换为关系表
     SELECT * FROM JSON_TABLE(
       '[{"id": 1, "name": "John"}, {"id": 2, "name": "Jane"}]',
       "$[*]" COLUMNS (
         id INT PATH "$.id",
         name VARCHAR(50) PATH "$.name"
       )
     ) AS jt;
     

   • 梧桐 6.0 替代方案:

     -- 将JSON数组转换为关系表
     SELECT * FROM json_to_recordset(
       '[{"id": 1, "name": "John"}, {"id": 2, "name": "Jane"}]'
     ) AS jt(id INT, name VARCHAR);
     

5. JSON_MERGE_PATCH(): 将两个JSON对象合并，后者的键值覆盖前者的键值

   • MySQL 8.0:

     -- 将两个JSON对象合并，后者的键值覆盖前者的键值
     SELECT JSON_MERGE_PATCH('{"a": 1, "b": 2}', '{"b": 3, "c": 4}');
     

   • 梧桐 6.0 替代方案:

     -- 将两个JSON对象合并，后者的键值覆盖前者的键值
     SELECT '{"a": 1, "b": 2}'::jsonb || '{"b": 3, "c": 4}'::jsonb;
     

6. JSON_STORAGE_SIZE(): 计算JSON对象的存储大小

   • MySQL 8.0:

     -- 计算JSON对象的存储大小
     SELECT JSON_STORAGE_SIZE('{"a": 1, "b": 2}');
     

   • 梧桐 6.0 替代方案:

     -- 计算JSON对象的存储大小
     SELECT pg_column_size('{"a": 1, "b": 2}'::jsonb);
     

7. JSON_KEYS(): 获取JSON对象中的所有键

   • MySQL 8.0:

     -- 获取JSON对象中的所有键
     SELECT JSON_KEYS('{"a": 1, "b": 2}');
     

   • 梧桐 6.0 替代方案:

     -- 获取JSON对象中的所有键
     SELECT jsonb_object_keys('{"a": 1, "b": 2}'::jsonb);
     

8. JSON_DEPTH(): 计算JSON对象的嵌套深度

   • MySQL 8.0:

     -- 计算JSON对象的嵌套深度
     SELECT JSON_DEPTH('{"a": {"b": {"c": 1}}}');
     

   • 梧桐 6.0 替代方案:
     梧桐DB没有直接的替代函数，需要通过递归查询实现。

     -- 计算JSON对象的嵌套深度
     WITH RECURSIVE json_tree AS (
       SELECT
         jsonb_typeof('{"a": {"b": {"c": 1}}}'::jsonb) AS type,
         1 AS depth,
         '{"a": {"b": {"c": 1}}}'::jsonb AS js
       UNION ALL
       SELECT
         jsonb_typeof(value) AS type,
         depth + 1 AS depth,
         value
       FROM json_tree, jsonb_each(js)
       WHERE jsonb_typeof(js) = 'object'
     )
     SELECT MAX(depth) FROM json_tree;
     

9. JSON_LENGTH(): 计算JSON对象的长度

   • MySQL 8.0:

     -- 计算JSON对象的长度
     SELECT JSON_LENGTH('{"a": 1, "b": 2}');
     

   • 梧桐 6.0 替代方案:

     -- 计算JSON对象的长度
     SELECT jsonb_array_length('{"a": 1, "b": 2}'::jsonb);
     

10. JSON_CONTAINS(): 检查一个JSON对象是否包含另一个JSON对象

    • MySQL 8.0:

      -- 检查一个JSON对象是否包含另一个JSON对象
      SELECT JSON_CONTAINS('{"a": 1, "b": 2}', '{"a": 1}');
      

    • 梧桐 6.0 替代方案:

      -- 检查一个JSON对象是否包含另一个JSON对象
      SELECT '{"a": 1, "b": 2}'::jsonb @> '{"a": 1}'::jsonb;
      

11. JSON_CONTAINS_PATH(): 检查JSON对象是否包含指定路径

    • MySQL 8.0:

      -- 检查JSON对象是否包含指定路径
      SELECT JSON_CONTAINS_PATH('{"a": 1, "b": 2}', 'one', '$.a');
      

    • 梧桐 6.0 替代方案:
      梧桐DB没有直接的替代函数，可以通过 jsonb_path_exists 实现类似功能。

      -- 检查JSON对象是否包含指定路径
      SELECT jsonb_path_exists('{"a": 1, "b": 2}'::jsonb, '$.a');
      

12. JSON_EXTRACT(): 从JSON对象中提取指定路径的值

    • MySQL 8.0:

      -- 从JSON对象中提取指定路径的值
      SELECT JSON_EXTRACT('{"a": 1, "b": 2}', '$.a');
      

    • 梧桐 6.0 替代方案:

      -- 从JSON对象中提取指定路径的值
      SELECT '{"a": 1, "b": 2}'::jsonb -> 'a';
      

13. JSON_REMOVE(): 从JSON对象中删除指定路径的值

    • MySQL 8.0:

      -- 从JSON对象中删除指定路径的值
      SELECT JSON_REMOVE('{"a": 1, "b": 2}', '$.a');
      

    • 梧桐 6.0 替代方案:

      -- 从JSON对象中删除指定路径的值
      SELECT '{"a": 1, "b": 2}'::jsonb - 'a';
      

14. JSON_INSERT(): 向JSON对象中插入值

    • MySQL 8.0:

      -- 向JSON对象中插入值
      SELECT JSON_INSERT('{"a": 1}', '$.b', 2);
      

    • 梧桐 6.0 替代方案:
      梧桐DB没有直接的替代函数，可以通过 jsonb_set 实现类似功能。

      -- 向JSON对象中插入值
      SELECT jsonb_set('{"a": 1}'::jsonb, '{b}', '2'::jsonb);
      

15. JSON_REPLACE(): 替换JSON对象中的指定路径的值

    • MySQL 8.0:

      -- 替换JSON对象中的指定路径的值
      SELECT JSON_REPLACE('{"a": 1, "b": 2}', '$.a', 3);
      

    • 梧桐 6.0 替代方案:

      -- 替换JSON对象中的指定路径的值
      SELECT jsonb_set('{"a": 1, "b": 2}'::jsonb, '{a}', '3'::jsonb);
      

16. JSON_SET(): 设置JSON对象中的指定路径的值

    • MySQL 8.0:

      -- 设置JSON对象中的指定路径的值
      SELECT JSON_SET('{"a": 1, "b": 2}', '$.a', 3);
      

    • 梧桐 6.0 替代方案:

      -- 设置JSON对象中的指定路径的值
      SELECT jsonb_set('{"a": 1, "b": 2}'::jsonb, '{a}', '3'::jsonb);
      

17. JSON_UNQUOTE(): 去掉JSON字符串的引号

    • MySQL 8.0:

      -- 去掉JSON字符串的引号
      SELECT JSON_UNQUOTE('"hello"');
      

    • 梧桐 6.0 替代方案:
      梧桐DB没有直接的替代函数，可以使用 text 类型的转换来实现类似功能。

      -- 去掉JSON字符串的引号
      SELECT 'hello'::text;
      

18. JSON_VALID(): 检查字符串是否为有效的JSON

    • MySQL 8.0:

      -- 检查字符串是否为有效的JSON
      SELECT JSON_VALID('{"a": 1}');
      

    • 梧桐 6.0 替代方案:
      梧桐DB没有直接的替代函数，可以通过 TRY_CAST 进行 JSONB 类型转换判断。

      -- 检查字符串是否为有效的JSON
      SELECT CASE WHEN TRY_CAST('{"a": 1}' AS JSONB) IS NOT NULL THEN TRUE ELSE FALSE END;
      

正则表达式函数：

1. REGEXP_LIKE(): 检查字符串是否匹配正则表达式

   • MySQL 8.0:

     -- 检查字符串是否匹配正则表达式
     SELECT column FROM table_name WHERE REGEXP_LIKE(column, 'pattern');
     

   • 梧桐 6.0 替代方案:

     -- 检查字符串是否匹配正则表达式
     SELECT column FROM table_name WHERE column ~ 'pattern';
     

2. REGEXP_INSTR(): 返回正则表达式匹配的位置

   • MySQL 8.0:

     -- 返回正则表达式匹配的位置
     SELECT REGEXP_INSTR(column, 'pattern') AS position FROM table_name;
     

   • 梧桐 6.0 替代方案:
     梧桐DB没有直接的替代函数，可以使用 POSITION 和 REGEXP_MATCHES 结合实现。

     -- 返回正则表达式匹配的位置
     SELECT POSITION((REGEXP_MATCHES(column, 'pattern'))[1] IN column) AS position FROM table_name;
     

3. REGEXP_SUBSTR(): 返回匹配正则表达式的子字符串

   • MySQL 8.0:

     -- 返回匹配正则表达式的子字符串
     SELECT REGEXP_SUBSTR(column, 'pattern') AS substring FROM table_name;
     

   • 梧桐 6.0 替代方案:

     -- 返回匹配正则表达式的子字符串
     SELECT (REGEXP_MATCHES(column, 'pattern'))[1] AS substring FROM table_name;
     

4. REGEXP_REPLACE(): 用正则表达式替换字符串中的匹配部分

   • MySQL 8.0:

     -- 用正则表达式替换字符串中的匹配部分
     SELECT REGEXP_REPLACE(column, 'pattern', 'replacement') AS replaced_column FROM table_name;
     

   • 梧桐 6.0 替代方案:

     -- 用正则表达式替换字符串中的匹配部分
     SELECT REGEXP_REPLACE(column, 'pattern', 'replacement') AS replaced_column FROM table_name;
     

地理空间函数：

1. ST_DISTANCE_SPHERE(): 计算球面上两点之间的距离

   • MySQL 8.0:

     -- 计算球面上两点之间的距离
     SELECT ST_DISTANCE_SPHERE(POINT(-71.060316, 48.432044), POINT(-71.055017, 48.431664));
     

   • 梧桐 6.0 替代方案:

     -- 计算球面上两点之间的距离
     SELECT ST_Distance_Sphere(ST_MakePoint(-71.060316, 48.432044), ST_MakePoint(-71.055017, 48.431664));
     

2. ST_MAKEENVELOPE(): 创建一个矩形包络

   • MySQL 8.0:

     -- 创建一个矩形包络
     SELECT ST_MAKEENVELOPE(-71.060316, 48.432044, -71.055017, 48.431664);
     

   • 梧桐 6.0 替代方案:

     -- 创建一个矩形包络
     SELECT ST_MakeEnvelope(-71.060316, 48.432044, -71.055017, 48.431664);
     

3. ST_MAKEPOINT(): 创建一个点

   • MySQL 8.0:

     -- 创建一个点
     SELECT ST_MAKEPOINT(-71.060316, 48.432044);
     

   • 梧桐 6.0 替代方案:

     -- 创建一个点
     SELECT ST_MakePoint(-71.060316, 48.432044);
     

4. ST_TRANSFORM(): 变换几何对象的坐标系

   • MySQL 8.0:

     -- 变换几何对象的坐标系
     SELECT ST_TRANSFORM(ST_GeomFromText('POINT(-71.060316 48.432044)', 4326), 3857);
     

   • 梧桐 6.0 替代方案:

     -- 变换几何对象的坐标系
     SELECT ST_Transform(ST_GeomFromText('POINT(-71.060316 48.432044)', 4326), 3857);
     

5. ST_GEOMFROMGEOJSON(): 从GeoJSON创建几何对象

   • MySQL 8.0:

     -- 从GeoJSON创建几何对象
     SELECT ST_GEOMFROMGEOJSON('{"type": "Point", "coordinates": [48.432044, -71.060316]}');
     

   • 梧桐 6.0 替代方案:

     -- 从GeoJSON创建几何对象
     SELECT ST_GeomFromGeoJSON('{"type": "Point", "coordinates": [48.432044, -71.060316]}');
     

6. ST_ASGEOJSON(): 将几何对象转换为GeoJSON

   • MySQL 8.0:

     -- 将几何对象转换为GeoJSON
     SELECT ST_ASGEOJSON(ST_GeomFromText('POINT(-71.060316 48.432044)'));
     

   • 梧桐 6.0 替代方案:

     -- 将几何对象转换为GeoJSON
     SELECT ST_AsGeoJSON(ST_GeomFromText('POINT(-71.060316 48.432044)'));
     

7. ST_ISVALID(): 检查几何对象是否有效

   • MySQL 8.0:

     -- 检查几何对象是否有效
     SELECT ST_ISVALID(ST_GeomFromText('POINT(-71.060316 48.432044)'));
     

   • 梧桐 6.0 替代方案:

     -- 检查几何对象是否有效
     SELECT ST_IsValid(ST_GeomFromText('POINT(-71.060316 48.432044)'));
     

8. ST_ASTEXT(): 将几何对象转换为文本

   • MySQL 8.0:

     -- 将几何对象转换为文本
     SELECT ST_ASTEXT(ST_GeomFromText('POINT(-71.060316 48.432044)'));
     

   • 梧桐 6.0 替代方案:

     -- 将几何对象转换为文本
     SELECT ST_AsText(ST_GeomFromText('POINT(-71.060316 48.432044)'));
     

9. ST_SRID(): 返回几何对象的空间参考系统标识符

   • MySQL 8.0:

     -- 返回几何对象的空间参考系统标识符
     SELECT ST_SRID(ST_GeomFromText('POINT(-71.060316 48.432044)', 4326));
     

   • 梧桐 6.0 替代方案:

     -- 返回几何对象的空间参考系统标识符
     SELECT ST_SRID(ST_GeomFromText('POINT(-71.060316 48.432044)', 4326));
     

10. ST_SETSRID(): 设置几何对象的空间参考系统标识符

    • MySQL 8.0:

      -- 设置几何对象的空间参考系统标识符
      SELECT ST_SETSRID(ST_GeomFromText('POINT(-71.060316 48.432044)', 4326), 3857);
      

    • 梧桐 6.0 替代方案:

      -- 设置几何对象的空间参考系统标识符
      SELECT ST_SetSRID(ST_GeomFromText('POINT(-71.060316 48.432044)', 4326), 3857);
      

11. ST_DISTANCE(): 计算两几何对象之间的距离

    • MySQL 8.0:

      -- 计算两几何对象之间的距离
      SELECT ST_DISTANCE(ST_GeomFromText('POINT(-71.060316 48.432044)'), ST_GeomFromText('POINT(-71.055017 48.431664)'));
      

    • 梧桐 6.0 替代方案:

      -- 计算两几何对象之间的距离
      SELECT ST_Distance(ST_GeomFromText('POINT(-71.060316 48.432044)'), ST_GeomFromText('POINT(-71.055017 48.431664)'));
      

12. ST_INTERSECTS(): 检查两几何对象是否相交

    • MySQL 8.0:

      -- 检查两几何对象是否相交
      SELECT ST_INTERSECTS(ST_GeomFromText('POINT(-71.060316 48.432044)'), ST_GeomFromText('POLYGON((0 0, 1 1, 1 0, 0 0))'));
      

    • 梧桐 6.0 替代方案:

      -- 检查两几何对象是否相交
      SELECT ST_Intersects(ST_GeomFromText('POINT(-71.060316 48.432044)'), ST_GeomFromText('POLYGON((0 0, 1 1, 1 0, 0 0))'));
      

以上内容涵盖了MySQL 8.0中大部份特有的JSON函数、正则表达式函数和地理空间函数，同时提供了梧桐 6.0版本中的对应替代解决方案。