I am in the beginning stages of a project and I have so far been using the default MySQL database.
By the way, does the default database have name?
My question is how I can change the existing tables to be utf-8 and InnoDB without deleting the current ones and making new tables. Is there an alter table to make the table utf-8 and InnoDB?
Thanks, Alex
MyISAM is the default Storage Engine for MySQL (until 5.5.5, at which point InnoDB became the default). There is no concept of a default database.
To make an existing table use InnoDB, use the following:
ALTER TABLE tbl_name ENGINE = InnoDB;
To change the character set of an existing table to utf8, use the following:
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8;
If you want to play it safe with changing a table to InnoDB, I have a nice suggestion:
Create a new table as InnoDB and load it
For this example you have a MyISAM table called mytable in the mydb database. You can perform the following:
use mydb
CREATE TABLE mytable_innodb LIKE mytable;
ALTER TABLE mytable_innodb ENGINE=InnoDB;
INSERT INTO mytable_innodb SELECT * FROM mytable;
ALTER TABLE mytable RENAME mytable_myisam;
ALTER TABLE mytable_innodb RENAME mytable;
That's it. The new table is InnoDB plus you have a backup of the original table in MyISAM with its original contents and layout. You are free to perform whatever other conversions you need on the new InnoDB table.
CAVEAT
Make sure you optimize InnoDB
Howto: Clean a mysql InnoDB storage engine?
