Working with Metadata - MySQL - page 359

• CHARACTER_SET_NAME , COLLATION_NAME : The character set and collation for string
columns. They are NULL for nonstring columns.
• COLUMN_KEY : Information about whether the column is indexed.
INFORMATION_SCHEMA content is easy to use from within programs. Here's a PHP func‐
tion 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 names in the
array are returned in table-definition order:
function 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" ;
$sth = $dbh -> prepare ( $stmt );
$sth -> execute ( array ( $db_name , $tbl_name ));
return ( $sth -> fetchAll ( PDO :: FETCH_COLUMN , 0 ));
}
get_column_names() returns an array containing only column names. If you require
additional column information, it's possible to write a more general get_column_in
fo() routine that returns an array of column information structures. For implementa‐
tions of both routines in PHP as well as other languages, check the library files in the
lib directory of the recipes distribution.
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 col‐
umn. The following example demonstrates SHOW COLUMNS output for the item table
colors column:
mysql> SHOW COLUMNS FROM item LIKE 'colors'\G
*************************** 1. row ***************************
Field: colors
Type: enum('chartreuse','mauve','lime green','puce')
Null: YES
Key:
Default: puce
Extra:
SHOW COLUMNS displays information for all columns having a name that matches the LIKE
pattern. To obtain information about all columns, omit the LIKE clause.
The values displayed by SHOW COLUMNS correspond to these columns of the INFORMA
TION_SCHEMA COLUMNS table: COLUMN_NAME , COLUMN_TYPE , COLUMN_KEY , IS_NULLABLE ,
COLUMN_DEFAULT , EXTRA .