Peregring-lk Peregring-lk - 28 days ago 12
MySQL Question

Truncate column names in SELECT (MySQL client)

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).

Answer

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.