[SOLVED] my sql use a variable in the ALTER TABLE ADD COLUMN statement

my sql use a variable in the ALTER TABLE ADD COLUMN statement

Hi I am looking to create a table with date as column name.

I am using this code to Add column to the table:

DROP PROCEDURE IF EXISTS filldates;
DELIMITER |
CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)
BEGIN
  WHILE dateStart <= dateEnd DO
    ALTER TABLE dates
    ADD dateStart VARCHAR(30);

    SET dateStart = date_add(dateStart, INTERVAL 1 DAY);
  END WHILE;
END;
|
DELIMITER ;
CALL filldates('2017-01-01','2017-12-31');

But it is showing the error dateStart as duplicate content, because it creates a column 'dateStart' instead of date. How can I use dateStart as a varible.

When I use "INSERT INTO tablename (_date) VALUES (dateStart);" instead of ALTER TABLE statement, it doesn't show any error and it is inserting the dates to database, but as rows in '_date' column. I want the dates to be added as column name.

How can use dateStart as a variable

Solution

The short answer is that you cannot use variables as database, table, or column names in MySQL.

The only thing that you can do is to concatenate the sql statement as string and execute it as a prepared statement.

SET @s=CONCAT('ALTER TABLE dates ADD COLUMN `',dateStart,'` VARCHAR(30)');
PREPARE stmt1 FROM @s;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

However, adding field names dynamically in large quantities to a table usually indicates a bad design (unless your are preparing materialised pivot tables for reporting purposes out of data that cannot be changed).