syntax - IF NOT EXISTS SyntaxError in MySQL

Question

I'm trying to write a kind of update SQL to add new columns if they don't already exist.

I've already tried a few things, but nothing works.

If I try an IF NOT EXISTS I always get a syntax error that I can't understand.

IF NOT EXISTS( 
  SELECT * 
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'nutzer'AND table_schema = 'restdb' AND column_name = 'api_nutzer')
THEN
  ALTER TABLE `nutzer` ADD `api_nutzer` TINYINT;
END IF;

[SQL] IF NOT EXISTS( SELECT *
            FROM INFORMATION_SCHEMA.COLUMNS
           WHERE table_name = 'nutzer'
             AND table_schema = 'restdb'
             AND column_name = 'api_nutzer')  THEN

  ALTER TABLE `nutzer` ADD `api_nutzer` TINYINT;
[Err] 1064 - 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
          ' at line 1

If I execute the SELECT directly, I get a result

Does anyone have an idea or know where my mistake is?

I use MySQL Server Version 8.0.22

question from:https://stackoverflow.com/questions/65649436/if-not-exists-syntaxerror-in-mysql

Answer 1

I have understood that the Exists clause can be used in the WHERE section

SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);

try to reconstruct your query for captured if the row exists

for example

<i>if( /*begin extra condition*/ Select 1 where exists 
(SELECT *
            FROM INFORMATION_SCHEMA.COLUMNS
           WHERE table_name = 'nutzer'
             AND table_schema = 'restdb'
             AND column_name = 'api_nutzer') = 1 /*end extra condition*/) then

ALTER TABLE `nutzer` ADD `api_nutzer` TINYINT;</i>