← Back to the summary

ksql's Session-Wide Output Controls Beat MySQL's Per-Statement Hacks

When querying wide tables in MySQL, output wraps when there are too many fields, making column alignment hard to read. MySQL's solution is to append \G at the end of the SQL to list each row's fields vertically. ksql handles this problem differently—by using several meta-commands to control the output behavior of the entire session, without needing to add anything to the end of each SQL statement.

This is verified in the KingbaseES V009R001C010 environment: \x (expanded mode), \x auto (automatic judgment), \timing (execution time), \pset border (border adjustment), \o (output redirection). The demonstration is performed on the app_schema.t_meta_demo table in the app_db database, connected as app_user. After entering, set search_path to app_schema, public is executed first so subsequent queries don't need the schema prefix.

There are several activities recommended to everyone:

"1. Recommend Business Opportunities · Win Gifts — Kingbase Community 'Fellow Traveler Plan' Launched (https://bbs.kingbase.com.cn/forumDetail?articleId=1d09d598f414ab764eda4907e8f54758) 2. 2026 Kingbase Database Intelligent O&M Tool Development Competition (<https://bbs.kingbase.com.cn/forumDetail?articleId=2394013b19f3ef84a43edb994692b88e) (https://bbs.kingbase.com.cn/forumDetail?articleId=6152608d769b472397ccfbd29879c0bd)>"

The Wide Table Problem with Default Output

Turn off the pager and query the full table directly:

\pset pager off

select * from t_meta_demo;

Default output wide table wrapping

t_meta_demo has six fields: id, order_no, user_name, status, amount, created_at. When the terminal width is insufficient, each row wraps into multiple lines, and the correspondence between field names and values becomes hard to see at a glance. This is the problem \x solves.

\x Toggle: Vertical Expansion On and Off

Turn on expanded mode and query again:

\x

select * from t_meta_demo limit 2;

Execute \x again to turn it off, comparing the two outputs:

\x

select * from t_meta_demo limit 2;

Comparison before and after x mode toggle

After \x is turned on, ksql prompts Expanded display is on. The output format changes from a horizontal table to a vertical column display, with each field on its own line in the format field_name | value. Executing \x again prompts Expanded display is off, restoring the horizontal format.

In MySQL, vertical display uses select ... \G, written at the end of the SQL, and only affects that single statement. \x is a session-level switch; once turned on, all queries use the vertical format without needing to append anything to each SQL statement. When querying multiple wide tables consecutively, \x is much more convenient than \G.

\x auto: Let ksql Decide Whether to Expand

Manually toggling \x back and forth is a bit cumbersome. In \x auto mode, ksql judges automatically:

\x auto

ksql returns Expanded display is used automatically.

Query a single-row result:

select * from t_meta_demo where order_no = 'ORD-20240601-001';

Then query multiple rows:

select * from t_meta_demo;

x auto automatic switching

For the single-row query, ksql automatically used vertical expansion; for the multi-row query, ksql judged the terminal width to be sufficient and output a horizontal table directly.

The judgment basis for \x auto is the current terminal width, not the number of fields. For the same table, narrowing the terminal window makes ksql more inclined to expand; widening it may cause the same query to revert to horizontal format. MySQL does not have this mode; \G must be manually written at the end of the SQL each time.

\timing Displays Execution Time

Turn off expanded mode and turn on timing:

\x off
\timing

Execute a few queries to observe the time consumed:

select count(*) from t_meta_demo;

select * from t_meta_demo where status = 'paid';

select user_name, sum(amount) from t_meta_demo group by user_name;

timing displays execution time

After \timing is turned on, it prompts Timing is on, and each SQL execution is followed by a line Time: x.xxx ms.

In MySQL, viewing execution time usually requires show profiles or the help of client tools. In ksql, \timing automatically attaches the time after each result once turned on, with no extra steps needed.

However, \timing provides client-side wall-clock time, including network round-trips and client processing, and is only suitable for a coarse-grained judgment of whether a query is fast or slow. For precise analysis of whether SQL uses an index and the time distribution across execution nodes, use EXPLAIN (ANALYZE, BUFFERS). Do not use \timing as a performance analysis tool.

\pset border Adjusts Output Borders

Turn off timing and demonstrate three border styles:

\timing off

Default border (border 1):

\pset border 1
select order_no, user_name, status, amount from t_meta_demo limit 3;

No border (border 0):

\pset border 0
select order_no, user_name, status, amount from t_meta_demo limit 3;

Double-line border (border 2):

\pset border 2
select order_no, user_name, status, amount from t_meta_demo limit 3;

pset border three border styles

The differences between the three styles:

border 1: Columns are separated by |, with a +---+---+ style separator line under the header row. This is the default style.
border 0: All separators are removed, columns are separated only by spaces, and the header and data are aligned with short dashes.
border 2: A complete border is drawn around the entire result set, with separator lines at the top, bottom, and between the header and data.

\pset border only changes the display format, not the data, and takes effect immediately after switching. MySQL client output has a fixed border style; ksql allows adjustment as needed. border 0 is most useful when pasting terminal output into documents, eliminating the need to manually clean up grid lines. After the demonstration, restore the default:

\pset border 1

\o Writes Output to a File

Redirect query results to a file within an interactive session, without exiting to use shell redirection:

\pset border 1
\o /tmp/ksql-output.txt

select order_no, user_name, status, amount from t_meta_demo;

\o

o output redirection to file

After \o /tmp/ksql-output.txt is opened, ksql no longer outputs query results to the terminal—the prompt returns directly after select executes, with no table output, and all results are written to the file. \o without parameters closes the redirection and restores terminal output. In another terminal, using cat /tmp/ksql-output.txt shows all five rows of data, consistent with the results queried directly in the terminal.

There is a pitfall that is easy to step into: closing redirection must use \o without any parameters. If you forget to close it, all subsequent query outputs, including meta-command prompts, will continue to be written to the file, and nothing will be visible in the terminal, making it easy to think the command didn't execute. In MySQL, exporting results within an interactive session usually requires external tools. In ksql, \o can switch directly within the session; just remember to close it after use.