Truncate column names in SELECT (MySQL client)

Question

When I'm looking into new databases to explore what is there, usually I get tables with long column names but short contents, like:

mysql> select * from Seat limit 2;

+---------+---------------------+---------------+------------------+--------------+---------------+--------------+-------------+--------------+-------------+---------+---------+----------+------------+---------------+------------------+-----------+-------------+---------------+-----------------+---------------------+-------------------+-----------------+

| seat_id | seat_created | seat_event_id | seat_category_id | seat_user_id | seat_order_id | seat_item_id | seat_row_nr | seat_zone_id | seat_pmp_id | seat_nr | seat_ts | seat_sid | seat_price | seat_discount | seat_discount_id | seat_code | seat_status | seat_sales_id | seat_checked_by | seat_checked_date | seat_old_order_id | seat_old_status |

+---------+---------------------+---------------+------------------+--------------+---------------+--------------+-------------+--------------+-------------+---------+---------+----------+------------+---------------+------------------+-----------+-------------+---------------+-----------------+---------------------+-------------------+-----------------+

| 4897 | 2016-09-01 00:05:54 | 330 | 331 | NULL | NULL | NULL | 0 | NULL | NULL | 0 | NULL | NULL | NULL | 0.00 | NULL | NULL | free | NULL | NULL | 0000-00-00 00:00:00 | NULL | NULL | | 4898 | 2016-09-01 00:05:54 | 330 | 331 | NULL | NULL | NULL | 0 | NULL | NULL | 0 | NULL | NULL | NULL | 0.00 | NULL | NULL | free | NULL | NULL | 0000-00-00 00:00:00 | NULL | NULL |

+---------+---------------------+---------------+------------------+--------------+---------------+--------------+-------------+--------------+-------------+---------+---------+----------+------------+---------------+------------------+-----------+-------------+---------------+-----------------+---------------------+-------------------+-----------------+

Since the length of the header is longer that the contents of each row, I see a unformatted output which is hard to standard, specially when you search for little clues like fields that aren't being used and so on.

Is there any way to tell mysql client to truncate column names automatically, for example, to 10 characters as maximum? With the first 10 character is usually enough to know which column they refer to.

Of course I could stablish column aliases for that with AS, but if there's too much columns and you want to do a fast exploration, that would take too long for each table.

Other solution will be to tell mysql to remove the prefix seat_ for each column for example (of course, for each column I would need to change the used prefix).

No, there's no way to make it automatically truncate or edit column names, AFAIK. You could use `\G` to display columns vertically instead of horizontally. — Barmar, Nov 09 '16 at 19:02
@Barmar That option doesn't answer my question, but indeed, it solves my problem. Post it as an answer and I will flag it as my valid answer (with an example to show that the `\G` option must be written at the end of the sentence instead of `;`. — ABu, Nov 09 '16 at 19:18
You could also use a graphical table viewer like PhpMyAdmin. None of this really counts as "programming", IMHO. — Barmar, Nov 09 '16 at 19:19

score 3 · Accepted Answer · edited May 23 '17 at 10:30

3

I don't think there's any way to do that automatically. Some options are:

1) Use a graphical UI such as PhpMyAdmin to view the table contents. These typically allow you to adjust column widths.

2) End the query with \G instead of ;:

    mysql> SELECT * FROM seat LIMIT 2\G

This will display the columns horizontally instead of vertically:

      seat_id: 4897
 seat_created: 2016-09-01 00:05:54
seat_event_id: 330
...

I often use the latter for tables with lots of columns because reading the horizontal format can be difficult, especially when it wraps around on the terminal.

3) Use the less pager in a mode that doesn't wrap lines. You can then scroll left and right with the arrow keys.

    mysql> pager less -S

See How to better display MySQL table on Terminal

edited May 23 '17 at 10:30

Community

1
1

answered Nov 09 '16 at 19:27

Barmar

741,623
53
500
612

Why have you rollbacked my edition? The question could not be understood if the output isn't shown unformatted. – ABu Nov 09 '16 at 19:35
Did you look at it? It was completely unreadable because it performed wrapping. – Barmar Nov 09 '16 at 19:57
The question is about column headings, you couldn't see where they were. – Barmar Nov 09 '16 at 19:58
And that exactly the intended question, to show that it's pretty hard to know which column headers the column contents refers to. – ABu Nov 09 '16 at 20:12
1

Now I understand better, see my new option 3. – Barmar Nov 09 '16 at 20:26
I'm using the pager option and its marvelous!!! It is exactly what I was looking for. The worst thing is that I have read about that option long time ago and I didn't remember it. – ABu Nov 09 '16 at 21:15

score 2 · Answer 2 · edited Aug 20 '20 at 17:35

2

You can skip the column names completely by running the MySQL client with the -N or --skip-column-names option. Then the width of your columns will be determined by the widest data, not the column name. But there would be no row for the column names.

You can also use column aliases to set your own column names, but you'd have to enter these yourself manually.

edited Aug 20 '20 at 17:35

Braiam

1
11
47
78

answered Nov 09 '16 at 19:35

Bill Karwin

538,548
86
673
828

Thanks but I need to know the column names because I'm seeying tables that I don't know. – ABu Nov 09 '16 at 19:38

Truncate column names in SELECT (MySQL client)

2 Answers2