Добавить столбец в таблицу mysql, если он не существует

Ответ 1

Обратите внимание, что INFORMATION_SCHEMA не поддерживается в MySQL до 5.0. Не поддерживаются хранимые процедуры до 5.0, поэтому, если вам нужно поддерживать MySQL 4.1, это решение не очень хорошо.

Одним из решений, используемым фреймворками, использующими миграции баз данных, является запись в вашей базе данных номера версии для схемы. Просто таблица с одним столбцом и одной строкой с целым числом, указывающим, какая ревизия актуальна. Когда вы обновляете схему, увеличивайте число.

Другим решением было бы просто попробовать команду ALTER TABLE ADD COLUMN. Он должен выдать ошибку, если столбец уже существует.

ERROR 1060 (42S21): Duplicate column name 'newcolumnname'

Поймите ошибку и проигнорируйте ее в своем обновлении script.

Ответ 2

Вот рабочее решение (только что опробованное с MySQL 5.0 на Solaris):

DELIMITER $$

DROP PROCEDURE IF EXISTS upgrade_database_1_0_to_2_0 $$
CREATE PROCEDURE upgrade_database_1_0_to_2_0()
BEGIN

-- rename a table safely
IF NOT EXISTS( (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE()
        AND TABLE_NAME='my_old_table_name') ) THEN
    RENAME TABLE 
        my_old_table_name TO my_new_table_name,
END IF;

-- add a column safely
IF NOT EXISTS( (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE()
        AND COLUMN_NAME='my_additional_column' AND TABLE_NAME='my_table_name') ) THEN
    ALTER TABLE my_table_name ADD my_additional_column varchar(2048) NOT NULL DEFAULT '';
END IF;

END $$

CALL upgrade_database_1_0_to_2_0() $$

DELIMITER ;

На первый взгляд это, вероятно, выглядит более сложным, чем нужно, но мы должны иметь дело со следующими проблемами:

• Операторы
• IF работают только в хранимых процедурах, а не при запуске напрямую, например в mysql-клиенте
• более элегантный и лаконичный SHOW COLUMNS не работает в хранимой процедуре, поэтому необходимо использовать INFORMATION_SCHEMA
• Синтаксис для операторов с разделителями странный в MySQL, поэтому вы должны переопределите разделитель, чтобы иметь возможность создавать хранимые процедуры. Не забудьте выключить разделитель!
• ИНФОРМАЦИЯ_SCHEMA является глобальной для всех баз данных, не забудьте фильтр TABLE_SCHEMA=DATABASE(). DATABASE() возвращает имя текущая выбранная база данных.

Ответ 3

В большинстве ответов рассматривается вопрос о том, как безопасно добавлять столбцы в хранимую процедуру, мне нужно было безопасно добавить столбец в таблицу без использования хранимой процедуры и обнаружил, что MySQL не позволяет использовать IF Exists() за пределами SP. Я опубликую свое решение, чтобы оно могло помочь кому-то в той же ситуации.

SELECT count(*)
INTO @exist
FROM information_schema.columns 
WHERE table_schema = database()
and COLUMN_NAME = 'original_data'
AND table_name = 'mytable';

set @query = IF(@exist <= 0, 'alter table intent add column mycolumn4 varchar(2048) NULL after mycolumn3', 
'select \'Column Exists\' status');

prepare stmt from @query;

EXECUTE stmt;

Ответ 4

Если вы находитесь на MariaDB, вам не нужно использовать хранимые процедуры. Просто используйте, например:

ALTER TABLE table_name ADD COLUMN IF NOT EXISTS column_name tinyint(1) DEFAULT 0;

Смотрите здесь

Ответ 5

Другой способ сделать это - проигнорировать ошибку с помощью declare continue handler:

delimiter ;;
create procedure foo ()
begin
    declare continue handler for 1060 begin end;
    alter table atable add subscriber_surname varchar(64);
end;;
call foo();;

Я думаю, что он более аккуратный, чем с подзапросом exists. Особенно, если у вас много столбцов для добавления, и вы хотите запустить script несколько раз.

Дополнительная информация о обработчиках продолжения можно найти на http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html

