How To Enable Auto Complete and Auto Suggest In MySQL

To enable auto completion and auto suggest on MySQL command prompt, let us use a open source utility "mycli" from git. We can use pip to install that.

pip install -U mycli

Once it is installed, do mycli --help to see all the different options.

Usage: mycli [OPTIONS] [DATABASE]

 A MySQL terminal client with auto-completion and syntax highlighting.

 Examples:
  - mycli my_database
  - mycli -u my_user -h my_host.com my_database
  - mycli mysql://my_user@my_host.com:3306/my_database

Lets now login to our database using mycli

mycli classicmodels

A following mysql command prompt will show up...

mysql root@localhost:classicmodels>

To see auto suggest drop down menu; Just start typing any mysql Command and a drop down menu will popup. Below command "use" is invoking the available databases to choose from.

Now lets try to execute the command "show columns" from a particular table. You will see a list of tables to choose from.

If you have lot of columns to choose from, you can write the select statement without the column name first and then hit tab in place of column to invoke the drop down menu as shown below...

It also invokes an auto suggest drop down menu for MySQL built in functions as soon as you type any character.

mycli also auto completes the previous commands from command line history. As we can see, as soon as I type select, it auto completes the command from my command line history. I can hit tab to choose this prompted command by the tool.

I find mycli to be very useful and productive.There is much more you can do with this tool to increase your productivity.