I am trying to write a query that will check if a specific table in MySQL has a specific column, and if not — create it. Otherwise do nothing. This is really an easy procedure in any enterprise-class database, yet MySQL seems to be an exception.

MySQL, Check if a column exists in a table with SQL. Ask Question 108. I am trying to write a query that will check if a specific table in MySQL has a specific column, and if not — create it. Otherwise do nothing. This is really an easy procedure in any enterprise-class database, yet MySQL seems to be an exception. Check if column.

I thought something like

would work, but it fails badly. Is there a way?

Gareth Davis

In this article, we show how to check if a MySQL table exists using PHP. Using the code below, you can check to see if a MySQL table exists. So once we make the connection to the database, we can then check to see if the table exists. Code to Check if a MySQL Table Exists. Check if a MySQL table exists Posted in MySql - Last updated Jun. MySQL has a couple of ways (that I know of, there may be more) of working out if a table exists. This post looks at how to check if a table exists in the MySQL database.

24.3k11 gold badges66 silver badges99 bronze badges
Phpclopsclops
2,6116 gold badges32 silver badges49 bronze badges

10 Answers

This works well for me.

With PHP it would be something like..

MfooMfoo
2,3931 gold badge11 silver badges11 bronze badges

@julio

Thanks for the SQL example. I tried the query and I think it needs a small alteration to get it working properly.

That worked for me.

Thanks!

forsvarir
9,4636 gold badges33 silver badges64 bronze badges
IainIain
1,7971 gold badge11 silver badges5 bronze badges

Just to help anyone who is looking for a concrete example of what @Mchl was describing, try something like

SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'my_table' AND COLUMN_NAME = 'my_column'

If it returns false (zero results) then you know the column doesn't exist.

radbyx
4,67515 gold badges64 silver badges112 bronze badges
juliojulio
3,22712 gold badges50 silver badges81 bronze badges

Following is another way of doing it using plain PHP without the information_schema database:

wvasconceloswvasconcelos

Select just column_name from information schema and put the result of this query into variable. Then test the variable to decide if table needs alteration or not.

P.S. Don't foget to specify TABLE_SCHEMA for COLUMNS table as well.

MchlColumnMchl
52.9k7 gold badges96 silver badges111 bronze badges

I threw this stored procedure together with a start from @lain's comments above, kind of nice if you need to call it more than a few times (and not needing php):

Working with fieldExists

Idm

quickshiftinquickshiftin
40.7k8 gold badges51 silver badges68 bronze badges

I am using this simple script:

It works if you are already connected to the database.

viovio

DO NOT put ALTER TABLE/MODIFY COLS or any other such table mod operations inside a TRANSACTION. Transactions are for being able to roll back a QUERY failure not for ALTERations..it will error out every time in a transaction.

Just run a SELECT * query on the table and check if the column is there..

gmizegmize

Many thanks to Mfoo who has put the really nice script for adding columns dynamically if not exists in the table. I have improved his answer with PHP. The script additionally helps you find how many tables actually needed 'Add column' mysql comand. Just taste the recipe. Works like charm.

webbloverwebblover
4311 gold badge5 silver badges16 bronze badges
user3706926user3706926

Not the answer you're looking for? Browse other questions tagged mysql or ask your own question.

Mysql If Exists Select

I have a procedure that should check if a record exists or not for particular date range, if exists then fetch the record else fetch last 20 record.

For this i have to write a query multiple times, one for checking the existance , then fetch the same record or fetch record without where clause but with limit .

Php Check If Column Exists Mysql Free

Query goes something like this inside procedure

Is there any way i can do it in a single query because my query has too many joins and too complicated, it may take some amount of time, so if i call it two times the fetching time will increase.

hot2useColumn
8,8985 gold badges24 silver badges63 bronze badges
Nikesh KedlayaNikesh Kedlaya
1061 gold badge2 silver badges10 bronze badges

2 Answers

You could probably reduce this to 2 queries with something like:

markpmarkp
2,1151 gold badge3 silver badges16 bronze badges

This probably eliminates the extra 'result set' (and is faster than using COUNT(*)):

Rick JamesRick James
45.3k2 gold badges25 silver badges63 bronze badges

Not the answer you're looking for? Browse other questions tagged mysql or ask your own question.