Databases Reference
In-Depth Information
in a
.frm
file with the same name as the table. Thus, when you create a table named
MyTable
, MySQL stores the table definition in
MyTable.frm
. Because MySQL uses the
filesystem to store database names and table definitions, case sensitivity depends on
the platform. On a Windows MySQL instance, table and database names are case
insensitive; on Unix-like systems, they are case sensitive. Each storage engine stores the
table's data and indexes differently, but the server itself handles the table definition.
You can use the
SHOW TABLE STATUS
command (or in MySQL 5.0 and newer versions,
query the
INFORMATION_SCHEMA
tables) to display information about tables. For example,
to examine the
user
table in the
mysql
database, execute the following:
mysql>
SHOW TABLE STATUS LIKE 'user' \G
*************************** 1. row ***************************
Name: user
Engine: MyISAM
Row_format: Dynamic
Rows: 6
Avg_row_length: 59
Data_length: 356
Max_data_length: 4294967295
Index_length: 2048
Data_free: 0
Auto_increment: NULL
Create_time: 2002-01-24 18:07:17
Update_time: 2002-01-24 21:56:29
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment: Users and global privileges
1 row in set (0.00 sec)
The output shows that this is a MyISAM table. You might also notice a lot of other
information and statistics in the output. Let's look briefly at what each line means:
Name
The table's name.
Engine
The table's storage engine. In old versions of MySQL, this column was named
Type
, not
Engine
.
Row_format
The row format. For a MyISAM table, this can be
Dynamic
,
Fixed
, or
Compressed
.
Dynamic rows vary in length because they contain variable-length fields such as
VARCHAR
or
BLOB
. Fixed rows, which are always the same size, are made up of fields
that don't vary in length, such as
CHAR
and
INTEGER
. Compressed rows exist only in
compressed tables; see
“Compressed MyISAM tables” on page 19
.
Rows
The number of rows in the table. For MyISAM and most other engines, this number
is always accurate. For InnoDB, it is an estimate.
