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

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:

Autocompletion in the MySQL command-line client
First connect to a mysql database named foo with a user named vivek and force for password prompt:

ADVERTISEMENTS

$ mysql -u vivek -p foo
OR
$ mysql -u vivek -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-rehash option to mysql. That causes mysql to start faster.
$ mysql -A -u vivek -p foo
OR
$ mysql --skip-auto-rehash -u vivek -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> \#
🐧 Get the latest tutorials on SysAdmin, Linux/Unix, Open Source & DevOps topics via:
CategoryList of Unix and Linux commands
File Managementcat
FirewallAlpine Awall CentOS 8 OpenSUSE RHEL 8 Ubuntu 16.04 Ubuntu 18.04 Ubuntu 20.04
Network Utilitiesdig host ip nmap
OpenVPNCentOS 7 CentOS 8 Debian 10 Debian 8/9 Ubuntu 18.04 Ubuntu 20.04
Package Managerapk apt
Processes Managementbg chroot cron disown fg jobs killall kill pidof pstree pwdx time
Searchinggrep whereis which
User Informationgroups id lastcomm last lid/libuser-lid logname members users whoami who w
WireGuard VPNAlpine CentOS 8 Debian 10 Firewall Ubuntu 20.04

ADVERTISEMENTS
10 comments… add one
  • Sheeri Nov 26, 2006 @ 17:38

    Great tip! You do have to have done a “use database” at least once before in your session, because that’s when it loads all that information in. Also, does that trick work on Unix only or on Windows too? (Mac OS X = Unix)

  • 🐧 nixCraft Nov 26, 2006 @ 17:49

    Hmm..

    mysql -u vivek -p foo

    foo is database name; so no need to use database name again (foo is not my password) :). Otherwise execute use database as suggested by you.

    I have not tested this on Microsoft Windows. But it should work as this is mysql client feature.

    Appreciate your post.

  • mysqlonWindows Jun 20, 2007 @ 4:42

    This doesn’t appear to work on Windows with cmd.exe. Does anyone know if it’s possible to get mysql tab-completion working with cmd.exe? If it is, then any additional guidance on how to get it set up would be great.

  • Paolo Jun 25, 2008 @ 12:00

    can be possible autocomplete sql command?!
    for example: select, from, having and so way?
    thanks a lot
    bye

  • cipak Jul 3, 2010 @ 11:47

    hi,

    i reached here searching how to enable the feature, as you said in this post: http://nixcraft.com/databases-servers/793-tab-completion-mysql-client.html

    however, you only say here that it’s enabled by default, and how to disable it.

    i have an installation where it seems to be disabled (compiled mysql5 on a debian etch system), and i was hoping to find a way to enable it. there is another mysql installation (version 4) on the same machine that has tab completion.

    • Ankit May 30, 2011 @ 12:02

      use “#” on mysql prompt

  • John Ortiz Mar 30, 2012 @ 16:27

    Someone have experienced problems with this feature in putty (in my case, auto-completion does not work!)?

    Thanks in advance.

  • Jos Visser Apr 30, 2012 @ 9:38

    The underlying feature in MySQL is called rehashing. There are a number of command line flags (and associated options in my.cnf) to enable, disable or skip this. See this for more information.

    Tip: If it’s not working, issue the ‘rehash’ command from the MySQL command line interface.

  • zzzz Dec 25, 2012 @ 5:03

    @John Ortiz
    Yes dude i have same problem too.

  • zzzz Dec 25, 2012 @ 5:07

    Jos Visser

    Thanks a lot.
    After issusing rehash it worked!

Leave a Reply

Your email address will not be published. Required fields are marked *

Use HTML <pre>...</pre>, <code>...</code> and <kbd>...</kbd> for code samples.