The MySQL Prompt

Following on from my previous post about tips I have been picking up from the MySQL certification study guide I have another which I think is worth mentioning.

Personally I had never noticed that the MySQL prompt changes in relation to what went before it. How often have you done something similar to the following.

mysql> select * from emps where emp_name = 'john ;
    '> ;
    '> ';
Empty set (0.01 sec)

You type in a select statement only to open a quote but never close it, personally I always stick in another ; before I realise the mistake and terminate the quote and end the statement correctly. But what I didn't realise before reading the study guide (and making me very unobservant) was that the mysql> prompt changes in response to the currently open quote. In the above example this isn't obvious because it just looks like a continuation but in fact the prompt has been changed to show us that it is expecting a closing single quote. Take for example the following.

mysql> select * from emps
    -> where emp_name = 'john';

In this case it gives us the standard -> continuation prompt. Firstly I never even noticed this and secondly even if I did I doubt I would have seen the significance, generally I'm more focused on why the five semi-colons I entered have failed to be recognised. The certification study guide points out that there are in fact 5 prompts.

mysql> 
    ->
    '>
    ">
    `>

This becomes much more relevant when we are entering large complex SQL statements where it might not be obvious where the mistake has occurred. Take this SQL for example.

mysql> select e.emp_id, e.emp_name, d.dept_name
    -> from emps e, dept d
    -> where e.dept_id = d.dept_id
    -> and e.emp_name = 'john
    '> and e.dept_id = 1;
    '>

While this isn't as complex as it could be it’s easy to see in this instance where the problem occurred because our prompt changes from -> to '> on line 5.

We can use this in combination with the \c tip I mentioned earlier.

mysql> select e.emp_id, e.emp_name, d.dept_name
    -> from emps e, dept d
    -> where e.dept_id = d.dept_id
    -> and e.emp_name = 'john
     '> '\c