Getting Table Structure Information

9.6.1 Problem

You want to find out how a table is defined.

9.6.2 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.

9.6.3 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)
);

9.6.4 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.[1] I suggest that you try the SHOW COLUMNS statement with several of your own tables to get a feel for the kind of output it produces for various column types. The following example demonstrates the output that SHOW COLUMNS produces for the item table. (Note the use of G as the statement terminator to generate "vertical" output; SHOW COLUMNS output lines often are so long that they wrap around and become difficult to read.)

[1] SHOW COLUMNS FROM tbl_name is equivalent to SHOW FIELDS FROM tbl_name or DESCRIBE tbl_name.

mysql> SHOW COLUMNS FROM itemG
*************************** 1. row ***************************
 Field: id
 Type: int(10) unsigned
 Null:
 Key: PRI
Default: NULL
 Extra: auto_increment
*************************** 2. row ***************************
 Field: name
 Type: char(20)
 Null: YES
 Key:
Default: NULL
 Extra:
*************************** 3. row ***************************
 Field: colors
 Type: set('chartreuse','mauve','lime green','puce')
 Null: YES
 Key:
Default: puce
 Extra:

The information displayed by the statement is as follows:

Field

Indicates the column's name

Type

Shows the column type

Null

YES if the column can contain NULL values, blank otherwise

Key

Provides information about whether or not the column is indexed

Default

Indicates the default value

Extra

Lists miscellaneous information

The format of SHOW COLUMNS changes occasionally, but the values just described should always be available. Your version of MySQL may display additional information.

SHOW COLUMNS output is easy to use from within programs. The important thing to know is that SHOW is like SELECT in that it returns a result set, so you process a SHOW query by issuing the statement and performing a row-retrieval fetch loop. Here's a PHP function that illustrates this process. It takes a table name argument, then uses SHOW COLUMNS to obtain a list of the table's column names:

function get_column_names_with_show ($conn_id, $tbl_name)
{
 $query = "SHOW COLUMNS FROM $tbl_name";
 if (!($result_id = mysql_query ($query, $conn_id)))
 return (FALSE);
 $names = array( ); # create empty array
 # first value in each output row is the column name
 while (list ($name) = mysql_fetch_row ($result_id))
 $names[ ] = $name; # append name to array
 mysql_free_result ($result_id);
 return ($names);
}

The array returned by the function contains column names in the same order the columns appear in the table. Notice that get_column_names_with_show( ) takes no argument for specifying a database name. It's not necessary, because MySQL understands fully qualified table references of the form db_name.tbl_name. To access a table in a database other than the current one, just pass a $tbl_name argument that includes the database name:

$names = get_column_names_with_show ($conn_id, "some_db.some_tbl");

The equivalent function in Python looks like this:

def get_column_names_with_show (conn, tbl_name):
 names = [ ]
 cursor = conn.cursor ( )
 cursor.execute ("SHOW COLUMNS FROM " + tbl_name)
 rows = cursor.fetchall ( )
 cursor.close ( )
 for row in rows:
 names.append (row[0])
 return (names)

In DBI, this operation is trivial, because selectcol_arrayref( ) returns the first column of the query result directly:

sub get_column_names_with_show
{
my ($dbh, $tbl_name) = @_;

 my $ref = $dbh->selectcol_arrayref ("SHOW COLUMNS FROM $tbl_name");
 return (defined ($ref) ? @{$ref} : ( ));
}

Interpreting the Default Value Displayed by SHOW COLUMNS

When you run SHOW COLUMNS from the mysql program, it displays the word "NULL" as the Default indicator for columns that may contain NULL values. However, if you issue a SHOW COLUMNS statement from within your own programs, don't look for the string "NULL." Look for the special value your API uses to represent NULL values, such as undef for DBI or None for DB-API.

If you want information only about a single column, use a LIKE clause that matches the column name:

mysql> SHOW COLUMNS FROM item LIKE 'colors'G
*************************** 1. row ***************************
 Field: colors
 Type: set('chartreuse','mauve','lime green','puce')
 Null: YES
 Key:
Default: puce
 Extra:

