在MYSQL存储过程中动态选择列的用例

SQL篡改表模式

I was following this thread to create a stored procedure for dynamic selection of columns. I was wondering if there is any solution to select more than one field in the more condition. I'm getting syntax error when aliasing a column name and putting multiple columns in the more condition and I have read that my following attempt is not possible in MySQL. I want to know if there is any working statement to the effect of

我按照这个线程创建了一个存储过程，用于动态选择列。我想知道是否有解决方案可以在多个条件下选择多个字段。我得到语法错误，当别名列和多列在更多的条件，我读到我的后续尝试是不可能在MySQL。我想知道是否有任何工作声明的效果

      CASE choose_field
           WHEN 'one' THEN COUNT(*) AS Count
           WHEN 'more' THEN n.id,pmu.name sender,pm.message, m.modify_id,ps.submit_id
      END

Here's the complete command that works when only one field is specified in each condition without using alias:

当在每个条件中只指定一个字段而不使用别名时，以下是完整的命令:

DELIMITER//
CREATE PROCEDURE notification(IN choose_field varchar(4),IN user varchar(10))
BEGIN
    SELECT
      CASE choose_field
           WHEN 'one' THEN COUNT(*)
           WHEN 'more' THEN n.modification_id
      END
    FROM
      `notification` n
    LEFT JOIN user_modification m ON (n.modification_id = m.modification_id)
    LEFT JOIN pm ON (n.pm_id = pm.pm_id)
    LEFT JOIN users pmu ON (pm.recipent_id = pmu.user_id)
    LEFT JOIN users pmus ON (pm.sender_id = pmus.user_id)
    LEFT JOIN users pmm ON (m.user_id = pmm.user_id)
    WHERE
    (pmu.name = user OR 
     pmm.name = user) AND
    n.is_read = 0;
 END//

Then I call the procedure with Call notification('one','Peter')

然后调用通知程序('one'，'Peter')

Table Schema

表模式

CREATE TABLE notification
    (`id` int, `modification_id` int,`pm_id` int,`is_read` int)
;

INSERT INTO notification
    (`id`,`modification_id`,`pm_id`,`is_read`)
VALUES
    (1,1,NULL,0),
    (2,NULL,1,0),
    (3,2,NULL,0)
;

CREATE TABLE user_modification
    (`modification_id` int, `user_id` int,`page_id` int, `is_approved` int)
;

INSERT INTO user_modification
    (`modification_id`,`user_id`,`page_id`,`is_approved`)
VALUES
    (1,1,5,1),
    (2,2,2,1),
    (3,3,3,0)

;

CREATE TABLE pm
    (`pm_id` int, `sender_id` int,`recipent_id` int,`msg` varchar(200))
;

INSERT INTO pm
    (`pm_id`,`sender_id`,`recipent_id`,`msg`)
VALUES
    (1,1,2,'Hello');

CREATE TABLE users
    (`user_id` int, `name`varchar(20))
;

INSERT INTO users
    (`user_id`,`name`)
VALUES
    (1,'Tom'),
    (2,'Peter'),
    (3,'David')
;

2 个解决方案

#1

I would change it to something like this

我把它变成这样

DELIMITER//
CREATE PROCEDURE notification(IN choose_field varchar(4),IN user varchar(10))
BEGIN
    IF choose_field = 'one' THEN
        SELECT
            COUNT(*)
        FROM
          `notification` n
        LEFT JOIN user_modification m ON (n.modification_id = m.modification_id)
        LEFT JOIN pm ON (n.pm_id = pm.pm_id)
        LEFT JOIN users pmu ON (pm.recipent_id = pmu.user_id)
        LEFT JOIN users pmus ON (pm.sender_id = pmus.user_id)
        LEFT JOIN users pmm ON (m.user_id = pmm.user_id)
        WHERE
        (pmu.name = user OR 
         pmm.name = user) AND
        n.is_read = 0;
    ELSE -- # -- could also be -- ELSEIF choose_field = 'more'
        SELECT
            n.id,pmu.name sender,pm.message, m.modify_id,ps.submit_id
        FROM
          `notification` n
        LEFT JOIN user_modification m ON (n.modification_id = m.modification_id)
        LEFT JOIN pm ON (n.pm_id = pm.pm_id)
        LEFT JOIN users pmu ON (pm.recipent_id = pmu.user_id)
        LEFT JOIN users pmus ON (pm.sender_id = pmus.user_id)
        LEFT JOIN users pmm ON (m.user_id = pmm.user_id)
        WHERE
        (pmu.name = user OR 
         pmm.name = user) AND
        n.is_read = 0;
    END IF;

 END//

basically just use the if inside of your stored procedure to do the check for your query and run one or the other query... you could do this with setting user - defined variables equal to the part you want to select and concat it to a query as well.

基本上只需在存储过程中使用if来检查查询并运行一个或另一个查询……您可以将用户定义的变量设置为您想要选择的部分，并将其转换为查询。

#2

I think you are trying to do more in one statement than what you should be / want to be. A couple solutions:

我认为你在一份声明中要做的比你应该做的要多。两个解决方案:

Dynamic SQL can handle this...use the stored procedure to build a sql statement then execute the variable. Not sure if I'd recommend this though

动态SQL可以处理这个…使用存储过程构建sql语句，然后执行变量。我不确定我是否会推荐这个
Create an if statement that reads the variable and runs one of two select statements depending on what it is

创建一个if语句来读取变量并运行两个select语句中的一个，具体取决于它是什么
Create 2 additional stored procs, one for count and one for 'more'. Use the main stored proc to evaulate if statments (one / more) and run the correct storedproc.

创建两个额外的存储proc，一个用于计数，一个用于“more”。使用主存储的proc来计算if语句(一个/多个)，并运行正确的storedproc。

One statement to return different number of columns is a bit difficult.

返回不同列数的语句有点困难。

#1