MySQL structure synchronization

Re: MySQL structure synchronization

am 11.01.2006 20:49:30 von Jim Michaels

This is a multi-part message in MIME format.

------=_NextPart_000_0137_01C616A5.155A9270
Content-Type: text/plain;
charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

you can always do an ALTER TABLE statement on the remote structure. be =
careful what you are doing though. like the other post said, adding =
columns is fine, but dropping columns doesn't seem to sound like what =
you want.

here's the manual:
ALTER [IGNORE] TABLE tbl_name
alter_specification [, alter_specification] ...

alter_specification:
ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
| ADD [COLUMN] (column_definition,...)
| ADD INDEX [index_name] [index_type] (index_col_name,...)
| ADD [CONSTRAINT [symbol]]
PRIMARY KEY [index_type] (index_col_name,...)
| ADD [CONSTRAINT [symbol]]
UNIQUE [index_name] [index_type] (index_col_name,...)
| ADD [FULLTEXT|SPATIAL] [index_name] (index_col_name,...)
| ADD [CONSTRAINT [symbol]]
FOREIGN KEY [index_name] (index_col_name,...)
[reference_definition]
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name column_definition
[FIRST|AFTER col_name]
| MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP INDEX index_name
| DROP FOREIGN KEY fk_symbol
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO] new_tbl_name
| ORDER BY col_name
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
| DISCARD TABLESPACE
| IMPORT TABLESPACE
| table_options
ALTER TABLE allows you to change the structure of an existing table. For =
example, you can add or delete columns, create or destroy indexes, =
change the type of existing columns, or rename columns or the table =
itself. You can also change the comment for the table and type of the =
table. The syntax for many of the allowable alterations is similar to =
clauses of the CREATE TABLE statement. This includes table_options =
modifications, for options such as ENGINE, AUTO_INCREMENT, and =
AVG_ROW_LENGTH. See Section 13.2.5, â=9CCREATE TABLE =
Syntaxâ=9D. Some operations may result in warnings if attempted on =
a table for which the storage engine does not support the operation. In =
MySQL 4.1 and up, these warnings can be displayed with SHOW WARNINGS. =
See Section 13.5.4.20, â=9CSHOW WARNINGS Syntaxâ=9D. If you =
use ALTER TABLE to change a column specification but DESCRIBE tbl_name =
indicates that your column was not changed, it is possible that MySQL =
ignored your modification for one of the reasons described in Section =
13.2.5.1, â=9CSilent Column Specification Changesâ=9D. For =
example, if you try to change a VARCHAR column to CHAR, MySQL still uses =
VARCHAR if the table contains other variable-length columns. ALTER TABLE =
works by making a temporary copy of the original table. The alteration =
is performed on the copy, then the original table is deleted and the new =
one is renamed. While ALTER TABLE is executing, the original table is =
readable by other clients. Updates and writes to the table are stalled =
until the new table is ready, then are automatically redirected to the =
new table without any failed updates. Note that if you use any other =
option to ALTER TABLE than RENAME, MySQL always creates a temporary =
table, even if the data wouldn't strictly need to be copied (such as =
when you change the name of a column). We plan to fix this in the =
future, but because ALTER TABLE is not a statement that is normally used =
frequently, this isn't high on our TODO list. For MyISAM tables, you can =
speed up the index re-creation operation (which is the slowest part of =
the alteration process) by setting the myisam_sort_buffer_size system =
variable to a high value. a.. To use ALTER TABLE, you need ALTER, =
INSERT, and CREATE privileges for the table. b.. IGNORE is a MySQL =
extension to standard SQL. It controls how ALTER TABLE works if there =
are duplicates on unique keys in the new table or if warnings occur when =
STRICT mode is enabled. If IGNORE isn't specified, the copy is aborted =
and rolled back if duplicate-key errors occur. If IGNORE is specified, =
then for rows with duplicates on a unique key, only the first row is =
used. The others conflicting rows are deleted. Wrong values are =
truncated to the closest matching acceptable value. c.. You can issue =
multiple ADD, ALTER, DROP, and CHANGE clauses in a single ALTER TABLE =
statement. This is a MySQL extension to standard SQL, which allows only =
one of each clause per ALTER TABLE statement. For example, to drop =
multiple columns in a single statement: mysql> ALTER TABLE t2 DROP =
COLUMN c, DROP COLUMN d;
d.. CHANGE col_name, DROP col_name, and DROP INDEX are MySQL extensions =
to standard SQL. e.. MODIFY is an Oracle extension to ALTER TABLE. f.. =
The word COLUMN is purely optional and can be omitted. g.. If you use =
ALTER TABLE tbl_name RENAME TO new_tbl_name without any other options, =
MySQL simply renames any files that correspond to the table tbl_name. =
There is no need to create a temporary table. (You can also use the =
RENAME TABLE statement to rename tables. See Section 13.2.9, =
â=9CRENAME TABLE Syntaxâ=9D.) h.. column_definition clauses =
use the same syntax for ADD and CHANGE as for CREATE TABLE. Note that =
this syntax includes the column name, not just the column type. See =
Section 13.2.5, â=9CCREATE TABLE Syntaxâ=9D. i.. You can =
rename a column using a CHANGE old_col_name column_definition clause. To =
do so, specify the old and new column names and the type that the column =
currently has. For example, to rename an INTEGER column from a to b, you =
can do this: mysql> ALTER TABLE t1 CHANGE a b INTEGER;
If you want to change a column's type but not the name, CHANGE syntax =
still requires an old and new column name, even if they are the same. =
For example: mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
However, as of MySQL 3.22.16a, you can also use MODIFY to change a =
column's type without renaming it: mysql> ALTER TABLE t1 MODIFY b BIGINT =
NOT NULL;
j.. If you use CHANGE or MODIFY to shorten a column for which an index =
exists on the column, and the resulting column length is less than the =
index length, MySQL shortens the index automatically. k.. When you =
change a column type using CHANGE or MODIFY, MySQL tries to convert =
existing column values to the new type as well as possible. l.. In MySQL =
3.22 or later, you can use FIRST or AFTER col_name to add a column at a =
specific position within a table row. The default is to add the column =
last. From MySQL 4.0.1 on, you can also use FIRST and AFTER in CHANGE or =
MODIFY operations. m.. ALTER COLUMN specifies a new default value for a =
column or removes the old default value. If the old default is removed =
and the column can be NULL, the new default is NULL. If the column =
cannot be NULL, MySQL assigns a default value, as described in Section =
13.2.5, â=9CCREATE TABLE Syntaxâ=9D. n.. DROP INDEX removes an =
index. This is a MySQL extension to standard SQL. See Section 13.2.7, =
â=9CDROP INDEX Syntaxâ=9D. o.. If columns are dropped from a =
table, the columns are also removed from any index of which they are a =
part. If all columns that make up an index are dropped, the index is =
dropped as well. p.. If a table contains only one column, the column =
cannot be dropped. If what you intend is to remove the table, use DROP =
TABLE instead. q.. DROP PRIMARY KEY drops the primary index. (Prior to =
MySQL 4.1.2, if no primary index exists, DROP PRIMARY KEY drops the =
first UNIQUE index in the table. MySQL marks the first UNIQUE key as the =
PRIMARY KEY if no PRIMARY KEY was specified explicitly.) If you add a =
UNIQUE INDEX or PRIMARY KEY to a table, it is stored before any =
non-unique index so that MySQL can detect duplicate keys as early as =
possible. r.. ORDER BY allows you to create the new table with the rows =
in a specific order. Note that the table does not remain in this order =
after inserts and deletes. This option is mainly useful when you know =
that you are mostly going to query the rows in a certain order; by using =
this option after big changes to the table, you might be able to get =
higher performance. In some cases, it might make sorting easier for =
MySQL if the table is in order by the column that you want to order it =
by later. s.. If you use ALTER TABLE on a MyISAM table, all non-unique =
indexes are created in a separate batch (as for REPAIR TABLE). This =
should make ALTER TABLE much faster when you have many indexes. As of =
MySQL 4.0, this feature can be activated explicitly. ALTER TABLE ... =
DISABLE KEYS tells MySQL to stop updating non-unique indexes for a =
MyISAM table. ALTER TABLE ... ENABLE KEYS then should be used to =
re-create missing indexes. MySQL does this with a special algorithm that =
is much faster than inserting keys one by one, so disabling keys before =
performing bulk insert operations should give a considerable speedup. =
Using ALTER TABLE ... DISABLE KEYS requires the INDEX privilege in =
addition to the privileges mentioned earlier. t.. The FOREIGN KEY and =
REFERENCES clauses are supported by the InnoDB storage engine, which =
implements ADD [CONSTRAINT [symbol]] FOREIGN KEY (...) REFERENCES ... =
(...). See Section 15.7.4, â=9CFOREIGN KEY Constraintsâ=9D. =
For other storage engines, the clauses are parsed but ignored. The CHECK =
clause is parsed but ignored by all storage engines. See Section 13.2.5, =
â=9CCREATE TABLE Syntaxâ=9D. The reason for accepting but =
ignoring syntax clauses is for compatibility, to make it easier to port =
code from other SQL servers, and to run applications that create tables =
with references. See Section 1.7.5, â=9CMySQL Differences from =
Standard SQLâ=9D. u.. Starting from MySQL 4.0.13, InnoDB supports =
the use of ALTER TABLE to drop foreign keys: ALTER TABLE yourtablename =
DROP FOREIGN KEY fk_symbol;
For more information, see Section 15.7.4, â=9CFOREIGN KEY =
Constraintsâ=9D. v.. ALTER TABLE ignores the DATA DIRECTORY and =
INDEX DIRECTORY table options. w.. From MySQL 4.1.2 on, if you want to =
change the table default character set and all character columns (CHAR, =
VARCHAR, TEXT) to a new character set, use a statement like this: ALTER =
TABLE tbl_name CONVERT TO CHARACTER SET charset_name;
This is useful, for example, after upgrading from MySQL 4.0.x to 4.1.x. =
See Section 10.10, â=9CUpgrading Character Sets from MySQL =
4.0â=9D. Warning: The preceding operation converts column values =
between the character sets. This is not what you want if you have a =
column in one character set (like latin1) but the stored values actually =
use some other, incompatible character set (like utf8). In this case, =
you have to do the following for each such column: ALTER TABLE t1 CHANGE =
c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;
The reason this works is that there is no conversion when you convert to =
or from BLOB columns. If you specify CONVERT TO CHARACTER SET binary, =
the CHAR, VARCHAR, and TEXT columns are converted to their corresponding =
binary string types (BINARY, VARBINARY, BLOB). This means that the =
columns no longer will have a character set and a subsequent CONVERT TO =
operation will not apply to them. To change only the default character =
set for a table, use this statement: ALTER TABLE tbl_name DEFAULT =
CHARACTER SET charset_name;
The word DEFAULT is optional. The default character set is the character =
set that is used if you don't specify the character set for a new column =
you add to a table (for example, with ALTER TABLE ... ADD column). =
Warning: From MySQL 4.1.2 and up, ALTER TABLE ... DEFAULT CHARACTER SET =
and ALTER TABLE ... CHARACTER SET are equivalent and change only the =
default table character set. In MySQL 4.1 releases before 4.1.2, ALTER =
TABLE ... DEFAULT CHARACTER SET changes the default character set, but =
ALTER TABLE ... CHARACTER SET (without DEFAULT) changes the default =
character set and also converts all columns to the new character set. =
x.. For an InnoDB table that is created with its own tablespace in an =
..ibd file, that file can be discarded and imported. To discard the .ibd =
file, use this statement: ALTER TABLE tbl_name DISCARD TABLESPACE;
This deletes the current .ibd file, so be sure that you have a backup =
first. Attempting to access the table while the tablespace file is =
discarded results in an error. To import the backup .ibd file back into =
the table, copy it into the database directory, then issue this =
statement: ALTER TABLE tbl_name IMPORT TABLESPACE;
See Section 15.7.6, â=9CUsing Per-Table Tablespacesâ=9D. y.. =
With the mysql_info() C API function, you can find out how many records =
were copied, and (when IGNORE is used) how many records were deleted due =
to duplication of unique key values. See Section 24.2.3.31, =
â=9Cmysql_info()â=9D. Here are some examples that show uses of =
ALTER TABLE. Begin with a table t1 that is created as shown here: mysql> =
CREATE TABLE t1 (a INTEGER,b CHAR(10));
To rename the table from t1 to t2: mysql> ALTER TABLE t1 RENAME t2;
To change column a from INTEGER to TINYINT NOT NULL (leaving the name =
the same), and to change column b from CHAR(10) to CHAR(20) as well as =
renaming it from b to c: mysql> ALTER TABLE t2 MODIFY a TINYINT NOT =
NULL, CHANGE b c CHAR(20);
To add a new TIMESTAMP column named d: mysql> ALTER TABLE t2 ADD d =
TIMESTAMP;
To add indexes on column d and on column a: mysql> ALTER TABLE t2 ADD =
INDEX (d), ADD INDEX (a);
To remove column c: mysql> ALTER TABLE t2 DROP COLUMN c;
To add a new AUTO_INCREMENT integer column named c: mysql> ALTER TABLE =
t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
-> ADD PRIMARY KEY (c);
Note that we indexed c (as a PRIMARY KEY), because AUTO_INCREMENT =
columns must be indexed, and also that we declare c as NOT NULL, because =
primary key columns cannot be NULL. When you add an AUTO_INCREMENT =
column, column values are filled in with sequence numbers for you =
automatically. For MyISAM tables, you can set the first sequence number =
by executing SET INSERT_ID=3Dvalue before ALTER TABLE or by using the =
AUTO_INCREMENT=3Dvalue table option. See Section 13.5.3, â=9CSET =
Syntaxâ=9D. From MySQL 5.0.3, you can use the ALTER TABLE ... =
AUTO_INCREMENT=3Dvalue table option for InnoDB tables to set the =
sequence number for new rows if the value is greater than the maximum =
value in the AUTO_INCREMENT column. If the value is less than the =
maximum column value, no error message is given and the current sequence =
value is not changed. With MyISAM tables, if you don't change the =
AUTO_INCREMENT column, the sequence number is not affected. If you drop =
an AUTO_INCREMENT column and then add another AUTO_INCREMENT column, the =
numbers are resequenced beginning with 1. See Section A.7.1, =
â=9CProblems with ALTER TABLEâ=9D. "Piotr" =
wrote in message news:dntq89$195$1@nemesis.news.tpi.pl...
> NC napisaÅ(a):
>=20
>> You can't. You need to dump the local table, drop the remote table,
>> and recreate it with the dump of the local table. =20
>=20
> Iou don't understood me. I can't drop remote table because I have=20
> important data there. I want to change structure only!
------=_NextPart_000_0137_01C616A5.155A9270
Content-Type: text/html;
charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

