Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 27.1Mb
PDF (A4) - 27.1Mb
EPUB - 7.5Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb


13.1.7 ALTER TABLE 構文

ALTER [ONLINE|OFFLINE] [IGNORE] 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
  | 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
  | ADD PARTITION (partition_definition)
  | DROP PARTITION partition_names
  | TRUNCATE PARTITION {partition_names | ALL}
  | COALESCE PARTITION number
  | REORGANIZE PARTITION partition_names INTO (partition_definitions)
  | EXCHANGE PARTITION partition_name WITH TABLE tbl_name
  | 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

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'

table_options:
    table_option [[,] table_option] ...  (see CREATE TABLE options)

partition_options:
    (see CREATE TABLE options)

ALTER TABLE は、テーブルの構造を変更します。たとえば、カラムを追加または削除したり、インデックスを作成または破棄したり、既存のカラムの型を変更したり、カラムまたはテーブル自体の名前を変更したりできます。また、テーブルに使用されているストレージエンジンやテーブルのコメントなどの特性を変更することもできます。

テーブル名のあとに、行う変更を指定します。何も指定されていない場合、ALTER TABLE は何もしません。

許可される多くの変更の構文は、CREATE TABLE ステートメントの各句に似ています。詳細は、セクション13.1.17「CREATE TABLE 構文」を参照してください。

table_options は、ENGINEAUTO_INCREMENTAVG_ROW_LENGTHMAX_ROWSROW_FORMAT などの、CREATE TABLE ステートメントで使用できる種類のテーブルオプションを示します。すべてのテーブルオプションのリストと各オプションの説明については、セクション13.1.17「CREATE TABLE 構文」を参照してください。ただし、ALTER TABLE は、DATA DIRECTORY および INDEX DIRECTORY テーブルオプションを無視します。

partition_options は、再パーティション化、パーティションの追加、削除、マージ、および分割、パーティション化の保守の実行などのために、パーティション化されたテーブルで使用できるオプションを示します。ALTER TABLE ステートメントには、ほかの変更指定に加えて、PARTITION BY または REMOVE PARTITIONING 句を含めることができますが、PARTITION BY または REMOVE PARTITIONING 句は、ほかのどの指定よりもあとの最後に指定する必要があります。ADD PARTITIONDROP PARTITIONCOALESCE PARTITIONREORGANIZE PARTITIONEXCHANGE PARTITIONANALYZE PARTITIONCHECK PARTITION、および REPAIR PARTITION オプションは、個々のパーティションに対して機能するため、1 つの ALTER TABLE 内でほかの変更指定と組み合わせることはできません。パーティションのオプションの詳細は、セクション13.1.17「CREATE TABLE 構文」およびセクション13.1.7.1「ALTER TABLE パーティション操作」を参照してください。ALTER TABLE ... EXCHANGE PARTITION ステートメントの詳細および例については、セクション19.3.1「RANGE および LIST パーティションの管理」を参照してください。

操作によっては、ストレージエンジンがその操作をサポートしていないテーブルに対して試行されると、警告が生成される場合があります。これらの警告は、SHOW WARNINGS で表示できます。セクション13.7.5.41「SHOW WARNINGS 構文」を参照してください。

ALTER TABLE のトラブルシューティングについては、セクションB.5.7.1「ALTER TABLE での問題」を参照してください。

ストレージ、パフォーマンス、および並列性に関する考慮事項

ほとんどの場合、ALTER TABLE は元のテーブルの一時的なコピーを作成します。MySQL は、そのテーブルを変更しているほかの操作を待ってから、処理を続行します。そのコピーに変更を組み込み、元のテーブルを削除したあと、新しいテーブルの名前を変更します。ALTER TABLE の実行中、ほかのセッションは元のテーブルを読み取ることができます (例外については、すぐあとに説明します)。ALTER TABLE 操作の開始後に開始されたテーブルへの更新や書き込みは、新しいテーブルの準備ができるまで停止されてから、どの更新も失敗することなく、新しいテーブルに自動的にリダイレクトされます。元のテーブルの一時的なコピーは、新しいテーブルのデータベースディレクトリ内に作成されます。これは、テーブルの名前を別のデータベースに変更した ALTER TABLE 操作のための元のテーブルのデータベースディレクトリとは異なる場合があります。

前に触れた例外とは、ALTER TABLE が、テーブルの .frm ファイルの新しいバージョンをインストールし、古いファイルを破棄して、テーブルおよびテーブル定義キャッシュから古くなったテーブル構造をクリアする準備ができた時点で (書き込みだけでなく) 読み取りをブロックすることです。この時点で、このステートメントは排他的ロックを取得する必要があります。それを行うために、現在の読み取り側が完了するのを待って、新しい読み取り (および書き込み) をブロックします。

MyISAM テーブルの場合は、myisam_sort_buffer_size システム変数を大きな値に設定することによって、インデックスの再作成 (変更プロセスのもっとも遅い部分) を高速化できます。

一部の操作では、一時テーブルを必要としないインプレース ALTER TABLE が可能です。

  • ALTER TABLE tbl_name RENAME TO new_tbl_name をほかのオプションなしで実行すると、MySQL は単純に、コピーを作成することなく、テーブル tbl_name に対応するすべてのファイルの名前を変更します。(RENAME TABLE ステートメントを使用してテーブルの名前を変更することもできます。セクション13.1.32「RENAME TABLE 構文」を参照してください。)名前変更されたテーブル専用に付与された権限は、どれも新しい名前には移行されません。それらは、手動で変更する必要があります。

  • サーバーが変更する必要があるのはテーブルの内容ではなく、テーブルの .frm ファイルだけであるため、テーブルデータを変更せず、テーブルメタデータだけを変更する変更はただちに実行されます。次の変更は、この方法で実行できる迅速な変更です。

    • カラムの名前変更。ただし、MySQL 5.6.6 より前の InnoDB ストレージエンジンを除きます。

    • カラムのデフォルト値の変更 (NDB テーブルを除きます。「MySQL Cluster オンライン操作の制限」を参照してください)。

    • 有効なメンバー値のリストの最後に新しい列挙またはセットメンバーを追加することによる、ENUM または SET カラムの定義の変更。ただし、データ型のストレージサイズが変更される場合を除きます。たとえば、メンバー数が 8 の SET カラムにメンバーを追加すると、値ごとに必要なストレージが 1 バイトから 2 バイトに変更されます。これには、テーブルコピーが必要になります。リストの途中にメンバーを追加すると、既存のメンバーの番号が変更されます。これには、テーブルコピーが必要になります。

  • ADD PARTITIONDROP PARTITIONCOALESCE PARTITIONREBUILD PARTITION、または REORGANIZE PARTITION を含む ALTER TABLE は、一時テーブルを作成しません (NDB テーブルで使用される場合を除きます)。ただし、これらの操作では、一時的なパーティションファイルが作成されます。

    RANGE または LIST パーティションに対する ADD または DROP 操作は即座の操作か、ほぼ即座の操作です。HASH または KEY パーティションに対する ADD または COALESCE 操作では、LINEAR HASH または LINEAR KEY が使用されていないかぎり、すべてのパーティション間でデータがコピーされます。ADD または COALESCE 操作はパーティションごとに実行されますが、これは実質的に、新しいテーブルの作成と同じです。REORGANIZE 操作では変更されたパーティションのみがコピーされ、変更されていないものはそのままです。

  • インデックスの名前変更。InnoDB を除きます。

  • InnoDBNDB に対するインデックスの追加または削除。

