1

I hate to nitpick over style, but for the purposes of use Database Administrators is there a way to clean this up a bit,

SELECT 1 AS "myCol1", 2 AS "myCol2";
+--------+--------+
| myCol1 | myCol2 |
+--------+--------+
|      1 |      2 |
+--------+--------+

For comparison, with PostgreSQL,

SELECT 1 AS "myCol1", 2 AS "myCol2";
 myCol1 | myCol2 
--------+--------
      1 |      2

Can I get the psql output from MySQL or MariaDB?

I know `psql, the client for PostgreSQL has a ton of options for formatting the output table

  • \C Sets the title of any tables being printed as the result of a query or unset any such title. This command is equivalent to \pset title title. (The name of this command derives from “caption”, as it was previously only used to set the caption in an HTML table.)
  • \a If the current table output format is unaligned, it is switched to aligned. If it is not unaligned, it is set to unaligned. This command is kept for backwards compatibility. See \pset for a more general solution.
  • \pset,

    • border
    • columns
    • expanded
    • fieldsep
    • fieldsep_zero
    • footer
    • format (supports html, asciidoc, latex, latex-longtable, and troff-ms)
    • linestyle
    • null
    • numericlocale
    • recordsep
    • recordsep_zero
    • tableattr
    • title
    • tuples_only
    • unicode_border_linestyle, unicode_column_linestyle, unicode_header_linestyle
    • and on, and on.
2

Sadly, no, but there are a few options which may help

  • --skip-column-names, -NDo not write column names in results.

  • --skip-line-numbers, -L Do not write line numbers for errors. Useful when you want to compare result files that include error messages.

  • --silent, -s Silent mode. Produce less output. This option can be given multiple times to produce less and less output. This option results in nontabular output format and escaping of special characters. Escaping may be disabled by using raw mode; see the description for the --raw option.

May be worth nothing that while silent is almost nice,

SELECT 1 AS "myCol1", 2 AS "myCol2";
myCol1  myCol2
1       2

You're actually losing the tabulated display and just getting a \t.

SELECT 10000000000000 AS "myCol1", 2 AS "myCol2";
myCol1  myCol2
10000000000000  2
1

You always can get any desired formatting with sed batch editor:

mysql -t -e 'SELECT * FROM table;' | sed -E '1d;$d;s/^[|+]//;s/[|+]$//'
0

Instead of the standard mysql command-line client that comes with MariaDB and MySQL, try usql, the universal command-line interface. From the README file:

usql provides a simple way to work with SQL and NoSQL databases via a command-line inspired by PostgreSQL's psql. usql supports most of the core psql features, such as variables, backticks, and commands and has additional features that psql does not, such as syntax highlighting, context-based completion, and multiple database support.

Database administrators and developers that would prefer to work with a tool like psql with non-PostgreSQL databases, will find usql intuitive, easy-to-use, and a great replacement for the command-line clients/tools for other databases.

Github project at: https://github.com/xo/usql

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.