Database Reference
In-Depth Information
SHOW
FULL
COLUMNS
displays additional
Collation
,
Privileges
, and
Comment
fields for
each column. These correspond to the
COLUMNS
table
COLLATION_NAME
,
PRIVILEGES
, and
COLUMN_COMMENT
columns.
SHOW
interprets the pattern the same way as for the
LIKE
operator in the
WHERE
clause of
a
SELECT
statement. (For information about pattern matching, see
Recipe 5.8
.) 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
_
) that you want to match
literally, you must escape them with a backslash in the pattern string to avoid matching
other names as well.
The need to escape
%
and
_
characters to match a
LIKE
pattern literally also applies to
other
SHOW
statements that permit a name pattern in the
LIKE
clause, such as
SHOW
TABLES
and
SHOW
DATABASES
.
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.
In Perl, Ruby, and PHP, use the following expressions.
Perl:
$name
=~
s/([%_])/\\$1/g
;
Ruby:
name
.
gsub!
(
/([%_])/
,
'\\\\\1'
)
PHP:
$name
=
preg_replace
(
'/([%_])/'
,
'\\\\$1'
,
$name
);
For Python, import the
re
module, and use its
sub()
method:
name
=
re
.
sub
(
r'([%_])'
,
r'
\\
\1'
,
name
)
For Java, use methods from 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 lan‐
guage 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.
Another level on top of that is needed if the pattern processor strips a set.
