MySQL
他の机

構文

• 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と同様の効果がありOPTIMIZE TABLE 。ディスクスペースを少しでも増やすことができます。

innodb_file_per_tableの値が現在t1ビルド時に有効な値と異なる場合、これはfile_per_tableに変換されます（またはfile_per_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データベースの名前を変更するコマンドは1つではありませんが、簡単な回避策を使用してバックアップとリストアを行うことでこれを達成できます。

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. コマンドラインで1つずつ実行します（MySQLの "bin"フォルダがパスにあると仮定し、プロンプトが表示されたら "y"を入力します）。

代替手順：

1つのデータベースから別のテーブルに名前を変更（移動）します。各テーブルでこれを行います：

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とは対照的に）移動させるには、 "トランスポータブルテーブルスペース"を使用する必要があります。近い将来には、届くファイルもありません。

2つのMySQLデータベースの名前を交換する

次のコマンドを使用して、2つの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. コマンドラインで1つずつ実行します（MySQLの "bin"フォルダがパスにあると仮定し、プロンプトが表示されたら "y"を入力します）。

MySQLテーブルの名前を変更する

1つのコマンドでテーブルの名前を変更することができます：

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>を関連する値に置き換えて実行します。