Recipe 9.6. 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 *************************** 1. row ***************************            TABLE_CATALOG: NULL             TABLE_SCHEMA: cookbook               TABLE_NAME: item              COLUMN_NAME: id         ORDINAL_POSITION: 1           COLUMN_DEFAULT: NULL              IS_NULLABLE: NO                DATA_TYPE: int CHARACTER_MAXIMUM_LENGTH: NULL   CHARACTER_OCTET_LENGTH: NULL        NUMERIC_PRECISION: 10            NUMERIC_SCALE: 0       CHARACTER_SET_NAME: NULL           COLLATION_NAME: NULL              COLUMN_TYPE: int(10) unsigned               COLUMN_KEY: PRI                    EXTRA: auto_increment               PRIVILEGES: select,insert,update,references           COLUMN_COMMENT: *************************** 2. row ***************************            TABLE_CATALOG: NULL             TABLE_SCHEMA: cookbook               TABLE_NAME: item              COLUMN_NAME: name         ORDINAL_POSITION: 2           COLUMN_DEFAULT: NULL              IS_NULLABLE: YES                DATA_TYPE: char CHARACTER_MAXIMUM_LENGTH: 20   CHARACTER_OCTET_LENGTH: 20        NUMERIC_PRECISION: NULL            NUMERIC_SCALE: NULL       CHARACTER_SET_NAME: latin1           COLLATION_NAME: latin1_swedish_ci              COLUMN_TYPE: char(20)               COLUMN_KEY:                    EXTRA:               PRIVILEGES: select,insert,update,references           COLUMN_COMMENT: *************************** 3. row ***************************            TABLE_CATALOG: NULL             TABLE_SCHEMA: cookbook               TABLE_NAME: item              COLUMN_NAME: colors         ORDINAL_POSITION: 3           COLUMN_DEFAULT: puce              IS_NULLABLE: YES                DATA_TYPE: set CHARACTER_MAXIMUM_LENGTH: 32   CHARACTER_OCTET_LENGTH: 32        NUMERIC_PRECISION: NULL            NUMERIC_SCALE: NULL       CHARACTER_SET_NAME: latin1           COLLATION_NAME: latin1_swedish_ci              COLUMN_TYPE: set('chartreuse','mauve','lime green','puce')               COLUMN_KEY:                    EXTRA:               PRIVILEGES: select,insert,update,references           COLUMN_COMMENT: 

Here are some of the COLUMNS table values likely to be of most use:

• COLUMN_NAME indicates the column name.

• ORDINAL_POSITION is the position of the column within the table definition.

• COLUMN_DEFAULT is the column's default value.

• IS_NULLABLE is YES or NO to indicate whether the column can contain NULL values.

• DATA_TYPE and COLUMN_TYPE provide data-type information. DATA_TYPE is the data-type keyword and COLUMN_TYPE contains additional information such as type attributes.

• CHARACTER_SET_NAME and COLLATION_NAME indicate the character set and collation for string columns. They are NULL for nonstring columns.

To retrieve information only about a single column, add a condition to the WHERE clause that names the appropriate COLUMN_NAME value:

mysql> SELECT * FROM INFORMATION_SCHEMA.COLUMNS     -> WHERE TABLE_SCHEMA = 'cookbook' AND TABLE_NAME = 'item'     -> AND COLUMN_NAME = 'colors'\G *************************** 1. row ***************************            TABLE_CATALOG: NULL             TABLE_SCHEMA: cookbook               TABLE_NAME: item              COLUMN_NAME: colors         ORDINAL_POSITION: 3           COLUMN_DEFAULT: puce              IS_NULLABLE: YES                DATA_TYPE: set CHARACTER_MAXIMUM_LENGTH: 32   CHARACTER_OCTET_LENGTH: 32        NUMERIC_PRECISION: NULL            NUMERIC_SCALE: NULL       CHARACTER_SET_NAME: latin1           COLLATION_NAME: latin1_swedish_ci              COLUMN_TYPE: set('chartreuse','mauve','lime green','puce')               COLUMN_KEY:                    EXTRA:               PRIVILEGES: select,insert,update,references           COLUMN_COMMENT: 

If you want only certain types of information, replace SELECT * with a list of the values of interest:

