当前位置: > 数据库 > MySQL >

MySQL:格式化输出

时间:2016-05-17 11:26来源:linux.it.net.cn 作者:IT
MySQL 客户端工具同样非常人性化 , 例如当查询结果集很多时,可以设置以列模式显示,还可以调用操作系统的命令显示。
    
--先看一个查询

mysql> select * from information_schema.tables where table_name='test_1';

+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME         | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT | +---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+ | def           | francs       | test_1     | BASE TABLE | InnoDB |      10 | Compact    |          3 |           5461 |       16384 |               0 |            0 |         0 |           NULL | 2014-08-14 09:30:23 | NULL        | NULL       | utf8_general_ci |     NULL |                |               | +---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+ 1 row in set (0.00 sec)

备注:这个查询很长,不便于观看。



--\G 设置列模式显示

mysql> select * from information_schema.tables where table_name='test_1'\G *************************** 1. row ***************************   TABLE_CATALOG: def    TABLE_SCHEMA: francs      TABLE_NAME: test_1      TABLE_TYPE: BASE TABLE          ENGINE: InnoDB         VERSION: 10      ROW_FORMAT: Compact      TABLE_ROWS: 3  AVG_ROW_LENGTH: 5461     DATA_LENGTH: 16384 MAX_DATA_LENGTH: 0    INDEX_LENGTH: 0       DATA_FREE: 0  AUTO_INCREMENT: NULL     CREATE_TIME: 2014-08-14 09:30:23     UPDATE_TIME: NULL      CHECK_TIME: NULL TABLE_COLLATION: utf8_general_ci        CHECKSUM: NULL  CREATE_OPTIONS:   TABLE_COMMENT: 1 row in set (0.00 sec)

备注: 在查询结尾使用\G 代替分号便是以列模式显示,太棒了!



--文档上的解释

ego, \G Send the current statement to the server to be executed and display the result using vertical format.

备注:如果查询结果很多, mysql 也能设置调用 OS 的系统命令显示查询结果,例如调用 linux 的 more, less 命令。



--使用 pager设 置显示方式

mysql> pager less PAGER set to 'less'

--取消 pager 的设置

mysql> nopager ; PAGER set to stdout

备注: pager 可以调用操作系统命令显示结果例 ,例如 less, more 等,  确实很方便,同样 mysql 客户端的帮助命令也很人性化。




--查看 create index 帮助命令

mysql> \h create index Name: 'CREATE INDEX' Description: Syntax: CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name     [index_type]     ON tbl_name (index_col_name,...)     [index_option]     [algorithm_option | lock_option] ... index_col_name:     col_name [(length)] [ASC | DESC] index_type:     USING {BTREE | HASH} index_option:     KEY_BLOCK_SIZE [=] value   | index_type   | WITH PARSER parser_name   | COMMENT 'string' algorithm_option:     ALGORITHM [=] {DEFAULT|INPLACE|COPY} lock_option:     LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE} CREATE INDEX is mapped to an ALTER TABLE statement to create indexes. See [HELP ALTER TABLE]. CREATE INDEX cannot be used to create a PRIMARY KEY; use ALTER TABLE instead. For more information about indexes, see http://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html. URL: http://dev.mysql.com/doc/refman/5.6/en/create-index.html

--参考
  • mysql Commands
(责任编辑:IT)
------分隔线----------------------------
栏目列表
推荐内容