Observe that quotes surround the column name following the LIKE keyword. Quotes are required because the name isn't really a column name, it a SQL pattern string. The string is interpreted the same way as for the LIKE operator in the WHERE clause of a SELECT statement. (For information about pattern matching, see Recipe 4.7.) SHOW COLUMNS displays information for any columns having names that match the pattern; if you specify a literal column name, the string matches only that name and SHOW COLUMNS displays information only for that column. However, a trap awaits the unwary here. If your column name contains SQL pattern characters (% or _) and you want to match them literally, you must escape them with a backslash in the pattern string to avoid matching other names as well. The % character isn't used very often in column names, but _ is quite common, so it's possible that you'll run into this problem. Suppose you have a table that contains the results of carbon dioxide measurements in a column named co_2, and trigonometric cosine and cotangent calculations in columns named cos1, cos2, cot1, and cot2. If you want to get information only for the co_2 column, you can't use this query:

SHOW COLUMNS FROM tbl_name LIKE 'co_2';

The _ character means "match any character" in pattern strings, so the query would return rows for co_2, cos2, and cot2. To match only the co_2 column, write the SHOW command like this:

SHOW COLUMNS FROM tbl_name LIKE 'co\_2';

Within a program, you can use your API language's pattern matching capabilities to escape SQL pattern characters before putting the column name into a SHOW query. For example, in Perl, PHP, and Python, you can use the following expressions:

Perl:

$name =~ s/([%_])/\$1/g;

PHP:

$name = ereg_replace ("([%_])", "\\1", $name);

For Python, import the re module, then do this:

name = re.sub (r'([%_])', r'\1', name)

If these expressions appear to have too many backslashes, remember that the API language processor itself interprets backslashes and strips off a level before performing the pattern match. To get a literal backslash into the result, it must be doubled in the pattern. PHP has another level on top of that because it strips a set and the pattern processor strips a set.

For Java, you'll need to select a regular expression class library. The following example uses the ORO library available at jakarta.apache.org, which includes classes that emulate Perl5 regular expressions:

import org.apache.oro.text.perl.*;

Perl5Util util = new Perl5Util ( );
name = util.substitute ("s/([_%])/\\$1/g", name);

The need to escape % and _ characters to match a LIKE value literally also applies to other forms of the SHOW statement that allow a name pattern in the LIKE clause, such as SHOW TABLES, SHOW DATABASES, and SHOW VARIABLES.

9.6.5 Using Result Set Metadata to Get Table Structure

Another way to obtain table column information is to use the metadata generated when you issue a SELECT statement. (See Recipe 9.3.) Metadata information is available for any arbitrary query, and therefore can be obtained specifically for the columns of a given table. For example, the result set for SELECT * FROM tbl_name will contain metadata for each column in tbl_name. However, if you're interested only in the metadata, not in the data contained in the table, you'll naturally want to minimize the size of the result set so as to generate as little network traffic as possible. It's easy to guarantee that the result will be empty by adding a WHERE clause that is always false:

SELECT * FROM tbl_name WHERE 1 = 0

Although this query selects no rows, it's perfectly legal and MySQL goes ahead and generates the metadata, from which you can extract whatever information you want. For example, earlier in this section we wrote a PHP function get_column_names_with_show( ) that gets a list of the column names by issuing a SHOW COLUMNS statement. The function can be reimplemented as follows to get the column names from the query metadata instead by running a SELECT query and calling mysql_fetch_field( ):

function get_column_names_with_meta ($conn_id, $tbl_name)
{
 $query = "SELECT * FROM $tbl_name WHERE 1 = 0";
 if (!($result_id = mysql_query ($query, $conn_id)))
 return (FALSE);
 $names = array( ); # create empty array
 for ($i = 0; $i < mysql_num_fields ($result_id); $i++)
 {
 if ($field = mysql_fetch_field ($result_id, $i))
 $names[ ] = $field->name; # append name to array
 }
 mysql_free_result ($result_id);
 return ($names);
}

The equivalent function in Perl is simpler. DBI organizes metadata into arrays, so we just need to access the $sth->{NAME} reference to the column name array. The only trick is that it's necessary to make a copy of the array before calling finish( ), because finish( ) destroys the metadata and renders the NAME array unavailable:

sub get_column_names_with_meta
{
my ($dbh, $tbl_name) = @_;
my ($sth, @names);

 $sth = $dbh->prepare ("SELECT * FROM $tbl_name WHERE 1=0");
 $sth->execute ( );
 @names = @{$sth->{NAME}}; # make a copy; finish( ) destroys meta info
 $sth->finish ( ); # release result set
 return (@names);
}

You can easily convert these functions that get column names into more general versions that allow you to specify which kind of metadata information you want. Name each function get_column_info( ) and add a parameter for the information type. In PHP, the function looks like this:

