CODEWITHSUNDEEP
mysqlmysql-event
hammer
hammer

Reputation: 82

Declare variable MySQL trigger and create table using that variable

I have a event in which I am storing a string in a variable. Now I want to use that variable to create a new table. Everytime my event runs it creates table with the name of "mon". What is I am doing wrong ?

BEGIN
DECLARE onlyweek INT;
DECLARE mon VARCHAR(20);

SET @mon = "rehan";

CREATE TABLE mon(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    capacity INT NOT NULL
);

END

Upvotes: 0

Views: 228

Answers (2)

Tropen
Tropen

Reputation: 125

This is a design mistake. For example, you need to make report for the year. In your design you have to join 12 tables and where-s how to join. And this is very slow. Better design is creating 2 tables - "months" and "reporting_periods" with foreign key to table 'months'. This way when you need year report - you join only 2 tables by ID with "where".

Table 'months' can be filled once a year using same mysql events. Then use mysql "stored procedure" (and mysql event) for periodic insert into reporting_period with month id. Months` names can include year as "bad way" or have the field 'year' = 'better one'.

CREATE TABLE months(
    id int auto_increment,
    name varchar(10),
    number int not null,
    year int not null,
    constraint monthes_pk
    primary key (id)
);

and reporting_period

   CREATE TABLE reporting_period(
   id INT auto_increment,
   period_id INT NOT NOT,
   capacity INT NOT NULL,
   constraint `reporting_period_pk`
      primary key (id),
   constraint `reporting_period__fk`
      foreign key (period_id) references months (id)
   );

More about DB design: normalization

Upvotes: 0

juergen d
juergen d

Reputation: 204766

Because you use mon instead of @mon. And even then it wont work because you need dynamic SQL for that.

But what is even more important:

Don't do that!

Don't create a table on the fly. Table designs should be static. That smells like a big design flaw.

Upvotes: 2

Related Questions