Autocompletion in the MySQL command-line client

Question

In Linux, and many other systems, when navigating the terminal you can press Tab to auto complete a directory or file name.

I'm wondering if there is anything like that in the MySQL terminal. For example, if I want to get the description of someTableWithRidiculousLongName I could type describe someTableW then Tab and it would auto-complete the rest.

Does anything like that exist in the MySQL terminal?

Try mycli, https://github.com/dbcli/mycli – CodyChan Nov 19 '16 at 03:47 — CodyChan, Nov 19 '16 at 03:47

Teun Zengerink · Accepted Answer · 2012-04-04T11:00:29.363

133

Edit or create a file called .my.cnf in your home directory, containing:

[mysql]
auto-rehash

edited Apr 04 '12 at 11:00

answered Mar 16 '12 at 11:14

Teun Zengerink

4,277
5
30
32

@galarant not necessarily, it better be optional – rkmax Jun 10 '14 at 16:13
2

@rkmax - It still is optional with the `--disable-auto-rehash` flag. But now you've turned it on by default. – Teun Zengerink Jun 11 '14 at 07:29
3

But, it does not work when I type the commands in small letter. – alhelal Mar 23 '18 at 09:25

score 98 · Answer 2 · edited Apr 02 '13 at 18:22

98

To enable autocomplete within the MySQL prompt type:

mysql> \#

After that you can type:

mysql> describe someTableW[TAB]

To get:

mysql> describe someTableWithRidiculousLongName

edited Apr 02 '13 at 18:22

Teun Zengerink

4,277
5
30
32

answered Oct 27 '12 at 01:29

catmantiger

1,150
9
11

8

why that is not the default? – Oussama Elgoumri Jan 12 '15 at 07:17
1

simple and elegant – sp1rs May 13 '16 at 12:14
2

`mysql> rehash` - As an alternative to "\#", we can also type "rehash" mysql command as follows. Thought of sharing as "rehash" might be easier to remember for some people like me: `mysql> rehash` After that autocomplete will start working! – janeshs Apr 29 '18 at 17:01
3

@OussamaElgoumri Because some of us like to paste complex queries into the command line, and these queries might contain tab characters for indentation. – alexg Feb 21 '20 at 13:50

score 64 · Answer 3 · answered Nov 30 '11 at 20:16

64

start MySQL console with additional option --auto-rehash, i.e.

mysql --auto-rehash -u root -p

answered Nov 30 '11 at 20:16

rabudde

7,498
6
53
91

9

i dnt know why that does not work for me! the tab is being interpreted as a tab only and does nothing for auto completion. – Praveen Puglia Feb 04 '13 at 14:19
8

@pjp - This feature [does not work on regular Windows builds](http://stackoverflow.com/questions/269653/autocomplete-in-mysql-under-windows) – Álvaro González Feb 18 '13 at 11:41
3

Caution, tabcomplete only works after one is connected to a database as Otheus pointed out in https://unix.stackexchange.com/questions/270309/tab-completion-does-not-work-in-mysql-command-line-client/270616#270616?newreg=334448009e904ef9addb6a9d140cb12e – Simeon Feb 07 '18 at 09:00

score 33 · Answer 4 · answered Feb 26 '17 at 19:42

33

I know this is an old question, but I've found very helpful MySql cli client with advanced autocompletion: mycli. It's much smarter than builtin auto-rehash feature.

answered Feb 26 '17 at 19:42

mateuszlewko

1,110
8
18

7

Excellent alternative! – kraxor Sep 01 '17 at 10:14
3

Oh yea, top notch answer – smac89 Apr 17 '18 at 16:57
1

`auto-rehash` doesn't work on mine. Switched to mycli and boom.... worked like a pro. – Rajesh Chaudhary Apr 11 '20 at 08:44

score 7 · Answer 5 · answered Feb 13 '17 at 22:05

On OS X 10.11.6 I set --auto-rehash as described above, but it did not work. (This is OS X so mysql is compiled with the BSD libedit library.)

Then I remembered that I had set vi key-bindings for mysql client by creating ~/.editrc, containing one line: bind -v. This works great for giving me vi-like navigation in mysql client, but it broke column name completion (I was able to verify this by removing .editrc).

So I researched a little bit and found that ~/.editrc should have at least the following lines:

bind -v
bind \\t rl_complete

With this additional line, name completion works correctly in mysql AND vi-like navigation works also. (There are other .editrc settings which greatly improve mysql client navigation, but this isn't the place to start that thread of discussion.)

score 3 · Answer 6 · answered Aug 24 '16 at 08:10

Some notes about auto-rehash:

When you enable autocompletion editing the mysql config file..

[mysql]
auto-rehash

You can do it for all users or only for one user:

/etc/my.cnf: All Users

~/.my.cnf: Actual user

You can also disable autocompletion adding:

no-auto-rehash

Extracted from: http://www.sysadmit.com/2016/08/linux-mysql-autocompletar.html

score 2 · Answer 7 · answered Jun 17 '16 at 09:52

You can also auto-complete based on the command history. Start typing, then invoke the keys which are bound to ed-search-prev-history and ed-search-next-history. This applies if mysql comes with libedit support. The default keybindings are Ctrl-P and Ctrl-N, but this can be customized in .editrc. My example for Ctrl-up and Ctrl-down:

# start typing, then press Ctrl-Up
bind "\e[1;5A" ed-search-prev-history
# start typing, then press Ctrl-Up, then Ctrl-Down
bind "\e[1;5B" ed-search-next-history

Previously, mysql was based on readline, and then history-search-backward and history-search-forward are the correct commands. Configuration then was by means of .inputrc. Same example as above:

# these are the key bindings for the readline library
# start typing, then press Ctrl-Up
"\e[1;5A": history-search-backward
# start typing, then press Ctrl-Up, then Ctrl-Down
"\e[1;5B": history-search-forward

So, say you started typing sel and invoke Ctrl-Up, select * from some_long_table_name would come up if that is a command I have used earlier.

Thank you, your answer is gold to me! Your post is the only explanation I could find as to why my CLI no longer recognised my .inputrc file; as due to change from libreadline to libedit. — Brownrice, Aug 22 '23 at 07:00

Autocompletion in the MySQL command-line client

7 Answers7

Linked