old_alter_table システム変数を ON に設定するか、または alter_specification 句の 1 つとして ALGORITHM=COPY を指定することによって、通常であればテーブルコピーを必要としない ALTER TABLE 操作で強制的に (MySQL 5.0 でサポートされている) 一時テーブルの方法を使用するようにできます。old_alter_table 設定と、DEFAULT 以外の値を持つ ALGORITHM 句の間に矛盾がある場合は、ALGORITHM 句が優先されます。(ALGORITHM = DEFAULT は、ALGORITHM 句をまったく指定しないのと同じです。)

ALGORITHM=INPLACE を指定すると、それをサポートする句やストレージエンジンに対してインプレースの手法が使用されるようになり、サポートされていない場合はエラーで失敗します。そのため、予測していたものとは異なるストレージエンジンを使用するテーブルを変更しようとした場合の非常に長いテーブルコピーが回避されます。InnoDB テーブルに対するオンライン DDL については、セクション14.11「InnoDB とオンライン DDL」を参照してください。

MySQL 5.6.16 では、ALTER TABLE は、ADD COLUMNCHANGE COLUMNMODIFY COLUMNADD INDEX、および FORCE 操作に関して、古い時間カラムを 5.6 形式にアップグレードします。テーブルを再構築しなければならないため、この変換は INPLACE アルゴリズムを使用して実行することはできません。そのため、これらの場合に ALGORITHM=INPLACE を指定するとエラーになります。必要であれば、ALGORITHM=COPY を指定します。

