MySQL
ALTER TABLE

통사론

• ALTER [무시] TABLE tbl_name [ alter_specification [, alter_specification] ...] [partition_options]

비고

    alter_specification: table_options
      | ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ]
      | ADD [COLUMN] (col_name column_definition,...)
      | ADD {INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option] ...
      | ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) [index_option] ...
      | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) [index_option] ...
      | ADD FULLTEXT [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ...
      | ADD SPATIAL [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ...
      | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition
      | ALGORITHM [=] {DEFAULT|INPLACE|COPY}
      | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
      | CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
      | LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
      | MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
      | DROP [COLUMN] col_name
      | DROP PRIMARY KEY
      | DROP {INDEX|KEY} index_name
      | DROP FOREIGN KEY fk_symbol
      | DISABLE KEYS
      | ENABLE KEYS
      | RENAME [TO|AS] new_tbl_name
      | RENAME {INDEX|KEY} old_index_name TO new_index_name
      | ORDER BY col_name [, col_name] ...
      | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
      | [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
      | DISCARD TABLESPACE
      | IMPORT TABLESPACE
      | FORCE
      | {WITHOUT|WITH} VALIDATION
      | ADD PARTITION (partition_definition)
      | DROP PARTITION partition_names
      | DISCARD PARTITION {partition_names | ALL} TABLESPACE
      | IMPORT PARTITION {partition_names | ALL} TABLESPACE
      | TRUNCATE PARTITION {partition_names | ALL}
      | COALESCE PARTITION number
      | REORGANIZE PARTITION partition_names INTO (partition_definitions)
      | EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH|WITHOUT} VALIDATION]
      | ANALYZE PARTITION {partition_names | ALL}
      | CHECK PARTITION {partition_names | ALL}
      | OPTIMIZE PARTITION {partition_names | ALL}
      | REBUILD PARTITION {partition_names | ALL}
      | REPAIR PARTITION {partition_names | ALL}
      | REMOVE PARTITIONING
      | UPGRADE PARTITIONING
   index_col_name: col_name [(length)] [ASC | DESC]
   index_type: USING {BTREE | HASH}
   index_option: KEY_BLOCK_SIZE [=] value
      | index_type
      | WITH PARSER parser_name
      | COMMENT 'string'

스토리지 엔진 변경; 재건 테이블; 변경 file_per_table

예를 들어, t1 가 현재 InnoDB 테이블이 아닌 경우,이 명령문은 스토리지 엔진을 InnoDB로 변경한다.

ALTER TABLE t1 ENGINE = InnoDB;

테이블이 벌써 InnoDB의 경우는, 테이블과 그 인덱스를 재 구축 해, OPTIMIZE TABLE 와 같은 효과가 있습니다. 디스크 공간을 약간 늘릴 수 있습니다.

innodb_file_per_table 의 값이 현재 t1 이 빌드되었을 때 유효한 값과 다른 경우 이것은 file_per_table로 변환됩니다.

TABLE의 ALTER COLUMN

CREATE DATABASE stackoverflow;

USE stackoverflow;

Create table stack(
    id_user int NOT NULL,
    username varchar(30) NOT NULL,
    password varchar(30) NOT NULL
);

ALTER TABLE stack ADD COLUMN submit date NOT NULL; -- add new column
ALTER TABLE stack DROP COLUMN submit; -- drop column
ALTER TABLE stack MODIFY submit DATETIME NOT NULL; -- modify type column
ALTER TABLE stack CHANGE submit submit_date DATETIME NOT NULL; -- change type and name of column
ALTER TABLE stack ADD COLUMN mod_id INT NOT NULL AFTER id_user; -- add new column after existing column

ALTER 테이블에 INDEX 추가

성능 향상을 위해 열에 인덱스를 추가 할 수 있습니다.

ALTER TABLE TABLE_NAME ADD INDEX `index_name` (`column_name`)

복합 (다중 열) 색인을 추가하도록 변경

ALTER TABLE TABLE_NAME ADD INDEX `index_name` (`col1`,`col2`)

자동 증가 값 변경

자동 증가 값을 변경하면 대량 삭제 후에 AUTO_INCREMENT 컬럼에 간격을 두지 않을 때 유용합니다.

예를 들어, 원하지 않는 (광고) 행이 테이블에 게시 된 경우이를 삭제하고 자동 증가 값의 차이를 수정하려고합니다. AUTO_INCREMENT 컬럼의 MAX 값이 100이라고 가정합니다. 다음을 사용하여 자동 증가 값을 수정할 수 있습니다.

ALTER TABLE your_table_name AUTO_INCREMENT = 101;

기본 키 열의 유형 변경

ALTER TABLE fish_data.fish DROP PRIMARY KEY;
ALTER TABLE fish_data.fish MODIFY COLUMN fish_id DECIMAL(20,0) NOT NULL PRIMARY KEY;

먼저 기본 키를 제거하지 않고이 열의 유형을 수정하려고하면 오류가 발생합니다.

열 정의 변경

db 컬럼의 정의를 변경하면 아래의 쿼리를 사용할 수 있습니다.이 db 스키마가 있다면

users (
    firstname varchar(20),
    lastname varchar(20),
    age char(2)
)

age 열의 유형을 char 에서 int 변경하려면 아래 쿼리를 사용합니다.

ALTER TABLE users CHANGE age age tinyint UNSIGNED NOT NULL;

일반적인 형식은 다음과 같습니다.

ALTER TABLE table_name CHANGE column_name new_column_definition

MySQL 데이터베이스 이름 바꾸기

MySQL 데이터베이스의 이름을 바꾸는 단일 명령은 없지만 백업 및 복원을 통해 간단한 해결 방법을 사용할 수 있습니다.

mysqladmin -uroot -p<password> create <new name>
mysqldump -uroot -p<password> --routines <old name> | mysql -uroot -pmypassword <new name>
mysqladmin -uroot -p<password> drop <old name>

단계 :

1. 위의 행을 텍스트 편집기로 복사하십시오.
2. 관련 값으로 <old name> , <new name> 및 <password> (다른 사용자를 사용하려면 선택적 root 에 대한 모든 참조를 바꿉니다.
3. 명령 줄에서 하나씩 실행하십시오 (MySQL "bin"폴더가 경로에 있다고 가정하고 메시지가 나타나면 "y"를 입력하십시오).

대체 단계 :

한 테이블에서 다른 테이블로 각 테이블의 이름을 바꾸십시오 (이동). 각 테이블에 대해 이렇게하십시오.

RENAME TABLE `<old db>`.`<name>` TO  `<new db>`.`<name>`;

당신은 다음과 같은 일을함으로써 그 문장을 만들 수 있습니다.

SELECT CONCAT('RENAME TABLE old_db.', table_name, ' TO ',
                           'new_db.', table_name)
    FROM information_schema.TABLES
    WHERE table_schema = 'old_db';

경고. 파일 시스템에서 단순히 파일을 옮겨서 테이블이나 데이터베이스를 만들지 마십시오. 이것은 MyISAM의 옛날에는 잘 작동했지만, InnoDB와 테이블 스페이스의 새로운 시대에는 작동하지 않습니다. 특히 "Data Dictionary"가 파일 시스템에서 시스템 InnoDB 테이블로 옮겨 졌을 때, 아마도 다음 주요 릴리즈에서. InnoDB 테이블의 PARTITION 을 옮기는 것 (단지 DROPping 과 반대)은 "transportable tablespaces"를 사용해야한다. 가까운 장래에 도달 할 파일조차 없을 것입니다.

두 개의 MySQL 데이터베이스 이름 바꾸기

다음 명령을 사용하여 두 개의 MySQL 데이터베이스 ( <db1> 및 <db2> )의 이름을 바꿀 수 있습니다.

mysqladmin -uroot -p<password> create swaptemp
mysqldump -uroot -p<password> --routines <db1> | mysql -uroot -p<password> swaptemp
mysqladmin -uroot -p<password> drop <db1>
mysqladmin -uroot -p<password> create <db1>
mysqldump -uroot -p<password> --routines <db2> | mysql -uroot -p<password> <db1>
mysqladmin -uroot -p<password> drop <db2>
mysqladmin -uroot -p<password> create <db2>
mysqldump -uroot -p<password> --routines swaptemp | mysql -uroot -p<password> <db2>
mysqladmin -uroot -p<password> drop swaptemp

단계 :

1. 위의 행을 텍스트 편집기로 복사하십시오.
2. 관련 값과 <db1> , <db2> 및 <password> (다른 사용자를 사용하려면 선택적 root 참조를 모두 바꿉니다.
3. 명령 줄에서 하나씩 실행하십시오 (MySQL "bin"폴더가 경로에 있다고 가정하고 메시지가 나타나면 "y"를 입력하십시오).

MySQL 테이블 이름 바꾸기

단일 명령으로 테이블의 이름을 바꿀 수 있습니다.

RENAME TABLE `<old name>` TO `<new name>`;

다음 구문은 정확히 동일합니다.

ALTER TABLE `<old name>` RENAME TO `<new name>`;

임시 테이블의 이름을 바꾸면 구문의 ALTER TABLE 버전을 사용해야합니다.

단계 :

1. 위의 줄에서 <old name> 및 <new name> 을 관련 값으로 대체하십시오. 주 : 테이블이 다른 데이터베이스로 이동중인 경우, dbname . tablename 구문은 <old name> 및 / 또는 <new name> 사용할 수 있습니다.
2. MySQL 커맨드 라인의 관련 데이터베이스 또는 MySQL Workbench와 같은 클라이언트에서 실행하십시오. 주 : 사용자는 이전 테이블에 대해 ALTER 및 DROP 특권을 갖고 새 테이블에 대해 CREATE 및 INSERT 특권을 가져야합니다.

MySQL 테이블의 열 이름 바꾸기

열의 이름을 바꾸는 것은 단일 명령문에서 수행 할 수 있지만 새 이름, "열 정의"(즉 데이터 형식 및 null 허용, 자동 증가 등과 같은 기타 선택적 속성)도 지정해야합니다.

ALTER TABLE `<table name>` CHANGE `<old name>` `<new name>` <column definition>;

단계 :

1. MySQL 명령 행이나 MySQL Workbench와 같은 클라이언트를 엽니 다.
2. 다음 명령문을 실행하십시오. SHOW CREATE TABLE <table name>; ( <table name> 을 관련 값으로 대체).
3. 이름을 바꿀 열의 전체 열 정의를 기록하십시오 (예 : 열의 이름 다음에 있지만 쉼표가 다음 열 이름과 구분되기 전에 나타나는 모든 것) .
4. 위의 행에서 <old name> , <new name> 및 <column definition> 을 관련 값으로 바꾸고 실행하십시오.