How can i add dynamic column in table exists with mysql?

Question

i have table and i want to add column but the name of column will be variable

like this :

$coulName = col_1_2;
ALTER TABLE `table name` ADD `$coulmName` DOUBLE NOT NULL DEFAULT '0' AFTER `col2`;

how can i do that ?

You'll possibly have to compose [dynamic SQL](http://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure#5728155). — Álvaro González, Jan 11 '17 at 15:51

score 2 · Accepted Answer · answered Jan 11 '17 at 15:52

You need to use a prepared statement for this:

SET @colName = 'col_1_2';

SET @s = CONCAT('ALTER TABLE `mytable` ADD `', @colName, 
                '` DOUBLE NOT NULL DEFAULT 0 AFTER `col2`');
PREPARE stmt1 FROM @s;

EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

score 1 · Answer 2 · answered Jan 11 '17 at 15:53

Within MySQL you can only achieve this using prepared statements because you cannot tie a variable to a table or column name. This means that you have to assemble the sql statement in a string and execute it.

However, you can accomplish this from your application code as well - the variable name suggests that you may use php. The same applies: you have to concatenate the sql statement string, cannot use parameters.

Code would look sg like the below in MySQL:

@coulName = 'col_1_2';
@sql = 'ALTER TABLE `table name` ADD `',@coulmName,'` DOUBLE NOT NULL DEFAULT '0' AFTER `col2`;
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

However, I'm not sure if it a really good idea to regularly and dynamically change the existing data structure. That usually indicates poor database design.

How can i add dynamic column in table exists with mysql?

2 Answers2