Undo or Edit Last Line of Multi-Line Query in MySQL

MySQLmysql-5.7

I often make mistakes when entering multi-line queries in mysql. Is there an easier way to undo the last line instead of retyping the entire query?

mysql> SELECT age, name
    -> FROM people
    -> WHERE age > 20
    ->

For example, I meant to type age < 20, is there a way to quicky fix that? Normally I would ctrl + C and use the up arrow key to re-enter first two lines quickly, but I feel like there should be an easier way.

I tried reading documentation and read some forums but I couldn't find anything. It should be a new feature if it doesn't exist.

Best Answer

Type edit.

mysql> edit

It would open a vi editor. You can make any number of mistakes and undo them. Once done with your multiline query, exit(:wq) out the editor. Type ; to execute your query.

mysql> ;

Related Solutions

Mysql – multi-table thesql query

looks like you need to use "join" syntax. Something like:

SELECT 
u.name,
u.id,
m.id,
t.id,
p.id,
FROM User u
LEFT JOIN Message m ON m.owner_user_id = u.id
LEFT JOIN Thread  t ON t.owner_user_id = u.id
LEFT JOIN Project p ON p.owner_user_id = u.id
WHERE u.id = 12345

Of course, this will have to be modified to include the fields you actually want returned and whatever sorting and ordering you may want... but this should get you started. Good luck!

MySQL still seems to be establishing a connection even when access is denied, causing aborted connects and instability

Sometimes, running FLUSH HOSTS; will clear up the host cache when you can no longer connect. Why ?

According to the MySQL Documentation on FLUSH HOSTS

Empties the host cache. You should flush the host cache if some of your hosts change IP address or if the error message Host 'host_name' is blocked occurs. (See Section B.5.2.6, “Host 'host_name' is blocked”.) When more than max_connect_errors errors occur successively for a given host while connecting to the MySQL server, MySQL assumes that something is wrong and blocks the host from further connection requests. Flushing the host cache enables further connection attempts from the host. The default value of max_connect_errors is 10. To avoid this error message, start the server with max_connect_errors set to a large value.

When you restart mysql, it clears the host cache in a very heavy-handed manner. Therefore, to apply some preventative to this problem, you should be setting max_connect_errors to 1000000000000 (Yes, that's a trillion). Since max_connect_errors is dynamic, a mysql restart is not needed. Just run

mysql> SET GLOBAL max_connect_errors = 1000000000000;

then add the variable to my.cnf like this

[mysqld]
max_connect_errors = 1000000000000

Best Answer

Related Solutions

Mysql – multi-table thesql query

MySQL still seems to be establishing a connection even when access is denied, causing aborted connects and instability

Give it a Try !!!

Related Question