ï»=BF

you can always do an ALTER TABLE =
statement on the=20
remote structure. be careful what you are doing though. like the =
other=20
post said, adding columns is fine, but dropping columns doesn't seem to =
sound=20
like what you want.

here's the manual:

class=3Dprogramlisting>ALTER [IGNORE] TABLE 
class=3Dreplaceable>tbl_name

    alter_specification [, 
class=3Dreplaceable>alter_specification] ...



alter_specification:

    ADD [COLUMN] 
class=3Dreplaceable>column_definition [FIRST | AFTER =

col_name ]

  | ADD [COLUMN] (
class=3Dreplaceable>column_definition,...)

  | ADD INDEX [index_name] =

[index_type] (
class=3Dreplaceable>index_col_name,...)

  | ADD [CONSTRAINT [symbol]]

        PRIMARY KEY [
class=3Dreplaceable>index_type] (
class=3Dreplaceable>index_col_name,...)

  | ADD [CONSTRAINT [symbol]]

        UNIQUE [index_name] =

[index_type] (
class=3Dreplaceable>index_col_name,...)

  | ADD [FULLTEXT|SPATIAL] [
class=3Dreplaceable>index_name] (
class=3Dreplaceable>index_col_name,...)

  | ADD [CONSTRAINT [symbol]]

        FOREIGN KEY [
class=3Dreplaceable>index_name] (
class=3Dreplaceable>index_col_name,...)

        [reference_definition]

  | ALTER [COLUMN] col_name =

