Database Reference
In-Depth Information
The first two SQL statements will show you the structure of both tables. They will con-
firm that they are identical except for their names. To save space, I didn't include the res-
ults of those two SQL statements here.
The third SQL statement should show you all of the rows of data in the
birds_new
table. Because we copied only the structure of the
birds
table when we created the new
table, there is no data — as indicated by the message returned. We could copy the data
over when we're finished altering the table if that's what we want to do.
This method can also be used when making major modifications to a table. In such a situ-
ation, it's good to work from a copy of the table. You would then use the
ALTER TABLE
statement to change the new table (e.g.,
birds_new
). When you're finished making the
changes, you would then copy all of the data from the old table to the new table, delete the
original table, and then rename the new table.
In such a situation, you may have one minor problem. I said earlier that the tables are
identical except for the table names, but that's not exactly true. There may be one other
difference. If the table has a column that uses
AUTO_INCREMENT
for the default value,
the counter will be set to 0 for the new table. You must determine the current value of
AUTO_INCREMENT
for the
birds
table to be assured that the rows in the new table
have the correct identification numbers. Enter the followingSQL statement in
mysql
:
SHOW CREATE TABLE
birds
\
G
In the results, which are not shown, the last line will reveal the current value ofthe
AUTO_INCREMENT
variable. For instance, the last line may look as follows:
...
)
ENGINE
=
MyISAM
AUTO_INCREMENT
=
6
DEFAULT
CHARSET
=
latin1
COLLATE
=
latin1_bin
In this excerpt of the results, you can see that the variable,
AUTO_INCREMENT
is cur-
rently 6. Set
AUTO_INCREMENT
to the same value in the
birds_new
table by entering
the following SQL statement in
mysql
:
ALTER TABLE
birds_new
AUTO_INCREMENT
=
6
;
When you're ready to copy the data from one table to the other, you can use the
INSERT...SELECT
syntax. This is covered in
Other Possibilities
.
Instead of copying the data after you're finished modifying the new table, you can copy
the data while creating the new table. This might be useful when you want to move only
certain columns with their data to a new table, without any alterations to the columns. To
