'Alter table add column if not exists in MySql

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 stored procedure", I get this error

Error 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 stored procedure, it runs without error.

Any suggestion about that?

mysqlsqlstored-procedures


Solution 1:[1]

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

Solution 2:[2]

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

The stored procedure runs under a context which provides the database name which may not be available when you run independently.

Solution 3:[3]

https://dev.mysql.com/doc/refman/8.0/en/sql-compound-statements.html says:

This section describes the syntax for the BEGIN ... END compound statement and other statements that can be used in the body of stored programs: Stored procedures and functions, triggers, and events.

The other compound statements mentioned by this sentence include IF ... THEN ... END IF. This means you can't use these statements as direct SQL. They must be inside a BEGIN ... END block, and that must be in a stored program. This is the reason you get a syntax error as soon as you use the token IF in an SQL query directly, but you don't get the error if you use it in a stored procedure.

But MySQL also has an IF() function you can use for scalar expressions. With this, you can test a condition and return one or the other expression. Then you can assign the result to a string variable, and run that as a new dynamic SQL statement. The "else" part of this expression is DO TRUE which is a statement that will serve as a no-op.

SET @query = IF(
    NOT EXISTS(
      SELECT *
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE table_name = 'user'
      AND table_schema = 'table_schema'
      AND column_name = 'username3'
    ),
    'ALTER TABLE `table_schema`.`user` ADD COLUMN username3 VARCHAR(50) NOT NULL DEFAULT ''''',
    'DO TRUE'
  );
PREPARE stmt FROM @query;
EXECUTE stmt;

Also notice I replaced the quotes in your ALTER TABLE statement. Use back-ticks as identifier delimiters. Use single-quotes as string delimiters. If you need to use literal single-quotes inside a string, use two single-quote characters for each one.

Solution 4:[4]

You can try this

Alter table user add username3 varchar(50) not null default '';

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 TouseefAwan
Solution 2 marc_s
Solution 3 Bill Karwin
Solution 4 Aditi Sarker

Related Questions