CODEWITHSUNDEEP
mysql
AboYousef16
AboYousef16

Reputation: 85

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

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 ?

Upvotes: 0

Views: 84

Answers (2)

Shadow
Shadow

Reputation: 34231

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.

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

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;

Upvotes: 2

Related Questions