Search in book...
Toggle Font Controls
Create new playlist

Name your new playlist

Playlist description (optional)
Sign In

Email address

Password

Forgot Password?

or

Continue with Facebook

Continue with Google
Sign Up

Full Name

Email address

Confirm Email Address

Password

or

Continue with Facebook

Continue with Google

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

mysql>SHOW COLUMNS FROM itemG
*************************** 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.

SHOWCOLUMNS supports a LIKE clause that takes an SQL pattern:

SHOW COLUMNS FROMtbl_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 Pattern Matching with SQL Patterns.) 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 FROMtbl_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 FROMtbl_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 itemG
*************************** 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.

^[12]SHOWCOLUMNSFROMtbl_name is equivalent to SHOWFIELDSFROMtbl_name or DESCRIBEtbl_name.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.

Table of Contents for Accessing Table Column Definitions

Create new playlist

Sign In

Sign Up