{SET DEFAULT literal | DROP =

DEFAULT}

  | CHANGE [COLUMN] 
class=3Dreplaceable>old_col_name 
class=3Dreplaceable>column_definition

        [FIRST|AFTER col_name]

  | MODIFY [COLUMN] 
class=3Dreplaceable>column_definition [FIRST | AFTER =

col_name]

  | DROP [COLUMN] col_name

  | DROP PRIMARY KEY

  | DROP INDEX index_name

  | DROP FOREIGN KEY fk_symbol

  | DISABLE KEYS

  | ENABLE KEYS

  | RENAME [TO] new_tbl_name

  | ORDER BY col_name

  | CONVERT TO CHARACTER SET 
class=3Dreplaceable>charset_name [COLLATE 
class=3Dreplaceable>collation_name]

  | [DEFAULT] CHARACTER SET 
class=3Dreplaceable>charset_name [COLLATE 
class=3Dreplaceable>collation_name]

  | DISCARD TABLESPACE

  | IMPORT TABLESPACE

  | table_options

ALTER TABLE =

allows you to change the structure of an existing table. For example, =

you can add or delete columns, create or destroy indexes, change the =

type of existing columns, or rename columns or the table itself. You can =

also change the comment for the table and type of the table. =

The syntax for many of the allowable =

