ALTER [IGNORE] TABLEtbl_name
alter_specification
[,alter_specification
] ...alter_specification
:table_options
| ADD [COLUMN]col_name
column_definition
[FIRST | AFTERcol_name
] | ADD [COLUMN] (col_name
column_definition
,...) | ADD {INDEX|KEY} [index_name
] [index_type
] (index_col_name
,...) | ADD [CONSTRAINT [symbol
]] PRIMARY KEY [index_type
] (index_col_name
,...) | ADD [CONSTRAINT [symbol
]] UNIQUE [INDEX|KEY] [index_name
] [index_type
] (index_col_name
,...) | ADD [FULLTEXT|SPATIAL] [INDEX|KEY] [index_name
] (index_col_name
,...) | ADD [CONSTRAINT [symbol
]] FOREIGN KEY [index_name
] (index_col_name
,...)reference_definition
| ALTER [COLUMN]col_name
{SET DEFAULTliteral
| DROP DEFAULT} | CHANGE [COLUMN]old_col_name
new_col_name
column_definition
[FIRST|AFTERcol_name
] | MODIFY [COLUMN]col_name
column_definition
[FIRST | AFTERcol_name
] | DROP [COLUMN]col_name
| DROP PRIMARY KEY | DROP {INDEX|KEY}index_name
| DROP FOREIGN KEYfk_symbol
| DISABLE KEYS | ENABLE KEYS | RENAME [TO]new_tbl_name
| ORDER BYcol_name
[,col_name
] ... | CONVERT TO CHARACTER SETcharset_name
[COLLATEcollation_name
] | [DEFAULT] CHARACTER SET [=]charset_name
[COLLATE [=]collation_name
] | DISCARD TABLESPACE | IMPORT TABLESPACEindex_col_name
:col_name
[(length
)] [ASC | DESC]index_type
: USING {BTREE | HASH | RTREE}table_options
:table_option
[[,]table_option
] ... (seeCREATE TABLE
options)
ALTER TABLE
enables 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. See Section 12.1.5, “CREATE TABLE
Syntax”, for more
information.
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 12.4.5.26, “SHOW WARNINGS
Syntax”.
If you use ALTER TABLE
to change a
column specification but DESCRIBE
indicates that your
column was not changed, it is possible that MySQL ignored your
modification for one of the reasons described in
Section 12.1.5.1, “Silent Column Specification Changes”. For example, if you try
to change a tbl_name
VARCHAR
column to
CHAR
, MySQL still uses
VARCHAR
if the table contains other
variable-length columns.
In most cases, ALTER TABLE
works by
making a temporary copy of the original table. The alteration is
performed on the copy, and then the original table is deleted and
the new one is renamed. While ALTER
TABLE
is executing, the original table is readable by
other sessions. Updates and writes to the table are stalled until
the new table is ready, and then are automatically redirected to
the new table without any failed updates. The temporary table is
created in the database directory of the new table. This can be
different from the database directory of the original table if
ALTER TABLE
is renaming the table
to a different database.
If you use ALTER TABLE
without any
other options, MySQL simply renames any files that correspond to
the table tbl_name
RENAME TO
new_tbl_name
tbl_name
. (You can also use
the RENAME TABLE
statement to
rename tables. See Section 12.1.9, “RENAME TABLE
Syntax”.) Any privileges
granted specifically for the renamed table are not migrated to the
new name. They must be changed manually.
If you use any option to ALTER
TABLE
other 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). 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.
For information on troubleshooting ALTER
TABLE
, see Section A.5.7.1, “Problems with ALTER TABLE
”.
To use ALTER TABLE
, you need
ALTER
,
INSERT
, and
CREATE
privileges for the
table.
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
is not specified, the
copy is aborted and rolled back if duplicate-key errors occur.
If IGNORE
is specified, only the first row
is used of rows with duplicates on a unique key, The other
conflicting rows are deleted. Incorrect values are truncated
to the closest matching acceptable value.
table_option
signifies a table
option of the kind that can be used in the
CREATE TABLE
statement, such as
ENGINE
, AUTO_INCREMENT
,
or AVG_ROW_LENGTH
.
(Section 12.1.5, “CREATE TABLE
Syntax”, lists all table options.)
However, ALTER TABLE
ignores
the DATA DIRECTORY
and INDEX
DIRECTORY
table options.
For example, to convert a table to be an
InnoDB
table, use this statement:
ALTER TABLE t1 ENGINE = InnoDB;
To change the value of the AUTO_INCREMENT
counter to be used for new rows, do this:
ALTER TABLE t2 AUTO_INCREMENT = value
;
You cannot reset the counter to a value less than or equal to
any that have already been used. For
MyISAM
, if the value is less than or equal
to the maximum value currently in the
AUTO_INCREMENT
column, the value is reset
to the current maximum plus one. For
InnoDB
, you can use ALTER TABLE
... AUTO_INCREMENT =
as of MySQL 4.1.12,
but if the value is less than the current maximum
value in the column, no error occurs and the current sequence
value is not changed.
value
You can issue multiple ADD
,
ALTER
, DROP
, and
CHANGE
clauses in a single
ALTER TABLE
statement,
separated by commas. 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, do
this:
ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;
CHANGE
,
col_name
DROP
,
and col_name
DROP INDEX
are MySQL
extensions to standard SQL.
MODIFY
is an Oracle extension to
ALTER TABLE
.
The word COLUMN
is optional and can be
omitted.
column_definition
clauses use the
same syntax for ADD
and
CHANGE
as for CREATE
TABLE
. See Section 12.1.5, “CREATE TABLE
Syntax”.
You can rename a column using a CHANGE
clause.
To do so, specify the old and new column names and the
definition that the column currently has. For example, to
rename an old_col_name
new_col_name
column_definition
INTEGER
column from
a
to b
, you can do this:
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:
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:
ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
When you use CHANGE
or
MODIFY
,
column_definition
must include the
data type and all attributes that should apply to the new
column, other than index attributes such as PRIMARY
KEY
or UNIQUE
. Attributes present
in the original definition but not specified for the new
definition are not carried forward. Suppose that a column
col1
is defined as INT UNSIGNED
DEFAULT 1 COMMENT 'my column'
and you modify the
column as follows:
ALTER TABLE t1 MODIFY col1 BIGINT;
The resulting column will be defined as
BIGINT
, but will not include the attributes
UNSIGNED DEFAULT 1 COMMENT 'my column'
. To
retain them, the statement should be:
ALTER TABLE t1 MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT 'my column';
When you change a data type using CHANGE
or
MODIFY
, MySQL tries to convert existing
column values to the new type as well as possible.
This conversion may result in alteration of data. For example, if you shorten a string column, values may be truncated.
In MySQL 3.22 or later, to add a column at a specific position
within a table row, use FIRST
or
AFTER
.
The default is to add the column last. From MySQL 4.0.1 on,
you can also use col_name
FIRST
and
AFTER
in CHANGE
or
MODIFY
operations to reorder columns within
a table.
ALTER ... SET DEFAULT
or ALTER ...
DROP DEFAULT
specify a new default value for a
column or remove the old default value, respectively. 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 10.1.4, “Data Type Default Values”.
DROP INDEX
removes an index.
This is a MySQL extension to standard SQL. See
Section 12.1.7, “DROP INDEX
Syntax”. If you are unsure of the index
name, use SHOW INDEX FROM
.
tbl_name
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 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.
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 key. If
there is no primary key, an error occurs. (Prior to MySQL
4.1.2, if no primary key 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 nonunique index so that MySQL can detect duplicate keys as
early as possible.
From MySQL 4.1.0 on, some storage engines allow you to specify
an index type when creating an index. The syntax for the
index_type
specifier is
USING
.
For details about type_name
USING
, see
Section 12.1.4, “CREATE INDEX
Syntax”.
ORDER BY
enables 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 useful primarily when you know that you are mostly
to query the rows in a certain order most of the time. By
using this option after major 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.
ORDER BY
syntax allows for one or more
column names to be specified for sorting, each of which
optionally can be followed by ASC
or
DESC
to indicate ascending or descending
sort order, respectively. The default is ascending order. Only
column names are allowed as sort criteria; arbitrary
expressions are not allowed.
ORDER BY
does not make sense for
InnoDB
tables that contain a user-defined
clustered index (PRIMARY KEY
or
NOT NULL UNIQUE
index).
InnoDB
always orders table rows according
to such an index if one is present. The same is true for
BDB
tables that contain a user-defined
PRIMARY KEY
.
If you use ALTER TABLE
on a
MyISAM
table, all nonunique 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 for
a MyISAM
table. ALTER TABLE ...
DISABLE KEYS
tells MySQL to stop updating nonunique
indexes. 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.
While the nonunique indexes are disabled, they are ignored for
statements such as SELECT
and
EXPLAIN
that otherwise would
use them.
If ALTER TABLE
for an
InnoDB
table results in changes to column
values (for example, because a column is truncated),
InnoDB
's FOREIGN KEY
constraint checks do not notice possible violations caused by
changing the values.
The FOREIGN KEY
and
REFERENCES
clauses are supported by the
InnoDB
storage engine, which implements
ADD [CONSTRAINT [
. See
Section 13.2.5.4, “symbol
]]
FOREIGN KEY (...) REFERENCES ... (...)FOREIGN KEY
Constraints”. For other
storage engines, the clauses are parsed but ignored. The
CHECK
clause is parsed but ignored by all
storage engines. See Section 12.1.5, “CREATE TABLE
Syntax”. 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.9.5, “MySQL Differences from Standard SQL”.
The inline REFERENCES
specifications
where the references are defined as part of the column
specification are silently ignored by
InnoDB
. InnoDB only accepts
REFERENCES
clauses defined as part of a
separate FOREIGN KEY
specification.
Starting from MySQL 4.0.13, InnoDB
supports
the use of ALTER TABLE
to drop
foreign keys:
ALTER TABLEtbl_name
DROP FOREIGN KEYfk_symbol
;
For more information, see
Section 13.2.5.4, “FOREIGN KEY
Constraints”.
You cannot add a foreign key and drop a foreign key in
separate clauses of a single ALTER
TABLE
statement. You must use separate statements.
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, and then issue
this statement:
ALTER TABLE tbl_name
IMPORT TABLESPACE;
Pending INSERT DELAYED
statements are lost if a table is write locked and
ALTER TABLE
is used to modify
the table structure.
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 TABLEtbl_name
CONVERT TO CHARACTER SETcharset_name
;
This is useful, for example, after upgrading from MySQL 4.0.x to 4.1.x. See Section 9.1.11, “Upgrading Character Sets from MySQL 4.0”.
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.
If charset_name
is
DEFAULT
, the database character set is
used.
The CONVERT TO
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.
To change only the default character set for a table, use this statement:
ALTER TABLEtbl_name
DEFAULT CHARACTER SETcharset_name
;
The word DEFAULT
is optional. The default
character set is the character set that is used if you do not
specify the character set for columns that you add to a table
later (for example, with ALTER TABLE ... ADD
column
).
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.
With the mysql_info()
C API
function, you can find out how many rows were copied, and (when
IGNORE
is used) how many rows were deleted due
to duplication of unique key values. See
Section 17.6.3.33, “mysql_info()
”.
Here are some examples that show uses of
ALTER TABLE
. Begin with a table
t1
that is created as shown here:
CREATE TABLE t1 (a INTEGER,b CHAR(10));
To rename the table from t1
to
t2
:
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
:
ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);
To add a new TIMESTAMP
column named
d
:
ALTER TABLE t2 ADD d TIMESTAMP;
To add an index on column d
and a
UNIQUE
index on column a
:
ALTER TABLE t2 ADD INDEX (d), ADD UNIQUE (a);
To remove column c
:
ALTER TABLE t2 DROP COLUMN c;
To add a new AUTO_INCREMENT
integer column
named c
:
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 automatically. For
MyISAM
tables, you can set the first sequence
number by executing SET
INSERT_ID=
before
value
ALTER TABLE
or by using the
AUTO_INCREMENT=
table option. See Section 5.1.4, “Session System Variables”.
value
With MyISAM
tables, if you do not 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.
When replication is used, adding an
AUTO_INCREMENT
column to a table might not
produce the same ordering of the rows on the slave and the master.
This occurs because the order in which the rows are numbered
depends on the specific storage engine used for the table and the
order in which the rows were inserted. If it is important to have
the same order on the master and slave, the rows must be ordered
before assigning an AUTO_INCREMENT
number.
Assuming that you want to add an AUTO_INCREMENT
column to the table t1
, the following
statements produce a new table t2
identical to
t1
but with an
AUTO_INCREMENT
column:
CREATE TABLE t2 (id INT AUTO_INCREMENT PRIMARY KEY) SELECT * FROM t1 ORDER BY col1, col2;
This assumes that the table t1
has columns
col1
and col2
.
This set of statements will also produce a new table
t2
identical to t1
, with the
addition of an AUTO_INCREMENT
column:
CREATE TABLE t2 LIKE t1; ALTER TABLE T2 ADD id INT AUTO_INCREMENT PRIMARY KEY; INSERT INTO t2 SELECT * FROM t1 ORDER BY col1, col2;
To guarantee the same ordering on both master and slave,
all columns of t1
must
be referenced in the ORDER BY
clause.
Regardless of the method used to create and populate the copy
having the AUTO_INCREMENT
column, the final
step is to drop the original table and then rename the copy:
DROP t1; ALTER TABLE t2 RENAME t1;
User Comments
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;
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.
1 row in set (0.00 sec)E.g.
mysql> describe Temp_Table;
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.
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
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.
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.
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.
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);
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.
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.
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.
For moving a table from one database to another just do:
use db_old;
alter table tab_name rename db_new.tab_name;
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'
Converting_Database_Character_Sets
http://codex.wordpress.org/Converting_Database_Character_Sets
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
Found some good alter table here:
http://www.examplenow.com/mysql/alter
--John
Add your own comment.