MySQL database table schema update
I want to update my MySQL database schema (with MySQL code), but I am unfortunately not sure about the state of the tables as they are allocated.
Let's say some "clients" have a table named "user" with a schema like
'name' VARCHAR(64) NOT NULL
'password' VARCHAR(64) NOT NULL
I want to add an "email" column, but it is possible that they already have an email column (depending on their install version).
How can I run a command that makes sure there is an "email" column and does nothing if it already exists? Keep in mind that I will be doing this for many complex tables.
I know I can create temporary tables and repopulate (and will be if that's the only solution), but I suppose there might be some CREATE or UPDATE table command that has "oh, you already have this column, skip ", logic.
source to share
You can try this:
DELIMITER $$
CREATE PROCEDURE Alter_MyTable()
BEGIN
DECLARE _count INT;
SET _count = ( SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'user' AND
COLUMN_NAME = 'email');
IF _count = 0 THEN
ALTER TABLE user
ADD COLUMN email varchar(512);
END IF;
END $$
DELIMITER ;
or rather, make it a shared stored procedure like this:
create procedure AddColumnIfDoesntExists(
IN dbName tinytext,
IN tableName tinytext,
IN fieldName tinytext,
IN fieldDef text)
begin
IF NOT EXISTS (
SELECT * FROM information_schema.COLUMNS
WHERE column_name=fieldName
and table_name=tableName
and table_schema=dbName
)
THEN
set @ddl=CONCAT('ALTER TABLE ',dbName,'.',tableName,
' ADD COLUMN ',fieldName,' ',fieldDef);
prepare stmt from @ddl;
execute stmt;
END IF;
end;
//
delimiter ';'
If the column already exists, the operator ALTER TABLE ADD COLUMN
will throw an error, so if you think you might lose data due to trying to add a column that already exists, that won't if you need to handle errors. See add column to mysql table if it doesn't exist
There are also resources to tell you how to handle them with shop procedures, etc. See MySQL add column if it doesn't exist .
Hope it helps,
Jose Luis
source to share