alterations is similar to clauses of the CREATE =

TABLE statement. This includes 
class=3Dreplaceable>table_options modifications, for =

options such as ENGINE, 
class=3Dliteral>AUTO_INCREMENT, and 
class=3Dliteral>AVG_ROW_LENGTH. See 
title=3D"13.2.5. CREATE TABLE Syntax" =

 href=3D"mk:@MSITStore:C:\Program%20Files\MySQL\MySQL%20Serve r%204.1\Docs\=

manual.chm::/ch13s02.html#create-table">
size=3D3>Section 13.2.5, â=9CCREATE =

TABLE Syntaxâ=9D. =

Some operations may result in warnings if =

attempted on a table for which the storage engine does not support the =

operation. In MySQL 4.1 and up, these warnings can be displayed with =

SHOW WARNINGS. See 
title=3D"13.5.4.20. SHOW WARNINGS Syntax" =

 href=3D"mk:@MSITStore:C:\Program%20Files\MySQL\MySQL%20Serve r%204.1\Docs\=

manual.chm::/ch13s05.html#show-warnings">
size=3D3>Section 13.5.4.20, â=9CSHOW =

WARNINGS Syntaxâ=9D. =

If you use ALTER =

TABLE to change a column specification but 
class=3Dliteral>DESCRIBE 
class=3Dreplaceable>tbl_name indicates that =

