I need mysql script (not a stored procedure) that check whether column exist or not before altering the table.
1
-
See this stackoverflow.com/questions/3395798/…abhinav– abhinav2012-03-05 06:51:13 +00:00Commented Mar 5, 2012 at 6:51
Add a comment
|
5 Answers
Try this
SELECT *
FROM information_schema.COLUMNS
WHERE
TABLE_SCHEMA = 'db_name'
AND TABLE_NAME = 'table_name'
AND COLUMN_NAME = 'column_name'
Comments
SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'my_table' AND COLUMN_NAME = 'my_column'
If the above returns 0 rows, well, you know the column doesn't exist.
Comments
You can do it in this way
SHOW columns from `yourtable` where field='yourfield'
Comments
You can retrieve the existence of the field ...
SELECT *
FROM information_schema.COLUMNS
WHERE
TABLE_SCHEMA = 'your_db_name'
AND TABLE_NAME = 'your_table_name'
AND COLUMN_NAME = 'your_column_name'
...but you cannot add an ALTER-statement depending on the outcome. SQL just can't do that.
The place for that kind of logic is either in a stored procedure or in an application language.
Comments
Try this
function add_column_if_not_exist($db, $column, $column_attr ="VARCHAR(255) NULL")
{
$exists = false;
$columns = mysql_query("show columns from $db");
while($c = mysql_fetch_assoc($columns)){
if($c['Field'] == $column){
$exists = true;
break;
}
}
if(!$exists){
mysql_query("ALTER TABLE `$db` ADD `$column` $column_attr");
}
}
