Programing

존재하지 않는 경우 mysql 테이블에 열 추가

lottogame 2020. 8. 14. 08:13
반응형

존재하지 않는 경우 mysql 테이블에 열 추가

내 연구와 실험이 아직 답을 얻지 못했기 때문에 도움이 필요합니다.

이전 버전에서 지금 추가하고 싶은 열이 없었던 응용 프로그램의 설치 파일을 수정하고 있습니다. 열을 수동으로 추가하지 않고 설치 파일에 새 열이 테이블에 아직없는 경우에만 추가하고 싶습니다.

테이블은 다음과 같이 생성됩니다.

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

create table 문 아래에 다음을 추가하면 열이 이미 존재하고 채워지면 어떻게되는지 확실하지 않습니다.

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

그래서 어딘가에서 찾은 다음을 시도했습니다. 이것은 작동하지 않는 것 같지만 제대로 사용했는지 완전히 확신하지 못합니다.

/*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;*/

누구든지 이것을 할 좋은 방법이 있습니까?

참고 INFORMATION_SCHEMA이전 5.0에 MySQL을 지원하지 않습니다. 5.0 이전에는 저장 프로 시저가 지원되지 않으므로 MySQL 4.1을 지원해야하는 경우이 솔루션은 좋지 않습니다.

데이터베이스 마이그레이션 을 사용하는 프레임 워크에서 사용하는 한 가지 솔루션 은 스키마의 개정 번호를 데이터베이스에 기록하는 것입니다. 단일 열과 단일 행이있는 테이블이며 현재 유효한 개정을 나타내는 정수가 있습니다. 스키마를 업데이트 할 때 숫자를 늘립니다.

또 다른 해결책은 명령을 시도 하는 것 ALTER TABLE ADD COLUMN입니다. 열이 이미 있으면 오류가 발생합니다.

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

오류를 포착하고 업그레이드 스크립트에서 무시하십시오.

다음은 작동하는 솔루션입니다 (Solaris에서 MySQL 5.0을 사용해 보았습니다).

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에서는 문을 구분하는 구문이 이상하므로 저장 프로 시저를 만들 수 있으려면 구분 기호를 다시 정의해야합니다. 구분 기호를 다시 바꾸는 것을 잊지 마십시오!
  • INFORMATION_SCHEMA는 모든 데이터베이스에 대해 전역이므로에서 필터링하는 것을 잊지 마십시오 TABLE_SCHEMA=DATABASE(). DATABASE()현재 선택된 데이터베이스의 이름을 반환합니다.

MariaDB를 사용하는 경우 저장 프로 시저를 사용할 필요가 없습니다. 예를 들어 다음을 사용하십시오.

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

여기를 보아라

대부분의 답변은 저장 프로 시저에서 안전하게 열을 추가하는 방법을 다루고 있으며, 저장 프로 시저를 사용하지 않고 안전하게 테이블에 열을 추가해야했고 MySQL이 SPIF Exists() 외부 에서 사용을 허용하지 않는다는 사실을 발견했습니다 . 같은 상황에있는 사람에게 도움이 될 수있는 솔루션을 게시하겠습니다.

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;

이를 수행하는 또 다른 방법은 다음과 같은 오류를 무시하는 것입니다 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하위 쿼리 보다 이런 방식으로 깔끔하다고 생각합니다 . 특히 추가 할 열이 많고 스크립트를 여러 번 실행하려는 경우.

계속 처리기에 대한 자세한 내용은 http://dev.mysql.com/doc/refman/5.0/en/declare-handler.html 에서 찾을 수 있습니다.

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

컬럼을 안전하게 수정하기위한 프로 시저를 작성하지는 않았지만 위의 프로시 저는 쉽게 수정 될 수 있다고 생각합니다.

나는 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 ;

저장 프로 시저 스크립트를 시도했습니다. 문제는 '구분 기호 주위의 표시입니다. MySQL의 문서는 그 구분 문자는 작은 따옴표가 필요하지 않습니다 보여줍니다.

따라서 원하는 : 

delimiter //

대신에: 

delimiter '//'

Works for me :)

If you are running this in a script, you'll want to add the following line afterwards to make it rerunnable, otherwise you get a procedure already exists error. 

drop procedure foo;

The best way for add the column in PHP > PDO : 

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

Note: the column in the table is not repeatable, that means we don't need to check the existance of a column, but for solving the problem we check the above code:

for example if it works alert 1,if not 0, which means the column exist ! :)

Check if Column Exist or not in 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 :)

Procedure from Jake https://stackoverflow.com/a/6476091/6751901 is very simple and good solution for adding new columns, but with one additional line:

DROP PROCEDURE IF EXISTS foo;;

you can add new columns later there, and it will work next time too:

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

Then in $res by cycle look for key of your column Smth like this:

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

Below are the Stored procedure in MySQL To Add Column(s) in different Table(s) in different Database(s) if column does not exists in a Database(s) Table(s) with following advantages

  • multiple columns can be added use at once to alter multiple Table in different Databases
  • three mysql commands run, i.e. DROP, CREATE, CALL For Procedure
  • DATABASE Name should be changes as per USE otherwise problem may occur for multiple datas

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

참고URL : https://stackoverflow.com/questions/972922/add-column-to-mysql-table-if-it-does-not-exist

반응형

'Programing' 카테고리의 다른 글

Active Directory에서 사용자 그룹을 가져 오는 방법은 무엇입니까?  (0) 2020.08.14
CSS 상자 그림자-상단 및 하단 만  (0) 2020.08.14
다른 모든 것이로드 될 때까지 jquery 스크립트 지연  (0) 2020.08.14
iPhone / iOS JSON 구문 분석 자습서  (0) 2020.08.14
PL / pgSQL에서 사용하는 변수에 쿼리 결과 저장  (0) 2020.08.14

'Programing'의 다른글

  • 현재글존재하지 않는 경우 mysql 테이블에 열 추가

관련글

댓글

티스토리툴바