your column was not changed, it is possible that MySQL ignored your =

modification for one of the reasons described in 
title=3D"13.2.5.1. Silent Column Specification Changes" =

 href=3D"mk:@MSITStore:C:\Program%20Files\MySQL\MySQL%20Serve r%204.1\Docs\=

manual.chm::/ch13s02.html#silent-column-changes">
size=3D3>Section 13.2.5.1, â=9CSilent Column Specification =

Changesâ=9D. For example, if you try to =

change a VARCHAR column to 
class=3Dliteral>CHAR, MySQL still uses 
class=3Dliteral>VARCHAR if the table contains other =

variable-length columns. 

class=3Dliteral>ALTER TABLE works by making a temporary copy of =

the original table. The alteration is performed on the copy, then the =

original table is deleted and the new one is renamed. While 
class=3Dliteral>ALTER TABLE is executing, the original table is =

readable by other clients. Updates and writes to the table are stalled =

until the new table is ready, then are automatically redirected to the =

new table without any failed updates. 
Note =

that if you use any other option to ALTER =

TABLE than RENAME, MySQL always =

creates a temporary table, even if the data wouldn't strictly need to be =

copied (such as when you change the name of a column). We plan to fix =

this in the future, but because ALTER TABLE =

is not a statement that is normally used frequently, this isn't high on =

our TODO list. For MyISAM tables, you can =

speed up the index re-creation operation (which is the slowest part of =

the alteration process) by setting the 
class=3Dliteral>myisam_sort_buffer_size system variable to a high =

value. 

size=3D3>To use ALTER TABLE, you need 
class=3Dliteral>ALTER, INSERT, and =

CREATE privileges for the table. =

IGNORE is =

a MySQL extension to standard SQL. It controls how 
class=3Dliteral>ALTER TABLE works if there are duplicates on =

unique keys in the new table or if warnings occur when 
class=3Dliteral>STRICT mode is enabled. If 
class=3Dliteral>IGNORE isn't specified, the copy is aborted and =

rolled back if duplicate-key errors occur. If 
class=3Dliteral>IGNORE is specified, then for rows with =

duplicates on a unique key, only the first row is used. The others =

conflicting rows are deleted. Wrong values are truncated to the closest =

matching acceptable value. 
You can =

issue multiple ADD, 
class=3Dliteral>ALTER, DROP, and =

CHANGE clauses in a single 
class=3Dliteral>ALTER TABLE statement. This is a MySQL extension =

to standard SQL, which allows only one of each clause per 
class=3Dliteral>ALTER TABLE statement. For example, to drop =

multiple columns in a single statement: 
class=3Dprogramlisting>mysql> ALTER TABLE t2 DROP =

COLUMN c, DROP COLUMN d;

CHANGE 
class=3Dreplaceable>col_name, 
class=3Dliteral>DROP 
class=3Dreplaceable>col_name, and 
class=3Dliteral>DROP INDEX are MySQL extensions to standard SQL. =

MODIFY is =

an Oracle extension to ALTER TABLE. =

The word 
class=3Dliteral>COLUMN is purely optional and can be omitted. =

If you use ALTER =

TABLE tbl_name RENAME TO 
class=3Dreplaceable>new_tbl_name without any =

other options, MySQL simply renames any files that correspond to the =

table tbl_name. There is no =

need to create a temporary table. (You can also use the 
class=3Dliteral>RENAME TABLE statement to rename tables. See =


 href=3D"mk:@MSITStore:C:\Program%20Files\MySQL\MySQL%20Serve r%204.1\Docs\=

manual.chm::/ch13s02.html#rename-table">
size=3D3>Section 13.2.9, â=9CRENAME =

TABLE Syntaxâ=9D.) =


class=3Dreplaceable>column_definition clauses use the =

same syntax for ADD and 
class=3Dliteral>CHANGE as for CREATE =

TABLE. Note that this syntax includes the column name, not just =

the column type. See 
Syntax" =

 href=3D"mk:@MSITStore:C:\Program%20Files\MySQL\MySQL%20Serve r%204.1\Docs\=

manual.chm::/ch13s02.html#create-table">
size=3D3>Section 13.2.5, â=9CCREATE =

TABLE Syntaxâ=9D. =

You can rename a column using a 
class=3Dliteral>CHANGE 
class=3Dreplaceable>old_col_name 
class=3Dreplaceable>column_definition clause. =

To do so, specify the old and new column names and the type that the =

column currently has. For example, to rename an 
class=3Dliteral>INTEGER column from 
class=3Dliteral>a to b, you can do =

this: 
mysql> =

ALTER TABLE t1 CHANGE a b INTEGER;

If you want to change a column's type but =

not the name, CHANGE syntax still requires =

an old and new column name, even if they are the same. For example: =

mysql> ALTER =

