MySQL: For every Column, create if not exists?

Question

I'm a student and am trying to create a column for my table, but only if it does not exist. I did come across a solution:

SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA="bd_company" AND TABLE_NAME="users" AND column_name="user_age";

If it returns 0 it means it does not exists, so i can create it. But my question is: What if i want to do this for 5 columns?

Like a for loop... Something like "For every column, do SELECT COUNT(*)... and if not exists create table"

Is this possible? Sorry if this is a silly question, i really have no idea, but want to know it anyway... I have both SQL Server Management Studio and MySQL Workbench. I can use both scenarios and solutions.

Thanks in advance.

Answer 1

You can create a table with the expected columns and subtract the existing ones. Example

SELECT tt.c 
FROM ( VALUES ('c1'), ('c2'),('c3')) tt(c)
LEFT JOIN information_schema.columns c
    ON tt.c = c.column_name
WHERE c.column_name is null

Now you can loop over those columns, construct the statement and execute that.

That said, you will be much better off with a tool that maintains your data model. Have a look at for example Flyway

Answer 2

You can use IN instead of = while passing the column name with column_name. For eg. : column_name IN ('cn1','cn2','cn3','cn4','cn5').

Answer 3

While your exercise may be interesting, it is not realistic. When adding columns, you should first know what columns exist, and how the new columns will complement the existing columns are be redundant. And maybe the functionality already exists, but with a different column name.

I suggest that ADD COLUMN is a manual task, not a IF NOT EXISTS activity.

OK, so you are building a system that can roll-forward and roll-back schema changes. (Never mind that this gets really gnarly if data is in the columns.) You will want to first find out what columns do exist, then decide which ones to add, and finally build a single ALTER to add the new columns all at once.

As a consolation prize, let me leave you with this tip: Instead of checking ( SELECT COUNT(*) ... ) = 0, use EXISTS( SELECT 1 ...) The latter is a "semi-join" in that it stops when it finds a matching row, rather than slogging through all the data to produce a count that is simply checked against zero.