How to create a database table column dynamically in MySQL?

Forums PHPHow to create a database table column dynamically in MySQL?
Staff asked 3 years ago

Answers (1)

Add Answer
Staff answered 3 years ago

To 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,
  1. concat the procedure parameter(s) with your query as necessary.
  2. prepare/execute a statement from this query string.

I hope this will help you.

Subscribe

Select Categories