当前位置: 首页 > 文档资料 > MySQL 中文手册 >

23.2. SHOW语句的扩展

优质
小牛编辑
128浏览
2023-12-01

某些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|
+---------------------------------------+
| SCHEMATA|
| TABLES|
| COLUMNS |
| CHARACTER_SETS |
| COLLATIONS  |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| ROUTINES|
| STATISTICS  |
| VIEWS |
| TRIGGERS|
| USER_PRIVILEGES|
| SCHEMA_PRIVILEGES  |
| TABLE_PRIVILEGES|
| COLUMN_PRIVILEGES  |
| TABLE_CONSTRAINTS  |
| KEY_COLUMN_USAGE|
+---------------------------------------+

SHOW COLUMNS和DESCRIBE能够显示单独INFORMATION_SCHEMA表中的列信息。

扩展了一些SHOW语句,允许使用WHERE子句:

SHOW CHARACTER SET
SHOW COLLATION
SHOW COLUMNS
SHOW DATABASES
SHOW FUNCTION STATUS
SHOW KEYS
SHOW OPEN TABLES
SHOW PROCEDURE STATUS
SHOW STATUS
SHOW TABLE STATUS
SHOW TABLES
SHOW VARIABLES

如果有WHERE子句的话,将根据SHOW语句显示的列名进行计算。例如,SHOW COLLATION语句可产生这些输出列:

例如,SHOW CHARACTER SET语句可产生这些输出列:

mysql> SHOW CHARACTER SET;
Charset
描述
默认校对
最大长度
 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 
 

要想与SHOW CHARACTER SET一起使用WHERE子句,应引用这些列名称。例如,在下面的语句中,给出了用于默认校对且含有字符串“japanese”的字符集的信息:

mysql> SHOW CHARACTER SET WHERE `Default collation` LIKE '%japanese%';
 描述  
<td valign="top"
 Charset 
默认校对
最大长度
 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 
 描述  
默认校对
最大长度
 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