Declare variable MySQL trigger and create table using that variable

Question

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

score 2 · Answer 1 · edited Jun 20 '20 at 09:12

2

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.

edited Jun 20 '20 at 09:12

Community

1
1

answered Dec 05 '19 at 06:35

juergen d

201,996
37
293
362

i am doing this to store data on monthly basis. if new month start don't insert data in previous month table, instead create new table and store data in it – hammer Dec 05 '19 at 06:38
Can you say why it is not good to have tables created on the fly ? There can have usecases where you need to have `temp` tables right? @juergen d – Selaka Nanayakkara Dec 05 '19 at 06:38
@hammer: No, that is bad design. Better have a table that stores also the date, then you don't need a table for every month. – juergen d Dec 05 '19 at 06:39
@Sela: Of course there are a few scenarios where you need to do that but normally not. – juergen d Dec 05 '19 at 06:40
@juergend I am storing date as well. all i want is to store data separately on monthly basis – hammer Dec 05 '19 at 06:41
@hammer: You could read up on [database normalization](https://en.wikipedia.org/wiki/Database_normalization) to get a hint why not to do it. – juergen d Dec 05 '19 at 06:44
@juergend You are right that this is not a good approach, also I am familiar with database normalization – hammer Dec 05 '19 at 06:47
@juergend Any suggestion to tackle this scenario ? – hammer Dec 05 '19 at 06:53
@hammer: No, since I see no need for doing it. You can simply select monthly data from a single table if you have a date in it. If you have billions of records each month I would suggest partitioning your data but I can't see why you need that. – juergen d Dec 05 '19 at 06:56
@hammer: So for what do you need that? – juergen d Dec 05 '19 at 06:58
@juergend thank you sir I will look further more in it – hammer Dec 05 '19 at 07:06
@juergend : My scenario is like this. I want to store data on monthly basis and i dont want to create table all statically I want to create them when current month comes – hammer Dec 05 '19 at 07:09
@hammer: But "I want" is no real reason. – juergen d Dec 05 '19 at 07:10
@hammer: Ah. OK. Then google *MySQL dynamic sql*. With that you can run the query you like with a dynamic table name. – juergen d Dec 05 '19 at 07:13
@juergend: I come up with the solution let me try that and I will let you know if that works or not – hammer Dec 05 '19 at 07:15
@juergend : Thanks. I got the solution. I also created a procedure in which i am creating table of current month, when month will be over it will create a new table of new month. Thanks again – hammer Dec 05 '19 at 07:47
@juergend I will post the solution shortly – hammer Dec 05 '19 at 07:54
1

@hammer: while it is hard for us to argue against "that is the client requirement", make sure that a) it actually is what they requested. They may just be used to excel and want to have your app display the data per month in a table, and don't care how the underlying table is designed, or improperly worded it. They might not have heard about normalization, that's where you come in. And b) if they actually want this, try to concince them otherwise, and make sure they understand the consequences (with the appropriate discretion of course). The bold text in juergens answer is bold for a reason. – Solarflare Dec 05 '19 at 09:22
@Solarflare : Totally agree with you sir. I know this is a very bad approach, I told him about the consequences but I failed maybe that was my fault (not able to convince him) but thanks to "juergend" and also you as well for clarifying it. I will try to be more careful from further on. Although I get the solution for this problem :) – hammer Dec 05 '19 at 09:43

score 0 · Answer 2 · answered Dec 17 '19 at 16:04

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

Hello ! No I don't need to join 12 tables in order to make a report for the year because i am moving data to a table name YEAR 2019 on monthly basis so when i want to generate a report for the year i will just query from that table. I have successfully achieved what i wanted through triggers and procedure. Thank you — hammer, Dec 20 '19 at 11:33

Declare variable MySQL trigger and create table using that variable

2 Answers2

Don't do that!