MySQL 5.6.22 から、テーブルを KEY によってパーティション化するために使用されるマルチカラムインデックスに対する ALTER TABLE 操作は、この操作によってカラムの順序が変更される場合はオンラインで実行できません。このような場合は、代わりに、コピーする ALTER TABLE を使用する必要があります。(Bug #17896265)

MySQL Cluster はオンライン ALTER TABLE 操作もサポートしていますが、ALGORITHM=INPLACE 構文を受け入れないため、代わりに ONLINE キーワードが使用されます。ONLINE および OFFLINE キーワードは、MySQL Cluster でのみサポートされます。これらのキーワードは、MySQL Cluster NDB 7.3 から非推奨です。MySQL Cluster NDB 7.4 では引き続きサポートされますが、将来のバージョンの MySQL Cluster で削除される可能性があります。正確な構文およびその他の詳細については、セクション13.1.7.2「MySQL Cluster での ALTER TABLE オンライン操作」を参照してください。

LOCK 句を使用すると、変更中のテーブルの並列読み取りおよび書き込みのレベルを制御できます。この句にデフォルト以外の値を指定すると、変更操作中に特定の量の並列アクセスまたは排他性が要求されるようにすることが可能であり、要求されたレベルのロックを使用できない場合は操作が停止されます。LOCK 句のパラメータは次のとおりです。

  • LOCK = DEFAULT
    

    指定された ALGORITHM 句 (存在する場合) および ALTER TABLE 操作に対する最大レベルの並列性: サポートされている場合は、並列読み取りおよび書き込みを許可します。そうでない場合、サポートされている場合は、並列読み取りを許可します。そうでない場合は、排他的アクセスを適用します。

  • LOCK = NONE
    

    サポートされている場合は、並列読み取りおよび書き込みを許可します。そうでない場合は、エラーメッセージを返します。

  • LOCK = SHARED
    

    サポートされている場合は、並列読み取りを許可しますが、書き込みはブロックします。書き込みは、指定された ALGORITHM 句 (存在する場合) および ALTER TABLE 操作に対して、並列書き込みがストレージエンジンによってサポートされている場合でもブロックされます。並列読み取りがサポートされていない場合は、エラーメッセージを返します。

  • LOCK = EXCLUSIVE
    

    排他的アクセスを適用します。これは、指定された ALGORITHM 句 (存在する場合) および ALTER TABLE 操作に対して、並列読み取り/書き込みがストレージエンジンによってサポートされている場合でも実行されます。

MySQL 5.6.3 の時点では、ALTER TABLE tbl_name FORCE を使用して、テーブルを再構築するnull変更操作を実行することもできます。以前は、FORCE オプションは認識されましたが、無視されました。MySQL 5.6.17 の時点では、オンライン DDL のサポートは FORCE オプションに対して提供されます。詳細は、セクション14.11.1「オンライン DDL の概要」を参照してください。

NDB テーブルの場合、可変幅カラム上のインデックスを追加および削除する操作は、そのほとんどの期間、テーブルコピーを行なったり、並列 DML アクションをブロックしたりすることなくオンラインで実行されます。セクション13.1.7.2「MySQL Cluster での ALTER TABLE オンライン操作」を参照してください。

使用上の注意

  • ALTER TABLE を使用するには、このテーブルに対する ALTERCREATE、および INSERT 権限が必要です。テーブルを名前変更するには、古いテーブル側で ALTER および DROP と、新しいテーブル側で ALTERCREATE、および INSERT 権限が必要です。

  • IGNORE は、標準 SQL への MySQL 拡張です。これは、新しいテーブル内の一意のキーに関して重複が存在する場合や、厳密モードが有効になっているときに警告が発生した場合の ALTER TABLE の動作を制御します。IGNORE が指定されていない場合は、重複キーエラーが発生すると、コピーは中止され、ロールバックされます。IGNORE が指定されている場合は、一意のキーが重複している行のうちの 1 行だけが使用されます。その他の競合している行は削除されます。正しくない値は、もっとも近い一致する許容可能な値に切り捨てられます。

    MySQL 5.6.17 の時点では、IGNORE 句は非推奨であり、使用すると警告が生成されます。MySQL 5.7 では、IGNORE は削除されます。

  • テーブルが書き込みロックされているときに、そのテーブル構造を変更するために ALTER TABLE が使用されると、保留中の INSERT DELAYED ステートメントは失われます。

  • table_option は、ENGINEAUTO_INCREMENTAVG_ROW_LENGTHMAX_ROWSROW_FORMAT などの、CREATE TABLE ステートメントで使用できる種類のテーブルオプションを示します。すべてのテーブルオプションのリストと各オプションの説明については、セクション13.1.17「CREATE TABLE 構文」を参照してください。ただし、ALTER TABLE は、DATA DIRECTORY および INDEX DIRECTORY テーブルオプションを無視します。

    たとえば、テーブルを InnoDB テーブルになるように変換するには、次のステートメントを使用します。

    ALTER TABLE t1 ENGINE = InnoDB;
    

    テーブルを InnoDB ストレージエンジンに切り替えるときの考慮事項については、セクション14.6.4「MyISAM から InnoDB へのテーブルの変換」を参照してください。

    ENGINE 句を指定すると、ALTER TABLE はテーブルを再構築します。これは、そのテーブルに指定されたストレージエンジンがすでに存在する場合にも当てはまります。

    既存の InnoDB テーブルに対して ALTER TABLE tbl_name ENGINE=INNODB を実行すると、null ALTER TABLE 操作が実行されます。これは、セクション14.10.4「テーブルのデフラグ」で説明されているように、InnoDB テーブルのデフラグに使用できます。InnoDB テーブルに対して ALTER TABLE tbl_name FORCE を実行しても、同じ機能が実行されます。

    MySQL 5.6.17 の時点では、ALTER TABLE tbl_name ENGINE=INNODBALTER TABLE tbl_name FORCE の両方がオンライン DDL (ALGORITHM=COPY) を使用します。詳細は、セクション14.11.1「オンライン DDL の概要」を参照してください。

    テーブルのストレージエンジンを変更しようとすると、セクション5.1.7「サーバー SQL モード」で説明されているように、その結果は目的のストレージエンジンが使用可能かどうかや、NO_ENGINE_SUBSTITUTION SQL モードの設定によって影響を受けます。

    データが誤って失われることのないように、ALTER TABLE を使用して、テーブルのストレージエンジンを MERGE または BLACKHOLE に変更することはできません。

    新しい行に使用される AUTO_INCREMENT カウンタの値を変更するには、次のようにします。

    ALTER TABLE t2 AUTO_INCREMENT = value;
    

    このカウンタを、現在使用されている値以下の値にリセットすることはできません。InnoDBMyISAM のどちらの場合も、この値が現在 AUTO_INCREMENT カラム内にある最大値以下である場合、この値は現在の AUTO_INCREMENT カラムの最大値に 1 を加えた値にリセットされます。

  • 1 つの ALTER TABLE ステートメントで、カンマで区切られた複数の ADDALTERDROP、および CHANGE 句を発行できます。これは、ALTER TABLE ステートメントごとに各句が 1 つしか許可されない標準 SQL への MySQL 拡張です。たとえば、1 つのステートメントで複数のカラムを削除するには、次のようにします。

    ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;
    
  • CHANGE col_nameDROP col_name、および DROP INDEX は、標準 SQL への MySQL 拡張です。

  • ワード COLUMN はオプションであり、省略できます。

  • column_definition 句は、ADDCHANGE に対して、CREATE TABLE に対するのと同じ構文を使用します。セクション13.1.17「CREATE TABLE 構文」を参照してください。

  • CHANGE old_col_name new_col_name column_definition 句を使用して、カラムの名前を変更できます。それを行うには、古いカラム名と新しいカラム名、およびそのカラムの現在の定義を指定します。たとえば、INTEGER カラムの名前を a から b に変更するには、次のように行うことができます。

    ALTER TABLE t1 CHANGE a b INTEGER;
    

    カラムの名前を変更せずに、その型を変更するには、古いカラム名と新しいカラム名が同じ場合でも、CHANGE 構文にはそれらの名前が引き続き必要です。例:

    ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
    

    MODIFY を使用しても、カラムの名前を変更せずに、その型を変更できます。

    ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
    

    MODIFY は、Oracle の互換性のための ALTER TABLE への拡張です。

    CHANGE または MODIFY を使用する場合は、column_definition に、データ型および PRIMARY KEYUNIQUE などのインデックス属性以外の、新しいカラムに適用されるすべての属性を含める必要があります。元の定義には存在するが、新しい定義として指定されていない属性は引き継がれません。カラム col1INT UNSIGNED DEFAULT 1 COMMENT 'my column' として定義されているときに、このカラムを次のように変更するとします。

    ALTER TABLE t1 MODIFY col1 BIGINT;
    

    結果として得られるカラムは BIGINT として定義されますが、属性 UNSIGNED DEFAULT 1 COMMENT 'my column' は含まれません。これらを保持するには、ステートメントを次のようにしてください。

    ALTER TABLE t1 MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT 'my column';
    
  • CHANGE または MODIFY を使用してデータ型を変更すると、MySQL は、既存のカラム値を新しい型にできるだけ変換しようとします。

    警告

    この変換によって、データが変更される可能性があります。たとえば、文字列カラムを短くすると、値が切り捨てられる可能性があります。新しいデータ型への変換によってデータが失われる場合は操作が成功しないようにするには、ALTER TABLE を使用する前に厳密な SQL モードを有効にします (セクション5.1.7「サーバー SQL モード」を参照してください)。

  • テーブル行内の特定の位置にカラムを追加するには、FIRST または AFTER col_name を使用します。デフォルトでは、そのカラムを最後に追加します。また、CHANGE または MODIFY 操作で FIRSTAFTER を使用して、テーブル内のカラムを並べ替えることもできます。

  • ALTER ... SET DEFAULT または ALTER ... DROP DEFAULT は、それぞれ、カラムの新しいデフォルト値を指定するか、または古いデフォルト値を削除します。古いデフォルトが削除され、かつカラムを NULL にできる場合、新しいデフォルトは NULL です。カラムを NULL にできない場合、MySQL は、セクション11.6「データ型デフォルト値」で説明されているようにデフォルト値を割り当てます。

  • DROP INDEX は、インデックスを削除します。これは、標準 SQL への MySQL 拡張です。セクション13.1.24「DROP INDEX 構文」を参照してください。インデックス名に確信がない場合は、SHOW INDEX FROM tbl_name を使用します。

  • テーブルからカラムが削除された場合、そのカラムは、それが含まれているすべてのインデックスからも削除されます。インデックスを構成するすべてのカラムが削除された場合は、そのインデックスも削除されます。CHANGE または MODIFY を使用して、インデックスが存在するカラムを短くしたときに、結果として得られるカラムの長さがインデックスの長さより短くなった場合、MySQL は自動的にそのインデックスを短くします。

  • テーブルに 1 つのカラムしか含まれていない場合は、そのカラムを削除できません。テーブルを削除することが目的である場合は、代わりに DROP TABLE を使用します。

  • DROP PRIMARY KEY は、主キーを削除します。主キーが存在しない場合は、エラーが発生します。主キーのパフォーマンス特性 (特に InnoDB テーブルの場合) については、セクション8.3.2「主キーの使用」を参照してください。

    テーブルに UNIQUE INDEX または PRIMARY KEY を追加すると、重複キーをできるだけ早く検出できるようにするために、MySQL はそれを一意でないどのインデックスよりも前に格納します。

  • 一部のストレージエンジンでは、インデックスの作成時にインデックスタイプを指定できます。index_type 指定子の構文は、USING type_name です。USING の詳細は、セクション13.1.13「CREATE INDEX 構文」を参照してください。推奨される位置は、カラムリストのあとです。このオプションをカラムリストの前に使用するためのサポートは、将来の MySQL リリースで削除される予定です。

    index_option 値は、インデックスの追加オプションを指定します。USING はそのようなオプションの 1 つです。許可される index_option 値の詳細は、セクション13.1.13「CREATE INDEX 構文」を参照してください。

  • ALTER TABLE ステートメントのあとに、インデックスカーディナリティー情報を更新するために ANALYZE TABLE の実行が必要になることがあります。セクション13.7.5.23「SHOW INDEX 構文」を参照してください。

  • ORDER BY を使用すると、特定の順序で行が含まれる新しいテーブルを作成できます。このオプションは主に、ほとんどは特定の順序で行をクエリーすることがわかっている場合に役立ちます。このオプションをテーブルの大幅な変更のあとに使用すると、パフォーマンスの向上が得られる可能性があります。場合によっては、テーブルが、あとでその並べ替えに使用するカラムごとの順番になっていれば、MySQL でのソートが簡単になることがあります。

    注記

    挿入や削除を行うと、このテーブルは指定された順序のままではなくなります。

    ORDER BY 構文では、ソートのためのカラム名を 1 つ以上指定できます。その各カラム名に続けて、オプションで、それぞれ昇順または降順のソート順序を示す ASC または DESC を指定できます。デフォルトは昇順です。ソート条件として許可されるのはカラム名だけです。任意の式は許可されていません。この句は、ほかのどの句よりもあとの最後に指定するようにしてください。

    InnoDB は常に、クラスタ化されたインデックスに従ってテーブル行を並べ替えるため、ORDER BYInnoDB テーブルでは意味がありません。

    注記

    パーティション化されたテーブルに対して使用されている場合、ALTER TABLE ... ORDER BY は、各パーティション内でのみ行を並べ替えます。

  • MyISAM テーブルに対して ALTER TABLE を使用した場合、一意でないインデックスはすべて (REPAIR TABLE として) 別のバッチに作成されます。多くのインデックスがあるときは、この方法で ALTER TABLE がはるかに早くなります。

    MyISAM テーブルの場合は、キーの更新を明示的に制御できます。ALTER TABLE ... DISABLE KEYS を使用して、一意でないインデックスの更新を停止するよう MySQL に指示します。次に、ALTER TABLE ... ENABLE KEYS を使用して、不足しているインデックスを再作成します。MyISAM はこれを、キーを 1 つずつ挿入するのに比べてはるかに高速な特殊なアルゴリズムで実行するため、一括挿入操作を実行する前にキーを無効にすると大幅な高速化が得られます。ALTER TABLE ... DISABLE KEYS を使用するには、先に説明した権限に加えて INDEX 権限が必要です。

    一意でないインデックスは、無効になっている間、有効なときにはこのインデックスを使用する SELECTEXPLAIN などのステートメントで無視されます。

  • MySQL 5.6.7 より前は、ALTER TABLE を使用して外部キーカラムの定義を変更すると、参照整合性が失われる可能性がありました。たとえば、NULL 値を含む外部キーカラムを NOT NULL になるように変更すると、NULL 値が空の文字列になりました。同様に、親テーブル内の行を削除する ALTER TABLE IGNORE によって、参照整合性が破壊される可能性がありました。

    5.6.7 の時点では、参照整合性が失われる可能性のある外部キーカラムへの変更がサーバーによって禁止されます。また、安全でない可能性のある、このようなカラムのデータ型への変更も禁止されます。たとえば、VARCHAR(20)VARCHAR(30) への変更は許可されますが、それを VARCHAR(1024) に変更することは、それによって個々の値を格納するために必要なバイト長の数が変更されるため許可されません。回避方法として、カラム定義を変更する前に ALTER TABLE ... DROP FOREIGN KEY を使用し、あとで ALTER TABLE ... ADD FOREIGN KEY を使用します。

  • FOREIGN KEY および REFERENCES 句は、ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (...) REFERENCES ... (...) を実装している InnoDB および NDB ストレージエンジンによってサポートされます。セクション14.6.6「InnoDB と FOREIGN KEY 制約」を参照してください。その他のストレージエンジンでは、これらの句は解析されますが、無視されます。CHECK 句は、すべてのストレージエンジンによって解析されますが、無視されます。セクション13.1.17「CREATE TABLE 構文」を参照してください。構文の句を受け入れるが、無視する理由は互換性のためです。つまり、ほかの SQL サーバーからコードを移植し、参照によってテーブルを作成するアプリケーションを実行することを容易にするためです。セクション1.8.2「MySQL と標準 SQL との違い」を参照してください。

    ALTER TABLE では、CREATE TABLE とは異なり、ADD FOREIGN KEYindex_name (指定されている場合) を無視し、自動的に生成された外部キー名を使用します。回避方法として、外部キー名を指定する CONSTRAINT 句を含めます。

    ADD CONSTRAINT name FOREIGN KEY (....) ...
    
    重要

    参照がカラム指定の一部として定義されているインラインの REFERENCES 指定は、暗黙のうちに無視されます。MySQL は、個別の FOREIGN KEY 指定の一部として定義されている REFERENCES 句のみを受け入れます。

    注記

    パーティション化された InnoDB テーブルは、外部キーをサポートしていません。この制限は、NDB テーブル ([LINEAR] KEY によって明示的にパーティション化されたテーブルを含む) には適用されません。詳細は、セクション19.6.2「ストレージエンジンに関連するパーティショニング制限」を参照してください。

  • InnoDB および NDB ストレージエンジンは、外部キーを削除するための ALTER TABLE の使用をサポートします。

    ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;
    

    詳細は、セクション14.6.6「InnoDB と FOREIGN KEY 制約」を参照してください。

  • MySQL 5.6.6 より前は、同じ ALTER TABLE ステートメントでの外部キーの追加と削除は、問題が発生する場合があるためサポートされていません。操作ごとに個別のステートメントを使用するようにしてください。MySQL 5.6.6 の時点では、同じ ALTER TABLE ステートメントでの外部キーの追加と削除は ALTER TABLE ... ALGORITHM=INPLACE ではサポートされますが、ALTER TABLE ... ALGORITHM=COPY では未サポートのままです。

  • .ibd ファイル内の独自のテーブルスペースで作成された InnoDB テーブルの場合は、そのファイルを破棄したり、インポートしたりできます。.ibd ファイルを破棄するには、次のステートメントを使用します。

    ALTER TABLE tbl_name DISCARD TABLESPACE;
    

    これにより、現在の .ibd ファイルが削除されるため、最初にバックアップがあることを確認してください。テーブルスペースファイルが破棄されている間にテーブルの内容を変更しようとすると、エラーが発生します。テーブルスペースファイルが破棄されている間に、セクション14.11「InnoDB とオンライン DDL」に示されている DDL 操作を実行できます。

    バックアップ .ibd ファイルを元のテーブルにインポートするには、それをデータベースディレクトリにコピーしてから、次のステートメントを発行します。

    ALTER TABLE tbl_name IMPORT TABLESPACE;
    

    テーブルスペースファイルは、必ずしも、あとでインポートされるサーバー上に作成されている必要はありません。MySQL 5.6 では、テーブルスペースファイルの別のサーバーからのインポートが機能するのは、両方のサーバーのステータスが GA (General Availablility) であり、かつそれらのバージョンが同じシリーズ内にある場合です。そうでない場合、そのファイルはインポートされるサーバー上に作成されている必要があります。

    注記

    ALTER TABLE ... IMPORT TABLESPACE 機能は、インポートされたデータに対して外部キー制約を課しません。

    セクション14.5.2「InnoDB File-Per-Table モード」を参照してください。

  • テーブルのデフォルトの文字セットおよびすべての文字カラム (CHARVARCHARTEXT) を新しい文字セットに変更するには、次のようなステートメントを使用します。

    ALTER TABLE tbl_name
    CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];
    

    このステートメントでは、すべての文字カラムの照合順序も変更されます。使用する照合順序を示す COLLATE 句を指定しない場合、このステートメントは、その文字セットのデフォルトの照合順序を使用します。この照合順序が目的とするテーブル使用に適していない (たとえば、大文字と小文字が区別される照合順序から大文字と小文字が区別されない照合順序に変更されてしまう) 場合は、照合順序を明示的に指定します。

    データ型が VARCHAR か、またはいずれかの TEXT 型であるカラムに対して、CONVERT TO CHARACTER SET は、新しいカラムが確実に元のカラムと同じ数の文字を格納できる十分な長さになるように、必要に応じてデータ型を変更します。たとえば、TEXT カラムには、そのカラム内の値のバイト長 (最大 65,535) を格納するための 2 バイト長があります。latin1 TEXT カラムの場合は、各文字に 1 バイトが必要なため、このカラムには最大 65,535 文字を格納できます。このカラムが utf8 に変換された場合は、各文字に最大 3 バイトが必要になる可能性があるため、可能性のある最大の長さは 3 × 65,535 = 196,605 バイトになります。その長さは TEXT カラムのバイト長には収まらないため、MySQL はそのデータ型を、バイト長に 196,605 の値を記録できる最小の文字列型である MEDIUMTEXT に変換します。同様に、VARCHAR カラムは MEDIUMTEXT に変換される可能性があります。

    今説明した型のデータ型の変更を回避するには、CONVERT TO CHARACTER SET を使用しないでください。代わりに、MODIFY を使用して個々のカラムを変更します。例:

    ALTER TABLE t MODIFY latin1_text_col TEXT CHARACTER SET utf8;
    ALTER TABLE t MODIFY latin1_varchar_col VARCHAR(M) CHARACTER SET utf8;
    

    CONVERT TO CHARACTER SET binary を指定した場合、CHARVARCHAR、および TEXT カラムは、それぞれ対応するバイナリ文字列型 (BINARYVARBINARYBLOB) に変換されます。つまり、これらのカラムには文字セットが含まれなくなるため、以降の CONVERT TO 操作は適用されません。

    charset_nameDEFAULT である場合は、データベース文字セットが使用されます。

    警告

    CONVERT TO 操作は、文字セット間でカラム値を変換します。これは、ある文字セット (latin1 など) のカラムがあるが、格納された値が実際には、ほかの何らかの互換性のない文字セット (utf8 など) を使用している場合に必要なものではありません。この場合は、このようなカラムごとに、次を実行する必要があります。

    ALTER TABLE t1 CHANGE c1 c1 BLOB;
    ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;
    

    これが機能する理由は、BLOB カラムとの間で変換する場合は変換が発生しないためです。

    テーブルのデフォルトの文字セットのみを変更するには、次のステートメントを使用します。

    ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name;
    

    ワード DEFAULT はオプションです。デフォルトの文字セットは、あとで (たとえば、ALTER TABLE ... ADD column で) テーブルに追加するカラムの文字セットを指定しない場合に使用される文字セットです。

mysql_info() C API 関数を使用すると、ALTER TABLE によってコピーされた行数、および (IGNORE が使用されている場合は) 一意のキー値の重複のために削除された行数を確認できます。セクション23.8.7.35「mysql_info()」を参照してください。


User Comments
  Posted by Tom S on December 18, 2002
IF you want to change a SET or ENUM column you may
not want to use the ALTER TABLE ... MODIFY
syntax.
It tries to keep the actual string values and not
the integer representation of the values, even
though they are stored as integers.
For example, if you just want to make a change in
spelling of the values in your enum column or your
set column, consider doing it like this:
ALTER TABLE table ADD new_column ...;
UPDATE table SET new_column = old_column + 0;
ALTER TABLE table DROP old_column;
  Posted by David Bell on August 15, 2003
You can use Alter Table to optimise a table without locking out selects (only writes), by altering a column to be the same as it's current definition. This is better than using repair table which obtains a read/write lock.

E.g.
mysql> describe Temp_Table;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| ID | int(10) unsigned | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> alter table Temp_Table change column ID ID int unsigned;

This will cause mysql to re-create the table and thus remove any deleted space.

This is useful for 24/7 databases where you don't want to completely lock a table.
  Posted by Mark Mackay on July 9, 2004
If you are just changing a column name on a MyISAM table and want to avoid duplicating the entire table, try the following (no warranty provided but worked for me):

For peace-of-mind -- try this with some dummy data first!

1. Backup the <original_table>.frm file from your master table (and the data if you can, but you're probably reading this because you can't).

2. create table with the identical schema to the one you want to alter (type "show create table <tablename> and just change the name to something). Lets say you called the table "rename_temp1"

3. execute the "alter table <rename_temp1> change <old_column_name> <new_column_name> char(128) not null" [substituting your the old definition -- ensuring you keep column type the same]

3. Ensuring you a have made a copy of your original .frm file -- copy the <rename_temp1>.frm file to <original_table>.frm.

4. voila -- all going well your column should be renamed without a full copy in/out (very useful for 140G tables...)

5. probably best to run a myisamchck on the table before making live again
  Posted by Hadi Rastgou on July 13, 2005
When you want to drop a UNIQUE KEY in an InnoDb table, have to pay attention not to occure this situation:
Please check that columns used in the UNIQUE KEY are not used as FOREIGN KEY (each of them).
If so, must to drop that Forign keys first.
See Example below please.

UNIQUE KEY `unique` (`id1`, `id2`),
CONSTRAINT `fk_1` FOREIGN KEY (`id1`) REFERENCES `tbl1` (`id`) ON DELETE CASCADE,
CONSTRAINT `fk_2` FOREIGN KEY (`id2`) REFERENCES `tbl2` (`id`) ON DELETE CASCADE

In this situation, you have to drop both FOREIGN KEYs first, in order to can drop the UNIQUE KEY.
  Posted by Flemming Funch on November 5, 2005
If you're trying to convert a whole database to a different character set, and you thought you might have to change the fields one by one, this kind of command is really handy:

ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

However, after using it on a lot of tables I made the grim discovery that for older myisam tables that didn't have any character set, it mangled the length of most varchar fields. Specifically, it divided their length with 3. Bizarrely, it didn't lose the existing data, even though it was longer than the field lengths, but it was a problem with new records and with indexes.

So, if you're going to do a character set converstion, make sure the table already has a character set. That it doesn't might not be immediately obvious, as tools like phpMyAdmin will show the default character set, if the table or the field doesn't have one set.
  Posted by Daniel Cottrell on November 21, 2005
TO ADD A FOREIGN KEY TO AN EXISTING TABLE (I couldn't see a good example) you can do this:

alter table users add foreign key(favGenre) references products_genre(gid);

Where favgenre is the column of the table that has the foreign key and products_genre(gid) is the table and primary key you are referencing.
  Posted by Duane Hitz on March 31, 2006
Attempting to "ALTER TABLE ... DROP PRIMARY KEY" on a table when an AUTO_INCREMENT column exists in the key generates an error:

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key.

To make this work without erroring, drop and re-add the new primary key in a single statement, e.g.:

ALTER TABLE mytable DROP PRIMARY KEY, ADD PRIMARY KEY(col1,col2);
  Posted by Rachel McConnell on September 7, 2006
You can't drop a NOT NULL constraint on a column the way you can a foreign key or an index, or a default. Instead, just use the 'change' or 'modify' syntax and leave off the NOT NULL bit:

alter table table_name modify col_name bigint default null;

Any pre-existing indexes or foreign keys on the column are not affected.
  Posted by Geoffrey Hoffman on June 17, 2007
If you are trying to change the case of a table name using the ALTER TABLE syntax and it appears to fail silently,

or if you try to RENAME TABLE something TO soMeThiNg and get a 'table already exists' error,

or if you try to CREATE TABLE MixedCaseTableName and get a table named mixedcasetablename, these are not bugs:

See: Identifier Case Sensitivity
http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html

If your development environment has MySQL5 and you're hosting on MySQL4 you can get 'table not found' errors based on the case of the table names.
  Posted by Cengiz Gunay on November 27, 2007
As mentioned above, ALTER TABLE is going to make a temporary copy of the whole table data (.MYD) in the same directory as the original table and not in the temporary directories given to MySQL.

In some cases a third copy of the table (.TMD) is made. This means you must have up to three copies of free space in that directory. Unfortunately MySQL does not break the files into pieces if it runs out of space.

As a table grows larger this process becomes more expensive. Therefore, keys and indices must be added as early as possible to large tables in spite of the update cost that comes with each insert.
  Posted by Reinhard Vielhaber on March 14, 2008
For moving a table from one database to another just do:

use db_old;
alter table tab_name rename db_new.tab_name;
  Posted by John Walker on May 16, 2008
There seem to any number of convoluted methods (not to mention some finger wagging by purists questioning the practice, even here) for altering the sequence of fields in a MySQL table but ALTER does the job as prescribed. It isn't completely self-evident from the description above so here's what worked for me:

ALTER TABLE tablex CHANGE colx colx colxtype AFTER coly.

That is, you're not changing the name of the column but still need to specify 'oldname newname' as 'oldname oldname'
  Posted by Nadav Kavalerchik on March 26, 2009
  Posted by federico cattozzi on April 10, 2009
If you want to change the table's engine for all tables, you can use this code to generate your sql script.

From MyISAM engine to InnoDB engine: set db_name and db_username then copy and paste the follow lines on a Linux/MacOSX shell.

DB_NAME="db_name";
mysql --user=db_username -p --execute="USE information_schema; SELECT CONCAT(\"ALTER TABLE \`\", TABLE_SCHEMA,\"\`.\`\", TABLE_NAME, \"\` TYPE = InnoDB;\") as MySQLCMD from TABLES where TABLE_SCHEMA = \""${DB_NAME}"\";" > ${DB_NAME}-temp.sql;
#delete first line
sed '/MySQLCMD/d' ${DB_NAME}-temp.sql > ${DB_NAME}-innodb.sql;
mysql --user=db_username -p < ${DB_NAME}-innodb.sql;
rm ${DB_NAME}-temp.sql;
rm ${DB_NAME}-innodb.sql;

You can customize the code above for your OS.

I used code from here:
http://forums.mysql.com/read.php?20,244395,244421#msg-244421

  Posted by John45 G on May 18, 2009
Found some good alter table here:

http://www.examplenow.com/mysql/alter

--John
  Posted by David Friedman on May 7, 2010
It is okay to add two or more columns in the same query where the before and after clauses refer to newly created columns:

ALTER TABLE mytable ADD COLUMN dummy1 VARCHAR(40) AFTER id ADD COLUMN dummy2 VARCHAR(12) AFTER dummy1;
  Posted by Bill Vogel on May 27, 2010
If you use "ALTER TABLE mytable AUTO_INCREMENT = N" the MySQL server will create a new table, copy all the data from the old table to the new, delete the old and then rename the new one even though there is no structural change to the table. The server response will show that all the rows have been "affected", like this:
mysql> alter table mytable auto_increment=1000000;
Query OK, 512691 rows affected (1 min 4.55 sec)
Records: 512691 Duplicates: 0 Warnings: 0

There are potential issues that may arise from the table copy, especially if you didn't expect it! I.e. is there a sufficient amount of free disk space for the second copy of the data, etc., etc..

The bottom line for me is to go back to the "old fashioned way" - just insert a dummy row and explicitly set the AUTO_INCREMENT column to N - 1, then immediately delete the dummy row. The next row that is inserted will start at N and go from there.

  Posted by nabeel khan on June 13, 2010
none of the alter code sql queries were working for me in phpmyadmin

however after digging much, finally found something that may help you all.

use this query code:

ALTER TABLE table_name MODIFY column_to_move column_type AFTER column_to_reference

have explained it with example here:

http://nabtron.com/alter-sequence-columns-field-database-table-mysql-phpmyadmin/1914/
  Posted by Karen Sanasaryan on August 11, 2010
If you need copy big MYISAM tables, just create a new table with same structure (SHOW CREATE TABLE table_to_copy; CREATE TABLE new_table %old table structure%), then copy old table's .frm, .MYD and .MYI files, rename copied files from old_name.* to new_name.*, then run ANALYZE TABLE new_name. No need to restart MySql.
  Posted by Ayden Bissessar on December 6, 2010
If you want to ALTER a table and set the default value to CURRENT_TIMESTAMP for a timestamp column, the listed syntax of "ALTER TABLE foo ALTER COLUMN ts SET DEFAULT CURRENT_TIMESTAMP" will NOT work.

Instead, the syntax below will work. You also need to put the column name twice, I don't know why, it just works.

"ALTER TABLE tablename CHANGE columnname columnname TIMESTAMP DEFAULT CURRENT_TIMESTAMP;"
  Posted by Arthur Liu on February 8, 2011

When adding a new column to a table, and making it a foreign key, if you get

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

please check :

1, The new column must be an index column,
2, All value in the new column must be valid foreign keys.

Here are correct steps:
(Suppose you have author in volumes table, but want to add it to books table.)

ALTER TABLE books ADD COLUMN `author` int(10) unsigned NOT NULL ;
ALTER TABLE books ADD INDEX (author) ;
UPDATE books SET author = ( SELECT author FROM volumes WHERE volumes.id = books.volume_id ) ;
ALTER TABLE books ADD FOREIGN KEY (author) REFERENCES `users` (`id`) ;


  Posted by Claude Warren on March 22, 2011
It is not possible to change a column name on an InnoDB table if the column participates in a foreign key without first dropping the foreign key. It does not matter if the column is used to reference another table or if it is referenced by another table in the foreign key.
  Posted by Lincoln Rickwood on June 24, 2011
Stored procedure to add a column (sorry about the lack of indentation, the comment box seems to strip leading spaces.)

delimiter //
drop procedure if exists AddTableColumn //
create procedure AddTableColumn
( in schemaName varchar(128) -- If null use name of current schema;
, in tableName varchar(128) -- If null an exception will be thrown.
, in columnName varchar(128) -- If null an exception will be thrown.
, in columnDefinition varchar(1024) -- E.g. 'int not null default 1' (Can include comment here if columnComment is null.)
, in columnComment varchar(1024) -- E.g. 'comment about column.' Can be null. (If null then the comment can be included in columnDefinition.)
, in ifPresent enum('leaveUnchanged', 'dropAndReplace', 'modifyExisting') -- null=leaveUnchanged.
, out outcome tinyint(1) -- 0=unchanged, 1=replaced, 2=modified, 4=added.
)
begin
declare doDrop tinyint(1) default null;
declare doAdd tinyint(1) default null;
declare doModify tinyint(1) default null;
declare tmpSql varchar(4096) default '';

set schemaName = coalesce(schemaName, schema());
set ifPresent = coalesce(ifPresent, 'leaveUnchanged');
-- select schemaName, ifPresent;
if exists
(select *
from `information_schema`.`COLUMNS`
where `COLUMN_NAME` = columnName
and `TABLE_NAME` = tableName
and `TABLE_SCHEMA` = schemaName
)
then
-- select 'exists';
if (ifPresent = 'leaveUnchanged')
then
set doDrop = 0;
set doAdd = 0;
set doModify = 0;
set outcome = 0;
elseif (ifPresent = 'dropAndReplace')
then
set doDrop = 1;
set doAdd = 1;
set doModify = 0;
set outcome = 1;
elseif (ifPresent = 'modifyExisting')
then
set doDrop = 0;
set doAdd = 0;
set doModify = 1;
set outcome = 2;
end if;
else
-- select 'not-exists';
set doDrop = 0;
set doAdd = 1;
set doModify = 0;
set outcome = 4;
end if;

-- select doDrop, doAdd, doModify, outcome;
if (doDrop = 1)
then
set tmpSql = concat( 'alter table `', schemaName, '`.`', tableName, '` drop column `', columnName, '` ');
set @sql = tmpSql;
prepare tmp_stmt from @sql;
execute tmp_stmt;
deallocate prepare tmp_stmt;
end if;

if (doAdd = 1)
then
set tmpSql = concat( 'alter table `', schemaName, '`.`', tableName, '` add column `', columnName, '` ', columnDefinition);
if (columnComment is not null)
then
set tmpSql = concat(tmpSql, ' comment "', columnComment, '"');
end if;
set @sql = tmpSql;
prepare tmp_stmt from @sql;
execute tmp_stmt;
deallocate prepare tmp_stmt;
end if;

if (doModify = 1)
then
set tmpSql = concat( 'alter table `', schemaName, '`.`', tableName, '` modify column `', columnName, '` ', columnDefinition);
if (columnComment is not null)
then
set tmpSql = concat(tmpSql, ' comment "', columnComment, '"');
end if;
set @sql = tmpSql;
prepare tmp_stmt from @sql;
execute tmp_stmt;
deallocate prepare tmp_stmt;
end if;
end; //

  Posted by Pavel Tishkin on September 8, 2011
CREATE PROCEDURE `clone_table`(IN tablex TEXT)
BEGIN
DECLARE sqls,tablexs,cols TEXT;
DECLARE done INT DEFAULT 0;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

SET tablexs = CONCAT(tablex,'_tmp');

SET @sql=CONCAT('ALTER TABLE `',tablex,'` RENAME TO `',tablexs,'`');
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

SET @sql=CONCAT('CREATE TABLE ',tablex,' LIKE ',tablexs);
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

SELECT column_name INTO cols
FROM `information_schema`.`COLUMNS`
WHERE table_name=tablexs
AND table_schema=DATABASE()
AND column_key='pri' AND extra='auto_increment';

IF NOT done THEN
SELECT `auto_increment` INTO @id
FROM `information_schema`.`TABLES`
WHERE table_name=tablexs
AND table_schema=DATABASE();

SET @sql=CONCAT('ALTER TABLE `',tablex,'` AUTO_INCREMENT=',@id);
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END IF;
END $$

AND

CREATE PROCEDURE `clone_table_sync`(IN table_namex VARCHAR(100), IN idx INT)
BEGIN
DECLARE done, ids INT DEFAULT 0;
DECLARE table_namexs TEXT;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

SET table_namexs = CONCAT(table_namex,'_tmp');

SELECT `auto_increment` INTO ids
FROM `information_schema`.`TABLES`
WHERE table_name=table_namexs
AND table_schema=DATABASE();

WHILE ids>idx DO
SET @sql=CONCAT('INSERT IGNORE INTO `',table_namex,'` SELECT * FROM `',table_namexs,
'` WHERE i_id>',idx,' AND i_id<=',(idx+5000));
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

-- SELECT SLEEP(1) INTO @tmp2;
SET idx=idx+5000;
END WHILE;

END $$
  Posted by Jonathan Evans on December 3, 2011
An addition to John Walker's example above for reordering the columns in a table.

ALTER TABLE tablex CHANGE colx colx colxtype AFTER coly

Not only do you need to specify 'oldname newname' as 'oldname oldname', you also need to respecify the type of 'oldname' as 'colxtype' (or change it of course) for the statement to work.
  Posted by Ferdous Khan on January 12, 2012
ALTER TABLE sales.order ADD UNIQUE(order_ref);

This command will update 'sales' databases 'order' tables 'order_ref' column to become uniquely indexed. If the column already have some duplicate data, an error message will be prompted.
  Posted by Robert Kline on August 12, 2014
Note that if you include 'UNIQUE' as part of the column definition in an ALTER TABLE MODIFY COLUMN ... statement for a column which was original defined as UNIQUE, MySQL will create a second UNIQUE index. What you need to do if you don't want that second index (any why would you? as it does nothing but slow things down), and you're dealing with scripted changes which you have to give to a separate DBA team who won't accept any instructions which involve manual work (so they won't examine the results of the ALTER TABLE statement to find out the name of the extra index which needs to be dropped), on a server over which you don't have administrative privileges, is to leave out the UNIQUE keyword from the column definition, knowing that MySQL will leave the UNIQUE constraint in place. It's as if MySQL doesn't consider 'UNIQUE' as part of the column definition (even though the syntax rules include it as part of column_definition).
  Posted by Saif Ullah on September 6, 2014
If you are just changing a column name on a MyISAM table and want to avoid duplicating the entire table, try the following (no warranty provided but worked for me for my website http://techleaks.us/ . I wasted my 4 days looking and searching all around this..):

For peace-of-mind -- try this with some dummy data first!

1. Backup the <original_table>.frm file from your master table (and the data if you can, but you're probably reading this because you can't).

2. create table with the identical schema to the one you want to alter (type "show create table <tablename> and just change the name to something). Lets say you called the table "rename_temp1"

3. execute the "alter table <rename_temp1> change <old_column_name> <new_column_name> char(128) not null" [substituting your the old definition -- ensuring you keep column type the same]

3. Ensuring you a have made a copy of your original .frm file -- copy the <rename_temp1>.frm file to <original_table>.frm.

4. voila -- all going well your column should be renamed without a full copy in/out (very useful for 140G tables...)

5. probably best to run a myisamchck on the table before making live again
Sign Up Login You must be logged in to post a comment.