MySQL存储过程输出异常求助：编写表列相关存储过程遇问题

Hey there, let's troubleshoot that wonky MySQL stored procedure of yours. From the partial code you shared, here are some common issues that could be causing those unexpected outputs:

1. Missing Cursor Completion Handler #

You declared the done variable to track when the cursor runs out of rows, but you didn’t set up a handler to actually flip that flag when there are no more records to fetch. Without this, your loop will keep running indefinitely (or throw errors) once the cursor is empty. Add this right after declaring your cursor:

declare continue handler for not found set done = true;

2. Incomplete Cursor Filter #

Your cursor query cuts off at and...—I’m guessing you forgot to add the filter for the target table! Without table_name = in_table, you’ll pull every column from every table in the specified database, which is almost certainly not what you want. Fix the WHERE clause to include:

and table_name = in_table collate utf8_general_ci;

3. Dynamic SQL Risks & Setup #

You initialized the stmt variable as an empty string, but if you’re building dynamic queries later, make sure you’re using PREPARE, EXECUTE, and DEALLOCATE PREPARE correctly. Also, since you’re accepting user-provided database and table names, watch out for SQL injection—even though you’re querying information_schema, it’s smart to validate inputs or use proper escaping.

4. Collation Mismatch Checks #

You added collate utf8_general_ci to the table_schema match, but if the collation of your input parameter in_database doesn’t align with what’s stored in information_schema, this could lead to no matches (or incorrect ones). Double-check if the explicit collation is necessary, or test without it to see if that fixes the issue.

5. Loop & Variable Handling #

Inside your cursor loop, make sure you’re properly fetching values into var_column_name and var_data_type, and resetting any dynamic SQL strings if needed. Leftover values from previous iterations can cause weird output or broken queries.

Here’s a quick revised snippet incorporating these fixes:

delimiter //
create procedure test ( in in_database varchar(255), in in_table varchar(255)) 
begin 
  declare done int default false; 
  declare stmt text default ''; 
  declare var_column_name varchar(64); 
  declare var_data_type varchar(64); 

  -- Add handler for cursor end
  declare continue handler for not found set done = true;

  declare cur_cols cursor for 
    select column_name, data_type 
    from information_schema.columns 
    where table_schema = in_database collate utf8_general_ci 
      and table_name = in_table collate utf8_general_ci;

  open cur_cols;
  read_loop: loop
    fetch cur_cols into var_column_name, var_data_type;
    if done then
      leave read_loop;
    end if;

    -- Example: Build your dynamic logic here
    set stmt = concat(stmt, 'ALTER TABLE ', in_database, '.', in_table, ' ADD INDEX idx_', var_column_name, ' (', var_column_name, ');');
  end loop;
  close cur_cols;

  -- Execute dynamic SQL if needed
  if stmt != '' then
    prepare dynamic_stmt from stmt;
    execute dynamic_stmt;
    deallocate prepare dynamic_stmt;
  end if;
end //
delimiter ;

内容的提问来源于stack exchange，提问作者Erik