Pasting SQL into the MySQL command line

Question

I have an application that is defining some SQL code:

mySql = "SELECT     
            sq.question, 
            qs.title, 
            sq.id as question_id, 
            sq.type,
            qs.id as option_id, 
            sri.title as rankTitle,
            sri.id as rankId,
            sfi.title as formTitle,
            sfi.id as formId,
            sq.sub_type,
            sq.sort_order
FROM        survey_questions as sq 
LEFT JOIN   question_suboptions as qs
ON          sq.id = qs.question_id 
LEFT JOIN   survey_rankingitems as sri
ON          sq.id = sri.question_id
LEFT JOIN   survey_formitems as sfi
ON          sq.id = sfi.question_id
WHERE       sq.survey_id = #{@surveyId}
ORDER BY    sq.sort_order"

I would like to paste this code (everything between the double quotes) in the MySQL command line, change the one parameter and execute it, but I have run into an issue where for every line above MySQL will display:

Display all 1450 possibilities? (y or n)

And then 1450 different available commands. If I remove all linebreaks and tabs then I can paste in, but that is time consuming and a pain. Is there a way that I can simply paste in the above code, edit it and then execute it as a single unit?

Answer 1

This is the default mysql (CLI) behavior each time the user presses the Tab key (mysql uses the underlying readline or EditLine libraries (not on Windows)).

By default, when the user requests to use a database, mysql reads tables and fields definitions. Then, pressing the Tab key makes mysql conveniently offers completion of the current input with the known tables and fields.

However, pasting some text into mysql that contains TAB characters (\t or 0x09) triggers the same behavior - even though no Tab key was actually pressed from the keyboard. And this can be annoying.

---

Two options given to mysql can prevent that behavior, though. My favorite is --disable-auto-rehash. The other one is --quiet or -q.

• --disable-auto-rehash to prevent database, table, and column name completion (which are not read from the database, use the rehash command if later on you need completion). Commands history is kept, though (retrieved via the ↑ and ↓ keys for instance). Which is convenient.

• --quick or -q which makes mysql not using the history file and no completion (does not read the database definitions).

On Linux one may add an alias in .bashrc to use --disable-auto-rehash automatically

alias mysql2='mysql --disable-auto-rehash'

Answer 2

You need to remove the line breaks and tabs. The double tab is causing it to display the Display all 1450 possibilities? (y or n) and the line breaks are causing it to execute early.

If it's PHP, write a little script to strip it for you:

echo (preg_replace("/\s+/", " ", $string));

Or something similar for other languages.