MySQL Cookbook

Getting Table Structure Information

Problem

You want to find out how a table is defined.

Solution

Thre are several ways to do this, ranging from statements that return this information directly, to using metadata from a query on the table.

Discussion

Information about the structure of tables allows you to answer questions such as “What columns does a table contain and what are their types?” or “What are the legal values for an ENUM or SET column?” In MySQL, there are several ways to find out about a table’s structure:

• Use a SHOW COLUMNS statement.

• Use a SELECT query that selects columns from the table, then examine the query metadata for information about each column.

• Use the mysqldump command-line program or the SHOW CREATE TABLE statement to obtain a CREATE TABLE statement that displays the table’s structure.

The following sections discuss how you can ask MySQL for table information using each of these methods. To try out the examples, create the following item table that lists item IDs, names, and the colors in which each item is available:

CREATE TABLE item
(
    id      INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name    CHAR(20),
    colors  SET('chartreuse','mauve','lime green','puce') DEFAULT 'puce',
    PRIMARY KEY (id)
);

Using SHOW COLUMNS to Get Table Structure

The SHOW COLUMNS statement produces one row of output for each column in the table, with each row providing various pieces of information about the corresponding column.^[37] I suggest that you try the SHOW COLUMNS statement with several of your ...