I’m currently working on a project which requires me to use MySQL from the command line. This presents a number of interesting issues for a developer who prefers a GUI to perform management functions.

I’ve started to compile a list of some of the more useful and maybe unknown MySQL commands.

\! clear;

Similar to the Linux clear command it gives you a shiny clean MySQL buffer for you to dirty up again.

SHOW FULL PROCESSLIST;

If you use the standard SHOW PROCESSLIST function, you’ll notice that the result in the info column is truncated which is unhelpful if there is a number of queries with similar syntax running. Adding FULL will return the full SQL query but will probably break the column layout which brings me to my next command:

SHOW FULL PROCESSLIST \G;

SELECT * FROM table \G;

Adding \G to the end of a query returns results in a ‘card view’ layout which is useful if you have columns with large amounts of data.

SHOW OPEN TABLES;

If you’re working on a database which uses transactions and are trying to work out if a table is locked, or in use, this query will tell you.

SHOW TABLE STATUS;

This view will show you details related to the table including engine, how many rows in the table when it was created, updated and rows.

SHOW CREATE TABLE `table_name`;

This will dump the create script for the specified table name, useful if you don’t want to use MySQL dump to get the schema.