TABLE t1 CHANGE b b BIGINT NOT NULL;

However, as of MySQL 3.22.16a, you can =

also use MODIFY to change a column's type =

without renaming it: 

size=3D3>mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;

If you use 
class=3Dliteral>CHANGE or MODIFY to =

shorten a column for which an index exists on the column, and the =

resulting column length is less than the index length, MySQL shortens =

the index automatically. 
When you =

change a column type using CHANGE or 
class=3Dliteral>MODIFY, MySQL tries to convert existing column =

values to the new type as well as possible. 

size=3D3>In MySQL 3.22 or later, you can use 
class=3Dliteral>FIRST or AFTER 
class=3Dreplaceable>col_name to add a column at =

a specific position within a table row. The default is to add the column =

last. From MySQL 4.0.1 on, you can also use 
class=3Dliteral>FIRST and AFTER in =

CHANGE or 
class=3Dliteral>MODIFY operations. 

size=3D3>ALTER COLUMN specifies a new =

default value for a column or removes the old default value. If the old =

default is removed and the column can be 
class=3Dliteral>NULL, the new default is 
class=3Dliteral>NULL. If the column cannot be 
class=3Dliteral>NULL, MySQL assigns a default value, as described =

in 
 href=3D"mk:@MSITStore:C:\Program%20Files\MySQL\MySQL%20Serve r%204.1\Docs\=

manual.chm::/ch13s02.html#create-table">
size=3D3>Section 13.2.5, â=9CCREATE =

TABLE Syntaxâ=9D. =

DROP INDEX =

removes an index. This is a MySQL extension to standard SQL. See =


 href=3D"mk:@MSITStore:C:\Program%20Files\MySQL\MySQL%20Serve r%204.1\Docs\=

manual.chm::/ch13s02.html#drop-index">
size=3D3>Section 13.2.7, â=9CDROP =

INDEX Syntaxâ=9D. =

If columns are dropped from a table, =

the columns are also removed from any index of which they are a part. If =

all columns that make up an index are dropped, the index is dropped as =

well. 
If a table contains only one =

column, the column cannot be dropped. If what you intend is to remove =

the table, use DROP TABLE instead. =

DROP PRIMARY =

KEY drops the primary index. (Prior to MySQL 4.1.2, if no primary =

index exists, DROP PRIMARY KEY drops the =

first UNIQUE index in the table. MySQL =

marks the first UNIQUE key as the 
class=3Dliteral>PRIMARY KEY if no PRIMARY =

KEY was specified explicitly.) 
If =

you add a UNIQUE INDEX or 
class=3Dliteral>PRIMARY KEY to a table, it is stored before any =

non-unique index so that MySQL can detect duplicate keys as early as =

possible. 
ORDER =

BY allows you to create the new table with the rows in a specific =

order. Note that the table does not remain in this order after inserts =

and deletes. This option is mainly useful when you know that you are =

mostly going to query the rows in a certain order; by using this option =

after big changes to the table, you might be able to get higher =

performance. In some cases, it might make sorting easier for MySQL if =

the table is in order by the column that you want to order it by later. =

If you use ALTER =

TABLE on a MyISAM table, all =

non-unique indexes are created in a separate batch (as for 
class=3Dliteral>REPAIR TABLE). This should make 
class=3Dliteral>ALTER TABLE much faster when you have many =

indexes. 
As of MySQL 4.0, this feature can =

be activated explicitly. ALTER TABLE ... DISABLE =

KEYS tells MySQL to stop updating non-unique indexes for a 
class=3Dliteral>MyISAM table. ALTER TABLE =

.... ENABLE KEYS then should be used to re-create missing indexes. =

MySQL does this with a special algorithm that is much faster than =

inserting keys one by one, so disabling keys before performing bulk =

insert operations should give a considerable speedup. Using 
class=3Dliteral>ALTER TABLE ... DISABLE KEYS requires the 
class=3Dliteral>INDEX privilege in addition to the privileges =

mentioned earlier. 
The 
class=3Dliteral>FOREIGN KEY and 
class=3Dliteral>REFERENCES clauses are supported by the 
class=3Dliteral>InnoDB storage engine, which implements 
class=3Dliteral>ADD [CONSTRAINT [
class=3Dreplaceable>symbol]] FOREIGN KEY (...) =

REFERENCES ... (...). See 
KEY Constraints" =

 href=3D"mk:@MSITStore:C:\Program%20Files\MySQL\MySQL%20Serve r%204.1\Docs\=

manual.chm::/ch15s07.html#innodb-foreign-key-constraints">
size=3D3>Section 15.7.4, â=9CFOREIGN =

KEY Constraintsâ=9D. For other =

storage engines, the clauses are parsed but ignored. The 
class=3Dliteral>CHECK clause is parsed but ignored by all storage =

engines. See 
 href=3D"mk:@MSITStore:C:\Program%20Files\MySQL\MySQL%20Serve r%204.1\Docs\=

manual.chm::/ch13s02.html#create-table">
size=3D3>Section 13.2.5, â=9CCREATE =

TABLE Syntaxâ=9D. The reason for =

accepting but ignoring syntax clauses is for compatibility, to make it =

easier to port code from other SQL servers, and to run applications that =

create tables with references. See 
Differences from Standard SQL" =

 href=3D"mk:@MSITStore:C:\Program%20Files\MySQL\MySQL%20Serve r%204.1\Docs\=

manual.chm::/ch01s07.html#differences-from-ansi">
size=3D3>Section 1.7.5, â=9CMySQL Differences from Standard =

SQLâ=9D. 

size=3D3>Starting from MySQL 4.0.13, InnoDB =

supports the use of ALTER TABLE to drop =

foreign keys: 

size=3D3>ALTER TABLE 
class=3Dreplaceable>yourtablename DROP FOREIGN KEY 
class=3Dreplaceable>fk_symbol;

For more information, see 
title=3D"15.7.4. FOREIGN KEY Constraints" =

 href=3D"mk:@MSITStore:C:\Program%20Files\MySQL\MySQL%20Serve r%204.1\Docs\=

manual.chm::/ch15s07.html#innodb-foreign-key-constraints">
size=3D3>Section 15.7.4, â=9CFOREIGN =

KEY Constraintsâ=9D. =

ALTER =

TABLE ignores the DATA DIRECTORY and =

INDEX DIRECTORY table options. =

From MySQL 4.1.2 on, if you want to =

change the table default character set and all character columns (
class=3Dliteral>CHAR, VARCHAR, 
class=3Dliteral>TEXT) to a new character set, use a statement =

like this: 
ALTER =

TABLE tbl_name CONVERT TO =

CHARACTER SET charset_name;

This is useful, for example, after =

upgrading from MySQL 4.0.x to 4.1.x. See 
title=3D"10.10. Upgrading Character Sets from MySQL 4.0" =

 href=3D"mk:@MSITStore:C:\Program%20Files\MySQL\MySQL%20Serve r%204.1\Docs\=

manual.chm::/ch10s10.html">Section 10.10, =

â=9CUpgrading Character Sets from MySQL =

4.0â=9D. 

size=3D3>Warning: The =

preceding operation converts column values between the character sets. =

This is not what you want if you =

have a column in one character set (like 
class=3Dliteral>latin1) but the stored values actually use some =

other, incompatible character set (like 
class=3Dliteral>utf8). In this case, you have to do the following =

for each such column: 

size=3D3>ALTER TABLE t1 CHANGE c1 c1 BLOB;

ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;

The reason this works is that there is no =

conversion when you convert to or from BLOB =

columns. 
If you specify 
class=3Dliteral>CONVERT TO CHARACTER SET binary, the 
class=3Dliteral>CHAR, VARCHAR, and =

TEXT columns are converted to their =

corresponding binary string types (BINARY, =

VARBINARY, 
class=3Dliteral>BLOB). This means that the columns no longer will =

have a character set and a subsequent CONVERT =

TO operation will not apply to them. 

size=3D3>To change only the 
class=3Demphasis>default character set for a table, use =

this statement: 

size=3D3>ALTER TABLE tbl_name =

DEFAULT CHARACTER SET 
class=3Dreplaceable>charset_name;

The word 
class=3Dliteral>DEFAULT is optional. The default character set is =

the character set that is used if you don't specify the character set =

for a new column you add to a table (for example, with 
class=3Dliteral>ALTER TABLE ... ADD column). 

size=3D3>Warning: From MySQL =

4.1.2 and up, ALTER TABLE ... DEFAULT CHARACTER =

SET and ALTER TABLE ... CHARACTER =

SET are equivalent and change only the default table character =

set. In MySQL 4.1 releases before 4.1.2, ALTER =

TABLE ... DEFAULT CHARACTER SET changes the default character =

set, but ALTER TABLE ... CHARACTER SET =

(without DEFAULT) changes the default =

character set and also converts =

all columns to the new character set. 

size=3D3>For an InnoDB table that is =

created with its own tablespace in an .ibd =

file, that file can be discarded and imported. To discard the 
class=3Dfilename>.ibd file, use this statement: 
class=3Dprogramlisting>ALTER TABLE 
class=3Dreplaceable>tbl_name DISCARD TABLESPACE;

This deletes the current 
class=3Dfilename>.ibd file, so be sure that you have a backup =

first. Attempting to access the table while the tablespace file is =

discarded results in an error. 
To import =

the backup .ibd file back into the table, =

copy it into the database directory, then issue this statement: =

ALTER TABLE 
class=3Dreplaceable>tbl_name IMPORT TABLESPACE;

