MySQL Cookbook, 2nd Edition

Accessing Table Column Definitions

Problem

You want to find out what columns a table has and how they are defined.

Solution

There are several ways to do this. You can obtain column definitions from INFORMATION_SCHEMA, from SHOW statements, or from mysqldump.

Discussion

Information about the structure of tables enables 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:

• Retrieve the information from INFORMATION_SCHEMA. The COLUMNS table contains the column definitions.

• Use a SHOW COLUMNS statement.

• Use the SHOW CREATE TABLE statement or the mysqldump command-line program 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 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 INFORMATION_SCHEMA to get table structure

To obtain information about the columns in a table by checking INFORMATION_SCHEMA, use a statement of the following form:

mysql>SELECT * FROM INFORMATION_SCHEMA.COLUMNS
    -> WHERE TABLE_SCHEMA = 'cookbook' AND TABLE_NAME = 'item'\G *************************** ...