Better way to view MySQL tables
The problem
You are trying to print a MySQL table with a large number of columns, with
SELECT *
command. You type SELECT * FROM mysql.user LIMIT 1
, and your terminal
becomes this.
If you wanted to view more than one row, you're in a trouble :)
The solution
Run MySQL with this option:
mysql --pager="less --chop-long-lines --quit-if-one-screen --no-init'
This will make your table display only the rows it can in the current screen, something like this. You can use the arrow keys to move sideways to view the hidden column. Pressing the ‘right’ arrow key will move half page towards right. Neat, isn't it?
You can create an alias for mysql:
# Using shorter version of 'less' flags mentioned above
alias mysql='mysql -SFX'
You can put the above line in your ~/.bashrc
file to load this alias
in every new terminal session.
Bonus point for Vim users
less
allows using keys j
and k
for scrolling down and scrolling up. Unfortunately, you cannot directly use keys h
and l
to move sideways using less
. Fortunately, you can map h
and l
to move left or right, respectively. Here's how to do that:
Create a file .lesskey
in your home directory, with the following contents
l noaction 10\e)
h noaction 10\e)
Now run this command, to generate ~/.less
file.
lesskey
This will generate a binary file which less
understands. If you now start a new MySQL terminal session (of course with the above said --pages
flag), you can use Vim's HJKL
movements. Here I have specified to move 10 characters to the right if you make one ‘right’ Vim movement.
Quick setup script
Don't want to do the above stuff manually? Just run this command and your computer will be set up in a second!
sh -c "$(wget -q https://raw.githubusercontent.com/rushiagr/public/master/scripts/mysql-pretty-table.sh -O -)"
Note that changes will take effect from a new shell session (or you can run source ~/.bashrc
if you want things to work in the current session too.
More information
Find more information at below links:
About mapping ‘h’ and ‘k’ to Vim movements in ‘less’
About using ‘less’ as MySQL pager
Cheers!