How to create a database table column dynamically in MySQL?
Answers (1)
Add AnswerTo make a table dynamically you will need to concat your query string with the variable and prepare/execute a statement right in the stored procedure.
Example,
-- DROP PROCEDURE IF EXISTS createTable; DELIMITER // CREATE PROCEDURE createTable(tblName VARCHAR(255)) BEGIN SET @tableName = tblName; SET @query = CONCAT(' CREATE TABLE IF NOT EXISTS `' , @tableName, '` ( `id` INT(11) [UNSIGNED NOT NULL AUTO_INCREMENT], `column1` VARCHAR(10) [NOT NULL], `column2` DATETIME [NOT NULL], . . `column n` datatype, [PRIMARY KEY (`id`)] ) ENGINE=MyISAM DEFAULT CHARSET=utf8 '); PREPARE stmt FROM @query; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- and you're done. Table is created. -- process it here if you like (INSERT,update,delete, etc) END //
Then, Here CALL createTable(‘exampleTable’);
- Points to remember,
- concat the procedure parameter(s) with your query as necessary.
- prepare/execute a statement from this query string.
I hope this will help you.