Database Reference
In-Depth Information
which is the same as the column it indexes, and it then shows in parentheses a lists of
columns from which the index is drawn. That could be more than one column, but it's just
There's one more aspect you should note in the results of
SHOW CREATE TABLE
.
Notice that the last line shows a few other settings after the closing parentheses for the set
of columns. First is the type of table used, or rather the type of storage engine used for this
table. In this case,it's
MyISAM
, which is the default for many servers. The default for
your server may be different. Data is stored and handled in different ways by different
storage engines. There are advantages and disadvantages to each.
The other two settings are the default character set (
latin1
) and the default collation
(
latin1_bin
) in the table. These come from the default values when the database was
created, or rather they came indirectly from there. You can set a different character and
collation, and you can even set a different character set and collation for an individual
column.
Let me give you an example where setting explicit values for the character set and colla-
tion might be useful. Suppose you have a typical database for a bird-watcher group loc-
ated in England with most of its common names written in English. Suppose further that
the site attracts bird-watchers from other countries in Europe, so you might want to in-
clude common bird names in other languages. Let's say that you want to set up a table for
the Turkish bird-watchers. For that table, you would use a different character set and col-
lation, because the Turkish alphabet contains both Latin and other letters. For the charac-
ter set, you would use
latin5
, which has both Latin and other letters. For collation, you
would use
latin5_turkish_ci
, which orders text based on the order of the letters in
the Turkish alphabet. To make sure you don't forget to use this character set and collation
when adding columns to this table later, you could set the
CHARSET
and
COLLATE
for
the table to these values.
Before moving on, let me make one more point about the
SHOW CREATE TABLE
state-
ment: if you want to create a table with plenty of special settings different from the de-
fault, you can use the results of the
SHOW CREATE TABLE
statement as a starting point
for constructing a more elaborate
CREATE TABLE
statement. Mostly you would use it to
see the assumptions that the server made when it created a table, based on the default set-
tingsduring installation.
The next table we'll create for the examples in this topic is
bird_families
. This will
hold information about bird families, which are groupings of birds. This will tie into the
family_id
column in the
birds
table. The new table will save us from having to enter