function get_column_info ($conn_id, $tbl_name, $info_type)
{
 $query = "SELECT * FROM $tbl_name WHERE 1 = 0";
 if (!($result_id = mysql_query ($query, $conn_id)))
 return (FALSE);
 $info = array( ); # create empty array
 for ($i = 0; $i < mysql_num_fields ($result_id); $i++)
 {
 if ($field = mysql_fetch_field ($result_id, $i))
 $info[ ] = $field->$info_type; # append info to array
 }
 mysql_free_result ($result_id);
 return ($info);
}

To use the function, call it as follows:

$names = get_column_info ($conn_id, "item", "name");
$types = get_column_info ($conn_id, "item", "type");
$numeric = get_column_info ($conn_id, "item", "numeric");

The Perl version looks like this:

sub get_column_info
{
my ($dbh, $tbl_name, $info_type) = @_;
my ($sth, @info);

 $sth = $dbh->prepare ("SELECT * FROM $tbl_name WHERE 1=0");
 $sth->execute ( );
 @info = @{$sth->{$info_type}}; # make a copy; finish( ) destroys meta info
 $sth->finish ( ); # release result set
 return (@info);
}

And is invoked like this:

my @names = get_column_info ($dbh, "item", "NAME");
my @types = get_column_info ($dbh, "item", "mysql_type_name");
my @numeric = get_column_info ($dbh, "item", "mysql_is_num");

One caution to observe with get_column_info( ) is that you can't use it to determine display widths for a table's columns. (That is, it is not useful for the mysql_max_length values in Perl or the max_length values in PHP.) When you use column metadata obtained from a SELECT statement, the column display widths reflect the widths of the values actually present in the result. When the query is SELECT ... WHERE 1=0, the result set is empty and the display widths are all zero!

Portability of SELECT WHERE 1=0

The SELECT ... WHERE 1=0 query is reasonably portable across database systems, but won't necessarily work with all possible engines. If you want to use this query with database systems other than MySQL to obtain table information, be sure to test it first to make sure it works.

Which Method Should You Use to Get Table Information?

Can you get away with using just SHOW COLUMNS or just SELECT ... WHERE 1=0 for all your table information needs? Not necessarily. The information that these approaches provide overlaps, but not completely. If you want to determine column names or whether or not columns can be NULL, you can use either method. However, if you want to know legal members of an ENUM or SET column, you must use SHOW COLUMNS. Know your tools, and pick the right one for the job. The most appropriate method may depend on what you want to know.

9.6.6 Using CREATE TABLE to Get Table Structure

A third way to obtain table structure information from MySQL is to use mysqldump --no-data from the command line to generate a CREATE TABLE statement that shows the structure of the table. The following command shows an example. The --no-data option tells mysqldump not to dump the data from the table, --all tells it to print all the CREATE TABLE options, and --quote-names causes names to be quoted in case they contain special characters.[2] You can omit --all or --quote-names if you don't need their effect.

[2] The --all option was introduced in MySQL 3.22.23 and --quote-names was introduced in 3.23.6.

% mysqldump --no-data --all --quote-names cookbook item
# MySQL dump 8.16
#
# Host: localhost Database: cookbook
#--------------------------------------------------------
# Server version 3.23.46-log

#
# Table structure for table 'item'
#

CREATE TABLE `item` (
 `id` int(10) unsigned NOT NULL auto_increment,
 `name` char(20) default NULL,
 `colors` set('chartreuse','mauve','lime green','puce') default 'puce',
 PRIMARY KEY (`id`)
) TYPE=MyISAM;

If you have MySQL 3.23.20 or later, you can get the same information using the SHOW CREATE TABLE statement:

mysql> SHOW CREATE TABLE item;
+-------+--------------+
| Table | Create Table |
+-------+--------------+
| item | CREATE TABLE `item` (
 `id` int(10) unsigned NOT NULL auto_increment,
 `name` char(20) default NULL,
 `colors` set('chartreuse','mauve','lime green','puce') default 'puce',
 PRIMARY KEY (`id`)
) TYPE=MyISAM |
+-------+--------------+

This format is highly informative and easy to read because it shows column information in a format similar to the one you use to create the table in the first place. It also shows the index structure clearly, which the other methods do not. However, you'll probably find this method for checking table structure more useful for visual examination than for use within programs. The information isn't provided in regular row-and-column format, so it's more difficult to parse. Also, the format is somewhat subject to change whenever the CREATE TABLE statement is enhanced, which happens from time to time as MySQL's capabilities are extended.

That's not to say there are no programmatic applications for SHOW CREATE TABLE output. It's useful, for example, for making an exact copy of a table, including all its indexes. The technique is described in Recipe 3.26, which discusses table cloning.