add mysql columns on the fly

Question

Is there a way to add mysql columns on the fly when I try to insert a row?

For example, table contains columns A, B, C and I want to insert into the table a row that contains A, C, D, and F?

Is there a way to actually add the D and F columns instead of resulting in an error?

Why not use a key-value relationship for the table then? That is, your table is three total columns: userid, key, value. Primary key being (userid,key) — Chris Forrence
– Chris Forrence, Commented Aug 25, 2012 at 21:12
you're a genius! You've just made my day. the only little problem would be with type of values (i.e. one of the columns was supposed to be a timestamp, and a few of them are text instead of varchar, but i really don't care about efficiency of the table, since its use will be minimal anyway — Uno Mein Ame
– Uno Mein Ame, Commented Aug 25, 2012 at 21:16

Uno Mein Ame · Accepted Answer · 2012-08-25 21:24:46Z

2

Thank you GlaciesofPacis and Adev

You made me think differently about the question that I asked, and it looks like your solution is a lot more elegant than what I was initially thinking about.

Instead of having a table with very few rows and over 100 columns, I will use the ID, Key, Value model.

Thank you!

answered Aug 25, 2012 at 21:24

Uno Mein Ame

1,0906 gold badges16 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ofir Baruch · Accepted Answer · 2012-08-25 21:07:50Z

0

You can use Alter and ADD in order to add new columns to your table.

For instance;

ALTER TABLE contacts ADD email VARCHAR(60);

Will add a new column named email which its type if Varchar , to the contacts table.

Of course that before you insert the row , you must check which columns to add, add them and just then insert the new row.

answered Aug 25, 2012 at 21:07

Ofir Baruch

10.3k2 gold badges29 silver badges39 bronze badges

2 Comments

Uno Mein Ame Over a year ago

well, that's exactly the issue, if I KNEW which fields I will have the database, there'd be no reason to add the fields on the fly. I will simply be dumping various variables into the database with the name of the variable being the name of the column, and I would like to be able to not bother with figuring out which column already exists...

Ofir Baruch Over a year ago

I am not following you... when you get the new values , you check which fields you want to add (using if for instance). Then , you add those fields and insert value. Rewrite your question in a proper way so I could understand. And add an example.

Ahmed Kato · Accepted Answer · 2012-08-25 21:16:11Z

0

you can use ALTER TABLE and give it any query you need for example

ALTER TABLE TABLE_NAME
ADD COLUMN_NAME (ATTRIBUTE)

hope this helps you

answered Aug 25, 2012 at 21:16

Ahmed Kato

1,7076 gold badges29 silver badges54 bronze badges

Collectives™ on Stack Overflow

add mysql columns on the fly

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related