9.3.4 Making table changesThis is NOT the latest copy of this book; click here for the latest version.
Once you have got your table created, we can make changes to it using ALTER TABLE syntax. Basic usage of alter table is either ALTER TABLE some_table ADD field type , or ALTER TABLE some_table DROP field - the former adds new field some_field of type some_type , and the latter deletes the field some_field . Consider the following code:
CREATE TABLE usertable (ID INT, FirstName CHAR(255), LastName CHAR(255), Age INT);
ALTER TABLE usertable ADD MiddleName CHAR(255);
ALTER TABLE usertable DROP Age;
That will leave usertable containing four fields: ID, FirstName, LastName, and MiddleName. When you delete a column, there is no way to get it back - MySQL has no method for "undo", remember.
There are two key things to note about using ALTER TABLE:
-
As it would be massively inefficient for MySQL simply tack a new field on to the end of a table or equally to delete a field and leave big gaps in a table, the ALTER TABLE process nearly always involves creating a new table with your requested change, copying all the data from the old table to the new, then deleting the old table. During this time, all queries that come in asking to read data will be processed from the old table, and all queries asking to write data will be put on hold until the operation has completed. If you are altering a big table, this operation can take some time, so be sure you are willing to put your writes on hold before you execute this command.
-
MySQL will sometimes overrule your choice of column type if it thinks it knows better, and MySQL won't even tell you it has changed your decision for you - you will need to check your table using "DESC <your table>" to make sure it has changed. More on this later.
The ALTER TABLE command can also be used to rename tables, like this:
ALTER TABLE foo RENAME TO bar;
Note that this use of ALTER TABLE is the only situation where MySQL will not need to create a temporary table.
|
Want to see this stuff in print? PHP in a Nutshell takes the core topics covered here, adds in thousands of edits from the editorial team and myself, and combines them to make an unbeatable reference for PHP programmers at all levels.
My latest book has hundreds more tips on how to use PHP, Apache, and MySQL, plus Perl, Python, shell scripts, performance tuning, and more!
|
