SHOW语句的扩展
SHOW
语句的某些扩展伴随以下实现INFORMATION_SCHEMA
:
SHOW
可用于获取有关其INFORMATION_SCHEMA
自身结构的信息。- 多个
SHOW
语句接受一个WHERE
子句,该子句在指定要显示的行时提供了更大的灵活性。
INFORMATION_SCHEMA
是一个信息数据库,因此其名称包含在的输出中SHOW DATABASES
。同样,SHOW TABLES
可用于INFORMATION_SCHEMA
获取其表的列表:
mysql>SHOW TABLES FROM INFORMATION_SCHEMA; +--------------------------------------- + | Tables_in_INFORMATION_SCHEMA | +--------------------------------------- + | CHARACTER_SETS | | COLLATIONS | | COLLATION_CHARACTER_SET_APPLICABILITY | | COLUMNS | | COLUMN_PRIVILEGES | | ENGINES | | EVENTS | | FILES | | KEY_COLUMN_USAGE | | PARTITIONS | | PLUGINS | | PROCESSLIST | | REFERENTIAL_CONSTRAINTS | | ROUTINES | | SCHEMATA | | SCHEMA_PRIVILEGES | | STATISTICS | | TABLES | | TABLE_CONSTRAINTS | | TABLE_PRIVILEGES | | TRIGGERS | | USER_PRIVILEGES | | VIEWS | +--------------------------------------- +
SHOW COLUMNS
并DESCRIBE
可以显示有关各个INFORMATION_SCHEMA
表中列的信息。
SHOW
接受LIKE
用来限制显示行的子句的语句还允许一个WHERE
子句,该子句指定所选行必须满足的更一般的条件:
SHOW CHARACTER SET SHOW COLLATION SHOW COLUMNS SHOW DATABASES SHOW FUNCTION STATUS SHOW INDEX SHOW OPEN TABLES SHOW PROCEDURE STATUS SHOW STATUS SHOW TABLE STATUS SHOW TABLES SHOW TRIGGERS SHOW VARIABLES
该WHERE
子句(如果存在)将根据该SHOW
语句显示的列名进行评估。例如,该SHOW CHARACTER SET
语句产生以下输出列:
mysql>SHOW CHARACTER SET ; +---------- +----------------------------- +--------------------- +-------- + | Charset | Description | Default collation | Maxlen | +---------- +----------------------------- +--------------------- +-------- + | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 | | dec8 | DEC West European | dec8_swedish_ci | 1 | | cp850 | DOS West European | cp850_general_ci | 1 | | hp8 | HP West European | hp8_english_ci | 1 | | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 | | latin1 | cp1252 West European | latin1_swedish_ci | 1 | | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 | ...
要将WHERE
子句与一起使用SHOW CHARACTER SET
,您将引用这些列名称。例如,以下语句显示有关字符集的信息,其默认归类包含字符串'japanese'
:
mysql>SHOW CHARACTER SET WHERE `Default collation ` LIKE '%japanese%'; +--------- +--------------------------- +--------------------- +-------- + | Charset | Description | Default collation | Maxlen | +--------- +--------------------------- +--------------------- +-------- + | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 | | sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 | | cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 | | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 | +--------- +--------------------------- +--------------------- +-------- +
此语句显示多字节字符集:
mysql>SHOW CHARACTER SET WHERE Maxlen > 1; +--------- +--------------------------- +--------------------- +-------- + | Charset | Description | Default collation | Maxlen | +--------- +--------------------------- +--------------------- +-------- + | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 | | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 | | sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 | | euckr | EUC-KR Korean | euckr_korean_ci | 2 | | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 | | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 | | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 | | cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 | | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 | +--------- +--------------------------- +--------------------- +-------- +