Alter table add column if not exists in MySql-CodePudding

I need to make an alter table via migration on a node:14 server with umzug.

the code is :

IF NOT EXISTS(
  SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'user'
AND table_schema = 'table_schema'
AND column_name = 'username3') THEN

ALTER TABLE 'table_schema'.'user' ADD COLUMN username3' varchar(50) NOT NULL DEFAULT '';
END IF

but if i run this "without a store procedure" I have this error

Errore SQL [1064] [42000]: You have an error in your SQL syntax; check the manual 
that > corresponds to your MySQL server version for the right syntax to use near 'IF NOT 
EXISTS(
SELECT *
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name ' at line 2

with the store procedure it goes without error

any suggestion about?

CodePudding user response：

I believe that you need to add a USE <dbname> before your IF EXISTS code.

The store procedure runs under a context which provides the data base name which may not be available when you run independently.

CodePudding user response：

I hope this works

     IF NOT EXISTS (
     SELECT * 
     FROM   sys.columns 
     WHERE  object_id = OBJECT_ID(N'user') 
     AND name = 'username3'
     )
      BEGIN
      ALTER TABLE user ADD  username3 varchar(50) NOT NULL 
      DEFAULT ;
      END