See 
Per-Table Tablespaces" =

 href=3D"mk:@MSITStore:C:\Program%20Files\MySQL\MySQL%20Serve r%204.1\Docs\=

manual.chm::/ch15s07.html#multiple-tablespaces">
size=3D3>Section 15.7.6, â=9CUsing Per-Table =

Tablespacesâ=9D. 

size=3D3>With the mysql_info() C API =

function, you can find out how many records were copied, and (when 
class=3Dliteral>IGNORE is used) how many records were deleted due =

to duplication of unique key values. See 
title=3D24.2.3.31. mysql_info() =

 href=3D"mk:@MSITStore:C:\Program%20Files\MySQL\MySQL%20Serve r%204.1\Docs\=

manual.chm::/ch24s02.html#mysql-info">
size=3D3>Section 24.2.3.31, â=9C
class=3Dliteral>mysql_info()â=9D. =

Here are some examples that =

show uses of ALTER TABLE. Begin with a =

table t1 that is created as shown here: =

mysql> CREATE =

TABLE t1 (a INTEGER,b CHAR(10));

To rename the table from 
class=3Dliteral>t1 to t2: =

mysql> ALTER =

TABLE t1 RENAME t2;

To change column 
class=3Dliteral>a from INTEGER to =

TINYINT NOT NULL (leaving the name the =

same), and to change column b from 
class=3Dliteral>CHAR(10) to CHAR(20) =

as well as renaming it from b to 
class=3Dliteral>c: 

size=3D3>mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c =

CHAR(20);

To add a new 
class=3Dliteral>TIMESTAMP column named 
class=3Dliteral>d: 

size=3D3>mysql> ALTER TABLE t2 ADD d TIMESTAMP;

To add indexes on column 
class=3Dliteral>d and on column a: =

mysql> ALTER =

TABLE t2 ADD INDEX (d), ADD INDEX (a);

To remove column 
class=3Dliteral>c: 

size=3D3>mysql> ALTER TABLE t2 DROP COLUMN c;

To add a new 
class=3Dliteral>AUTO_INCREMENT integer column named 
class=3Dliteral>c: 

size=3D3>mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL =

AUTO_INCREMENT,

    ->     ADD PRIMARY KEY (c);

Note that we indexed 
class=3Dliteral>c (as a PRIMARY =

KEY), because AUTO_INCREMENT columns =

must be indexed, and also that we declare c =

as NOT NULL, because primary key columns =

cannot be NULL. 

size=3D3>When you add an AUTO_INCREMENT =

column, column values are filled in with sequence numbers for you =

automatically. For MyISAM tables, you can =

set the first sequence number by executing SET =

INSERT_ID=3Dvalue =

before ALTER TABLE or by using the 
class=3Dliteral>AUTO_INCREMENT=3D
class=3Dreplaceable>value table option. See =


 href=3D"mk:@MSITStore:C:\Program%20Files\MySQL\MySQL%20Serve r%204.1\Docs\=

manual.chm::/ch13s05.html#set-option">
size=3D3>Section 13.5.3, â=9CSET =

Syntaxâ=9D. 

size=3D3>From MySQL 5.0.3, you can use the ALTER =

TABLE ... AUTO_INCREMENT=3D
class=3Dreplaceable>value table option for =

InnoDB tables to set the sequence number =

for new rows if the value is greater than the maximum value in the 
class=3Dliteral>AUTO_INCREMENT column. If the value is less than =

the maximum column value, no error message is given and the current =

sequence value is not changed. 
With 
class=3Dliteral>MyISAM tables, if you don't change the 
class=3Dliteral>AUTO_INCREMENT column, the sequence number is not =

affected. If you drop an AUTO_INCREMENT =

column and then add another AUTO_INCREMENT =

column, the numbers are resequenced beginning with 1. =

See 
with ALTER TABLE" =

 href=3D"mk:@MSITStore:C:\Program%20Files\MySQL\MySQL%20Serve r%204.1\Docs\=

manual.chm::/apas07.html#alter-table-problems">
size=3D3>Section A.7.1, â=9CProblems with 
class=3Dliteral>ALTER TABLEâ=9D
size=3D3>. 
size=3D2>

"Piotr" < href=3D"mailto:piotr@bez.maila"> size=3D2>piotr@bez.maila> =
wrote in message=20
face=3DArial=20
size=3D2>news:dntq89$195$1@nemesis.news.tpi.pl face=3DArial=20
size=3D2>...

> NC =
napisaÅ(a):
>=20

>> You can't. You need to dump the local table, drop the =
remote=20
table,
>> and recreate it with the dump of the local =
table. =20

>
> Iou don't understood me. I can't drop remote table =
because I=20
have
> important data there. I want to change structure=20
only!

------=_NextPart_000_0137_01C616A5.155A9270--