In this post I’m going to explain how to add a column to multiple tables in a mysql database.
Although it might sound a bit weird, recently I came across a requirement to add a column to a few dozen of tables in a mysql database. Here’s the main steps needed to achieve this followed by the complete code.
STEP 1. Get the names of the tables
First we need to select the names of the tables that we are going to modify and here’s how
SELECT * FROM `information_schema`.`tables` WHERE table_schema = 'db_name' AND table_name LIKE 'mytables_%';
STEP 2. Create a cursor to iterate over the table names
Now create a cursor on the above query so that we can iterate over the table names and run our query to add the column
DECLARE column_cursor CURSOR FOR SELECT * FROM `information_schema`.`tables` WHERE table_schema = 'db_name' AND table_name LIKE 'mytables_%';
STEP 3. Iterate the cursor over the table names and prepare the sql statement to add the column to the tables
Now we”ll iterate the cursor and prepare an sql statement with the alter table statement to add the column
SET @prepstmt = CONCAT('ALTER TABLE adtracker','.',v_table,' ADD COLUMN id INT AUTO_INCREMENT NOT NULL;'); PREPARE stmt FROM @prepstmt;
STEP 4. Execute the created statement
Execute the prepared statement to add the columns to the tables
EXECUTE stmt;
Those are the main steps in the solution but in the actual code we have to do a bit more coding to declare the cursor, loops, etc… so here’s the fully completed working code of the solution. Note that this is created as a stored procedure as the cursors cannot be used in regular sql queries.
Here we go:
DELIMITER $$ USE `db_name`$$ DROP PROCEDURE IF EXISTS `alter_test_1`$$ CREATE DEFINER=`db_name`@`10.%` PROCEDURE `alter_test_1`() BEGIN DECLARE v_finished INTEGER DEFAULT 0; DECLARE v_table VARCHAR(100) DEFAULT ""; DECLARE stmt VARCHAR(500) DEFAULT ""; DECLARE column_cursor CURSOR FOR SELECT * FROM `information_schema`.`tables` WHERE table_schema = 'db_name' AND table_name LIKE 'mytables_%'; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1; OPEN column_cursor; alter_tables: LOOP FETCH column_cursor INTO v_table; IF v_finished = 1 THEN LEAVE alter_tables; END IF; SET @prepstmt = CONCAT('ALTER TABLE adtracker','.',v_table,' ADD COLUMN id INT AUTO_INCREMENT NOT NULL;'); PREPARE stmt FROM @prepstmt; EXECUTE stmt; DEALLOCATE PREPARE stmt; END LOOP alter_tables; CLOSE column_cursor; END$$ DELIMITER ;
Happy coding π
