How to turn result of “SELECT *” INTO array in Store Procedure MYSQL?

Solution for How to turn result of “SELECT *” INTO array in Store Procedure MYSQL?
is Given Below:

Actually i’m trying to get some record and store it into a log_record before the record are being delete.
Here are my table. But the problem is i don’t know how to turn the result of “SELECT *” into an array, so i can’t loop it inside the Stored Procedure(SP) for insert it into record based on column name and column value.

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 messegeResult VARCHAR(30))
    BEGIN
        DECLARE ColumnName VARCHAR(30);
        DECLARE ColumnValue VARCHAR(30);
        DECLARE result TINYINT(1);
        DECLARE EXIT HANDLER FOR SQLEXCEPTION 
        BEGIN
            ROLLBACK;
            RESIGNAL;
            SET messegeResult = 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 messegeResult="FAILED";
            ELSE
                DELETE FROM TableName WHERE PrimaryKey = KeyValue;
                SET messegeResult="SUCCESS";
            END IF;
        COMMIT;
    END

Or do do it wrong from begining? Like using the wrong method to get about silly thing than should be done easyly.
I’m new one to this thing, please if you know that i’m using wrong way, you can tell me using other way. TT

I’m test it on maria DB, and work prefectly.

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

We need to call this SP like Call DeleteProcedure('[to table]','[from table]','[array of keys to delete]'); => Call DeleteProcedure('log_history','orders','1,3,4');

So we can choose as much as you want use it with comma sparator like ‘1,2,3’ on the 3rd parameter. But i’m haven’t try it on more then hundred of keys, because 3rd params data type is varchar(1000).
I hope there is someone can solve this issue in the near future.