mysql - 如何在存储过程 MYSQL 中将“SELECT *”的结果转换为数组？

Question

在删除记录之前，我正在尝试获取一些记录并将其存储到 log_record 中。

这是我的桌子。但问题是我不知道如何将结果SELECT *转换为数组，所以我不能在存储过程中循环它以根据列名和列值将其插入记录。

CREATE TABLE IF NOT EXISTS account 
(
    id_account int(11) NOT NULL AUTO_INCREMENT,
    account_name VARCHAR(30) NOT NULL,
    account_location tinyint(4) NOT NULL,
    create_at timestamp NOT NULL DEFAULT current_timestamp(),
    PRIMARY KEY (id_account)
) ENGINE=InnoDB;

INSERT INTO account (account_name, account_location) 
VALUES ('Me', 110),
       ('You', 110),
       ('Them', 2);

CREATE TABLE IF NOT EXISTS log_record 
(
    id_log INT AUTO_INCREMENT PRIMARY KEY,
    type tinyint not null,
    table_affected VARCHAR(10),
    column_name VARCHAR(30),
    new_value VARCHAR(255),
    create_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    constraint C_TYPE_LOG check(type IN (0,1))
)ENGINE=INNODB;

DROP PROCEDURE IF EXISTS deleteProcedure;

CREATE PROCEDURE deleteProcedure
    (IN KeyValue INT, 
     IN TableName VARCHAR(30), 
     IN PrimaryKey VARCHAR(30), 
     OUT messageResult VARCHAR(30))
BEGIN
    DECLARE ColumnName VARCHAR(30);
    DECLARE ColumnValue VARCHAR(30);
    DECLARE result TINYINT(1);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION 

    BEGIN
        ROLLBACK;
        RESIGNAL;

        SET messageResult = null;
    END;

    -- # THIS FOR TURN INTO ARRAY AREA
    -- # But i don't know how to turn result of "SELECT *" INTO array of value and array of column name
    SET @ArrayColumnValue = '';
    SET @ArrayColumnName = '';
        
    SELECT * FROM TableName WHERE PrimaryKey = KeyValue;
        
    START TRANSACTION;
        -- # LOOP AREA FOR INSERT 
        WHILE (LOCATE(',', @ArrayColumnValue) > 0)
        DO
            SET @ColumnName = ELT(1, @ArrayColumnName);
            SET @ArrayColumnName= SUBSTRING(@ArrayColumnName, LOCATE(',',@ArrayColumnName) + 1);
            SET @ColumnValue = ELT(1, @ArrayColumnValue);
            SET @ArrayColumnValue= SUBSTRING(@ArrayColumnValue, LOCATE(',',@ArrayColumnValue) + 1);

            INSERT INTO log_record(type, table_affected, column_name, old_value) 
            VALUES('DELETE', TableName, @ColumnName ,@ColumnValue);
        END WHILE;
            
        SELECT ROW_COUNT() INTO result;

        -- # CHECK IS SUCCESS BEING INSERT
        IF (@result = 0) THEN
            ROLLBACK;

        SET messageResult = 'FAILED';
    ELSE
        DELETE FROM TableName WHERE PrimaryKey = KeyValue;
        SET messageResult = 'SUCCESS';
    END IF;

    COMMIT;
END

还是从一开始就做错了？就像使用错误的方法来解决比应该轻松完成的愚蠢事情一样。

我是这个东西的新手，如果你知道我使用了错误的方式，你可以告诉我使用其他方式。TT

标签： mysqlstored-procedures

Answer 1

我在 maria DB 上对其进行了测试，并且工作正常。

