dodaj kolumnę do tabeli mysql, jeśli nie istnieje

Moje badania i eksperymenty nie przyniosły jeszcze odpowiedzi, więc liczę na pomoc.

Modyfikuję plik instalacyjny aplikacji, która w poprzednich wersjach nie miała kolumny, którą chcę teraz dodać. Nie chcę dodawać kolumny ręcznie, ale w pliku instalacyjnym i tylko wtedy, gdy nowa kolumna nie istnieje już w tabeli.

Tabela jest tworzona w następujący sposób:

CREATE TABLE IF NOT EXISTS `#__comm_subscribers` (
      `subscriber_id` int(11) NOT NULL auto_increment,
      `user_id` int(11) NOT NULL default '0',
      `subscriber_name` varchar(64) NOT NULL default '',
      `subscriber_surname` varchar(64) NOT NULL default '',
      `subscriber_email` varchar(64) NOT NULL default '',
      `confirmed` tinyint(1) NOT NULL default '0',
      `subscribe_date` datetime NOT NULL default '0000-00-00 00:00:00',
      PRIMARY KEY  (`subscriber_id`),
      UNIQUE KEY `subscriber_email` (`subscriber_email`)
    ) ENGINE=MyISAM CHARACTER SET 'utf8' COLLATE 'utf8_general_ci' COMMENT='Subscribers for Comm are stored here.';

Jeśli dodam następujący tekst pod instrukcją create table, nie jestem pewien, co się stanie, jeśli kolumna już istnieje (i być może jest zapełniona):

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

Więc wypróbowałem następujące, które gdzieś znalazłem. Wydaje się, że to nie działa, ale nie jestem do końca pewien, czy prawidłowo go użyłem.

/*delimiter '//'
CREATE PROCEDURE addcol() BEGIN
IF NOT EXISTS(
SELECT * FROM information_schema.COLUMNS
WHERE COLUMN_NAME='subscriber_surname' AND TABLE_NAME='#__comm_subscribers'
)
THEN
    ALTER TABLE `#__comm_subscribers`
    ADD COLUMN `subscriber_surname` varchar(64) NOT NULL default '';
END IF;
END;
//
delimiter ';'
CALL addcol();
DROP PROCEDURE addcol;*/

Czy ktoś ma dobry sposób, aby to zrobić?

Należy pamiętać, że INFORMATION_SCHEMAnie jest obsługiwane w MySQL przed wersją 5.0. Procedury składowane nie są również obsługiwane przed wersją 5.0, więc jeśli potrzebujesz obsługi MySQL 4.1, to rozwiązanie nie jest dobre.

Jednym z rozwiązań stosowanych przez platformy korzystające z migracji baz danych jest zarejestrowanie w bazie danych numeru wersji schematu. Tylko tabela z jedną kolumną i jednym wierszem, z liczbą całkowitą wskazującą, która wersja jest aktualnie obowiązująca. Podczas aktualizowania schematu zwiększ liczbę.

Innym rozwiązaniem byłoby po prostu spróbować na ALTER TABLE ADD COLUMNkomendę. Powinien zgłosić błąd, jeśli kolumna już istnieje.

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

Złap błąd i zignoruj ​​go w swoim skrypcie aktualizacji.

