Database Reference
In-Depth Information
Using CREATE TABLE to get table structure
Another way to obtain table structure information from MySQL is from the
CREATE
TABLE
statement that defines the table. To get this information, use the
SHOW
CREATE
TABLE
statement:
mysql>
SHOW CREATE TABLE item\G
*************************** 1. row ***************************
Table: item
Create Table: CREATE TABLE `item` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` char(20) DEFAULT NULL,
`colors` enum('chartreuse','mauve','lime green','puce') DEFAULT 'puce',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
From the command line, the same
CREATE
TABLE
information is available from
mysql‐
dump
if you use the
--no-data
option, which tells
mysqldump
to dump only the struc‐
ture of the table and not its data.
CREATE
TABLE
format is highly informative and easy to read because it shows column
information in a format similar to the one you used to create the table in the first place.
It also shows the index structure clearly, whereas the other methods do not. However,
you'll probably find this method of checking table structure more useful interactively
than within programs. The information isn't provided in regular row-and-column for‐
mat, so it's more difficult to parse. Also, the format is subject to change whenever the
CREATE
TABLE
statement is enhanced, which happens from time to time as MySQL's
capabilities are extended.
10.7. Getting ENUM and SET Column Information
Problem
You want to know the members of an
ENUM
or
SET
column.
Solution
This problem is a subset of getting table structure metadata. Obtain the column defi‐
nition from the table metadata, then extract the member list from the definition.
Discussion
It's often useful to know the list of legal values for an
ENUM
or
SET
column. Suppose that
you want to present a web form containing a pop-up menu that has options corre‐
sponding to each legal value of an
ENUM
column, such as the sizes in which a garment
can be ordered, or the available shipping methods for delivering a package. You could
hardwire the choices into the script that generates the form, but if you alter the column
