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?

possible duplicate of [MySQL add column if not exist](http://stackoverflow.com/questions/14381895/mysql-add-column-if-not-exist) — Rikesh, Jul 04 '14 at 10:02
possible duplicate of [MySQL: How to add a column if it doesn't already exist?](http://stackoverflow.com/questions/3242080/mysql-how-to-add-a-column-if-it-doesnt-already-exist) — Ankit Jaiswal, Jul 04 '14 at 10:03
Worth mentioning that MariaDB allows `ALTER TABLE tablename ADD COLUMN IF NOT EXISTS columnname columntype;`. — Skippy le Grand Gourou, Aug 24 '19 at 22:10
possible duplicate https://stackoverflow.com/questions/972922/add-column-to-mysql-table-if-it-does-not-exist — gawkface, Oct 07 '21 at 21:06

score 82 · Accepted Answer · answered Apr 03 '15 at 08:33

82

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;

answered Apr 03 '15 at 08:33

Harsh

2,893
29
16

14

"[I]n a stored procedure" is worth restating here. The above is valid T-SQL on its own, and using mostly that for the last couple of years, I was banging my head for a little bit before re-reading this post. – rstackhouse Apr 22 '17 at 02:59
4

So how would you do it in an adhoc script for example? This answer would work nicely in sql server, but MySql is being a real pain for me?!?! – redevill Jul 08 '20 at 02:34
any more simple way for managing bulk columns inserting and avoid dublications – Aadii Mughal Feb 12 '21 at 18:31

score 64 · Answer 2 · edited Jan 18 '19 at 13:23

64

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)
  ) > 0,
  "SELECT 1",
  CONCAT("ALTER TABLE ", @tablename, " ADD ", @columnname, " INT(11);")
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

edited Jan 18 '19 at 13:23

Yves M.

29,855
23
108
144

answered Aug 13 '15 at 13:23

abahet

10,355
4
32
23

1+ I did using it, but xperienced some trouble with php, but nevertheless I like this approach. http://dba.stackexchange.com/questions/160476/mysql-throws-error-but-if-copied-from-error-message-or-die-into-phpmyadmin-it – ddlab Jan 10 '17 at 12:48
I'm following this solution but got some error, please help. https://stackoverflow.com/questions/59999879/mysql-missing-expression-near-on-at-position-25-commands-out-of-sync-you-ca – fudu Jan 31 '20 at 08:32

score 57 · Answer 3 · answered Aug 07 '17 at 13:24

57

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 ?

answered Aug 07 '17 at 13:24

raugfer

1,844
19
19

5

I am surprised this answer is not better ranked. Is there any reason for that? Looks elegant to me. – rain_ Mar 09 '18 at 08:55
3

With this stored procedure I get to save resources for first checking if a table exists. Awesome answer, this should be voted as the best answer. – Kyad Aug 25 '18 at 09:11
this works fine when I execute it in mysql workbench but does not work when I execute it in code using mysqli_query() :( Any ideas? – john k Jan 06 '20 at 05:49
1

@johnktejik try calling mysqli_query() 4 times, one for each command individually: line 1, lines 3-7 (omitting the // delimiter from line 7), line 9, and line 10. – raugfer Jan 07 '20 at 23:50
1

@rain_ perhaps because this will skip ANY exception, not only when `column_name` already exists. answer by Harsh will not suppress any exceptions. – oriadam Jul 27 '20 at 08:02
the only answer that worked for me – Mustapha-Belkacim Jan 31 '22 at 10:01

Jonathan · Answer 4 · 2018-11-24T15:10:50.917

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/

Adiyya Tadikamalla · Answer 5 · 2018-09-26T12:39:26.567

9

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.

edited Sep 26 '18 at 12:39

answered Sep 26 '18 at 12:33

Adiyya Tadikamalla

893
10
8

score 5 · Answer 6 · answered Jul 04 '14 at 10:02

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;

score 2 · Answer 7 · edited Aug 26 '19 at 10:29

$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";
}

score 1 · Answer 8 · answered Jul 10 '18 at 13:22

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)
  ) > 0,
  "SELECT 1",
  CONCAT("ALTER TABLE ", @tablename, " ADD ", @columnname, " DECIMAL(18,4) NULL;")
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

score 1 · Answer 9 · answered Feb 15 '21 at 06:17

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 <= 0, 'ALTER TABLE mydatabase.`mytable`  ADD COLUMN `mycolumn` MEDIUMTEXT NULL',
    'select \'Column Exists\' status');

    prepare stmt from @query;

    EXECUTE stmt;

score 0 · Answer 10 · answered Dec 15 '20 at 12:40

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;

score 0 · Answer 11 · answered Jan 05 '23 at 18:33

Using this as an example: https://dbabulletin.com/index.php/2018/03/29/best-practices-using-flyway-for-database-migrations/

DELIMITER $$

DROP PROCEDURE IF EXISTS upgrade_database_4_to_5 $$

CREATE PROCEDURE upgrade_database_4_to_5()

BEGIN



IF NOT EXISTS( (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE()

AND COLUMN_NAME='multi_user' AND TABLE_NAME='settings') ) THEN

ALTER TABLE `settings` ADD COLUMN `multi_user` TINYINT(1) NOT NULL DEFAULT 1;

END IF;



END $$

CALL upgrade_database_4_to_5() $$

DELIMITER ;

score 0 · Answer 12 · answered Jun 13 '23 at 20:48

0

Simplified:

ALTER TABLE my_table ADD COLUMN IF NOT EXISTS my_column VARCHAR(255);

answered Jun 13 '23 at 20:48

Elson Costa

416
4
8

1

This won't work, have you tested this in mysql? – Ankit Jindal Jun 21 '23 at 04:07
@AnkitJindal It will works, this is valid on both mysql / mariadb – Paul-Marie Jul 04 '23 at 12:28
@Paul-Marie I have tried running on my system and it gives me this error - `#1064 - You have an error in your SOL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS my column VARCHAR (255) ' at line 1` – Ankit Jindal Jul 05 '23 at 14:56
1

@AnkitJindal it's available since years, which is your mysql version ? did you server stayed block in 2010's ? – Paul-Marie Jul 07 '23 at 06:44
@AnkitJindal Yes I had. – Elson Costa Jul 07 '23 at 14:33
In MySQL 8.0.30, this doesn't work. – Alexander Pravdin Aug 01 '23 at 13:08

MySQL: ALTER TABLE if column not exists

12 Answers12

Linked

Related