MySQL ALTER TABLE if column not exists

Question

I have this code   ALTER TABLE  settings  ADD COLUMN  multi user  TINYINT 1  NOT NULL DEFAULT 1   And I want to alter this table only if this column doesn t exist   I m trying a lot of different ways  but nothing works   ALTER TABLE  settings  ADD COLUMN IF NOT EXISTS  multi user  TINYINT 1  NOT NULL DEFAULT 1   With procedure   DELIMITER    CREATE PROCEDURE Alter Table   BEGIN     DECLARE  count INT      SET  count      SELECT COUNT                         FROM INFORMATION SCHEMA COLUMNS                     WHERE   TABLE NAME    settings  AND                              COLUMN NAME    multi user        IF  count   0 THEN         ALTER TABLE  settings  ADD COLUMN  multi user  TINYINT 1  NOT NULL DEFAULT 1     END IF  END    DELIMITER      I got error in END IF  then in END and then in 1  How can I make this as simple as possible

User · Answer

Sometimes it may happen that there are multiple schema created in a database.

So to be specific schema we need to target, so this will help to do it.

SELECT count(*) into @colCnt FROM information_schema.columns WHERE table_name = 'mytable' AND column_name = 'mycolumn' and table_schema = DATABASE();
IF @colCnt = 0 THEN
    ALTER TABLE `mytable` ADD COLUMN `mycolumn` VARCHAR(20) DEFAULT NULL;
END IF;

User · Answer

sql  SELECT COUNT    FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    Your Table Name  AND COLUMN NAME    Your New Column Name     RESULT   mysqli query  conn  sql     The abobe query return 0 if the column is not present in your table then you need to run alter query like below  if  RESULT        sqll  ALTER TABLE Your table Name ADD COLUMN Your New Column Name varchar 20  NOT NULL DEFAULT 0

User · Answer

Use PREPARE EXECUTE and querying the schema  The host doesn t need to have permission to create or run procedures    SET  dbname   DATABASE    SET  tablename    tableName   SET  columnname    colName   SET  preparedStatement    SELECT IF          SELECT COUNT    FROM INFORMATION SCHEMA COLUMNS     WHERE        table name    tablename        AND  table schema    dbname        AND  column name    columnname       gt  0     SELECT 1     CONCAT  ALTER TABLE     tablename    ADD     columnname    INT 11         PREPARE alterIfNotExists FROM  preparedStatement  EXECUTE alterIfNotExists  DEALLOCATE PREPARE alterIfNotExists

User · Answer

Here is a solution that does not involve querying INFORMATION SCHEMA  it simply ignores the error if the column does exist   DROP PROCEDURE IF EXISTS      DELIMITER    CREATE PROCEDURE       BEGIN   DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END    ALTER TABLE  table name  ADD COLUMN  column name  INTEGER  END    DELIMITER   CALL        DROP PROCEDURE        P S  Feel free to give it other name rather than

User · Answer

Add field if not exist   CALL addFieldIfNotExists   settings    multi user    TINYINT 1  NOT NULL DEFAULT 1      addFieldIfNotExists code   DELIMITER     DROP PROCEDURE IF EXISTS addFieldIfNotExists      DROP FUNCTION IF EXISTS isFieldExisting      CREATE FUNCTION isFieldExisting  table name IN VARCHAR 100   field name IN VARCHAR 100    RETURNS INT RETURN       SELECT COUNT COLUMN NAME       FROM INFORMATION SCHEMA columns      WHERE TABLE SCHEMA   DATABASE        AND TABLE NAME   table name IN      AND COLUMN NAME   field name IN       CREATE PROCEDURE addFieldIfNotExists       IN table name IN VARCHAR 100        IN field name IN VARCHAR 100        IN field definition IN VARCHAR 100    BEGIN      SET  isFieldThere   isFieldExisting table name IN  field name IN       IF   isFieldThere   0  THEN          SET  ddl   CONCAT  ALTER TABLE    table name IN           SET  ddl   CONCAT  ddl        ADD COLUMN             SET  ddl   CONCAT  ddl       field name IN           SET  ddl   CONCAT  ddl       field definition IN            PREPARE stmt FROM  ddl          EXECUTE stmt          DEALLOCATE PREPARE stmt       END IF   END        this is not original  but it IS copy-and-paste source  javajon blogspot com 2012 10

User · Answer

I used this approach  Without using stored procedure    SELECT COUNT    FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    tbl name  AND COLUMN NAME    column name   If it didnt return any rows then the column doesn t exists then alter the table   ALTER TABLE tbl name ADD COLUMN column name TINYINT 1  NOT NULL DEFAULT 1  Hope this helps

User · Answer

Use the following in a stored procedure   IF NOT EXISTS  SELECT NULL             FROM INFORMATION SCHEMA COLUMNS            WHERE table name    tablename               AND table schema    db name               AND column name    columnname    THEN    ALTER TABLE  TableName  ADD  ColumnName  int 1  NOT NULL default  0    END IF

User · Answer

SET  dbname   DATABASE    SET  tablename    table   SET  columnname    fieldname   SET  preparedStatement    SELECT IF          SELECT COUNT    FROM INFORMATION SCHEMA COLUMNS     WHERE        table name    tablename        AND  table schema    dbname        AND  column name    columnname       gt  0     SELECT 1     CONCAT  ALTER TABLE     tablename    ADD     columnname    DECIMAL 18 4  NULL        PREPARE alterIfNotExists FROM  preparedStatement  EXECUTE alterIfNotExists  DEALLOCATE PREPARE alterIfNotExists

User · Answer

This below worked for me      SELECT count        INTO  exist     FROM information schema columns     WHERE table schema    mydatabase      and COLUMN NAME    mycolumn      AND table name    mytable  LIMIT 1       set  query   IF  exist  lt   0   ALTER TABLE mydatabase  mytable   ADD COLUMN  mycolumn  MEDIUMTEXT NULL        select   Column Exists   status         prepare stmt from  query       EXECUTE stmt

User · Answer

hope this will help you  SELECT COLUMN NAME FROM INFORMATION SCHEMA COLUMNS WHERE table name    tbl name  AND table schema    db name  AND column name    column name    or  delimiter       CREATE PROCEDURE addcol   BEGIN IF NOT EXISTS  SELECT   FROM information schema COLUMNS WHERE COLUMN NAME  new column  AND TABLE NAME  tablename  AND TABLE SCHEMA  the schema    THEN     ALTER TABLE  the schema   the table      ADD COLUMN  new column  TINYINT 1  NOT NULL DEFAULT 1    END IF  END      delimiter      CALL addcol     DROP PROCEDURE addcol

[mysql] MySQL: ALTER TABLE if column not exists

Examples related to mysql

Examples related to ddl

Examples related to alter-table