mysql> SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE     -> FROM INFORMATION_SCHEMA.COLUMNS     -> WHERE TABLE_SCHEMA = 'cookbook' AND TABLE_NAME = 'item'; +-------------+-----------+-------------+ | COLUMN_NAME | DATA_TYPE | IS_NULLABLE | +-------------+-----------+-------------+ | id          | int       | NO          | | name        | char      | YES         | | colors      | set       | YES         | +-------------+-----------+-------------+ 

INFORMATION_SCHEMA content is easy to use from within programs. Here's a PHP function that illustrates this process. It takes database and table name arguments, selects from INFORMATION_SCHEMA to obtain a list of the table's column names, and returns the names as an array. The ORDER BY ORDINAL_POSITION clause ensures that the names in the array are returned in table definition order.

function get_column_names ($conn, $db_name, $tbl_name) {   $stmt = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS            WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?            ORDER BY ORDINAL_POSITION";   $result =& $conn->query ($stmt, array ($db_name, $tbl_name));   if (PEAR::isError ($result))     return (FALSE);   $names = array();   while (list ($col_name) = $result->fetchRow ())     $names[] = $col_name;   $result->free ();   return ($names); } 

The equivalent routine using Ruby DBI looks like this:

def get_column_names(dbh, db_name, tbl_name)   stmt = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS           WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?           ORDER BY ORDINAL_POSITION"   return dbh.select_all(stmt, db_name, tbl_name).collect { |row| row[0] } end 

And in Python, it looks like this:

def get_column_names (conn, db_name, tbl_name):   stmt = """            SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS            WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s            ORDER BY ORDINAL_POSITION          """   cursor = conn.cursor ()   cursor.execute (stmt, (db_name, tbl_name))   names = []   for row in cursor.fetchall ():     names.append (row[0])   cursor.close ()   return (names) 

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

sub get_column_names { my ($dbh, $db_name, $tbl_name) = @_;   my $stmt = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS               WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?               ORDER BY ORDINAL_POSITION";   my $ref = $dbh->selectcol_arrayref ($stmt, undef, $db_name, $tbl_name);   return defined ($ref) ? @{$ref} : (); } 

The routines just shown return an array containing only column names. If you require additional column information, you can write more general routines that return an array of structures, in which each structure contains information about a given column. The lib directory of the recipes distribution contains some examples. Look for routines named get_column_info⁠(⁠ ⁠ ⁠) in the library files.

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.^[*]The following example demonstrates the output that SHOW COLUMNS produces for the item table.

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

mysql> SHOW COLUMNS FROM item\G *************************** 1. row ***************************   Field: id    Type: int(10) unsigned    Null: NO     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 data type.

• Null is YES if the column can contain NULL values, NO otherwise.

• Key provides information about whether the column is indexed.

• Default indicates the default value.

• Extra lists miscellaneous information.

The format of SHOW COLUMNS changes occasionally, but the fields just described should always be available. SHOW FULL COLUMNS displays additional fields.

SHOW COLUMNS supports a LIKE clause that takes an SQL pattern:

SHOW COLUMNS FROM tbl_name LIKE 'pattern'; 

The pattern is interpreted the same way as for the LIKE operator in the WHERE clause of a SELECT statement. (For information about pattern matching, see Section 5.10.) With a LIKE clause, SHOW COLUMNS displays information for any column having a name that matches 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 issue. Suppose that 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 statement:

SHOW COLUMNS FROM tbl_name LIKE 'co_2'; 

The _ character means "match any character" in pattern strings, so the statement 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 statement. For example, in Perl, Ruby, and PHP, you can use the following expressions.

Perl:

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

Ruby:

name.gsub!(/([%_])/, '\\\\\1') 

PHP:

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

For Python, import the re module, and use its sub⁠(⁠ ⁠ ⁠) method:

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

For Java, use the java.util.regex package:

import java.util.regex.*; Pattern p = Pattern.compile("([_%])"); Matcher m = p.matcher(name); name = m.replaceAll ("\\\\$1"); 

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.

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

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. You can get this information using 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` set('chartreuse','mauve','lime green','puce') DEFAULT 'puce',   PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 

From the command line, the same information is available from mysqldump if you use the --no-data option, which tells mysqldump to dump only the structure of the table and not its data:

% mysqldump --no-data cookbook item -- MySQL dump 10.10 -- -- Host: localhost    Database: cookbook -- ------------------------------------------------------ -- Server version       5.0.27-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`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; 

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