Oto działające rozwiązanie (właśnie wypróbowane z MySQL 5.0 w systemie 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 ;

Na pierwszy rzut oka wygląda to prawdopodobnie na bardziej skomplikowane niż powinno, ale mamy tutaj do czynienia z następującymi problemami:

• IF instrukcje działają tylko w procedurach składowanych, a nie gdy są uruchamiane bezpośrednio, np. w kliencie mysql
• bardziej elegancki i zwięzły SHOW COLUMNSnie działa w procedurze składowanej, więc trzeba użyć INFORMATION_SCHEMA
• składnia instrukcji ograniczających jest dziwna w MySQL, więc aby móc tworzyć procedury składowane, musisz ponownie zdefiniować separator. Nie zapomnij o ponownym przełączeniu separatora!
• INFORMACJE_SCHEMA jest globalne dla wszystkich baz danych, nie zapomnij o filtrowaniu TABLE_SCHEMA=DATABASE(). DATABASE()zwraca nazwę aktualnie wybranej bazy danych.

Jeśli korzystasz z MariaDB, nie musisz używać procedur składowanych. Po prostu użyj na przykład:

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

Spójrz tutaj

Większość odpowiedzi dotyczy tego, jak bezpiecznie dodać kolumnę w procedurze składowanej, musiałem bezpiecznie dodać kolumnę do tabeli bez używania przechowywanej procedury i odkryłem, że MySQL nie pozwala na użycie IF Exists()spoza SP . Opublikuję swoje rozwiązanie, które może pomóc komuś w tej samej sytuacji.

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;

Innym sposobem na zrobienie tego byłoby zignorowanie błędu za pomocą 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();;

Myślę, że w ten sposób jest schludniej niż w przypadku existspodzapytania. Zwłaszcza jeśli masz wiele kolumn do dodania i chcesz kilka razy uruchomić skrypt.

Więcej informacji na temat programów obsługi kontynuacji można znaleźć pod adresem http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html

Używam MySQL 5.5.19.

Lubię mieć skrypty, które można uruchamiać i ponownie uruchamiać bez błędów, zwłaszcza tam, gdzie ostrzeżenia wydają się utrzymywać się i pojawiają się ponownie później, gdy uruchamiam skrypty, które nie mają błędów / ostrzeżeń. Jeśli chodzi o dodawanie pól, napisałem sobie procedurę, aby trochę mniej pisać:

-- 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');

Kod służący do tworzenia procedury addFieldIfNotExists jest następujący:

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

Nie napisałem procedury bezpiecznej modyfikacji kolumny, ale myślę, że powyższą procedurę można łatwo zmodyfikować, aby to zrobić.

Wziąłem sproc OP i uczyniłem go wielokrotnego użytku i niezależnym od schematu. Oczywiście nadal wymaga 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 ;

Właśnie wypróbowałem skrypt procedury składowanej. Wygląda na to, że problemem są 'znaki wokół ograniczników. W MySQL Dokumenty pokazują, że znaki ogranicznika nie potrzebują apostrofów.

Więc chcesz:

delimiter //

Zamiast:

delimiter '//'

Pracuje dla mnie :)

Jeśli uruchamiasz to w skrypcie, będziesz chciał później dodać następującą linię, aby umożliwić jej ponowne uruchomienie, w przeciwnym razie otrzymasz procedurę już istnieje.

drop procedure foo;

Najlepszy sposób na dodanie kolumny w PHP> PDO:

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

Uwaga: kolumna w tabeli nie jest powtarzalna, co oznacza, że ​​nie musimy sprawdzać istnienia kolumny, ale aby rozwiązać problem, sprawdzamy powyższy kod:

na przykład, jeśli działa alert 1, jeśli nie 0, oznacza to, że kolumna istnieje! :)

Sprawdź, czy kolumna istnieje, czy nie w 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 :)

Procedura od Jake'a https://stackoverflow.com/a/6476091/6751901 jest bardzo prostym i dobrym rozwiązaniem do dodawania nowych kolumn, ale z jedną dodatkową linią:

DROP PROCEDURE IF EXISTS foo;;

możesz dodać nowe kolumny później i będzie działać również następnym razem:

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();;

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

Następnie w $ res według cyklu poszukaj klucza swojej kolumny Smth w następujący sposób:

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

Poniżej znajduje się procedura przechowywana w MySQL, aby dodać kolumnę (y) w różnych tabelach w różnych bazach danych, jeśli kolumna nie istnieje w tabeli (ach) bazy danych, z następującymi zaletami

• można dodać wiele kolumn, aby zmienić wiele tabel w różnych bazach danych
• uruchamiane są trzy polecenia mysql, tj. DROP, CREATE, CALL For Procedure
• Nazwa bazy danych powinna zostać zmieniona zgodnie z UŻYCIEM, w przeciwnym razie może wystąpić problem dla wielu danych

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();

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

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