How to create a database table column dynamically in MySQL?
Apr 11, 2022
Answers (2)
Add Answer
The use SQL ALTER TABLE – ADD Column Statement to add a new column in the table, use the following syntax:
ALTER TABLE table_name ADD column_name datatype constraint [ FIRST | AFTER column_name ];
Please review the following an example:
First, create a table:
CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
Then add a new column:
ALTER TABLE Persons ADD Email varchar(255) NOT NULL;
Here, add the Email column in the Persons table.
For more information review my article on How To Create Dynamic Column In MySQL Table In PHP.
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,
- concat the procedure parameter(s) with your query as necessary.
- prepare/execute a statement from this query string.
I hope this will help you.