CREATE PROCEDURE DeleteProcedure(IN LogTableName VARCHAR(100), IN TableName VARCHAR(100), in InputValues varchar(1000))
ProcLabel:BEGIN
    -- Local Variable
    DECLARE log_column_names, column_names, current_column_name, current_input_value, column_names_repeat, input_values_found, input_value_params VARCHAR(1000);
    DECLARE table_key_name, this_table_name, table_key_data_type VARCHAR(100);
    DECLARE column_start_pos, column_comma_pos, column_loop_count, row_start_pos, row_comma_pos, row_loop_count, total_column_loop_count, input_values_found_length SMALLINT;
    DECLARE column_end_loop, row_end_loop, table_key_data_type_char TINYINT;
    -- Declare Condition
    DECLARE TableNotFound CONDITION for 1146 ;
    
    -- Exit Handler
    DECLARE EXIT HANDLER FOR TableNotFound
    BEGIN
        SELECT CONCAT('Table ', @this_table_name ,' Are Not Found') MESSAGE_TEXT;
    END;
    
    -- Set Array Column
    SET @this_table_name = TableName;
    SET @SQL = "SELECT GROUP_CONCAT(column_name SEPARATOR ', ') FROM information_schema.columns WHERE table_name = ? INTO @column_names";
    PREPARE stmt FROM @SQL;
    EXECUTE stmt USING @this_table_name;
    
    SET @this_table_name = LogTableName;
    SET @SQL = "SELECT GROUP_CONCAT(column_name SEPARATOR ', ') FROM information_schema.columns WHERE table_name = ? AND column_key != 'PRI' AND column_name != 'create_at' INTO @log_column_names";
    PREPARE stmt FROM @SQL;
    EXECUTE stmt USING @this_table_name;
    
    SET @SQL = "SELECT column_name, data_type FROM information_schema.columns WHERE table_name = ? AND column_key = 'PRI' INTO @table_key_name, @table_key_data_type";
    PREPARE stmt FROM @SQL;
    EXECUTE stmt USING TableName;
    
    SELECT IF(LOWER(SUBSTRING(@table_key_data_type, -3, 3) != 'int'), TRUE, FALSE) INTO @table_key_data_type_char;
   
    SET @SQL = CONCAT("INSERT INTO ", LogTableName ," (", @log_column_names ,") VALUES");
    
    -- Set Default Value Before Loop
    SET @SQLVAL = '';
    SET @row_start_pos = 1;
    SET @row_comma_pos = LOCATE(',', InputValues);
    SET @row_loop_count = 0;
    SET @total_column_loop_count = 0;
    SET @input_values_found = '';
    SET @input_value_params = InputValues;
    
    InputValueRepeat:REPEAT
        IF @row_comma_pos > 0 THEN
            SET @current_input_value = SUBSTRING(InputValues, @row_start_pos, @row_comma_pos - @row_start_pos);
            SET @row_end_loop = 0;
        ELSE
            SET @current_input_value = SUBSTRING(InputValues, @row_start_pos);
            SET @row_end_loop = 1;
        END IF;
        
        SET @SQLC = CONCAT("SELECT COUNT(1) FROM ", TableName ," WHERE ", @table_key_name ," = ", @current_input_value ," INTO @record_found");
        PREPARE stmtC FROM @SQLC;
        EXECUTE stmtC;
        DEALLOCATE PREPARE stmtC;
        
        IF @record_found = 1 AND @row_loop_count > 0 AND @column_loop_count IS NOT NULL THEN
            set @SQLVAL = CONCAT(@SQLVAL,",");
        END IF;
        
        IF @record_found = 1 THEN
            SET @column_start_pos = 1;
            SET @column_comma_pos = LOCATE(',', @column_names);
            SET @column_names_repeat = @column_names;
            SET @column_loop_count = 0;
            
            ColumnNameRepeat:REPEAT
                IF @column_comma_pos > 0 THEN
                    SET @current_column_name = SUBSTRING(@column_names_repeat, @column_start_pos, @column_comma_pos - @column_start_pos);
                    SET @column_end_loop = 0;
                ELSE
                    SET @current_column_name = SUBSTRING(@column_names_repeat, @column_start_pos);
                    SET @column_end_loop = 1;
                END IF;
    
                SET @SQLSV = CONCAT("SELECT ", @current_column_name ," FROM ", TableName ," WHERE ", @table_key_name ," = ? INTO @current_column_value");
                PREPARE stmtSV FROM @SQLSV;
                EXECUTE stmtSV USING @current_input_value;
                DEALLOCATE PREPARE stmtSV;
            
                SET @SQLVAL = CONCAT(@SQLVAL, "('", CONCAT_WS("','", 'D', TableName, @current_column_name, @current_column_value, @current_input_value) ,"')");

                IF @column_end_loop = 0 THEN
                    SET @column_names_repeat = substring(@column_names_repeat, @column_comma_pos + 1);
                    SET @column_comma_pos = LOCATE(',', @column_names_repeat);
                    set @SQLVAL = CONCAT(@SQLVAL,",");
                END IF;
            
                SET @column_loop_count = @column_loop_count + 1;
            
                UNTIL @column_end_loop = 1

            END REPEAT;
            SET @total_column_loop_count = @total_column_loop_count + @column_loop_count;
            IF @input_values_found != '' THEN
                IF (@table_key_data_type_char = TRUE) THEN
                    SET @input_values_found = CONCAT_WS("','", @input_values_found, @current_input_value);
                ELSE 
                    SET @input_values_found = CONCAT_WS(",", @input_values_found, @current_input_value);
                END IF;
            ELSE 
                SET @input_values_found = @current_input_value;
            END IF;
        END IF;
        
        IF @row_end_loop = 0 THEN
            SET InputValues = substring(InputValues, @row_comma_pos + 1);
            SET @row_comma_pos = LOCATE(',', InputValues);
        ELSE 
            set @SQLVAL = CONCAT(@SQLVAL,";");
        END IF;
        
        SET @row_loop_count = @row_loop_count + 1;
                       
        UNTIL @row_end_loop = 1

    END REPEAT;
    
    IF (table_key_data_type_char = TRUE) THEN
        SET @input_values_found = CONCAT("'", @input_values_found ,"'");   
    END IF;
    
    SELECT CHAR_LENGTH(CONCAT(@input_values_found)) INTO @input_values_found_length;
    if @input_values_found_length < 3 AND @table_key_data_type_char = TRUE OR @table_key_data_type_char = FALSE AND @input_values_found_length < 1 THEN
        SELECT CONCAT("Can't find input value : [", @input_value_params ,"] on table ", TableName) MESSAGE_TEXT; 
        DEALLOCATE PREPARE stmt;
        LEAVE ProcLabel;
    END IF;
    
    START TRANSACTION;
    
    SET @SQL = CONCAT(@SQL, @SQLVAL);
    PREPARE stmt FROM @SQL;
    EXECUTE stmt;
    
    SELECT ROW_COUNT() into @affected_row;
    
    IF (@affected_row != @total_column_loop_count) THEN
        SELECT CONCAT("Failed to insert data expected row : ", @column_loop_count ,", affected row : ", @affected_row) MESSAGE_TEXT;
        ROLLBACK;
        DEALLOCATE PREPARE stmt;
        LEAVE ProcLabel;
    END IF;
    
    SET @affected_row = 0;
    
    SET @SQL = CONCAT("DELETE FROM ", TableName ," WHERE ", @table_key_name ," IN (", @input_values_found ,")");
    PREPARE stmt FROM @SQL;
    EXECUTE stmt;
    
    SELECT ROW_COUNT() INTO @affected_row;
    
    IF (@affected_row = 0) THEN
        SELECT CONCAT("Failed to delete ", TableName ," on record key ", @input_values_found) MESSAGE_TEXT;
        ROLLBACK;
        DEALLOCATE PREPARE stmt;
        LEAVE ProcLabel;
    END IF;
    
    SELECT CONCAT("Success ", @affected_row ," record on ", TableName ," was deleted") MESSAGE_TEXT;
    COMMIT;
    DEALLOCATE PREPARE stmt;
END

我们需要像Call DeleteProcedure('[to table]','[from table]','[array of keys to delete]');=>一样调用这个 SPCall DeleteProcedure('log_history','orders','1,3,4');

所以我们可以选择尽可能多的使用逗号分隔符，如第三个参数上的 '1,2,3'。但我没有在超过数百个键上尝试过，因为第三个参数数据类型是 varchar(1000)。我希望有人能在不久的将来解决这个问题。