Ответ 6

Я использую MySQL 5.5.19.

Мне нравятся сценарии, которые вы можете запускать и перезапускать без ошибок, особенно когда предупреждения, похоже, задерживаются, появляются снова позже, когда я запускаю скрипты, у которых нет ошибок/предупреждений. Что касается добавления полей, я написал себе процедуру, чтобы сделать ее немного меньше:

-- add fields to template table to support ignoring extra data 
-- at the top/bottom of every page
CALL addFieldIfNotExists ('template', 'firstPageHeaderEndY', 'INT NOT NULL DEFAULT 0');
CALL addFieldIfNotExists ('template', 'pageHeaderEndY', 'INT NOT NULL DEFAULT 0');
CALL addFieldIfNotExists ('template', 'pageFooterBeginY', 'INT NOT NULL DEFAULT 792');

Код для создания процедуры addFieldIfNotExists выглядит следующим образом:

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

    -- http://javajon.blogspot.com/2012/10/mysql-alter-table-add-column-if-not.html

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

Я не написал процедуру для безопасного изменения столбца, но я думаю, что описанная выше процедура может быть легко изменена для этого.

Ответ 7

Я взял OP sproc и сделал его повторно используемым и независимым от схемы. Очевидно, что он все еще требует MySQL 5.

DROP PROCEDURE IF EXISTS AddCol;

DELIMITER //

CREATE PROCEDURE AddCol(
    IN param_schema VARCHAR(100),
    IN param_table_name VARCHAR(100),
    IN param_column VARCHAR(100),
    IN param_column_details VARCHAR(100)
) 
BEGIN
    IF NOT EXISTS(
    SELECT NULL FROM information_schema.COLUMNS
    WHERE COLUMN_NAME=param_column AND TABLE_NAME=param_table_name AND table_schema = param_schema
    )
    THEN
        set @paramTable = param_table_name ;
        set @ParamColumn = param_column ;
        set @ParamSchema = param_schema;
        set @ParamColumnDetails = param_column_details;
        /* Create the full statement to execute */
        set @StatementToExecute = concat('ALTER TABLE `',@ParamSchema,'`.`',@paramTable,'` ADD COLUMN `',@ParamColumn,'` ',@ParamColumnDetails);
        /* Prepare and execute the statement that was built */
        prepare DynamicStatement from @StatementToExecute ;
        execute DynamicStatement ;
        /* Cleanup the prepared statement */
        deallocate prepare DynamicStatement ;

    END IF;
END //

DELIMITER ;

Ответ 8

Просто попробовал хранимую процедуру script. Кажется, проблема заключается в отметках ' вокруг разделителей. Документы MySQL показывают, что символам-разделителям не нужны одиночные кавычки.

Итак, вы хотите:

delimiter //

Вместо:

delimiter '//'

Работает для меня:)

Ответ 9

Если вы используете это в script, вы захотите добавить следующую строку после этого, чтобы сделать ее повторно запущенной, иначе вы получите процедуру, которая уже существует.

drop procedure foo;

Ответ 10

Лучший способ добавить столбец в PHP > PDO:

$Add = $dbh->prepare("ALTER TABLE `YourCurrentTable` ADD `YourNewColumnName` INT NOT NULL");
$Add->execute();

Примечание: столбец в таблице не повторяется, это означает, что нам не нужно проверять существование столбца, но для решения проблемы мы проверяем приведенный выше код:

например, если он работает с предупреждением 1, если не 0, что означает, что столбец существует!:)

Ответ 11

Проверьте, существует ли столбец или нет в PDO (100%)

