使用 MySQL 函数或过程提取 SELECT 表达式

Question

我有兴趣通过提取选择表达式(从MySQL文档中select_expr(来简化SQL查询/视图。每个select_expr本质上都是重复的，带有少量的变化，可以提取到变量中。

例如，下面是一个现有的查询/视图。

CREATE OR REPLACE VIEW my_view AS
SELECT
  json_unquote(json_extract(sr.response, concat(SUBSTRING_INDEX(json_unquote(
    JSON_SEARCH(mt.response, 'one', 'pref.field_1', NULL, '$.f[*].q')), '.', 2), 
    '.', 'value'))) AS field_1,
  json_unquote(json_extract(sr.response, concat(SUBSTRING_INDEX(json_unquote(
    JSON_SEARCH(mt.response, 'one', 'pref.field_2', NULL, '$.f[*].q')), '.', 2), 
    '.', 'value'))) AS field_2,
  json_unquote(json_extract(sr.response, concat(SUBSTRING_INDEX(json_unquote(
    JSON_SEARCH(mt.response, 'one', 'pref.field_3', NULL, '$.f[*].q')), '.', 2), 
    '.', 'value'))) AS field_3,
FROM my_table mt;

变量位是：field_1、field_2和field_3。

从理论上讲，这就是我想做的：

CREATE OR REPLACE VIEW my_view AS
SELECT
  get_select_expr('field_1') AS field_1,
  get_select_expr('field_2') AS field_2,
  get_select_expr('field_3') AS field_3,
FROM my_table mt;

我一直在尝试类似以下内容的方法，但不确定如何让select_expr进行评估。它返回一个字符串是有道理的，但我不知道如何让它进行评估。也许我应该使用一个过程，但这是我的 MySQL 知识崩溃的地方。

DROP FUNCTION IF EXISTS get_select_expr;
CREATE FUNCTION get_select_expr (field_name VARCHAR(255))
RETURNS VARCHAR(255) DETERMINISTIC
RETURN concat('json_unquote(json_extract(mt.response, concat(
    SUBSTRING_INDEX(json_unquote(JSON_SEARCH(mt.response, 
    'one', 'pref.', field_3, '', NULL, '$.f[*].q')), 
    '.', 2), '.', 'value')))');
SELECT get_select_expr('field_1') AS field_1 FROM my_table;

我已经浏览了所有建议的类似问题，但没有找到我需要的东西。知道我可能出错的地方，或者指针吗？我什至不确定我是否在寻找正确的术语。

Answer 1

你把

代码复杂化了，这里不需要动态生成sql代码，无论如何它都不会工作。

只需创建一个将字段值和 json 字段值作为参数的函数，您就不需要动态 sql：

DROP FUNCTION IF EXISTS get_select_expr;
CREATE FUNCTION get_select_expr (field_name VARCHAR(255), json_field_name varchar (255))
RETURNS VARCHAR(255) DETERMINISTIC
RETURN json_unquote(json_extract(field_name, concat(
    SUBSTRING_INDEX(json_unquote(JSON_SEARCH(field_name, 
    'one', 'pref.', json_field_name, '', NULL, '$.f[*].q')), 
    '.', 2), '.', 'value')));
SELECT get_select_expr(my_table.response, 'field_1') AS field_1 FROM my_table;