How to Save time use MySQL/MariaDB auto completion for database or table names with auto-rehash option

149
0

There is a quick way to type both MySQL database and table names quickly by enabling MySQL auto completion feature. This is called automatic rehashing. This option is on by default, which allows table and column name completion with MySQL command line:

First connect to a mysql database named foo with a user named vivek and force for password prompt:

$ mysql -u aryo -p foo

OR

mysql -u aryo -h 192.168.1.15 -p foo

Now let us say you would like to list all records from tables wp_users. Type select * from w and press tab key to complete table name:

mysql> select * from w {Press TAB KEY}

Please note that if the partial name is unique, mysql will completes it for you. Otherwise, you can hit tab key again to see all possible matches. Auto-completion enables you to cut down typing time and great way to speed up your work.

How do I disable auto rehash feature?

If you don’t use this feature, disable this feature by passing -A or –skip-auto-rehashoption to mysql. That causes mysql to start faster.

mysql -A -u aryo -p foo

OR

mysql --skip-auto-rehash -u aryo -p foo

A note about when MySQL tab completion not working

To enable automatic rehashing at command prompt type:

mysql --auto-rehash -u user -p db_name

Or edit or create a file called .my.cnf in your home directory:

vi ~/.my.cnf

Append/edit as follows i.e. add auto-rehash:

[mysql]
auto-rehash

Save and close the file.

To enable automatic vertical result set display:

mysql --auto-vertical-output -u user -p db_name

To enable automatic rehashing at mysql> prompt type \#:

mysql -u user -p db_name

Now type \# at mysql> prompt:

mysql> \#

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.