{
    if(isset($_POST['Add']))
    {
        $ColumnExist = $dbh->prepare("SELECT * FROM ColumnChecker where column_name='$insert_column_name' LIMIT 1");
        $ColumnExist ->execute();
        $ColumnName = $ColumnExist->fetch(2);
        $Display_Column_Name = $ColumnName['column_name'];

        if($Display_Column_Name == $insert_column_name)
        {
            echo "$Display_Column_Name already exist";
        } //*****************************
        else 
        {
            $InsertColumn = $dbh->prepare("insert into ColumnChecker ( column_name ) values ('$insert_column_name')");
            $InsertColumn->execute();

            if($InsertColumn)
            {
                $Add = $dbh->prepare("ALTER TABLE `$Table` ADD `$insert_column_name` $insert_column_type($insert_column_Length) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ");
                $Add->execute();

                if($Add)
                {
                    echo 'Table has been updated';  
                }
                else 
                {
                    echo 'Sorry! Try again...'; 
                }
            }   
        }
    }
}#Add Column into Table :)

Ответ 12

$smpt = $pdo->prepare("SHOW fields FROM __TABLE__NAME__");
$smpt->execute();
$res = $smpt->fetchAll(PDO::FETCH_ASSOC);
//print_r($res);

Затем в $res по циклу найдите ключ вашей колонки Smth вот так:

    if($field['Field'] == '_my_col_'){
       return true;
    }
+

**Below code is good for checking column existing in the WordPress tables:**
public static function is_table_col_exists($table, $col)
    {
        global $wpdb;
        $fields = $wpdb->get_results("SHOW fields FROM {$table}", ARRAY_A);
        foreach ($fields as $field)
        {
            if ($field['Field'] == $col)
            {
                return TRUE;
            }
        }

        return FALSE;
    }

Ответ 13

Процедура от Jake fooobar.com/questions/60754/... - очень простое и эффективное решение для добавления новых столбцов, но с одной дополнительной строкой:

DROP PROCEDURE IF EXISTS foo;;

вы можете добавить новые столбцы позже, и он тоже будет работать в следующий раз:

delimiter ;;
DROP PROCEDURE IF EXISTS foo;;
create procedure foo ()
begin
    declare continue handler for 1060 begin end;
    alter table atable add subscriber_surname varchar(64);
    alter table atable add subscriber_address varchar(254);
end;;
call foo();;

Ответ 14

Ниже хранится процедура в MySQL для добавления столбцов в разные таблицы в разных БД, если столбец не существует в таблице (-ях) базы данных со следующими преимуществами

• можно добавить несколько столбцов, чтобы изменить несколько таблиц в разных базах данных
• выполняются три команды mysql, т.е. DROP, CREATE, CALL Для процедуры
• Имя базы данных должно быть изменено в соответствии с USE, иначе может возникнуть проблема с несколькими данными

DROP PROCEDURE  IF EXISTS `AlterTables`;
DELIMITER $$
CREATE PROCEDURE `AlterTables`() 
BEGIN
    DECLARE table1_column1_count INT;
    DECLARE table2_column2_count INT;
    SET table1_column1_count = (  SELECT COUNT(*) 
                    FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE   TABLE_SCHEMA = 'DATABASE_NAME' AND
			    TABLE_NAME = 'TABLE_NAME1' AND 
                            COLUMN_NAME = 'TABLE_NAME1_COLUMN1');
    SET table2_column2_count = (  SELECT COUNT(*) 
                    FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE   TABLE_SCHEMA = 'DATABASE_NAME' AND
			    TABLE_NAME = 'TABLE_NAME2' AND 
                            COLUMN_NAME = 'TABLE_NAME2_COLUMN2');
    IF table1_column1_count = 0 THEN
        ALTER TABLE `TABLE_NAME1`ADD `TABLE_NAME1_COLUMN1` text COLLATE 'latin1_swedish_ci' NULL AFTER `TABLE_NAME1_COLUMN3`,COMMENT='COMMENT HERE';
    END IF;
    IF table2_column2_count = 0 THEN
        ALTER TABLE `TABLE_NAME2` ADD `TABLE_NAME2_COLUMN2` VARCHAR( 100 ) NULL DEFAULT NULL COMMENT 'COMMENT HERE';
    END IF;
END $$
DELIMITER ;
call AlterTables();

Ответ 15

ALTER TABLE `subscriber_surname` ADD  IF NOT EXISTS  `#__comm_subscribers`.`subscriber_surname`;

ALTER TABLE `#__comm_subscribers` MODIFY `subscriber_surname` varchar(64) NOT NULL default '';