Skip navigation

Despite being dealing with databases for some years it was just recently that I started to run mysql queries straight from the shell by doing something like:

mysql -u(user) -p(password)  -t -e ‘whatever sql query here in one line’

Although a handy way to use every now and then, I still felt this sort of limited since the query to be passed in the “-e” argument must go inside a single line. In search of  a better alternative I was pleased to figure instead that it’s possible to combine heredoc sintaxis with the sql execution in the command line. Heredoc is a mechanism to break text into multiple lines, just as a file would be treated, after the “<<” sign, the parser says: “here follows a document” which gets wrapped inside the pair of whatever text delimeters we choose. The obvious convenience is that without having to create a file we could easily run multiline queries -which read more naturally with line breaks and indentation- in the same way we would write them inside a file.

To quickly see what I’m talking about type

mysql -u(yourDatabaseUserName) -p(your Password)   -t<<eof
show databases
eof

Now hit “Control D” and then, “Enter” to see the output directly thrown at your shell

To speed things up, since  I use the shell inside emacs, I bound the following to C-S f11 adding  what is below into my .emacs file:

(set-register ?q “mysql -u kabul -p ******** -t <<eofsql”)
(defalias ‘Q
(read-kbd-macro “M-x insert-register RET Q C-e “))
(global-set-key [C-S-f11] ‘Q)

Advertisements

2 Comments

  1. why don’t you use sql-mysql inside emacs?

    • yes Dmitry, I do use Emacs’s sql interactive mode, but -unless I’m missing something obvious- it doesn’t allow you to re-edit a multi-line query, does it?
      With the heredoc in the shell I can yank pieces from the last one and go about crafting it to my need.

      Another approach, along the same line, is to have a scratch file for the mysql query or script and refer to it using a lisp shorthand that inserts the following mysql command inside the shell:

      “mysql -uroot –password=`cat /etc/security/mysqlpassword` -vvv < scratch.sql"

      Actually, I currently tend to like this method better (which I use with the frame splited in two windows) as it permits to edit the given script more freely, plus I can save the scratch file to reuse latter if I want.

      Thanks for commenting!


One Trackback/Pingback

  1. […] that I’m getting fond of using a heredoc to insert mysql scripts into the bash shell, have put this into my emacs initialization file. The same shortcut will conveniently write […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: