PHP developers commonly have to work with many technologies other than PHP, and frequently only know enough about them to avoid tripping over themselves. I admit to being guilty of this, myself. So, in the spirit of cooperation and fun, let's take a look at a commonly-used tool for interacting with MySQL and see if we can make your life a little easier when using it. After all, knowing how to use a tool to its full potential can make you more productive.
It should be noted that most databases have an equivalent to the mysql interactive client—SQLPlus for Oracle or psql for PostgreSQL come to mind—so, if MySQL isn't your flavor, be sure to learn the interactive client for your database.
What is the mysql client and why use it?
The mysql client is an interactive command-line client that works directly with your MySQL server. Many PHP developers prefer to use phpMyAdmin, a web-based client, when working with MySQL. It is easy and rather intuitive to use, doesn't require you to know much SQL to fetch information, and is a whole lot less intimidating than a blank command prompt blinking at you. It is an application that sits between you and the database, providing a layer of abstraction. Sometimes that extra layer gets in the way. Sometimes, you need information that is not readily available from phpMyAdmin. Enter the mysql client.
This command-line client can be found in the bin directory of the MySQL installation and—like many command-line tools—it has plenty of options, from the username and password, to the character set to use between the client and server, to the option of executing a single query before exiting, as well as the standard --help option. I won't get into all of the details now, since you are more than capable of looking them up for yourself in the MySQL manual, but this is a powerful tool.
The mysql client understands a number of commands in addition to standard SQL. I won't be going over all of them, but I want to highlight some of the ones I have found really useful.
\G
How many times have you executed a SELECT query and had the results wrap, because your screen (or window) wasn't wide enough?
mysql> select * from db limit 1;
+------+------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+
| Host | Db | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Create_tmp_table_priv | Lock_tables_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Execute_priv |
+------+------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+
| % | test | | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | Y | Y | Y | Y | Y | N | N |
+------+------+------+-------------+-------------+-------------+-------------+-------------+-----------+------------+-----------------+------------+------------+-----------------------+------------------+------------------+----------------+---------------------+--------------------+--------------+
1 row in set (0.02 sec)This has hapened to me more times than I can count. I hate not being able to align the columns of the output to easily find the information I am looking for. This is when I use \G. Use it at the end of your query instead of the semicolon and your results will be displayed vertically instead of horizontally, making them so much easier to read.
mysql> select * from db limit 1\G
*************************** 1. row ***************************
Host: %
Db: test
User:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: N
Execute_priv: N
1 row in set (0.00 sec)\c
Have you ever made a typo in an SQL command that you could not fix? I admit my typing skills are not the best, and a common mistake I make is to type SELELCT instead of SELECT, which results in a half-written, multi-line query that I know will only give me a syntax error.
I have two solutions: enter a semi-colon and force a quick error, or use \c. \c clears the current command and gives me a new mysql prompt without sending the command to the MySQL server. This allows me to use the up arrow to recall the previous command, which I can edit to correct the error.
mysql> selelct *
-> from\c
mysql> selelct * from\e
What if you don't want to use the mysql client to edit the query? Use \e to open your preferred text editor (in my case, vim) to have all of its power at your fingertips. For really long, complex queries I can't live without it. Save and quit the editor after you are done, but be aware that the changes you make will not be reflected in the mysql client, although they will be used when you execute the query.
mysql> selelct * d=from mysql.db limit 1\e
-> \G
*************************** 1. row ***************************
Host: %
Db: test
User:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: N
Execute_priv: N
1 row in set (0.00 sec)tee and notee
When my server crashes, I need to keep a record of everything I do to restore it. I could copy and paste everything I do, but in the past I've forgotten, instead focusing on fixing the problem. Now, I use tee and notee. tee lets me specify a file to which everything—my commands and their results—is logged, until I stop it with notee. I can just attach the log to my documentation.
mysql> tee /Users/ligaya/Desktop/outfile.txt
Logging to file '/Users/ligaya/Desktop/outfile.txt'
mysql> select * from db limit 1\G
*************************** 1. row ***************************
Host: %
Db: test
User:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: N
Execute_priv: N
1 row in set (0.00 sec)
mysql> notee
Outfile disabled.Server-side help
Do you ever forget the exact syntax for a SQL command? I do it all the time. Does HAVING come before or after ORDER BY? As of version 4.1, you can use the mysql client to perform lookups in the MySQL manual. This saves me from having to keep a browser window open in order to look things up.
So how does it work? Simply type HELP followed by the command you need help with.
mysql> help select;
Name: 'SELECT'
Description:
Syntax:
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr, ...
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name' export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]]
SELECT is used to retrieve rows selected from one or more tables, and can include UNION statements and subqueries. See [HELP UNION], and
http://dev.mysql.com/doc/refman/5.0/en/subqueries.html.
[...]Not sure what command to give? What is the command to see how InnoDB is doing internally? If you think it's SHOW, you can check to be sure:
mysql> help show
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CREATE DATABASE db_name
SHOW CREATE FUNCTION funcname
SHOW CREATE PROCEDURE procname
SHOW CREATE TABLE tbl_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {LOGS | STATUS }
[...]Maybe you just want to see what help is available:
mysql> help me
Nothing found
Please try to run 'help contents' for a list of all accessible topics
mysql> help contents
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
Account Management
Administration
Data Definition
Data Manipulation
Data Types
Functions
Functions and Modifiers for Use with GROUP BY
Geographic Features
Language Structure
Storage Engines
Stored Routines
Table Maintenance
Transactions
TriggersI use the mysql client a lot, and these tricks save me a lot of time. If you have more tips like these, let me know, so we can share them with the world.
